Transcript Document

7
Chapter 7
Advanced SQL
Database Systems:
Design, Implementation, and Management,
Sixth Edition, Rob and Coronel
1
7
In this chapter, you will learn:
• About the relational set operators UNION,
UNION ALL, INTERSECT, and MINUS
• How to use the advanced SQL JOIN operator
syntax
• About the different types of subqueries and
correlated queries
• How to use SQL functions to manipulate dates,
strings, and other data
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
2
7
In this chapter, you will learn: (continued)
• How to create and use updatable views
• How to create and use triggers and stored
procedures
• How to create embedded SQL
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
3
7
UNION Query Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
4
7
UNION ALL Query Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
5
7
INTERSECT Query Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
6
7
MINUS Query Results
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
7
7
INTERSECT Alternative
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
8
7
MINUS Alternative
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
9
7
SQL Join Expression Styles
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
10
7
NATURAL JOIN Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
11
7
JOIN USING Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
12
7
JOIN ON Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
13
7
Outer Joins
• Returns not only rows matching join condition
but also rows with unmatched values
• Three types:
– Left
– Right
– Full
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
14
7
LEFT JOIN Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
15
7
RIGHT JOIN Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
16
7
FULL JOIN Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
17
7
7.3 SELECT Subquery Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
18
7
WHERE Subquery Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
19
7
IN Subquery Example
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
20
7
HAVING Subquery Example
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
21
7
Multirow Subquery Operator Example
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
22
7
FROM Subquery Example
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
23
7
Inline Subquery Example
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
24
7
Correlated Subquery Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
25
7
EXISTS Correlated Subquery Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
26
7
7.4 Selected MS Access/SQL Server
Date/Time Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
27
7
Selected Oracle Date/Time Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
28
7
Selected Oracle Date/Time Functions
(continued)
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
29
7
Selected Oracle Numeric Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
30
7
Selected Oracle String Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
31
7
Selected Oracle Conversion Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
32
7
Selected Oracle Conversion Functions
(continued)
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
33
7
7.5 Oracle Sequence (Skip)
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
34
7
Oracle Sequence Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
35
7
7.6 The PRODMASTER
and PRODSALES Tables
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
36
7
The Oracle UPDATE Error Message
MS Access
OK
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
37
7
The Oracle UPDATABLE VIEW restriction
• GROUP BY expressions or aggregate
functions cannot be used
• SET operators, such as UNION, INTERSECT,
MINUS, cannot be used (7.1)
• JOIN or group operators cannot be used (7.3)
If the primary key columns of the base table you want to
update still have unique values in the view, the base table
is updatable.
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
38
7
Creating an Updatable View in Oracle
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
39
7
PRODMASTER Table Update,
Using an Updatable View
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
40
7
7.7 Anonymous PL/SQL Block Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
41
7
SHOW ERRORS
• Can help diagnose errors found in PL/SQL
blocks
• Yields additional debugging information
whenever an error is generated after an
PL/SQL block is created or executed
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
42
7
Anonymous PL/SQL Block
with Variables and Loops
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
43
7
PL/SQL Basic Data Types
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
44
7
The PRODUCT Table
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
45
7
Creating the
TRG_PRODUCT_REORDER Trigger
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
46
7
Verifying the TRG_PRODUCT_REORDER
Trigger Execution
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
47
7
The P_REORDER Value Mismatch After
Update of the P_MIN Attribute
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
48
7
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
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
49
7
Creating the PRC_PROD_DISCOUNT
Stored Procedure
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
50
7
Results of the PRC_PROD_DISCOUNT
Stored Procedure
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
51
7
The PRC_CUS_ADD Stored Procedure
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
52
7
The PRC_INV_ADD and
PRC_LINE_ADD Stored Procedures
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
53
7
Testing the PRC_INV_ADD and
PRC_LINE_ADD Procedures
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
54
7
Cursor Processing Commands
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
55
7
Cursor Attributes
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
56
7
7.8 Embedded SQL
• Key differences between SQL and procedural
languages
• Run-time mismatch: SQL is nonprocedural,
interpreted, processed at the server side. The host
language is binary-executable program at the
client side.
• Processing mismatch: SQL is executed one
instruction at a time. Conventional programming
languages process one data element at a time.
• Data type mismatch
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
57
7
Embedded SQL Framework
• A standard syntax to identify embedded SQL
code within host language
• A standard syntax to identify host variables
• A communication area used to exchange
status and error information between SQL
and the host language: SQLCODE and
SQLSTATE
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
58
7
Embedded SQL Programming Steps
1.
2.
3.
4.
5.
Write embedded SQL code within the host language
instructions (先不要管 PL/SQL 相關部分)
EXEC SQL
SQL statement
END-EXEC
A preprocessor is used to transform the embedded SQL into
specialized procedure calls that are DBMS-and-languagespecific.
Compile the program using the host language compiler
The object code is linked to the respective library modules and
generates the executable program
The executable is run and the embedded SQL statement
retrieves data from the database
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
59
Embedded SQL Programming Steps
7
EXEC SQL
DELETE FROM EMPLOYEE WHERE EMP_NUM=109;
END-EXEC
The preceding code is good for the first run. Why?
This code would be more useful if a variable to indicate the
employee number to be deleted.
EXEC SQL
INCLUDE SQLCA;
END-EXEC
…
EXEC SQL
DELETE FROM EMPLOYEE WHERE EMP_NUM=:W_EMP_NUM;
END-EXEC
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
60
7
SQL Status and Error Reporting Variables
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
61
7
SQLCODE + COBOL
EXEC SQL
SELECT EMP_FNAME, EMP_LNAME INTO :W_EMP_FNAME,
:W_EMP_LNAME FROM EMPLOYEE
WHERE EMP_NUM=:W_EMP_NUM;
END-EXEC
IF SQLCODE = 0 THEN
PERFORM DATA_ROUTINE
ELSE
PERFORM ERROR_ROUTINE
END-IF
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
62
CURSOR
7
1. EXEC SQL
DECLARE PROD_CURSOR FOR
SELECT P_CODE, P_DESC FROM PRODUCT
WHERE P_ON_HAND >
(SELECT AVG(P_ON_HAND) FROM PRODUCT);
2. EXEC SQL
OPEN PROD_CURSOR;
END-EXEC
3. EXEC SQL
FETCH PRODUCT_CURSOR INTO :W_P_CODE, :W_P_DESC;
END-EXEC
IF SQLCODE = 0 THEN ….
4. EXEC SQL
CLOSE PROD_CURSOR;
END-EXEC
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
63
7
Static SQL
• Embedded SQL in which the 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
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
64
7
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 the condition are not known
until the end user specifies them
• Tends to be much slower than static SQL
• Requires more computer resources
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
65
7
Dynamic SQL Example
EXEC SQL
SELECT :W_ATTRIBUTE_LIST
FROM :W_TABLE
WHERE :W_CONDITION;
END-EXEC
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
66
7
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
• Subqueries and correlated queries are used
when it is necessary to process data based on
other processed data
• SQL functions are used to extract or transform
data
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
67
7
Summary (continued)
• Oracle sequences may be used to generate
values to be assigned to a record
• PL/SQL may be used to create triggers,
stored procedures, and PL/SQL functions
• If SQL statements are designed to return
more than one value inside the PL/SQL code,
a cursor is needed
• Embedded SQL refers to the use of SQL
statements within an application
programming language
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
68