Oracle Database 11g SQL P1

Chia sẻ: Vong Phat | Ngày: | Loại File: PDF | Số trang:30

Thêm vào BST

Báo xấu

144
lượt xem 14
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Today’s database management systems are accessed using a standard language known as Structured Query Language, or SQL. Among other things, SQL allows you to retrieve, add, update, and delete information in a database. In this book, you’ll learn how to master SQL, and you’ll find a wealth of practical examples. You can also get all the scripts and programs featured in this book online (see the last section, “Retrieving the Examples,” for details).

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle Database 11g SQL P1

Oracle Database 11g SQL
This page intentionally left blank
Oracle Database 11g SQL Jason Price New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto
Copyright © 2008 by The McGraw-Hill Companies, Inc. All rights reserved. Manufactured in the United States of America. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher. 0-07-159613-5 The material in this eBook also appears in the print version of this title: 0-07-149850-8. All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps. McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training programs. For more information, please contact George Hoare, Special Sales, at george_hoare@mcgraw-hill.com or (212) 904-4069. TERMS OF USE This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms. THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise.
Professional Want to learn more? We hope you enjoy this McGraw-Hill eBook! If you’d like more information about this book, its author, or related books and websites, please click here.
This book is dedicated to my family. Even though you’re far away, you are still in my heart.
About the Author Jason Price is a freelance consultant and former product manager of Oracle Corporation. He has contributed to many of Oracle’s products, including the database, the application server, and several of the CRM applications. Jason is an Oracle Certified Database Administrator and Application Developer, and has more than 15 years of experience in the software industry. Jason has written many books on Oracle, Java, and .NET. Jason holds a Bachelor of Science degree (with honors) in physics from the University of Bristol, England. About the Technical Editor Scott Mikolaitis is an applications architect at Oracle Corporation and has worked at Oracle for over ten years. He performs prototyping and standards development for the SOA technology in Oracle Fusion. Scott also enjoys working with web services in Java as well as Jabber for human and system interaction patterns. He spends his spare time on DIY home improvement and gas-fueled RC cars.
Contents at a Glance 1 Introduction ..................................................... 1 2 Retrieving Information from Database Tables ............................ 27 3 Using SQL*Plus ................................................... 63 4 Using Simple Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 5 Storing and Processing Dates and Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 6 Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 7 Advanced Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 8 Changing Table Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251 9 Users, Privileges, and Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275 10 Creating Tables, Sequences, Indexes, and Views . . . . . . . . . . . . . . . . . . . . . . . . . . 299 11 Introducing PL/SQL Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339 12 Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379 13 Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427 14 Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475 15 Running SQL Using Java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531 16 SQL Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 579 17 XML and the Oracle Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603 A Oracle Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639 vii
This page intentionally left blank
Contents Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 What Is a Relational Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Introducing the Structured Query Language (SQL) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Using SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Starting SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Starting SQL*Plus from the Command Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Performing a SELECT Statement Using SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . 6 SQL Developer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Creating the Store Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Running the SQL*Plus Script to Create the Store Schema . . . . . . . . . . . . . . . . . 10 Data Definition Language (DDL) Statements Used to Create the Store Schema . 11 Adding, Modifying, and Removing Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Adding a Row to a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Modifying an Existing Row in a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Removing a Row from a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 The BINARY_FLOAT and BINARY_DOUBLE Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Benefits of BINARY_FLOAT and BINARY_DOUBLE . . . . . . . . . . . . . . . . . . . . . 23 Using BINARY_FLOAT and BINARY_DOUBLE in a Table . . . . . . . . . . . . . . . . . 24 Special Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Quitting SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Introducing Oracle PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 2 Retrieving Information from Database Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Performing Single Table SELECT Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Retrieving All Columns from a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Specifying Rows to Retrieve Using the WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . 29 Row Identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Row Numbers ....................................................... 30 ix
x Oracle Database 11g SQL Performing Arithmetic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Performing Date Arithmetic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Using Columns in Arithmetic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Using Column Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Combining Column Output Using Concatenation ............................ 35 Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Displaying Distinct Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Comparing Values .................................................... 37 Using the SQL Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Using the LIKE Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Using the IN Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Using the BETWEEN Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Using the Logical Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Operator Precedence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Sorting Rows Using the ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Performing SELECT Statements That Use Two Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Using Table Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Cartesian Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Performing SELECT Statements That Use More than Two Tables . . . . . . . . . . . . . . . . . . . 49 Join Conditions and Join Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Non-equijoins ................................................. 50 Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Self Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Performing Joins Using the SQL/92 Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Performing Inner Joins on Two Tables Using SQL/92 . . . . . . . . . . . . . . . . . . . . . 56 Simplifying Joins with the USING Keyword ........................... 57 Performing Inner Joins on More than Two Tables Using SQL/92 . . . . . . . . . . . . 58 Performing Inner Joins on Multiple Columns Using SQL/92 . . . . . . . . . . . . . . . 58 Performing Outer Joins Using SQL/92 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Performing Self Joins Using SQL/92 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Performing Cross Joins Using SQL/92 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 3 Using SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Viewing the Structure of a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Editing SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Saving, Retrieving, and Running Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Formatting Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Setting the Page Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Setting the Line Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Clearing Column Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Using Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Temporary Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Defined Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Creating Simple Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Using Temporary Variables in a Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Using Defined Variables in a Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Passing a Value to a Variable in a Script .............................. 81 Adding a Header and Footer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Computing Subtotals ............................................ 83
Contents xi Getting Help from SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Automatically Generating SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Disconnecting from the Database and Exiting SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . 86 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 4 Using Simple Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 Using Single-Row Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Character Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Numeric Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Regular Expression Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Using Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 AVG() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 COUNT() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 MAX() and MIN() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 STDDEV() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 SUM() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 VARIANCE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 Grouping Rows ...................................................... 120 Using the GROUP BY Clause to Group Rows . . . . . . . . . . . . . . . . . . . . . . . . . . 121 Incorrect Usage of Aggregate Function Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 Using the HAVING Clause to Filter Groups of Rows . . . . . . . . . . . . . . . . . . . . . 125 Using the WHERE and GROUP BY Clauses Together . . . . . . . . . . . . . . . . . . . . 126 Using the WHERE, GROUP BY, and HAVING Clauses Together . . . . . . . . . . . . 126 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 5 Storing and Processing Dates and Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Simple Examples of Storing and Retrieving Dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 Converting Datetimes Using TO_CHAR() and TO_DATE() . . . . . . . . . . . . . . . . . . . . . . . 131 Using TO_CHAR() to Convert a Datetime to a String . . . . . . . . . . . . . . . . . . . . . 132 Using TO_DATE() to Convert a String to a Datetime . . . . . . . . . . . . . . . . . . . . . 136 Setting the Default Date Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 How Oracle Interprets Two-Digit Years . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Using the YY Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Using the RR Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Using Datetime Functions .............................................. 142 ADD_MONTHS() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 LAST_DAY() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 MONTHS_BETWEEN() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 NEXT_DAY() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 ROUND() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 SYSDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 TRUNC() ..................................................... 146 Using Time Zones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 Time Zone Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 The Database Time Zone and Session Time Zone . . . . . . . . . . . . . . . . . . . . . . . 147 Obtaining Time Zone Offsets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Obtaining Time Zone Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Converting a Datetime from One Time Zone to Another . . . . . . . . . . . . . . . . . . 150
xii Oracle Database 11g SQL Using Timestamps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 Using the Timestamp Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 Timestamp Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 Using Time Intervals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 Using the INTERVAL YEAR TO MONTH Type . . . . . . . . . . . . . . . . . . . . . . . . . . 160 Using the INTERVAL DAY TO SECOND Type . . . . . . . . . . . . . . . . . . . . . . . . . . 162 Time Interval Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 6 Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Types of Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 Writing Single-Row Subqueries .......................................... 168 Subqueries in a WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 Using Other Single-Row Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Subqueries in a HAVING Clause ................................... 170 Subqueries in a FROM Clause (Inline Views) .......................... 171 Errors You Might Encounter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 Writing Multiple-Row Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Using IN with a Multiple-Row Subquery ............................. 173 Using ANY with a Multiple-Row Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Using ALL with a Multiple-Row Subquery ............................ 175 Writing Multiple-Column Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Writing Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 A Correlated Subquery Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176 Using EXISTS and NOT EXISTS with a Correlated Subquery . . . . . . . . . . . . . . . 176 Writing Nested Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179 Writing UPDATE and DELETE Statements Containing Subqueries . . . . . . . . . . . . . . . . . 180 Writing an UPDATE Statement Containing a Subquery . . . . . . . . . . . . . . . . . . . 180 Writing a DELETE Statement Containing a Subquery . . . . . . . . . . . . . . . . . . . . . 181 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 7 Advanced Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 Using the Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 The Example Tables ............................................. 185 Using the UNION ALL Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186 Using the UNION Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Using the INTERSECT Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 Using the MINUS Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 Combining Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 Using the TRANSLATE() Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190 Using the DECODE() Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 Using the CASE Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 Using Simple CASE Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 Using Searched CASE Expressions .................................. 194 Hierarchical Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 The Example Data .............................................. 196 Using the CONNECT BY and START WITH Clauses . . . . . . . . . . . . . . . . . . . . . 198 Using the LEVEL Pseudo Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Formatting the Results from a Hierarchical Query . . . . . . . . . . . . . . . . . . . . . . . 199 Starting at a Node Other than the Root . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 Using a Subquery in a START WITH Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Contents xiii Traversing Upward Through the Tree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Eliminating Nodes and Branches from a Hierarchical Query .............. 201 Including Other Conditions in a Hierarchical Query . . . . . . . . . . . . . . . . . . . . . 202 Using the Extended GROUP BY Clauses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203 The Example Tables ............................................. 203 Using the ROLLUP Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 Using the CUBE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207 Using the GROUPING() Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 Using the GROUPING SETS Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211 Using the GROUPING_ID() Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212 Using a Column Multiple Times in a GROUP BY Clause . . . . . . . . . . . . . . . . . 214 Using the GROUP_ID() Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215 Using the Analytic Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 The Example Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 Using the Ranking Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Using the Inverse Percentile Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224 Using the Window Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 Using the Reporting Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230 Using the LAG() and LEAD() Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232 Using the FIRST and LAST Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233 Using the Linear Regression Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233 Using the Hypothetical Rank and Distribution Functions . . . . . . . . . . . . . . . . . 235 Using the MODEL Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236 An Example of the MODEL Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236 Using Positional and Symbolic Notation to Access Cells . . . . . . . . . . . . . . . . . . 237 Accessing a Range of Cells Using BETWEEN and AND . . . . . . . . . . . . . . . . . . . 238 Accessing All Cells Using ANY and IS ANY . . . . . . . . . . . . . . . . . . . . . . . . . . . 238 Getting the Current Value of a Dimension Using CURRENTV() . . . . . . . . . . . . . 239 Accessing Cells Using a FOR Loop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240 Handling Null and Missing Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241 Updating Existing Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 Using the PIVOT and UNPIVOT Clauses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244 A Simple Example of the PIVOT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244 Pivoting on Multiple Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 Using Multiple Aggregate Functions in a Pivot . . . . . . . . . . . . . . . . . . . . . . . . . 247 Using the UNPIVOT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249 8 Changing Table Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251 Adding Rows Using the INSERT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252 Omitting the Column List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253 Specifying a Null Value for a Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253 Including Single and Double Quotes in a Column Value . . . . . . . . . . . . . . . . . 254 Copying Rows from One Table to Another . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254 Modifying Rows Using the UPDATE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254 The RETURNING Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255 Removing Rows Using the DELETE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256 Database Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256 Enforcement of Primary Key Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257 Enforcement of Foreign Key Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
xiv Oracle Database 11g SQL Using Default Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258 Merging Rows Using MERGE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 Database Transactions ................................................. 262 Committing and Rolling Back a Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . 262 Starting and Ending a Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263 Savepoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264 ACID Transaction Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265 Concurrent Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266 Transaction Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266 Transaction Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267 A SERIALIZABLE Transaction Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269 Query Flashbacks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270 Granting the Privilege for Using Flashbacks . . . . . . . . . . . . . . . . . . . . . . . . . . . 270 Time Query Flashbacks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270 System Change Number Query Flashbacks . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273 9 Users, Privileges, and Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275 Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276 Creating a User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276 Changing a User’s Password . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277 Deleting a User ................................................ 278 System Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278 Granting System Privileges to a User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278 Checking System Privileges Granted to a User . . . . . . . . . . . . . . . . . . . . . . . . . 280 Making Use of System Privileges ................................... 281 Revoking System Privileges from a User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 Granting Object Privileges to a User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282 Checking Object Privileges Made . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283 Checking Object Privileges Received . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284 Making Use of Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286 Synonyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287 Public Synonyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287 Revoking Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288 Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289 Creating Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289 Granting Privileges to Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 Granting Roles to a User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 Checking Roles Granted to a User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 Checking System Privileges Granted to a Role . . . . . . . . . . . . . . . . . . . . . . . . . 291 Checking Object Privileges Granted to a Role ......................... 292 Making Use of Privileges Granted to a Role . . . . . . . . . . . . . . . . . . . . . . . . . . . 293 Default Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294 Revoking a Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294 Revoking Privileges from a Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294 Dropping a Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 Privileges Required to Perform Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 Auditing Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Contents xv Audit Trail Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297 10 Creating Tables, Sequences, Indexes, and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300 Creating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300 Getting Information on Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302 Getting Information on Columns in Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303 Altering a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303 Renaming a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 Adding a Comment to a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 Truncating a Table .............................................. 314 Dropping a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314 Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314 Creating a Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314 Retrieving Information on Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316 Using a Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317 Populating a Primary Key Using a Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . . 319 Modifying a Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320 Dropping a Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320 Creating a B-tree Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321 Creating a Function-Based Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 Retrieving Information on Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323 Retrieving Information on the Indexes on a Column . . . . . . . . . . . . . . . . . . . . . 323 Modifying an Index ............................................. 324 Dropping an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 Creating a Bitmap Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325 Creating and Using a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326 Modifying a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333 Dropping a View ............................................... 334 Flashback Data Archives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337 11 Introducing PL/SQL Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339 Block Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340 Variables and Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342 Conditional Logic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342 Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343 Simple Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343 WHILE Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344 FOR Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344 Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345 Step 1: Declare the Variables to Store the Column Values . . . . . . . . . . . . . . . . . 346 Step 2: Declare the Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346 Step 3: Open the Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346 Step 4: Fetch the Rows from the Cursor .............................. 347 Step 5: Close the Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347 Complete Example: product_cursor.sql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
xvi Oracle Database 11g SQL Cursors and FOR Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349 OPEN-FOR Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350 Unconstrained Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352 Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353 ZERO_DIVIDE Exception . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355 DUP_VAL_ON_INDEX Exception . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 INVALID_NUMBER Exception . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 OTHERS Exception . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357 Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358 Creating a Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358 Calling a Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360 Getting Information on Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361 Dropping a Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362 Viewing Errors in a Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362 Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363 Creating a Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363 Calling a Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364 Getting Information on Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365 Dropping a Function ............................................ 365 Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365 Creating a Package Specification ................................... 365 Creating a Package Body ......................................... 366 Calling Functions and Procedures in a Package ........................ 367 Getting Information on Functions and Procedures in a Package . . . . . . . . . . . . 368 Dropping a Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369 When a Trigger Fires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369 Set Up for the Example Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369 Creating a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369 Firing a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371 Getting Information on Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372 Disabling and Enabling a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374 Dropping a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374 New Oracle Database 11g PL/SQL Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374 SIMPLE_INTEGER Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 Sequences in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 PL/SQL Native Machine Code Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377 12 Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379 Introducing Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380 Creating Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 Using DESCRIBE to Get Information on Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . 382 Using Object Types in Database Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383 Column Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383 Object Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386 Object Identifiers and Object References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390 Comparing Object Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392 Using Objects in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394 The get_products() Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395 The display_product() Procedure ................................... 396
Contents xvii The insert_product() Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397 The update_product_price() Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398 The get_product() Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398 The update_product() Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399 The get_product_ref() Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400 The delete_product() Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400 The product_lifecycle() Procedure .................................. 401 The product_lifecycle2() Procedure ................................. 402 Type Inheritance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 Using a Subtype Object in Place of a Supertype Object . . . . . . . . . . . . . . . . . . . . . . . . 405 SQL Examples ................................................. 405 PL/SQL Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406 NOT SUBSTITUTABLE Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407 Other Useful Object Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408 IS OF() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408 TREAT() ...................................................... 412 SYS_TYPEID() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416 NOT INSTANTIABLE Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416 User-Defined Constructors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418 Overriding Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422 Generalized Invocation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425 13 Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427 Introducing Collections ................................................ 428 Creating Collection Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429 Creating a Varray Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429 Creating a Nested Table Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429 Using a Collection Type to Define a Column in a Table . . . . . . . . . . . . . . . . . . . . . . . . 430 Using a Varray Type to Define a Column in a Table . . . . . . . . . . . . . . . . . . . . . . 430 Using a Nested Table Type to Define a Column in a Table . . . . . . . . . . . . . . . . . 430 Getting Information on Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431 Getting Information on a Varray . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431 Getting Information on a Nested Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432 Populating a Collection with Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434 Populating a Varray with Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434 Populating a Nested Table with Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434 Retrieving Elements from Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 Retrieving Elements from a Varray . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 Retrieving Elements from a Nested Table ............................. 436 Using TABLE() to Treat a Collection as a Series of Rows ........................ 436 Using TABLE() with a Varray . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437 Using TABLE() with a Nested Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438 Modifying Elements of Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438 Modifying Elements of a Varray . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438 Modifying Elements of a Nested Table ............................... 439 Using a Map Method to Compare the Contents of Nested Tables . . . . . . . . . . . . . . . . . 440 Using CAST() to Convert Collections from One Type to Another . . . . . . . . . . . . . . . . . . 443 Using CAST() to Convert a Varray to a Nested Table . . . . . . . . . . . . . . . . . . . . . 443 Using CAST() to Convert a Nested Table to a Varray . . . . . . . . . . . . . . . . . . . . . 443
xviii Oracle Database 11g SQL Using Collections in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444 Manipulating a Varray . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444 Manipulating a Nested Table ...................................... 446 PL/SQL Collection Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448 Multilevel Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458 Oracle Database 10g Enhancements to Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . 461 Associative Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462 Changing the Size of an Element Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463 Increasing the Number of Elements in a Varray . . . . . . . . . . . . . . . . . . . . . . . . . 463 Using Varrays in Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463 Using a Different Tablespace for a Nested Table’s Storage Table . . . . . . . . . . . . 463 ANSI Support for Nested Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473 14 Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475 Introducing Large Objects (LOBs) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476 The Example Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476 Large Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477 Creating Tables Containing Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478 Using Large Objects in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478 Using CLOBs and BLOBs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478 Using BFILEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481 Using Large Objects in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 482 APPEND() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485 CLOSE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485 COMPARE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 486 COPY() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 CREATETEMPORARY() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488 ERASE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488 FILECLOSE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489 FILECLOSEALL() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489 FILEEXISTS() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490 FILEGETNAME() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490 FILEISOPEN() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490 FILEOPEN() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491 FREETEMPORARY() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492 GETCHUNKSIZE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492 GET_STORAGE_LIMIT() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492 GETLENGTH() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493 INSTR() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493 ISOPEN() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494 ISTEMPORARY() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495 LOADFROMFILE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496 LOADBLOBFROMFILE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497 LOADCLOBFROMFILE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497 OPEN() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 498 READ() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 499 SUBSTR() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500 TRIM() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501