oracle

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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .