Mastering Oracle SQL
Sanjay Mishra and Alan Beaulieu
Beijing•Cambridge•Farnham•Köln•Paris•Sebastopol•Taipei•Tokyo
,TITLE.12934 Page i Wednesday, March 27, 2002 2:34 PM
,DEDICATION.12802 Page iv Wednesday, March 27, 2002 2:34 PM
vi | Table of Contents
5. Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .68
What Is a Subquery? 68
Noncorrelated Subqueries 69
Correlated Subqueries 75
Inline Views 77
Subquery Case Study: The Top N Performers 89
6. Handling Temporal Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95
Internal DATE Storage Format 95
Getting Dates In and Out of a Database 96
Date Manipulation 111
Oracle9i New DATETIME Features 124
INTERVAL Literals 132
7. Set Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .144
Set Operators 145
Using Set Operations to Compare Two Tables 149
Using NULLs in Compound Queries 151
Rules and Restrictions on Set Operations 153
8. Hierarchical Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .157
Representing Hierarchical Information 157
Simple Hierarchy Operations 160
Oracle SQL Extensions 163
Complex Hierarchy Operations 167
Restrictions on Hierarchical Queries 174
9. DECODE and CASE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .175
DECODE, NVL, and NVL2 175
The Case for CASE 179
DECODE and CASE Examples 181
10. Partitions, Objects, and Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .192
Table Partitioning 192
Objects and Collections 202
11. PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .213
What Is PL/SQL? 213
Procedures, Functions, and Packages 214
Calling Stored Functions from Queries 216
,mast_ora_sqlTOC.fm.10634 Page vi Wednesday, March 27, 2002 2:23 PM
Table of Contents | vii
Restrictions on Calling PL/SQL from SQL 220
Stored Functions in DML Statements 224
The SQL Inside Your PL/SQL 226
12. Advanced Group Operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .228
ROLLUP 228
CUBE 238
The GROUPING Function 244
GROUPING SETS 249
Oracle9i Grouping Features 250
The GROUPING_ID and GROUP_ID Functions 260
13. Advanced Analytic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .267
Analytic SQL Overview 267
Ranking Functions 272
Windowing Functions 286
Reporting Functions 291
Summary 296
14. SQL Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .297
Know When to Use Specific Constructs 297
Avoid Unnecessary Parsing 302
Consider Literal SQL for Decision Support Systems 307
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
![](qr/37161/1.png)
Continue reading on your phone by scaning this QR Code
Tip: The current page has been bookmarked automatically. If you wish to continue reading later, just open the
Dertz Homepage, and click on the 'continue reading' link at the bottom of the page.