Advanced SQL

1
Chapter 8
Advanced SQL Database Systems Design,
Implementation, and Management, Seventh Edition,
Rob and Coronel
2
Numeric Functions
3
String Functions
4
String Functions (continued)
5
Conversion Functions
6
Conversion Functions (continued)
7
Oracle Sequences
8
Oracle Sequences (continued)
9
Updatable Views
10
Updatable Views (continued)
11
Updatable Views (continued)
12
Updatable Views (continued)
13
Procedural SQL
14
Procedural SQL (continued)

• SHOW ERRORS
• Can help diagnose errors found in PL/SQL blocks
• Yields additional debugging information whenever
  error is generated after creating or executing a
  PL/SQL block

15
Procedural SQL (continued)
16
Procedural SQL (continued)
17
Triggers
18
Triggers (continued)
19
Triggers (continued)
20
Triggers (continued)
21
Triggers (continued)
22
Triggers (continued)
23
Triggers (continued)
24
Triggers (continued)
25
Triggers (continued)
26
Triggers (continued)
27
Triggers (continued)
28
Stored Procedures

• Advantages
• Substantially reduce network traffic and increase
  performance
• No transmission of individual SQL statements over
  network
• Help reduce code duplication by means of code
  isolation and code sharing
• Minimize chance of errors and cost of application
  development and maintenance

29
Stored Procedures (continued)
30
Stored Procedures (continued)
31
Stored Procedures (continued)
32
Stored Procedures (continued)
33
Stored Procedures (continued)
34
Stored Procedures (continued)
35
Stored Procedures (continued)
36
PL/SQL Processing with Cursors
37
PL/SQL Processing with Cursors (continued)
38
PL/SQL Processing with Cursors (continued)
39
PL/SQL Stored Functions

• Syntax
• CREATE FUNCTION function_name (argument IN
  data-type, ) RETURN data- type
  ISBEGIN PL/SQL statements RETURN
  (value or expression)END

40
Embedded SQL

• Key differences between SQL and procedural
  languages are
• Run-time mismatch
• SQL executed one instruction at a time
• Host language typically runs at client side in
  its own memory space
• Processing mismatch
• Host language processes one data element at a
  time
• Data type mismatch
• Data types may not match

41
Embedded SQL (continued)

• Embedded SQL framework defines
• Standard syntax to identify embedded SQL code
  within host language
• Standard syntax to identify host variables
• Communication area used to exchange status and
  error information between SQL and host language

42
Embedded SQL (continued)
43
Embedded SQL (continued)

• Static SQL
• Embedded SQL in which programmer used predefined
  SQL statements and parameters
• End users of programs are limited to actions that
  were specified in application programs
• SQL statements will not change while application
  is running

44
Embedded SQL (continued)

• Dynamic SQL
• SQL statement is not known in advance, but
  instead is generated at run time
• Program can generate SQL statements at run time
  that are required to respond to ad hoc queries
• Attribute list and condition are not known until
  end user specifies them
• Tends to be much slower than static SQL
• Requires more computer resources

45
Summary

• SQL provides relational set operators to combine
  output of two queries to generate new relation
• Operations that join tables can be classified as
  inner joins and outer joins
• Natural join returns all rows with matching
  values in the matching columns and eliminates
  duplicate columns

46
Summary (continued)

• Joins may use keywords, such as USING and ON
• Subqueries and correlated queries are used when
  it is necessary to process data based on other
  processed data
• Most subqueries are executed in serial fashion

47
Summary (continued)

• SQL functions are used to extract or transform
  data
• Oracle sequences may be used to generate values
  to be assigned to a record
• PL/SQL can be used to create triggers, stored
  procedures, and PL/SQL functions
• A stored procedure is a named collection of SQL
  statements

48
Summary (continued)

• When SQL statements are designed to return more
  than one value inside the PL/SQL code, cursor is
  needed
• Embedded SQL refers to use of SQL statements
  within an application programming language