oracle 11g PL/SQL Programming学习一

盛弘雅

　　第一章 概览

PL/SQL历史背景(history and background)

PL/SQL是随着oracle在80年代末发展起来的.最初,其功能还很有限，但是在90年代初它开始提供oracle数据库内置解析器以及依赖于操作系统的编程环境.

你可以在SQL*PLUS的命令行中直接调用PLSQL语言.同样PLSQL也可以直接被外部程序语言调用.

oracle 8 首次引进了对象类型.它使得oracle数据库从一个单纯的关系模型数据库转变为一个对象关系模型的数据库.

在oracle 9i r2版本中开始在集合中使用这些对象类型作为标量变量了.这种实例化SQL对象类型的功能使得oracle对象也能兼容C++\java\c#对象类型,至此PL/SQL

已经拥有面向对象的语言能力.它既是一种过程化的编程语言也是面向对象的编程语言.

PL/SQL开始从解释语言进化成一种原生编译语言在oracle 11g中.也就是现在你可以写PLSQL在一个操作系统的平台上,然后部署它让数据库自己管理它的本地编译.

PS:

最初PLSQL版本和oracle版本不是一致的.PL/SQL 1.0对应oracle 6,PL/SQL 2.X对应oracle 7.X.从oracle 8开始PL/SQL版本开始和数据库版本一致。如:

PL/SQL 11.1对应 oracle 11g release 1 版本.


风格(Architecture)

plsql和sql一样是大小写不敏感的语言.建议使用一种风格的写法并保持它.例如:关键词大写,变量名、字段名等小写.


基础结构块(Basic block structures)

分为有名块和匿名块.

匿名块格式:
[DECLARE]

declaration_statements

BEGIN

execution_statements

[EXCEPTION]

exception_handling_statements

END;
最简单的匿名块:

BEGIN

NULL;

END;

有名块函数原型:
FUNCTION function_name

[( parameter1 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type

, parameter2 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type

, parameter(n+1) [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type )]

RETURN [ sql_data_type | plsql_data_type ]

[ AUTHID {DEFINER | CURRENT_USER}]

[ DETERMINISTIC | PARALLEL_ENABLED ]

[ PIPELINED ]

[ RESULT_CACHE [RELIES ON table_name]] IS

declaration_statements

BEGIN

execution_statements

[EXCEPTION]

exception_handling_statements

END;
有名块存储原型:
PROCEDURE procedure_name

( parameter1 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type

, parameter2 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type

, parameter(n+1) [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type )

[ AUTHID {DEFINER | CURRENT_USER}]

declaration_statements

BEGIN

execution_statements

[EXCEPTION]

exception_handling_statements

END;
　　oracle 10g 新特性

1、新增/修改的内置包(Built-in packages)

Beginning with Oracle 10g Release 2，you can gain access to several new or improved built-in

packages。（以下3个特性从10g r2版本就开始有了）

①DBMS_SCHEDULER：Replaces the DBMS_JOB built in and provides new functionality to

schedule and execute batch jobs。(作业调度方面)

②DBMS_CRYPTO :Now includes the ability to encrypt and decrypt large objects, and to

support globalization across multiple character sets.(加密解密方面)

③DBMS_MONITOR: Delivers an API supporting tracing and statistic gathering by sessions。(会话的跟踪和统计信息)


2、编译警告(Compile-time warnings)

Beginning with Oracle 10g Release 1, you can gain insight into the performance of your PL/SQL

programs by enabling the PLSQL_WARNINGS parameter in your development instances。(也是从10g r1就开始有了)

ALTER SESSION SET plsql_warnings = 'enable:all';--开启会话编译时警告


3、条件编译(Conditional compilation)

10g r2开始,可以使用条件编译.它可以让你在会话级设置变量来包含调试逻辑或专业逻辑.

如:

ALTER SESSION SET PLSQL_CCFLAGS = 'debug:1';--编译时标志变量(debug:1)是不区分大小写的

可以直接设置为TRUE或FALSE,如:

ALTER SESSION SET PLSQL_CCFLAGS ='debug:true';

也可以设置更多条件编译标志:

ALTER SESSION SET PLSQL_CCFLAGS = 'name1:value1 [, name(n+1):value(n+1) ]';

条件编译块:


08:36:19 hr@orcl> ALTER SESSION SET PLSQL_CCFLAGS = 'debug:1';
会话已更改。
已用时间:  00: 00: 00.01
08:36:21 hr@orcl> BEGIN
08:36:44   2       $IF $$DEBUG = 1 $THEN
08:36:44   3        dbms_output.put_line('Debug Level 1 Enabled.');
08:36:44   4       $END  --注意是$END(没有分号),不是END IF;
08:36:44   5     END;
08:36:45   6  /
Debug Level 1 Enabled.
PL/SQL 过程已成功完成。
已用时间:  00: 00: 00.01

　　


条件编译的规则是由SQL解析器控制的,与正常的PL/SQL块是不同的.你不能在SQL对象类型、嵌套表、数组里使用条件编译.

条件编译块里不能使用&占位符和绑定变量.

--在函数里使用条件编译,直接在参数后面加条件编译语句
CREATE OR REPLACE FUNCTION CONDITIONAL_TYPE

(MAGIC_NUMBER $IF $$DEBUG = 1 $THEN SIMPLE_NUMBER $ELSE NUMBER $END)

RETURN NUMBER IS

BEGIN

RETURN MAGIC_NUMBER;

END;
在匿名块或触发器中,条件编译语句只能出现在BEGIN后面.


08:58:53 hr@orcl> DECLARE
09:00:31   2    $IF  $$DEBUG = 1 $THEN I NUMBER := 1;
09:00:31   3    $ELSE I NUMBER := 100;
09:00:31   4    $END
09:00:31   5  BEGIN
09:00:31   6    DBMS_OUTPUT.PUT_LINE(I)
09:00:31   7  END;
09:00:32   8  /
END;
*
第 7 行出现错误:
ORA-06550: 第 7 行, 第 1 列:
PLS-00103: 出现符号 "END"在需要下列之一时：
:= . ( % ;
符号 ";" 被替换为 "END" 后继续。

已用时间:  00: 00: 00.04
09:03:32 hr@orcl> DECLARE
09:04:57   2   I NUMBER;
09:04:57   3  BEGIN
09:04:57   4  $IF  $$DEBUG = 1 $THEN I:=1;
09:04:57   5    $ELSE I:= 100;
09:04:57   6    $END
09:04:57   7    DBMS_OUTPUT.PUT_LINE(I);
09:04:57   8  END;
09:04:58   9  /
1
PL/SQL 过程已成功完成。
已用时间:  00: 00: 00.01

　　

4、数字数据类型(Number datatype behaviors)

从10G R1开始,数据库开始使用机器运算BINARY_INTEGER,INTEGER,INT,NATURAL,NATURALN,PLS_INTEGER,POSTIVE,POSITIVEN,SIGNTYPE数据类型.

也就是说它们和BINARY_INTEGER类型做一样的运算.

开始使用数据精度,而不是小数精度.
　　5、PL/SQL优化器(An optimized PL/SQL compiler)

从10G R1开始,优化器将优化PL/SQL编译.这是缺省的设置.

你可以通过参数PLSQL_OPTIMIZE_LEVEL来取消设置优化器或设置指定的优化级别.
--取消PL/SQL优化

ALTER SESSION SET plsql_optimize_level = 0;

--对指定存储设置指定的优化级别

ALTER PROCEDURE some_procedure COMPILE plsql_optimize_level = 1;

--还原设置

ALTER PROCEDURE some_procedure COMPILE REUSE SETTINGS;
通常情况下,优化后的代码比没有优化的代码运行的快.


6、正则表达式(Regular expressions)

从10G R1开始支持正则表达式.包括:

REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、REGEXP_REPLACE
　　7、引用替代(Quoting alternatives)

从10G R1开始,数据库支持使用另外的方式来替代单引号引用.

--原来的格式

SELECT 'It''s a bird, no plane, no it can''t be ice cream!' AS PHRASE FROM DUAL;

--支持的新格式

SELECT q'(It's a bird, no plane, no it can't be ice cream!)' AS PHRASE FROM DUAL;


8、SET操作符(Set operators)

从10G R1开始,数据库支持嵌套表的SET操作符.

包括MULTISET EXCEPT,MULTISET INTERSECT,MULTISET UNION(类似UNION ALL),MULTISET UNION DISTINCT(类似UNION).
　　9、栈错误跟踪(Stack tracing errors)

从10G R1开始,你可以格式化栈跟踪.使用DBMS_UTILITY.FORMAT_ERROR_BACKTRACE函数产生栈跟踪.(解决WHEN OTHERS捕捉不到异常行的问题)

例如:


10:07:39 hr@orcl> DECLARE
10:07:42   2    LOCAL_EXCEPTION EXCEPTION;
10:07:42   3    FUNCTION NESTED_LOCAL_FUNCTION RETURN BOOLEAN IS
10:07:42   4      RETVAL BOOLEAN := FALSE;
10:07:42   5    BEGIN
10:07:42   6      RAISE LOCAL_EXCEPTION;
10:07:42   7      RETURN RETVAL;
10:07:42   8    END;
10:07:42   9  BEGIN
10:07:42  10    IF NESTED_LOCAL_FUNCTION THEN
10:07:42  11      DBMS_OUTPUT.PUT_LINE('No raised exception');
10:07:42  12    END IF;
10:07:42  13  EXCEPTION
10:07:42  14    WHEN OTHERS THEN
10:07:42  15      DBMS_OUTPUT.PUT_LINE('DBMS_UTILITY.FORMAT_CALL_STACK');
10:07:42  16      DBMS_OUTPUT.PUT_LINE('------------------------------');
10:07:42  17      DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
10:07:42  18      DBMS_OUTPUT.PUT_LINE('DBMS_UTILITY.FORMAT_ERROR_BACKTRACE');
10:07:42  19      DBMS_OUTPUT.PUT_LINE('-----------------------------------');
10:07:42  20      DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
10:07:42  21      DBMS_OUTPUT.PUT_LINE('DBMS_UTILITY.FORMAT_ERROR_STACK');
10:07:42  22      DBMS_OUTPUT.PUT_LINE('-------------------------------');
10:07:42  23      DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
10:07:42  24  END;
10:07:43  25  /
DBMS_UTILITY.FORMAT_CALL_STACK
------------------------------
----- PL/SQL Call Stack -----
object      line  object
handle    number  name
0x2b6a25d50
17  anonymous block

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
-----------------------------------
ORA-06512: 在 line 6
ORA-06512: 在 line 10

DBMS_UTILITY.FORMAT_ERROR_STACK
-------------------------------
ORA-06510: PL/SQL: 用户定义的异常错误未得到处理

PL/SQL 过程已成功完成。
已用时间:  00: 00: 00.03

　　

10、PL/SQL加密存储(Wrapping PL/SQL stored programs)

从10G R2开始,支持工具加密你的PL/SQL存储.(使用DBMS_DDL包的CREATE_WRAPPED存储)

例:


--加密函数hello_world
BEGIN
DBMS_DDL.CREATE_WRAPPED('CREATE OR REPLACE FUNCTION hello_world RETURN STRING AS ' ||
'BEGIN ' || ' RETURN ''Hello World!''; ' ||
'END;');
END;

　　


--查看内容(目前已有此对应的解密方法)
14:07:55 hr@orcl> select text from user_source where name='HELLO_WORLD';
TEXT
-------------------------------------------------------------------------
FUNCTION hello_world wrapped
a000000
354
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
49 7d
L2do7G2dGNOopPFVxha8nxtjC2gwg8eZgcfLCNL+XoVa1/r60JZylpehzOfAsr2ym17ny72b
0sfquHQruMAy/tJeqfQKEql3xUbWd8XlutVTkr5Ugqam6e6UGA==
--调用函数
14:12:03 hr@orcl> SELECT HELLO_WORLD FROM DUAL;
HELLO_WORLD
----------------------------------------------
Hello World!   
--查看定义
14:12:08 hr@orcl> desc hello_world
FUNCTION hello_world RETURNS VARCHAR2


　　

oracle 11g 新特性

1.自动内联子程序(Automatic subprogram inlining)

使用指令PRAGMA INLINE来使得编译器内联编译子程序.


CREATE OR REPLACE PROCEDURE INLINE_DEMO(A NUMBER, B NUMBER) IS
PRAGMA INLINE(ADD_NUMBERS, 'YES');--ADD_NUMBERS为已有的函数名
BEGIN
FOR I IN 1 .. 10000 LOOP
DBMS_OUTPUT.PUT_LINE(ADD_FUNCTION(8, 3));
END LOOP;
END;

　　

当参数PLSQL_OPTIMIZE_LEVEL设置为3的时候,数据库使用自动内联.



2.continue语句(A continue statement)


BEGIN
FOR I IN 1 .. 5 LOOP
DBMS_OUTPUT.PUT_LINE('First statement, index is [' || I || '].');
IF MOD(I, 2) = 0 THEN
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE('Second statement, index is [' || I || '].');
END LOOP;
END;

　　

3.跨会话的函数结果集缓存(A cross-session PL/SQL function result cache)

跨会话函数结果集缓存机制在SGA共享频繁访问的函数.以前每次调用函数,每个会话都会缓存一次.使用下面两种方法缓存结果集:

RESULT_CACHE子句或RESULT_CACHE RELIES_ON(表名)

缓存也是有时间限制的,那种很常时间不活动的缓存会从SGA清除出去.


4.增强的动态SQL(Dynamic SQL enhancements)

两种:

1、本地动态SQL:11G开始,SQL支持的动态SQL可以大于32KB(也就是使用CLOB类型)

如:OPEN cursor_name FOR dynamic_string;(dynamic_string可以是CHAR,VARCHAR2,CLOB,不能是NCHAR、NVARCHAR2)

2、DBMS_SQL内置包:你可以使用PARSE存储解析大于32KB的语句

增加了TO_REFCURSOR和TO_CURSOR_NUMBER两个函数.可以让你把引用游标转换为游标或者把游标转为引用游标.

可以针对用户自定义的集合类型使用批量绑定的操作.


5.混合的、顺序的、按名称的SQL调用方法(Mixed, named, and positional notation SQL calls)

CREATE OR REPLACE FUNCTION ADD_THREE_NUMBERS(A NUMBER := 0,

B NUMBER := 0,

C NUMBER := 0) RETURN NUMBER IS

BEGIN

RETURN A + B + C;

END;

按顺序调用:

BEGIN

DBMS_OUTPUT.PUT_LINE(ADD_THREE_NUMBERS(3, 4, 5));

END;

按名称的调用:

BEGIN

DBMS_OUTPUT.PUT_LINE(ADD_THREE_NUMBERS(C => 4, B => 5, C => 3));

END;

混合调用:(有一定的限制,必须先调用顺序的参数,再调用名称的参数)

BEGIN

DBMS_OUTPUT.PUT_LINE(ADD_THREE_NUMBERS(3, C => 4, B => 5));

END;
　　6.多进程连接池(A multiprocess connection pool)

EJBs得到更好的多进程连接池释放.它正式的居住在连接池(DRCP).这个特性让你管理更多的可扩展的服务端连接池

多进程连接池大大降低数据库层的内存占用,并提高了中间层和数据库层的可扩展性.

一个标准的数据库连接需要4.4MB的物理内存,4MB分配为物理连接,400KB分配为用户会话.

因此专用服务器模式下,500个并发连接将需要大约2.2GB的物理内存(4.4MB*500).同样情况下共享服务器模式仅需要600MB的物理内存.

80%的内存被SGA管理,数据库居留连接池(DRCP)管理的更好,仅需要400MB的物理内存.显然对基于WEB的应用,特别是在使用OCI8做连接的时候,DRCP是非常合适的.

使用DBMS_CONNECTION_POOL包启动、停止、配置连接池参数.(启动/停止需要SYS权限)

EXECUTE DBMS_CONNECTION_POOL.START_POOL();--启动

EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();--停止

必须要在tnsnames.ora文件中配置启用连接共享池.

如:

ORCLCP =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)

(HOST = hostname.domain)

(PORT = port_number)

)

(CONNECT_DATA = (SERVER = POOLED)--POOLED表示支持连接到共享池

(SERVICE_NAME = orcl)

)

)

还要使用11G客户端连接,如果使用旧版本的客户端或者服务端库程序连接,将会得到以下错误信息:

ERROR:

ORA-56606: DRCP: Client version does not support the feature


7.PL/SQL层次分析器(A PL/SQL Hierarchical Profiler)

层次分析器让你看到所有组件如何在应用里执行的.它不像非层次分析器那样简单的记录每个模块的时间花费.

层次分析器做下面的事情:

1.报告动态执行你的PL/SQL程序的剖面,由各子程序调用.

2.分别报告SQL和PL/SQL的执行时间.

3.它不需要特殊源或预编译时间,像PRAGMA一样推荐内联

4.它存储结果在一组数据库表里,你可以使用它们来开发报表或者使用plshprof工具来生成简单的HTML报告.


8.PL/SQL本地编译生成本地代码(That the PL/SQL Native Compiler now generates native code)

在11G版本里本地编译发生了改变.在之前的版本,PL/SQL首先被转换到C语言代码,然后才编译。现在你可以直接编译.

最终代码的执行速度比以前快2倍甚至一个数量级.

ORACLE建议你运行所有的PL/SQL在NATIVE(本地)或INTERPRETED(解释)模式.INTERPRETED模式是缺省设置.


9.PL/Scope

PL/Scope是一个驱动编译池,可以让你检查标识符和它们在数据库的行为.缺省情况下是禁用的.

你可以在会话级或数据库级启用它.

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';--会话级启用

PL/Scope工具不收集统计信息直到你启用它.统计信息让你检查你的程序使用多少标识符.


10.正则增强(Regular expression enhancements)

11G R1增强了正则函数REGEXP_LIKE和REGEXP_INSTR,并介绍了REGEXP_COUNT函数.


11.SIMPLE_INTEGER数据类型(A SIMPLE_INTEGER datatype)

11G介绍了SIMPLE_INTEGER数据类型,它是BINARY_INTEGER的子类型,和BINARY_INTEGER有着同样的取值范围.

与BINARY_INTEGER不同的是,SIMPLE_INTEGER排除了NULL值,并且溢出后是直接截断的.


12.直接在SQL语句中调用序列(Direct sequence calls in SQL statements)

11g最终可以让你直接在SQL中使用.nextval或.currval,而不用SELECT some_sequence.nextval into some_local_variable from dual;