Oracle Database 10G Pl/SQL Programming

by Urman, Scott; Hardman, Ron; McLaughlin, Michael

• ISBN13: 9780072230666
• ISBN10: 0072230665
• eBook ISBN(s): 9780071502283
• Additional ISBN(s): 9780071494458
• Edition: 1st
• Format: Paperback
• Copyright: 2004-10-07
• Publisher: McGraw-Hill Education

Summary

Create dynamic client/server applications using PL/SQL and the comprehensive information contained in this authoritative volume. Expert Oracle insiders cover the features of PL/SQL, explain scripting semantics and syntax, and fully detail the PL/SQL functionality of Oracle Database 10g. You'll learn how to write powerful PL/SQL programs, interact with Oracle databases, perform complex calculations, and handle error conditions.

Author Biography

Scott Urman (Redwood Shores, CA) is a Senior Member of the Technical Staff in the Diagnostics and Defect Resolution (DDR) team in Oracle's Server Technologies Division. He currently focuses on JDBC, and has worked with both PL/SQL and OCI. Prior to joining DDR, he was a Senior Analyst in the Languages division of Oracle Support Services, focusing on all of Oracle's language tools. He is also the best-selling author of Oracle8 PL/SQL Programming and Oracle PL/SQL Programming.

Table of Contents

PART I Introduction
Introduction to PL/SQL	3	(20)
Introduction to Programming Languages	4	(2)
Note to Beginning Programmers	5	(1)
PL/What?	6	(9)
Structured Query Language (SQL)	6	(1)
Relational Database Overview	7	(2)
PL/SQL vs. SQL	9	(2)
PL/SQL vs. Java	11	(1)
PL/SQL History and Features	12	(3)
Language Fundamentals	15	(2)
Anonymous Blocks	15	(1)
Procedures	16	(1)
Functions	16	(1)
Packages	16	(1)
Object Types	16	(1)
PL/SQL Statement Processing	17	(1)
Interpreted	17	(1)
Native Compilation	17	(1)
Getting the Most from This Book	18	(3)
Audience	18	(1)
Objective	18	(1)
Scope	18	(1)
Assumptions	19	(1)
Conventions	20	(1)
Examples	20	(1)
Summary	21	(2)
Using SQL*Plus and JDeveloper	23	(22)
SQL*Plus	24	(10)
Connecting to the Instance	25	(1)
Testing the Connection	26	(2)
Using SQL*Plus	28	(3)
Changing SQL*Plus Session Settings	31	(1)
Running a Script from a File	31	(2)
Output to the Screen Using SQL*Plus and PL/SQL	33	(1)
JDeveloper	34	(9)
Installing JDeveloper	34	(2)
Working with PL/SQL in JDeveloper	36	(7)
Summary	43	(2)
PL/SQL Basics	45	(72)
The PL/SQL Block	46	(18)
The Basic Structure	46	(3)
Anonymous Blocks	49	(3)
Named Blocks	52	(10)
Nested Blocks	62	(1)
Triggers	63	(1)
Object Types	64	(1)
Language Rules and Conventions	64	(13)
Lexical Units	64	(13)
PL/SQL Data Types	77	(12)
Scalar	77	(1)
Character/String	77	(4)
NUMBER Data Type	81	(2)
Boolean	83	(1)
Date/Time	84	(3)
Composite	87	(1)
Reference	87	(2)
LOB	89	(1)
Using Variables	89	(8)
%Type	90	(1)
%Rowtype	90	(1)
Variable Scope	91	(2)
Bind Variables	93	(4)
Hiding Code	97	(3)
Expressions	100	(2)
Assignment Operator	101	(1)
Concatenation Operator	102	(1)
Controlling Program Flow	102	(13)
Conditional Evaluation	103	(7)
Circular Execution	110	(3)
Sequential Navigation using GOTO	113	(2)
Summary	115	(2)
Using SQL with PL/SQL	117	(64)
Transaction Processing	118	(12)
Transactions and Locking	119	(6)
Autonomous Transactions	125	(3)
Set Transaction	128	(2)
Retrieving Data	130	(12)
SQL SELECT Statement	130	(4)
Pattern Matching	134	(4)
Information Retrieval	138	(4)
Cursors	142	(16)
How Cursors Work	143	(3)
Explicit Cursors	146	(7)
Implicit Cursors	153	(1)
Cursor Variables	154	(2)
Cursor Subqueries	156	(1)
Open Cursors	157	(1)
DML and DDL	158	(7)
Pre-Compilation	159	(1)
Manipulating Data with DML	160	(3)
Delete	163	(1)
Introduction to Dynamic SQL	164	(1)
Using ROWID and ROWNUM	165	(7)
ROWID	165	(4)
ROWNUM	169	(3)
Built-in SQL Functions	172	(7)
Character Functions	172	(1)
Numeric Functions	172	(2)
Date Functions	174	(1)
Conversion Functions	175	(1)
Error Functions	176	(2)
Other Functions	178	(1)
Summary	179	(2)
Records	181	(32)
Introducing Records	182	(30)
What Is a Record?	182	(1)
Working with Records	182	(1)
Defining Record Types	183	(15)
Defining and Using Record Types as Formal Parameters	198	(4)
Defining and Using Object Types as Parameters	202	(3)
Returning Record Types from Functions	205	(1)
Defining and Using Record Types as Return Values	205	(2)
Defining and Using Object Types as Return Values	207	(3)
Verifying Work with Record Types	210	(2)
Summary	212	(1)
Collections	213	(90)
Introducing Collections	214	(1)
What Is a Collection?	214	(1)
Working with Collections	214	(68)
Working with Varrays	217	(20)
Working with Nested Tables	237	(24)
Working with Associative Arrays	261	(21)
Oracle 10g Collection API	282	(20)
COUNT Method	287	(1)
DELETE Method	288	(2)
EXISTS Method	290	(3)
EXTEND Method	293	(2)
FIRST Method	295	(2)
LAST Method	297	(1)
LIMIT Method	297	(2)
NEXT Method	299	(1)
PRIOR Method	299	(1)
TRIM Method	300	(2)
Summary	302	(1)
Error Handling	303	(32)
What Is an Exception?	304	(19)
Declaring Exceptions	306	(3)
Raising Exceptions	309	(1)
Handling Exceptions	310	(8)
The EXCEPTION_INIT Pragma	318	(1)
Using Raise_Application_Error	319	(4)
Exception Propagation	323	(6)
Exceptions Raised in the Executable Section	323	(3)
Exceptions Raised in the Declarative Section	326	(1)
Exceptions Raised in the Exception Section	327	(2)
Exception Guidelines	329	(5)
Scope of Exceptions	330	(1)
Avoiding Unhandled Exceptions	331	(1)
Masking Location of the Error	332	(1)
Exceptions and Transactions	333	(1)
Summary	334	(1)
Creating Procedures, Functions, and Packages	335	(46)
Procedures and Functions	336	(31)
Subprogram Creation	337	(5)
Subprogram Parameters	342	(22)
The CALL Statement	364	(3)
Procedures vs. Functions	367	(1)
Packages	367	(13)
Package Specification	367	(2)
Package Body	369	(2)
Packages and Scope	371	(3)
Overloading Packaged Subprograms	374	(4)
Package Initialization	378	(2)
Summary	380	(1)
Using Procedures, Functions, and Packages	381	(62)
Subprogram Locations	382	(11)
Stored Subprograms and the Data Dictionary	382	(3)
Local Subprograms	385	(7)
Stored vs. Local Subprograms	392	(1)
Considerations of Stored Subprograms and Packages	393	(31)
Subprogram Dependencies	394	(12)
Package Run-Time State	406	(6)
Privileges and Stored Subprograms	412	(12)
Stored Functions and SQL Statements	424	(15)
Single-Valued Functions	424	(11)
Multiple-Valued Functions	435	(4)
Native Compilation	439	(1)
Pinning in the Shared Pool	440	(2)
Keep	440	(1)
Unkeep	440	(1)
Sizes	441	(1)
Aborted_Request_Threshold	441	(1)
The PL/SQL Wrapper	442	(1)
Summary	442	(1)
Database Triggers	443	(56)
Types of Triggers	444	(5)
DML Triggers	445	(1)
Instead-of Triggers	446	(2)
System Triggers	448	(1)
Creating Triggers	449	(39)
Creating DML Triggers	449	(12)
Creating Instead-of Triggers	461	(8)
Creating System Triggers	469	(10)
Other Trigger Issues	479	(7)
Triggers and the Data Dictionary	486	(2)
Mutating Tables	488	(7)
Mutating Table Example	491	(1)
Workaround for the Mutating Table Error	492	(3)
Summary	495	(4)
PART II Advanced PL/SQL Features
Intersession Communication	499	(44)
Introducing Intersession Communication	500	(2)
Requiring Permanent or Semipermanent Structures	500	(1)
Not Requiring Permanent or Semipermanent Structures	501	(1)
The DBMS_PIPE Built-in Package	502	(28)
Introducing the DBMS_PIPE Package	502	(3)
Defining the DBMS_PIPE Package	505	(4)
Working with the DBMS_PIPE Package	509	(21)
DBMS_ALERT Built-in Package	530	(12)
Introducing the DBMS_ALERT Package	530	(1)
Defining the DBMS_ALERT Package	531	(3)
Working with the DBMS_ALERT Package	534	(8)
Summary	542	(1)
External Routines	543	(40)
Introducing External Procedures	544	(1)
Working with External Procedures	545	(30)
Defining the extproc Architecture	545	(3)
Defining extproc Oracle Net Services Configuration	548	(8)
Defining the Multithreaded External Procedure Agent	556	(4)
Working with a C Shared Library	560	(8)
Working with a Java Shared Library	568	(7)
Troubleshooting the Shared Library	575	(6)
Configuration of the Listener or Environment	576	(4)
Configuration of the Shared Library or PL/SQL Library Wrapper	580	(1)
Summary	581	(2)
Dynamic SQL	583	(52)
Introducing Dynamic SQL	585	(1)
Working with Native Dynamic SQL	586	(23)
Working with DDL and DML Without Bind Variables	588	(10)
Working with DML and a Known List of Bind Variables	598	(3)
Working with DQL	601	(8)
Working with the Oracle DBMS_SQL Built-in Package	609	(25)
Working with DDL and DML Without Bind Variables	618	(4)
Working with DML and a Known List of Bind Variables	622	(9)
Working with DQL	631	(3)
Summary	634	(1)
Introduction to Objects	635	(38)
Introduction to Object-Oriented Programming	636	(1)
Data and Procedural Abstraction	636	(1)
Object Type Overview	637	(1)
Creating Object Types	638	(14)
Object Type Specification	638	(7)
Object Type Body	645	(7)
Object Type Inheritance	652	(11)
Dynamic Method Dispatch	660	(3)
Attribute Chaining	663	(3)
Making Changes	666	(6)
Type Evolution	666	(6)
Summary	672	(1)
Objects in the Database	673	(38)
Introduction to Objects in the Database	674	(11)
Object Tables	674	(8)
Column Objects	682	(1)
Object Views	683	(2)
Accessing Persistent Objects Using SQL and PL/SQL	685	(20)
Object Tables	685	(4)
Accessing Column Objects	689	(2)
Accessing Object Views	691	(2)
Object Related Functions and Operators	693	(12)
Maintaining Persistent Objects	705	(4)
Type Evolution	705	(4)
Summary	709	(2)
Large Objects	711	(56)
Introduction to Large Objects	712	(13)
Features Comparison	713	(1)
Types of LOBs	713	(4)
LOB Structure	717	(1)
Internal LOB Storage	718	(4)
External LOB Storage	722	(1)
Temporary LOB Storage	723	(1)
Migrating from LONGs to LOBs	724	(1)
LOBs and SQL	725	(5)
SQL for Internal Persistent LOBs	725	(4)
External LOB - BFILE	729	(1)
LOBs and PL/SQL	730	(28)
DBMS_LOB	730	(28)
Performance Considerations	758	(7)
Returning Clause	758	(7)
Summary	765	(2)
Scheduling Tasks	767	(24)
Introducing DBMS_JOB	768	(12)
Submit	770	(4)
Broken	774	(2)
Run	776	(1)
Change	777	(2)
Remove	779	(1)
Oracle Scheduler	780	(8)
Terminology	780	(1)
Using DBMS_SCHEDULER	780	(5)
Migrating from DBMS_JOB	785	(3)
Removing a Job	788	(1)
Summary	788	(3)
PART III Appendixes
A PL/SQL Reserved Words	791	(4)
Table of Reserved Words	792	(3)
B Guide to Supplied Packages	795	(44)
SYS-Owned Built-in Packages	796	(31)
DBMS_Advanced_Rewrite	797	(1)
DBMS_Advisor	797	(1)
DBMS_Alert	798	(1)
DBMS_Application_Info	798	(1)
DBMS_Apply_ADM	799	(1)
DBMS_AQ	799	(1)
DBMS_Backup_Restore	799	(1)
DBMS_Capture_Adm	800	(1)
DBMS_Crypto	800	(1)
DBMS_Data_Mining, DBMS_Data_Mining_Transform	801	(1)
DBMS_Datapump	801	(1)
DBMS_DDL	801	(1)
DBMS_Debug	802	(1)
DBMS_Defer	802	(1)
DBMS_Describe	802	(1)
DBMS_Dimension	803	(1)
DBMS_Distributed_Trust_Admin	803	(1)
DBMS_FGA	803	(1)
DBMS_File_Transfer	804	(1)
DBMS_Flashback	804	(1)
DBMS_HS	805	(1)
DBMS_HS_Passthrough	805	(1)
DBMS_Java	805	(1)
DBMS_Job	806	(1)
DBMS_LDAP	806	(1)
DBMS_LOB	806	(1)
DBMS_Lock	807	(1)
DBMS_Logmir	807	(1)
DBMS_Metadata	807	(1)
DBMS_Monitor	808	(1)
DBMS_Obfuscation_Toolkit	808	(1)
DBMS_ODCI	808	(1)
DBMS_Offline_OG	809	(1)
DBMS_OLAP	809	(1)
DBMS_Outln	809	(1)
DBMS_Outln_Edit	810	(1)
DBMS_Output	810	(1)
DBMS_Pipe	811	(1)
DBMS_Profiler	811	(1)
DBMS_Propagation_ADM	811	(1)
DBMS_Random	812	(1)
DBMS_Redefinition	812	(1)
DBMS_Refresh	812	(1)
DBMS_Repair	813	(1)
DBMS_Repcat	813	(1)
DBMS_Resource_Manager/DBMS_Resource_Manager_Privs	813	(1)
DBMS_Resumable	814	(1)
DBMS_Rowid	814	(1)
DBMS_Rule, DBMS_Rule_Adm	815	(1)
DBMS_Scheduler	815	(1)
DBMS_Schema_Copy	815	(1)
DBMS_Server_Alert	816	(1)
DBMS_Service	816	(1)
DBMS_Session	816	(1)
DBMS_Shared_Pool	816	(1)
DBMS_Space	817	(1)
DBMS_SQL	817	(1)
DBMS_Sqltune	817	(1)
DBMS_Standard, Standard	818	(1)
DBMS_Stat_Funcs	818	(1)
DBMS_Stats	818	(1)
DBMS_Summary	819	(1)
DBMS_Trace	819	(1)
DBMS_Transaction	819	(1)
DBMS_Transform	820	(1)
DBMS_Types	820	(1)
DBMS_Utility	820	(1)
DBMS_Warning	820	(1)
DBMS_Workload_Repository	821	(1)
DBMS_Xmlgen, DBMS_Xmlquery	821	(1)
DBMS_Xplan	821	(1)
UTL_Coll	822	(1)
UTL_Compress	822	(1)
UTL_DBWS	822	(1)
UTL_Encode	823	(1)
UTL_File	823	(1)
UTL_Http	823	(1)
UTL_I18N	824	(1)
UTL_INADDR	824	(1)
UTL_LMS	824	(1)
UTL_Mail	825	(1)
UTL_Raw	825	(1)
UTL_Recomp	825	(1)
UTL_Ref	825	(1)
UTL_SMTP	826	(1)
UTL_TCP	826	(1)
UTL_Url	826	(1)
CTXSYS-Owned Built-in Packages	827	(12)
CTX_Adm	828	(1)
CTX_CLS	828	(1)
CTX_DDL	829	(2)
CTX_DOC	831	(2)
CTX_Output	833	(1)
CTX_Query	834	(1)
CTX_Report	835	(1)
CTX_Thes	836	(3)
Index	839