Oracle Database PLSQL Users Guide and Reference 10g Release 2 (10.2) b14261

Description

Contents......Page 3 Send Us Your Comments......Page 17 Documentation Accessibility......Page 19 Structure......Page 20 PL/SQL Sample Programs......Page 21 Conventions......Page 22 New Features in PL/SQL for Oracle Database 10g Release 2 (10.2)......Page 25 New Features in PL/SQL for Oracle Database 10g Release 1 (10.1)......Page 26 Advantages of PL/SQL......Page 29 Better Performance......Page 30 Tight Security......Page 31 Understanding PL/SQL Block Structure......Page 32 Declaring Variables......Page 33 Assigning Values to a Variable......Page 34 Declaring Constants......Page 35 %TYPE......Page 36 Understanding PL/SQL Control Structures......Page 37 Iterative Control......Page 38 Understanding Conditional Compilation......Page 40 Packages: APIs Written in PL/SQL......Page 41 Inputting and Outputting Data with PL/SQL......Page 43 Records......Page 44 Object Types......Page 45 PL/SQL Architecture......Page 46 Stored Subprograms......Page 47 Database Triggers......Page 48 In Oracle Tools......Page 49 Character Sets and Lexical Units......Page 51 Delimiters......Page 52 Identifiers......Page 53 Literals......Page 54 Numeric Literals......Page 55 String Literals......Page 56 Single-Line Comments......Page 57 Declarations......Page 58 Using NOT NULL......Page 59 Using the %TYPE Attribute......Page 60 Using the %ROWTYPE Attribute......Page 61 Using Aliases......Page 62 PL/SQL Naming Conventions......Page 63 Scope and Visibility of PL/SQL Identifiers......Page 65 Assigning Values to Variables......Page 68 PL/SQL Expressions and Comparisons......Page 69 Logical Operators......Page 70 Short-Circuit Evaluation......Page 71 Relational Operators......Page 72 Concatenation Operator......Page 73 BOOLEAN Character Expressions......Page 74 Guidelines for PL/SQL BOOLEAN Expressions......Page 75 Simple CASE expression......Page 76 Handling Null Values in Comparisons and Conditional Statements......Page 77 NULLs and the NOT Operator......Page 78 Conditional Compilation......Page 80 Using Conditional Compilation Inquiry Directives......Page 81 Using Predefined Inquiry Directives With Conditional Compilation......Page 82 Using Static Expressions with Conditional Compilation......Page 83 Setting the PLSQL_CCFLAGS Initialization Parameter......Page 84 Using Conditional Compilation to Specify Code for Database Versions......Page 85 Using DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text......Page 86 Conditional Compilation Restrictions......Page 87 Summary of PL/SQL Built-In Functions......Page 88 Overview of Predefined PL/SQL Datatypes......Page 91 BINARY_FLOAT and BINARY_DOUBLE Datatypes......Page 92 NUMBER Datatype......Page 93 CHAR Datatype......Page 94 LONG and LONG RAW Datatypes......Page 95 ROWID and UROWID Datatype......Page 96 VARCHAR2 Datatype......Page 98 NCHAR Datatype......Page 99 PL/SQL LOB Types......Page 100 CLOB Datatype......Page 101 PL/SQL Date, Time, and Interval Types......Page 102 TIMESTAMP Datatype......Page 103 TIMESTAMP WITH TIME ZONE Datatype......Page 104 TIMESTAMP WITH LOCAL TIME ZONE Datatype......Page 105 INTERVAL DAY TO SECOND Datatype......Page 106 Overview of PL/SQL Subtypes......Page 107 Using Subtypes......Page 108 Type Compatibility With Subtypes......Page 109 Constraints and Default Values With Subtypes......Page 110 Implicit Conversion......Page 111 Assigning Character Values......Page 113 Comparing Character Values......Page 114 Inserting Character Values......Page 115 Selecting Character Values......Page 116 Overview of PL/SQL Control Structures......Page 117 Using the IF-THEN-ELSE Statement......Page 118 Using the IF-THEN-ELSIF Statement......Page 119 Using CASE Statements......Page 120 Searched CASE Statement......Page 121 Guidelines for PL/SQL Conditional Statements......Page 122 Using the EXIT Statement......Page 123 Labeling a PL/SQL Loop......Page 124 Using the FOR-LOOP Statement......Page 125 How PL/SQL Loops Iterate......Page 126 Scope of the Loop Counter Variable......Page 127 Using the EXIT Statement in a FOR Loop......Page 128 Using the GOTO Statement......Page 129 Using the NULL Statement......Page 131 What are PL/SQL Collections and Records?......Page 133 Understanding Nested Tables......Page 134 Understanding Associative Arrays (Index-By Tables)......Page 135 How Globalization Settings Affect VARCHAR2 Keys for Associative Arrays......Page 136 Choosing Between Nested Tables and Varrays......Page 137 Defining Collection Types and Declaring Collection Variables......Page 138 Declaring PL/SQL Collection Variables......Page 139 Initializing and Referencing Collections......Page 142 Referencing Collection Elements......Page 143 Assigning Collections......Page 144 Comparing Collections......Page 148 Using Multilevel Collections......Page 150 Checking If a Collection Element Exists (EXISTS Method)......Page 151 Checking the Maximum Size of a Collection (LIMIT Method)......Page 152 Finding the First or Last Collection Element (FIRST and LAST Methods)......Page 153 Looping Through Collection Elements (PRIOR and NEXT Methods)......Page 154 Increasing the Size of a Collection (EXTEND Method)......Page 155 Decreasing the Size of a Collection (TRIM Method)......Page 156 Deleting Collection Elements (DELETE Method)......Page 157 Applying Methods to Collection Parameters......Page 158 Avoiding Collection Exceptions......Page 159 Defining and Declaring Records......Page 161 Using Records as Procedure Parameters and Function Return Values......Page 163 Assigning Values to Records......Page 164 Inserting PL/SQL Records into the Database......Page 165 Updating the Database with PL/SQL Record Values......Page 166 Restrictions on Record Inserts and Updates......Page 167 Querying Data into Collections of Records......Page 168 Data Manipulation......Page 169 SQL Pseudocolumns......Page 171 Implicit Cursors......Page 174 Attributes of Implicit Cursors......Page 175 Declaring a Cursor......Page 176 Fetching with a Cursor......Page 177 Fetching Bulk Data with a Cursor......Page 179 Attributes of Explicit Cursors......Page 180 Selecting At Most One Row: SELECT INTO Statement......Page 182 Performing Complicated Query Processing: Explicit Cursors......Page 183 Querying Data with PL/SQL: Explicit Cursor FOR Loops......Page 184 Using Subqueries......Page 185 Using Correlated Subqueries......Page 186 Writing Maintainable PL/SQL Queries......Page 187 Declaring REF CURSOR Types and Cursor Variables......Page 188 Opening a Cursor Variable......Page 190 Using a Cursor Variable as a Host Variable......Page 192 Fetching from a Cursor Variable......Page 193 Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL......Page 194 Restrictions on Cursor Variables......Page 195 Restrictions on Cursor Expressions......Page 196 Constructing REF CURSORs with Cursor Subqueries......Page 197 Using COMMIT in PL/SQL......Page 198 Using SAVEPOINT in PL/SQL......Page 199 How Oracle Does Implicit Rollbacks......Page 200 Setting Transaction Properties with SET TRANSACTION......Page 201 Overriding Default Locking......Page 202 Defining Autonomous Transactions......Page 205 Controlling Autonomous Transactions......Page 207 Using Autonomous Triggers......Page 209 Calling Autonomous Functions from SQL......Page 210 Why Use Dynamic SQL with PL/SQL?......Page 211 Using the EXECUTE IMMEDIATE Statement in PL/SQL......Page 212 Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings......Page 214 Using Dynamic SQL with Bulk SQL......Page 215 Examples of Dynamic Bulk Binds......Page 216 When to Use or Omit the Semicolon with Dynamic SQL......Page 217 Passing Schema Object Names As Parameters......Page 218 Using Cursor Attributes with Dynamic SQL......Page 219 Using Database Links with Dynamic SQL......Page 220 Avoiding Deadlocks with Dynamic SQL......Page 221 Using Dynamic SQL With PL/SQL Records and Collections......Page 222 What Are Subprograms?......Page 223 Advantages of PL/SQL Subprograms......Page 224 Understanding PL/SQL Procedures......Page 225 Understanding PL/SQL Functions......Page 226 Declaring Nested PL/SQL Subprograms......Page 227 Actual Versus Formal Subprogram Parameters......Page 228 Using the IN Mode......Page 229 Using the IN OUT Mode......Page 230 Using Default Values for Subprogram Parameters......Page 231 Guidelines for Overloading with Numeric Types......Page 232 Restrictions on Overloading......Page 233 How Subprogram Calls Are Resolved......Page 234 How Overloading Works with Inheritance......Page 236 Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)......Page 237 Who Is the Current User During Subprogram Execution?......Page 238 Overriding Default Name Resolution in Invoker's Rights Subprograms......Page 239 Granting Privileges on an Invoker's Rights Subprogram: Example......Page 240 Using Database Links with Invoker's Rights Subprograms......Page 241 Using Object Types with Invoker's Rights Subprograms......Page 242 What Is a Recursive Subprogram?......Page 243 Calling External Subprograms......Page 244 Controlling Side Effects of PL/SQL Subprograms......Page 245 Understanding Subprogram Parameter Aliasing......Page 246 What Is a PL/SQL Package?......Page 249 What Goes In a PL/SQL Package?......Page 250 Understanding The Package Specification......Page 251 Referencing Package Contents......Page 252 Understanding The Package Body......Page 253 Some Examples of Package Features......Page 254 How Package STANDARD Defines the PL/SQL Environment......Page 256 About the DBMS_OUTPUT Package......Page 257 Guidelines for Writing Packages......Page 258 Separating Cursor Specs and Bodies with Packages......Page 259 Overview of PL/SQL Runtime Error Handling......Page 261 Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions......Page 262 Advantages of PL/SQL Exceptions......Page 263 Summary of Predefined PL/SQL Exceptions......Page 264 Defining Your Own PL/SQL Exceptions......Page 265 Scope Rules for PL/SQL Exceptions......Page 266 Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR......Page 267 Redeclaring Predefined Exceptions......Page 268 How PL/SQL Exceptions Propagate......Page 269 Reraising a PL/SQL Exception......Page 271 Handling Raised PL/SQL Exceptions......Page 272 Branching to or from an Exception Handler......Page 273 Retrieving the Error Code and Error Message: SQLCODE and SQLERRM......Page 274 Continuing after an Exception Is Raised......Page 275 Retrying a Transaction......Page 276 PL/SQL Warning Categories......Page 277 Controlling PL/SQL Warning Messages......Page 278 Using the DBMS_WARNING Package......Page 279 Initialization Parameters for PL/SQL Compilation......Page 281 When to Tune PL/SQL Code......Page 282 Make Function Calls as Efficient as Possible......Page 283 Make Loops as Efficient as Possible......Page 284 Minimize Datatype Conversions......Page 285 Group Related Subprograms into Packages......Page 286 Using The Profiler API: Package DBMS_PROFILER......Page 287 Reducing Loop Overhead for DML Statements and Queries with Bulk SQL......Page 288 Using the FORALL Statement......Page 289 Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute......Page 292 Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute......Page 294 Retrieving Query Results into Collections with the BULK COLLECT Clause......Page 295 Examples of Bulk-Fetching from a Cursor......Page 297 Retrieving DML Results into a Collection with the RETURNING INTO Clause......Page 298 Using Host Arrays with Bulk Binds......Page 299 Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables......Page 300 Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint......Page 301 Compiling PL/SQL Code for Native Execution......Page 302 Determining Whether to Use PL/SQL Native Compilation......Page 303 Dependencies, Invalidation and Revalidation......Page 304 Setting up Initialization Parameters for PL/SQL Native Compilation......Page 305 PLSQL_CODE_TYPE Initialization Parameter......Page 306 Setting Up and Testing PL/SQL Native Compilation......Page 307 Setting Up a New Database for PL/SQL Native Compilation......Page 308 Modifying the Entire Database for PL/SQL Native or Interpreted Compilation......Page 309 Setting Up Transformations with Pipelined Functions......Page 311 Writing a Pipelined Table Function......Page 312 Using Pipelined Table Functions for Transformations......Page 313 Returning Results from Pipelined Table Functions......Page 314 Fetching from the Results of Pipelined Table Functions......Page 315 Passing Data with Cursor Variables......Page 316 Performing DML Operations on Pipelined Table Functions......Page 318 Handling Exceptions in Pipelined Table Functions......Page 319 Declaring and Initializing Objects in PL/SQL......Page 321 Declaring Objects in a PL/SQL Block......Page 322 How PL/SQL Treats Uninitialized Objects......Page 323 Calling Object Constructors and Methods......Page 324 Updating and Deleting Objects......Page 325 Defining SQL Types Equivalent to PL/SQL Collection Types......Page 326 Manipulating Individual Collection Elements with SQL......Page 328 Using PL/SQL Collections with SQL Object Types......Page 329 Using Dynamic SQL With Objects......Page 331 13 PL/SQL Language Elements......Page 333 Assignment Statement......Page 335 AUTONOMOUS_TRANSACTION Pragma......Page 338 Block Declaration......Page 340 CASE Statement......Page 346 CLOSE Statement......Page 348 Collection Definition......Page 349 Collection Methods......Page 352 Comments......Page 355 COMMIT Statement......Page 356 Constant and Variable Declaration......Page 357 Cursor Attributes......Page 360 Cursor Variables......Page 362 Cursor Declaration......Page 365 DELETE Statement......Page 368 EXCEPTION_INIT Pragma......Page 370 Exception Definition......Page 371 EXECUTE IMMEDIATE Statement......Page 373 EXIT Statement......Page 376 Expression Definition......Page 377 FETCH Statement......Page 385 FORALL Statement......Page 388 Function Declaration......Page 391 GOTO Statement......Page 395 IF Statement......Page 396 INSERT Statement......Page 398 Literal Declaration......Page 400 LOCK TABLE Statement......Page 403 LOOP Statements......Page 404 MERGE Statement......Page 408 NULL Statement......Page 409 Object Type Declaration......Page 410 OPEN Statement......Page 412 OPEN-FOR Statement......Page 414 Package Declaration......Page 417 Procedure Declaration......Page 422 RAISE Statement......Page 426 Record Definition......Page 427 RESTRICT_REFERENCES Pragma......Page 430 RETURN Statement......Page 432 RETURNING INTO Clause......Page 433 ROLLBACK Statement......Page 435 %ROWTYPE Attribute......Page 436 SAVEPOINT Statement......Page 438 SELECT INTO Statement......Page 439 SERIALLY_REUSABLE Pragma......Page 443 SET TRANSACTION Statement......Page 445 SQL Cursor......Page 447 SQLCODE Function......Page 449 SQLERRM Function......Page 450 %TYPE Attribute......Page 451 UPDATE Statement......Page 453 Tips When Obfuscating PL/SQL Units......Page 457 Obfuscating PL/SQL Code With the wrap Utility......Page 458 Running the wrap Utility......Page 459 Using the DBMS_DDL create_wrapped Procedure......Page 460 What Is Name Resolution?......Page 463 Examples of Qualified Names and Dot Notation......Page 464 Differences in Name Resolution Between PL/SQL and SQL......Page 465 Inner Capture......Page 466 Avoiding Inner Capture in DML Statements......Page 467 References to Attributes and Methods......Page 468 References to Row Expressions......Page 469 C PL/SQL Program Limits......Page 471 D PL/SQL Reserved Words and Keywords......Page 473 A......Page 475 B......Page 476 C......Page 477 D......Page 479 E......Page 480 I......Page 482 L......Page 483 N......Page 484 P......Page 486 R......Page 489 S......Page 490 T......Page 493 V......Page 494 Z......Page 495