Oracle PL/SQL Language Pocket Reference

Steven Feuerstein, Bill Pribyl, Chip Dawes0-596-51404-2, 978-0-596-51404-4

The fourth edition of this popular pocket guide provides quick-reference information that will help you use Oracle’s PL/SQL language, including the newest Oracle Database 11g 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 PL/SQL information into an accessible summary of:
Fundamental language elements (e.g., block structure, datatypes, declarations)

Statements for program control, cursor management, and exception handling

Records, procedures, functions, triggers, and packages

Calling PL/SQL functions in SQL

Compilation options, object-oriented features, collections, and Java integration The new edition describes such Oracle Database 11g elements as PL/SQL’s function result cache, compound triggers, the CONTINUE statement, the SIMPLE_INTEGER datatype, and improvements to native compilation, regular expressions, and compiler optimization (including intra-unit inlining). In addition, this book now includes substantial new sections on Oracle’s built-in functions and packages. When you need answers quickly, the Oracle PL/SQL Language Pocket Reference will save you hours of frustration.

---

Table of contents :
Oracle PL/SQL Language Pocket Reference, Fourth Edition……Page 0
Contents……Page 3
Acknowledgments……Page 9
PL/SQL Character Set……Page 10
Identifiers……Page 11
Boolean, Numeric, and String Literals……Page 12
Numeric Literals……Page 13
Delimiters……Page 14
Pragmas……Page 16
Block Structure……Page 17
Variables and Program Data……Page 19
Numeric datatypes……Page 20
Character datatypes……Page 22
Datetime datatypes……Page 23
BOOLEAN datatype……Page 25
LOB Datatypes……Page 26
Declaring Variables……Page 27
Default values……Page 29
Anchored Declarations……Page 30
Conditional Control Statements……Page 31
IF-THEN-ELSIF combination……Page 32
CASE statement……Page 33
CASE expression……Page 34
GOTO……Page 35
Loops……Page 36
Numeric FOR Loop……Page 37
WHILE Loop……Page 38
CONTINUE Statement (Oracle Database 11g)……Page 39
Loop Labels……Page 41
Transaction Management……Page 42
SET TRANSACTION……Page 43
LOCK TABLE……Page 44
Autonomous Transactions……Page 45
Explicit Cursors……Page 46
Opening explicit cursors……Page 47
Closing explicit cursors……Page 48
Explicit cursor attributes……Page 49
Implicit Cursors……Page 50
SELECT FOR UPDATE clause……Page 51
WHERE CURRENT OF clause……Page 52
Dynamic Cursors……Page 53
DBMS_SQL……Page 54
SQL Injection and Bind Variables……Page 55
Cursor Variables……Page 56
Cursor Expressions……Page 57
Exception Handling……Page 58
Declaring Exceptions……Page 59
Raising Exceptions……Page 61
Propagation……Page 62
SQLCODE, SQLERRM, and DBMS_UTILITY.FORMAT_ CALL_STACK……Page 63
Records in PL/SQL……Page 64
Declaring Records……Page 65
Assigning Records……Page 66
Records and DML……Page 67
Nested Records……Page 68
Collections in PL/SQL……Page 69
Declaring a Collection……Page 71
Initializing a Collection……Page 72
Nested Table Functions……Page 73
Collection Methods……Page 76
Bulk Binds……Page 79
Built-in Functions……Page 83
Conversion functions……Page 84
String functions……Page 86
Numeric functions……Page 90
Datetime functions……Page 93
Built-in Regular Expression Functions……Page 95
Metacharacters……Page 96
REGEXP_LIKE……Page 97
REGEXP_SUBSTR……Page 98
REGEXP_REPLACE……Page 100
Built-in Packages……Page 101
Stored Procedures and Functions……Page 105
Procedures……Page 106
Functions……Page 107
Datatype……Page 108
Mode……Page 109
Parameter-passing notations……Page 110
Local Programs……Page 111
Program Overloading……Page 112
Table Functions……Page 114
Function Result Cache……Page 115
Triggers……Page 117
Creating Triggers……Page 118
DML Events……Page 122
Compound DML Triggers……Page 123
Packages……Page 125
Package Structure……Page 126
SERIALLY_REUSABLE Pragma……Page 128
Package Initialization……Page 129
Calling PL/SQL Functions in SQL……Page 130
Calling a Function……Page 131
Calling Packaged Functions in SQL……Page 132
Object-Oriented Features……Page 133
Object Types……Page 134
Member methods……Page 136
Constructor methods……Page 137
Comparison methods……Page 138
Methods in Subtypes……Page 140
Manipulating Objects in PL/SQL and SQL……Page 141
Upcasting and Downcasting……Page 143
REF operator……Page 144
DEREF operator……Page 145
Changing Object Types……Page 146
Compilation……Page 147
Compiling Stored PL/SQL Programs……Page 148
Conditional Compilation……Page 150
Compiler Warnings……Page 153
Optimizing Compiler……Page 155
Performing Native Compilation of PL/SQL……Page 157
Oracle Database 10g……Page 158
Oracle Database 11g……Page 159
Java Language Integration……Page 160
Example……Page 161
Publishing Java to PL/SQL……Page 162
Data Dictionary……Page 163
Index……Page 165