iii
Naming Conventions 2-35
Synonyms 2-36
Scoping 2-36
Case Sensitivity 2-36
Name Resolution 2-36
Scope and Visibility 2-37
Assignments 2-40
Boolean Values 2-40
Database Values 2-41
Expressions and Comparisons 2-41
Operator Precedence 2-42
Logical Operators 2-43
Comparison Operators 2-44
Concatenation Operator 2-46
Boolean Expressions 2-46
Handling Nulls 2-48
Built-In Functions 2-51
3 Control Structures
Overview 3-2
Conditional Control: IF Statements 3-2
IF-THEN 3-3
IF-THEN-ELSE 3-3
IF-THEN-ELSIF 3-4
Guidelines 3-5
Iterative Control: LOOP and EXIT Statements 3-6
LOOP 3-6
WHILE-LOOP 3-9
FOR-LOOP 3-10
Sequential Control: GOTO and NULL Statements 3-15
GOTO Statement 3-15
NULL Statement 3-19
iv
4 Collections and Records
What Is a Collection? 4-2
Understanding Nested Tables 4-2
Nested Tables versus Index-by Tables 4-3
Understanding Varrays 4-4
Varrays versus Nested Tables 4-4
Defining and Declaring Collections 4-5
Declaring Collections 4-7
Initializing and Referencing Collections 4-8
Referencing Collection Elements 4-10
Assigning and Comparing Collections 4-11
Comparing Whole Collections 4-13
Manipulating Collections 4-13
Some Nested Table Examples 4-13
Some Varray Examples 4-16
Manipulating Individual Elements 4-18
Manipulating Local Collections 4-20
Using Collection Methods 4-21
Using EXISTS 4-22
Using COUNT 4-22
Using LIMIT 4-22
Using FIRST and LAST 4-23
Using PRIOR and NEXT 4-23
Using EXTEND 4-24
Using TRIM 4-25
Using DELETE 4-26
Applying Methods to Collection Parameters 4-27
Avoiding Collection Exceptions 4-27
Taking Advantage of Bulk Binds 4-29
How Do Bulk Binds Improve Performance? 4-30
Using the FORALL Statement 4-32
Using the BULK COLLECT Clause 4-34
Using FORALL and BULK COLLECT Together 4-35
Using Host Arrays 4-36
Using Cursor Attributes 4-36
v
What Is a Record? 4-37
Defining and Declaring Records 4-38
Declaring Records 4-39
Initializing and Referencing Records 4-40
Referencing Records 4-40
Assigning and Comparing Records 4-42
Comparing Records 4-44
Manipulating Records 4-44
5 Interaction with Oracle
SQL Support 5-2
Data Manipulation 5-2
Transaction Control 5-2
SQL Functions 5-3
SQL Pseudocolumns 5-3
SQL Operators 5-5
Managing Cursors 5-6
Explicit Cursors 5-6
Implicit Cursors 5-11
Packaging Cursors 5-12
Using Cursor FOR Loops 5-13
Using Subqueries 5-14
Using Aliases 5-14
Passing Parameters 5-15
Using Cursor Variables 5-15
What Are Cursor Variables? 5-16
Why Use Cursor Variables? 5-16
Defining REF CURSOR Types 5-17
Declaring Cursor Variables 5-17
Controlling Cursor Variables 5-19
Example 1 5-24
Example 2 5-25
Example 3 5-26
Example 4 5-28
Reducing Network Traffic 5-30
vi
Avoiding Errors 5-31
Restrictions on Cursor Variables 5-33
Using Cursor Attributes 5-34
Explicit Cursor Attributes 5-34
Implicit Cursor Attributes 5-38
Processing Transactions 5-40
How Transactions Guard Your Database 5-41
Using COMMIT 5-42
Using ROLLBACK 5-43
Using SAVEPOINT 5-44
Implicit Rollbacks 5-45
Ending Transactions 5-45
Using SET TRANSACTION 5-46
Overriding Default Locking 5-47
Dealing with Size Limitations 5-50
Using Autonomous Transactions 5-52
Advantages of Autonomous Transactions 5-52
Defining Autonomous Transactions 5-53
Controlling Autonomous Transactions 5-56
Example 1: Using an Autonomous Trigger 5-58
Example 2: Calling an Autonomous Function from SQL 5-59
Improving Performance 5-60
Use Object Types and Collections 5-60
Use Bulk Binds 5-61
Use Native Dynamic SQL 5-62
Use External Routines 5-62
Use the NOCOPY Compiler Hint 5-63
Use the RETURNING Clause 5-63
Use Serially Reusable Packages 5-64
Use the PLS_INTEGER Datatype 5-65
Avoid the NOT NULL Constraint 5-66
Rephrase Conditional Control Statements 5-66
Avoid Implicit Datatype Conversions 5-67
Ensuring Backward Compatibility 5-68
vii
6 Error Handling
Overview 6-2
Advantages of Exceptions 6-3
Predefined Exceptions 6-4
User-Defined Exceptions 6-7
Declaring Exceptions 6-7
Scope Rules 6-7
Using EXCEPTION_INIT 6-8
Using raise_application_error 6-9
Redeclaring Predefined Exceptions 6-10
How Exceptions Are Raised 6-11
Using the RAISE Statement 6-11
How Exceptions Propagate 6-12
Reraising an Exception 6-14
Handling Raised Exceptions 6-15
Exceptions Raised in Declarations 6-16
Exceptions Raised in Handlers 6-17
Branching to or from an Exception Handler 6-17
Using SQLCODE and SQLERRM 6-18
Unhandled Exceptions 6-19
Useful Techniques 6-20
Continuing after an Exception Is Raised 6-20
Retrying a Transaction 6-21
Using Locator Variables 6-22
7 Subprograms
What Are Subprograms? 7-2
Advantages of Subprograms 7-3
Procedures 7-3
Functions 7-5
Controlling Sides Effects 7-7
RETURN Statement 7-8
Declaring Subprograms 7-9
Forward Declarations 7-9
Stored Subprograms 7-11
viii
Actual versus Formal Parameters 7-12
Positional and Named Notation 7-13
Positional Notation 7-13
Named Notation 7-13
Mixed Notation 7-13
Parameter Modes 7-14
IN Mode 7-14
OUT Mode 7-14
IN OUT Mode 7-16
Summary 7-16
NOCOPY Compiler Hint 7-17
The Trade-Off for Better Performance 7-18
Restrictions on NOCOPY 7-19
Parameter Default Values 7-19
Parameter Aliasing 7-21
Overloading 7-23
Restrictions 7-24
How Calls Are Resolved 7-25
Avoiding Call Resolution Errors 7-27
Calling External Routines 7-27
Invoker Rights versus Definer Rights 7-29
Advantages of Invoker Rights 7-30
Using the AUTHID Clause 7-32
How External References Are Resolved 7-32
Granting the EXECUTE Privilege 7-35
Using Views and Database Triggers 7-36
Using Database Links 7-36
Invoking Instance Methods 7-37
Recursion 7-37
Recursive Subprograms 7-38
Mutual Recursion 7-40
Recursion versus Iteration 7-41
ix
8 Packages
What Is a Package? 8-2
Advantages of Packages 8-4
The Package Spec 8-5
Referencing Package Contents 8-6
The Package Body 8-7
Some Examples 8-8
Private versus Public Items 8-14
Overloading 8-14
Package STANDARD 8-15
Product-specific Packages 8-16
DBMS_STANDARD 8-16
DBMS_ALERT 8-16
DBMS_OUTPUT 8-16
DBMS_PIPE 8-17
UTL_FILE 8-17
UTL_HTTP 8-17
Guidelines 8-18
9 Object Types
The Role of Abstraction 9-2
What Is an Object Type? 9-3
Why Use Object Types? 9-5
Structure of an Object Type 9-5
Components of an Object Type 9-7
Attributes 9-7
Methods 9-8
Defining Object Types 9-12
Object Type Stack 9-13
Object Type Ticket_Booth 9-16
Object Type Bank_Account 9-18
Object Type Rational 9-20
Declaring and Initializing Objects 9-22
Declaring Objects 9-22
x
Initializing Objects 9-23
How PL/SQL Treats Uninitialized Objects 9-24
Accessing Attributes 9-24
Calling Constructors 9-25
Calling Methods 9-26
Sharing Objects 9-27
Using Refs 9-28
Forward Type Definitions 9-29
Manipulating Objects 9-30
Selecting Objects 9-31
Inserting Objects 9-35
Updating Objects 9-37
Deleting Objects 9-37
10 Native Dynamic SQL
What Is Dynamic SQL? 10-2
The Need for Dynamic SQL 10-2
Using the EXECUTE IMMEDIATE Statement 10-3
Some Examples 10-4
Using the OPEN-FOR, FETCH, and CLOSE Statements 10-5
Opening the Cursor Variable 10-5
Fetching from the Cursor Variable 10-6
Closing the Cursor Variable 10-6
Some Examples 10-7
Specifying Parameter Modes 10-9
Tips and Traps 10-10
Passing the Names of Schema Objects 10-10
Using Duplicate Placeholders 10-10
Using Cursor Attributes 10-11
Passing Nulls 10-12
Doing Remote Operations 10-12
Using Invoker Rights 10-13
Using Pragma RESTRICT_REFERENCES 10-13
Avoiding Deadlocks 10-14
xi
11 Language Elements
Assignment Statement 11-3
Blocks 11-7
CLOSE Statement 11-14
Collection Methods 11-16
Collections 11-21
Comments 11-27
COMMIT Statement 11-28
Constants and Variables 11-30
Cursor Attributes 11-34
Cursor Variables 11-39
Cursors 11-45
DELETE Statement 11-49
EXCEPTION_INIT Pragma 11-53
Exceptions 11-55
EXECUTE IMMEDIATE Statement 11-58
EXIT Statement 11-61
Expressions 11-63
FETCH Statement 11-73
FORALL Statement 11-77
Functions 11-79
GOTO Statement 11-84
IF Statement 11-86
INSERT Statement 11-89
Literals 11-93
LOCK TABLE Statement 11-96
LOOP Statements 11-98
NULL Statement 11-104
Object Types 11-105
OPEN Statement 11-113
OPEN-FOR Statement 11-115
OPEN-FOR-USING Statement 11-119
Packages 11-122
Procedures 11-127
RAISE Statement 11-132
xii
Records 11-134
RETURN Statement 11-138
ROLLBACK Statement 11-140
%ROWTYPE Attribute 11-142
SAVEPOINT Statement 11-144
SELECT INTO Statement 11-145
SET TRANSACTION Statement 11-149
SQL Cursor 11-151
SQLCODE Function 11-153
SQLERRM Function 11-155
%TYPE Attribute 11-157
UPDATE Statement 11-159
A Sample Programs
Running the Programs A-2
Sample 1. FOR Loop A-3
Sample 2. Cursors A-4
Sample 3. Scoping A-6
Sample 4. Batch Transaction Processing A-7
Sample 5. Embedded PL/SQL A-11
Sample 6. Calling a Stored Procedure A-15
B CHAR versus VARCHAR2 Semantics
Assigning Character Values B-2
Comparing Character Values B-2
Inserting Character Values B-4
Selecting Character Values B-4
C PL/SQL Wrapper
Advantages of Wrapping C-2
Running the PL/SQL Wrapper C-2
Không có nhận xét nào:
Đăng nhận xét