Oracle Pl/SQL Language Pocket Reference: Pocket Reference

by Feuerstein, Steven; Pribyl, Bill; Dawes, Chip

• ISBN13: 9780596004729
• ISBN10: 0596004729
• Edition: 2nd
• Format: Paperback
• Copyright: 2003-02-01
• Publisher: Oreilly & Associates Inc

Summary

The second edition of this popular pocket guide provides quick-reference information that will help you use Oracle's PL/SQL language, including the newest Oracle9i Release 2 features. It's a companion to Steven Feuerstein and Bill Pribyl's bestselling Oracle PL/SQL Programming. This concise guide boils down the most vital information from the larger book into an accessible quick-reference to fundamental language elements (e.g., block structure, identifiers, variables, datatypes, and declarations); statements for program control, cursor management, and exception handling; the basics of records, procedures, functions, triggers, and packages; and the calling of PL/SQL functions in SQL. It also includes Oracle's object-oriented features, collections, external procedures, Java integration, and such Oracle9i elements as new timestamp and interval datatypes, CASE statements and expressions, native compilation of PL/SQL, pipelined table functions, object type inheritance, upcasting/downcasting of subtypes, nested collections, and much more. Book jacket.

Author Biography

Steven Feuerstein has adopted PL/SQL as a member of his immediate family. Pay them both a visit at http://www.stevenfeuerstein.com. Bill Pribyl is thankful if his writing and lectures about PL/SQL make things a little bit easier for Oracle users. Chip Dawes is a DBA and PL/SQL developer consultant in the Chicago area.

Table of Contents

Introduction	1	(1)
PL/SQL Language Fundamentals	2	(7)
PL/SQL Character Set	2	(1)
Identifiers	3	(1)
Boolean, Numeric, and String Literals	3	(1)
Datetime Interval Literals (Oracle9i)	4	(1)
Delimiters	5	(1)
Comments	6	(1)
Pragmas	7	(1)
Statements	7	(1)
Block Structure	8	(1)
Variables and Program Data	9	(11)
Scalar Datatypes	10	(5)
NLS Character Datatypes	15	(1)
LOB Datatypes	15	(1)
Implicit Datatype Conversions	16	(1)
NULLs in PL/SQL	16	(1)
Declaring Variables	16	(3)
Anchored Declarations	19	(1)
Programmer-Defined Subtypes	20	(1)
Conditional and Sequential Control	20	(5)
Conditional Control Statements	20	(4)
Sequential Control Statements	24	(1)
Loops	25	(4)
Simple Loop	26	(1)
Numeric FOR Loop	26	(1)
Cursor FOR Loop	27	(1)
WHILE Loop	27	(1)
REPEAT UNTIL Loop Emulation	28	(1)
EXIT Statement	28	(1)
Loop Labels	28	(1)
Database Interaction	29	(4)
Transaction Management	29	(3)
Autonomous Transactions	32	(1)
Cursors in PL/SQL	33	(11)
Explicit Cursors	33	(4)
Implicit Cursors	37	(3)
Dynamic Cursors	40	(1)
Cursor Variables	41	(2)
Cursor Expressions	43	(1)
Exception Handling	44	(6)
Declaring Exceptions	44	(2)
Raising Exceptions	46	(1)
Scope	47	(1)
Propagation	47	(3)
Records in PL/SQL	50	(3)
Declaring Records	50	(1)
Referencing Fields of Records	51	(1)
Record Assignment	52	(1)
Nested Records	53	(1)
Named Program Units	53	(12)
Procedures	54	(1)
Functions	54	(2)
Parameters	56	(9)
Triggers	65	(4)
Creating Triggers	65	(3)
Trigger Predicates	68	(1)
DML Events	68	(1)
DDL Events	69	(1)
Database Events	69	(1)
Packages	69	(5)
Package Structure	70	(2)
Referencing Package Elements	72	(1)
Package Data	72	(1)
Serially_Reusable Pragma	73	(1)
Package Initialization	73	(1)
Calling PL/SQL Functions in SQL	74	(3)
Calling a Function	75	(1)
Requirements and Restrictions	76	(1)
Calling Packaged Functions in SQL	76	(1)
Column/Function Name Precedence	77	(1)
Oracle's Object-Oriented Features	77	(13)
Object Types	78	(2)
Type Inheritance (Oracle9i)	80	(1)
Methods	80	(3)
Methods in Subtypes (Oracle9i)	83	(1)
Manipulating Objects in PL/SQL and SQL	84	(1)
Upcasting and Downcasting (Oracle9i)	85	(3)
Changing Object Types	88	(2)
Collections	90	(12)
Declaring a Collection	91	(1)
Initializing Collections	92	(2)
Adding and Removing Elements	94	(1)
Collection Pseudo-Functions	94	(1)
Collection Methods	95	(3)
Collections and Privileges	98	(1)
Nested Collections (Oracle9i)	98	(1)
Bulk Binds	98	(4)
External Procedures	102	(9)
Creating an External Procedure	102	(5)
Parameters	107	(4)
Java Language Integration	111	(4)
Example	113	(1)
Publishing Java to PL/SQL	114	(1)
Data Dictionary	115	(1)
Reserved Words	115	(2)
Index	117