Oracle Database PL/SQL 11g Language Reference

3星 · 超过75%的资源需积分: 3 110 浏览量更新于2024-07-29 收藏 11.91MB PDF 举报

"Oracle数据库PL/SQL语言参考11g第一版(11.1)" Oracle数据库PL/SQL是Oracle数据库系统中用于处理结构化查询语言（SQL）的编程组件，专为数据库开发和管理设计。11g版本是Oracle数据库的一个重要版本，它提供了许多增强的功能和改进，以提升性能、可维护性和开发效率。 1. **PL/SQL基本概念**：PL/SQL是一种过程化语言，结合了SQL的查询能力与编程语言的控制结构。它由声明部分、执行部分和异常处理部分组成，支持变量、常量、过程、函数、游标、表和记录等概念。 2. **变量和数据类型**：在PL/SQL中，你可以声明各种内置数据类型，如 NUMBER、VARCHAR2、DATE、BOOLEAN等，以及用户自定义的数据类型。变量可以在程序中存储和操作数据。 3. **流程控制语句**：包括条件语句（IF-THEN-ELSIF-ELSE）、循环语句（WHILE、FOR、LOOP）、CASE表达式，以及跳转语句（GOTO，虽然不推荐使用）。 4. **异常处理**：PL/SQL提供了一个内置的异常处理机制，允许开发者捕获并处理运行时错误。通过EXCEPTION块，可以定义预定义和自定义异常，以优雅地处理程序中的错误。 5. **游标**：游标允许你遍历查询结果集，一次处理一行。在PL/SQL中，你可以声明游标，打开、提取数据，然后关闭游标。 6. **存储过程和函数**：存储过程是一组预编译的SQL语句和PL/SQL代码，可以在数据库中保存并重复使用。函数与存储过程类似，但返回一个值。它们能提高应用程序的性能，减少网络流量，并实现业务逻辑的封装。 7. **包（PACKAGE）**：包是将相关的常量、类型、过程和函数组织在一起的机制，提供模块化和命名空间管理。包可以包含公有和私有部分，增强了代码的重用性。 8. **触发器（TRIGGER）**：触发器是在特定数据库事件（如INSERT、UPDATE或DELETE）发生时自动执行的PL/SQL代码，用于实现复杂的业务规则和约束。 9. **索引和性能优化**：PL/SQL允许创建和管理索引，优化查询性能。通过分析和调整索引策略，可以显著提升数据库的响应速度。 10. **并发控制**：PL/SQL支持事务管理和并发控制，如锁定、乐观锁和悲观锁，以确保多用户环境下的数据一致性。 11. **游标表达式（Bulk Collect）**：11g引入了Bulk Collect，允许一次性收集多行数据，极大地提高了处理大量数据时的性能。 12. **绑定变量**：绑定变量是一种优化技术，它允许将动态SQL中的参数值以变量的形式传递，减少了解析和执行计划的生成，提高了性能。 13. **PL/SQL的XML支持**：11g版加强了对XML的支持，允许在PL/SQL中处理XML数据，包括XMLType数据类型、XMLDB存储方案以及XML相关的函数和过程。 Oracle Database PL/SQL Language Reference 11g Release 1 (11.1) 是开发人员和DBA的重要参考资料，详细介绍了这些概念和更多高级特性，帮助读者深入理解和使用PL/SQL进行高效数据库编程。

xvi

Scope Rules for PL/SQL Exceptions............................................................................................ 11-6

Associating a PL/SQL Exception with a Number (EXCEPTION_INIT Pragma) ................. 11-7

Defining Your Own Error Messages (RAISE_APPLICATION_ERROR Procedure)............ 11-8

Redeclaring Predefined Exceptions.............................................................................................. 11-9

How PL/SQL Exceptions Are Raised................................................................................................. 11-9

How PL/SQL Exceptions Propagate................................................................................................. 11-10

Reraising a PL/SQL Exception.......................................................................................................... 11-12

Handling Raised PL/SQL Exceptions.............................................................................................. 11-13

Exceptions Raised in Declarations.............................................................................................. 11-14

Handling Exceptions Raised in Exception Handlers............................................................... 11-14

Branching To or from an Exception Handler............................................................................ 11-15

Retrieving the Error Code and Error Message ......................................................................... 11-15

Catching Unhandled Exceptions ................................................................................................ 11-16

Guidelines for Handling PL/SQL Errors .................................................................................. 11-16

Continuing Execution After an Exception Is Raised......................................................... 11-16

Retrying a Transaction .......................................................................................................... 11-17

Using Locator Variables to Identify Exception Locations................................................ 11-18

Overview of PL/SQL Compile-Time Warnings............................................................................. 11-19

PL/SQL Warning Categories ...................................................................................................... 11-19

Controlling PL/SQL Warning Messages................................................................................... 11-20

Using DBMS_WARNING Package ............................................................................................ 11-20

12 Tuning PL/SQL Applications for Performance

How PL/SQL Optimizes Your Programs........................................................................................... 12-1

When to Tune PL/SQL Code................................................................................................................ 12-2

Guidelines for Avoiding PL/SQL Performance Problems ............................................................ 12-3

Avoiding CPU Overhead in PL/SQL Code................................................................................ 12-3

Make SQL Statements as Efficient as Possible..................................................................... 12-3

Make Function Calls as Efficient as Possible ....................................................................... 12-4

Make Loops as Efficient as Possible...................................................................................... 12-5

Use Built-In String Functions ................................................................................................. 12-5

Put Least Expensive Conditional Tests First........................................................................ 12-5

Minimize Data Type Conversions......................................................................................... 12-5

Use PLS_INTEGER or SIMPLE_INTEGER for Integer Arithmetic .................................. 12-6

Use BINARY_FLOAT, BINARY_DOUBLE, SIMPLE_FLOAT, and SIMPLE_DOUBLE for

Floating-Point Arithmetic 12-6

Avoiding Memory Overhead in PL/SQL Code......................................................................... 12-7

Declare VARCHAR2 Variables of 4000 or More Characters............................................. 12-7

Group Related Subprograms into Packages ........................................................................ 12-7

Pin Packages in the Shared Memory Pool............................................................................ 12-7

Apply Advice of Compiler Warnings................................................................................... 12-7

Collecting Data About User-Defined Identifiers............................................................................ 12-7

Profiling and Tracing PL/SQL Programs .......................................................................................... 12-8

Using the Profiler API: Package DBMS_PROFILER.................................................................. 12-8

Using the Trace API: Package DBMS_TRACE............................................................................ 12-9

Reducing Loop Overhead for DML Statements and Queries with Bulk SQL.......................... 12-9

Running One DML Statement Multiple Times (FORALL Statement) .................................. 12-10

xvii

How FORALL Affects Rollbacks......................................................................................... 12-14

Counting Rows Affected by FORALL (%BULK_ROWCOUNT Attribute) .................. 12-14

Handling FORALL Exceptions (%BULK_EXCEPTIONS Attribute).............................. 12-16

Retrieving Query Results into Collections (BULK COLLECT Clause) ................................. 12-17

Examples of Bulk Fetching from a Cursor ......................................................................... 12-19

Limiting Rows for a Bulk FETCH Operation (LIMIT Clause)......................................... 12-20

Retrieving DML Results Into a Collection (RETURNING INTO Clause) ..................... 12-21

Using FORALL and BULK COLLECT Together............................................................... 12-21

Using Host Arrays with Bulk Binds.................................................................................... 12-22

SELECT BULK COLLECT INTO Statements and Aliasing............................................. 12-22

Writing Computation-Intensive PL/SQL Programs...................................................................... 12-27

Tuning Dynamic SQL with EXECUTE IMMEDIATE Statement and Cursor Variables ....... 12-27

Tuning PL/SQL Subprogram Calls with NOCOPY Hint ............................................................ 12-28

Compiling PL/SQL Units for Native Execution ............................................................................ 12-30

Determining Whether to Use PL/SQL Native Compilation .................................................. 12-30

How PL/SQL Native Compilation Works................................................................................ 12-31

Dependencies, Invalidation, and Revalidation......................................................................... 12-31

Setting Up a New Database for PL/SQL Native Compilation............................................... 12-31

Compiling the Entire Database for PL/SQL Native or Interpreted Compilation............... 12-32

Performing Multiple Transformations with Pipelined Table Functions.................................. 12-34

Overview of Pipelined Table Functions..................................................................................... 12-34

Writing a Pipelined Table Function............................................................................................ 12-35

Using Pipelined Table Functions for Transformations............................................................ 12-36

Returning Results from Pipelined Table Functions................................................................. 12-37

Pipelining Data Between PL/SQL Table Functions................................................................. 12-37

Optimizing Multiple Calls to Pipelined Table Functions........................................................ 12-38

Fetching from Results of Pipelined Table Functions ............................................................... 12-38

Passing Data with Cursor Variables........................................................................................... 12-38

Performing DML Operations Inside Pipelined Table Functions ........................................... 12-41

Performing DML Operations on Pipelined Table Functions.................................................. 12-41

Handling Exceptions in Pipelined Table Functions................................................................. 12-42

13 PL/SQL Language Elements

Assignment Statement ......................................................................................................................... 13-3

AUTONOMOUS_TRANSACTION Pragma ................................................................................... 13-6

Block ........................................................................................................................................................ 13-8

CASE Statement................................................................................................................................... 13-15

CLOSE Statement................................................................................................................................ 13-18

Collection .............................................................................................................................................. 13-19

Collection Method Call ...................................................................................................................... 13-23

Comment............................................................................................................................................... 13-27

Constant ................................................................................................................................................ 13-28

CONTINUE Statement....................................................................................................................... 13-31

Cursor Attribute................................................................................................................................... 13-32

Cursor Variable Declaration.............................................................................................................. 13-34

EXCEPTION_INIT Pragma ............................................................................................................... 13-38

Exception Declaration......................................................................................................................... 13-39

xviii

Exception Handler............................................................................................................................... 13-40

EXECUTE IMMEDIATE Statement ................................................................................................. 13-42

EXIT Statement .................................................................................................................................... 13-45

Explicit Cursor ..................................................................................................................................... 13-47

Expression............................................................................................................................................. 13-51

FETCH Statement................................................................................................................................ 13-60

FORALL Statement............................................................................................................................. 13-63

Function Declaration and Definition .............................................................................................. 13-66

GOTO Statement................................................................................................................................. 13-70

IF Statement.......................................................................................................................................... 13-71

INLINE Pragma ................................................................................................................................... 13-73

Literal..................................................................................................................................................... 13-76

LOOP Statements ................................................................................................................................ 13-79

NULL Statement .................................................................................................................................. 13-84

OPEN Statement.................................................................................................................................. 13-85

OPEN-FOR Statement ........................................................................................................................ 13-87

Parameter Declaration ........................................................................................................................ 13-90

Procedure Declaration and Definition............................................................................................ 13-92

RAISE Statement ................................................................................................................................. 13-94

Record Definition................................................................................................................................ 13-95

RESTRICT_REFERENCES Pragma ................................................................................................. 13-98

RETURN Statement .......................................................................................................................... 13-100

RETURNING INTO Clause ............................................................................................................ 13-102

%ROWTYPE Attribute..................................................................................................................... 13-105

SELECT INTO Statement ................................................................................................................ 13-107

SERIALLY_REUSABLE Pragma..................................................................................................... 13-111

SQL (Implicit) Cursor Attribute..................................................................................................... 13-113

SQLCODE Function ......................................................................................................................... 13-116

SQLERRM Function ......................................................................................................................... 13-117

%TYPE Attribute ............................................................................................................................... 13-119

Variable ................................................................................................................................................ 13-121

14 SQL Statements for Stored PL/SQL Units

ALTER FUNCTION Statement .......................................................................................................... 14-3

ALTER PACKAGE Statement ............................................................................................................ 14-6

ALTER PROCEDURE Statement ...................................................................................................... 14-9

ALTER TRIGGER Statement ........................................................................................................... 14-11

ALTER TYPE Statement .................................................................................................................... 14-14

CREATE FUNCTION Statement...................................................................................................... 14-27

CREATE PACKAGE Statement ........................................................................................................ 14-36

CREATE PACKAGE BODY Statement .......................................................................................... 14-39

CREATE PROCEDURE Statement................................................................................................... 14-42

CREATE TRIGGER Statement ......................................................................................................... 14-47

CREATE TYPE Statement ................................................................................................................. 14-60

CREATE TYPE BODY Statement .................................................................................................... 14-77

DROP FUNCTION Statement .......................................................................................................... 14-82

DROP PACKAGE Statement ............................................................................................................ 14-84

xix

DROP PROCEDURE Statement....................................................................................................... 14-86

DROP TRIGGER Statement ............................................................................................................. 14-87

DROP TYPE Statement ...................................................................................................................... 14-88

DROP TYPE BODY Statement ......................................................................................................... 14-90

A Wrapping PL/SQL Source Code

Overview of Wrapping........................................................................................................................... A-1

Guidelines for Wrapping....................................................................................................................... A-1

Limitations of Wrapping........................................................................................................................ A-2

Wrapping PL/SQL Code with wrap Utility ........................................................................................ A-2

Input and Output Files for the PL/SQL wrap Utility.................................................................. A-3

Running the wrap Utility................................................................................................................. A-3

Limitations of the wrap Utility........................................................................................................ A-4

Wrapping PL/QL Code with DBMS_DDL Subprograms................................................................ A-4

Using DBMS_DDL.CREATE_WRAPPED Procedure.................................................................. A-5

Limitation of the DBMS_DDL.WRAP Function........................................................................... A-6

B How PL/SQL Resolves Identifier Names

What is Name Resolution? .................................................................................................................... B-1

Examples of Qualified Names and Dot Notation............................................................................. B-2

How Name Resolution Differs in PL/SQL and SQL........................................................................ B-4

What is Capture?...................................................................................................................................... B-4

Inner Capture..................................................................................................................................... B-4

Same-Scope Capture......................................................................................................................... B-5

Outer Capture.................................................................................................................................... B-5

Avoiding Inner Capture in DML Statements .................................................................................... B-5

Qualifying References to Attributes and Methods....................................................................... B-6

Qualifying References to Row Expressions................................................................................... B-7

C PL/SQL Program Limits

D PL/SQL Reserved Words and Keywords

Index

剩余711页未读，继续阅读

ksblk87321

粉丝: 0
资源: 1

Oracle Database PL/SQL 11g Language Reference

11g_plsql_user_guide_and_reference.pdf

Oracle11g及PLSQL安装过程1.0..pdf

PLSQL_Developer9.0中文手册.pdf

用PLSQLDeveloper连接Oracle10g.pdf

plsql中文指南.pdf

Plsql操作excel.pdf

SUSE10安装Oracle11g配置oracle自动启动安装配置plsql和ssh.pdf

精通Oracle 10g PLSQL编程.pdf

plsql函数[借鉴].pdf

PLSQL教程.pdf

最新资源