Oracle PL/SQL Language Pocket Reference (Pocket Reference (O'Reilly))

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. 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 This pocket reference condenses the most vital information from Oracle PL/SQL programming into an accessible quick reference that summarises the basics of PL/SQL - block structure, fundamental language elements, data structures, control statements, and use of procedures, functions and packages