sunchao2002的专栏

plsql用户参考手册 Overview of PL/SQL Advantages of PL/SQL............................................................................................................................ 1-1 Tight Integration with SQL............................................................................................................... 1-2 Better Performance............................................................................................................................. 1-2 Higher Productivity........................................................................................................................... 1-3 Full Portability.................................................................................................................................... 1-3 Tight Security...................................................................................................................................... 1-3 Access to Pre-defined Packages....................................................................................................... 1-4 Support for Object-Oriented Programming................................................................................... 1-4 Support for Developing Web Applications and Pages................................................................. 1-4 Understanding the Main Features of PL/SQL.................................................................................... 1-4 Understanding PL/SQL Block Structure........................................................................................ 1-4 Understanding PL/SQL Variables and Constants........................................................................ 1-5 Declaring Variables..................................................................................................................... 1-5 Assigning Values to a Variable................................................................................................. 1-6 Bind Variables............................................................................................................................. 1-7 Declaring Constants.................................................................................................................... 1-7 Processing Queries with PL/SQL.................................................................................................... 1-8 Declaring PL/SQL Subprograms..................................................................................................... 1-8 Declaring Datatypes for PL/SQL Variables................................................................................... 1-8 %TYPE.......................................................................................................................................... 1-8 iii %ROWTYPE................................................................................................................................ 1-9 Understanding PL/SQL Control Structures.................................................................................. 1-9 Conditional Control................................................................................................................. 1-10 Iterative Control....................................................................................................................... 1-10 Sequential Control................................................................................................................... 1-12 Understanding Conditional Compilation................................................................................... 1-12 Writing Reusable PL/SQL Code................................................................................................... 1-13 Subprograms: Procedures and Functions............................................................................ 1-13 Packages: APIs Written in PL/SQL....................................................................................... 1-13 Inputting and Outputting Data with PL/SQL............................................................................ 1-15 Understanding PL/SQL Data Abstraction.................................................................................. 1-16 Cursors...................................................................................................................................... 1-16 Collections................................................................................................................................. 1-16 Records...................................................................................................................................... 1-16 Object Types ............................................................................................................................. 1-17 Understanding PL/SQL Error Handling .................................................................................... 1-18 PL/SQL Architecture............................................................................................................................. 1-18 In the Oracle Database Server....................................................................................................... 1-19 Anonymous Blocks.................................................................................................................. 1-19 Stored Subprograms................................................................................................................ 1-19 Database Triggers.................................................................................................................... 1-20 In Oracle Tools................................................................................................................................. 1-21 2 Fundamentals of the PL/SQL Language Character Sets and Lexical Units........................................................................................................... 2-1 Delimiters............................................................................................................................................ 2-2 Identifiers ............................................................................................................................................ 2-3 Reserved Words.......................................................................................................................... 2-4 Predefined Identifiers................................................................................................................. 2-4 Quoted Identifiers....................................................................................................................... 2-4 Literals................................................................................................................................................. 2-4 Numeric Literals.......................................................................................................................... 2-5 Character Literals........................................................................................................................ 2-6 String Literals............................................................................................................................... 2-6 BOOLEAN Literals..................................................................................................................... 2-7 Datetime Literals......................................................................................................................... 2-7 Comments........................................................................................................................................... 2-7 Single-Line Comments............................................................................................................... 2-7 Multi-line Comments................................................................................................................. 2-8 Restrictions on Comments......................................................................................................... 2-8 Declarations............................................................................................................................................... 2-8 Constants............................................................................................................................................. 2-9 Using DEFAULT................................................................................................................................ 2-9 Using NOT NULL.............................................................................................................................. 2-9 Using the %TYPE Attribute........................................................................................................... 2-10 Using the %ROWTYPE Attribute................................................................................................. 2-11 Aggregate Assignment............................................................................................................ 2-12 iv Using Aliases............................................................................................................................ 2-12 Restrictions on Declarations.......................................................................................................... 2-13 PL/SQL Naming Conventions............................................................................................................ 2-13 Scope and Visibility of PL/SQL Identifiers..................................................................................... 2-15 Assigning Values to Variables............................................................................................................ 2-18 Assigning BOOLEAN Values........................................................................................................ 2-19 Assigning a SQL Query Result to a PL/SQL Variable.............................................................. 2-19 PL/SQL Expressions and Comparisons............................................................................................. 2-19 Logical Operators............................................................................................................................ 2-20 Order of Evaluation................................................................................................................. 2-21 Short-Circuit Evaluation......................................................................................................... 2-21 Comparison Operators............................................................................................................ 2-22 Relational Operators................................................................................................................ 2-22 IS NULL Operator.................................................................................................................... 2-23 LIKE Operator.......................................................................................................................... 2-23 BETWEEN Operator................................................................................................................ 2-23 IN Operator............................................................................................................................... 2-23 Concatenation Operator.......................................................................................................... 2-23 BOOLEAN Expressions................................................................................................................. 2-24 BOOLEAN Arithmetic Expressions...................................................................................... 2-24 BOOLEAN Character Expressions........................................................................................ 2-24 BOOLEAN Date Expressions................................................................................................. 2-25 Guidelines for PL/SQL BOOLEAN Expressions................................................................ 2-25 CASE Expressions........................................................................................................................... 2-26 Simple CASE expression......................................................................................................... 2-26 Searched CASE Expression.................................................................................................... 2-27 Handling Null Values in Comparisons and Conditional Statements..................................... 2-27 NULLs and the NOT Operator.............................................................................................. 2-28 Conditional Compilation..................................................................................................................... 2-30 How Does Conditional Compilation Work?............................................................................... 2-31 Conditional Compilation Control Tokens............................................................................ 2-31 Using Conditional Compilation Selection Directives......................................................... 2-31 Using Conditional Compilation Error Directives............................................................... 2-31 Using Conditional Compilation Inquiry Directives........................................................... 2-31 Using Predefined Inquiry Directives With Conditional Compilation ............................ 2-32 Using Static Expressions with Conditional Compilation................................................... 2-33 Setting the PLSQL_CCFLAGS Initialization Parameter..................................................... 2-34 Using DBMS_DB_VERSION Package Constants................................................................ 2-35 Conditional Compilation Examples............................................................................................. 2-35 Using Conditional Compilation to Specify Code for Database Versions........................ 2-35 Using DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text............... 2-36 Conditional Compilation Restrictions.......................................................................................... 2-37 Using PL/SQL to Create Web Applications and Server Pages...................................................... 2-38 PL/SQL Web Applications............................................................................................................ 2-38 PL/SQL Server Pages..................................................................................................................... 2-38 Summary of PL/SQL Built-In Functions........................................................................................... 2-38 v