OCP认证考试指南（10）：使用PL/SQL进行Oracle编程

1、编程语言与Oracle数据库

1.1、存储的与匿名的PL/SQL程序块

与存储的PL/SQL程序块相比，匿名的PL/SQL程序块效率较低，此外由于可能在多台机器中公布源代码，还会引发管理问题。

1.2、PL/SQL对象

PL/SQL对象具有下列5种类型：

• 过程
• 函数
• 程序包
• 程序包主体
• 触发器

2、过程、函数以及程序包

2.1、过程与函数

过程（Procedure）是一个执行某些动作的代码块。要运行一个过程，既可以在PL/SQL程序块内部调用这个过程，也可以使用交互式的EXECUTE命令。

函数（Function）与过程的概念相似，但并不具有OUT型实参，并且无法使用EXECUTE命令来调用。函数应当通过RETURN返回一个值。

如果使用的是CREATE命令而不是CREATE OR REPLACE命令，那么在指定对象已存在的情况下必须先删除这个对象。

函数：

^?[Copy to clipboard]View Code PLSQL

SQL> create or replace function odd_even(v1 number)  2  return varchar2  3  as  4  begin  5  if  6  mod(v1,2) = 0  7  then  8  return 'even';  9  else 10  return 'odd'; 11  end if; 12  end odd_even; 13  / Function created. SQL> select odd_even(8) from dual; ODD_EVEN(8)-------------------------------------------------------------------even SQL> select odd_even(9) from dual; ODD_EVEN(9)-------------------------------------------------------------------odd

过程：

^?[Copy to clipboard]View Code PLSQL

SQL> conn system/oracleConnected.SQL> create table integers (  2  C1 number(2),  3  C2 varchar2(5)); Table created. SQL> create or replace procedure ins_ints(v1 in number)  2  as  3  begin  4  for i in 1..v1 loop  5  insert into integers values (i, odd_even(i));  6  end loop;  7  end ins_ints;  8  / Procedure created. SQL> execute ins_ints(5); PL/SQL procedure successfully completed. SQL> select * from integers;         C1 C2---------- -----         1 odd         2 even         3 odd         4 even         5 odd

2.2、程序包

程序包（Package）由规范和主体两个对象组成。

使用CREATE PACKAGE命令可以创建一个程序包规范：

^?[Copy to clipboard]View Code PLSQL

SQL> create or replace package numbers  2  as  3  function odd_even(v1 number) return varchar2;  4  procedure ins_ints(v1 in number);  5  end numbers;  6  / Package created.

使用CREATE PACKAGE BODY创建程序包主体：

^?[Copy to clipboard]View Code PLSQL

SQL> create or replace package body numbers  2  as  3  function odd_even(v1 number)  4  return varchar2  5  as  6  begin  7  if  8  mod(v1,2)=0  9  then 10  return 'even'; 11  else 12  return 'odd'; 13  end if; 14  end odd_even; 15  procedure ins_ints(v1 in number) 16  as 17  begin 18  for i in 1..v1 loop 19  insert into integers values (i, odd_even(i)); 20  end loop; 21  end ins_ints; 22  end; 23  / Package body created.

查看：

^?[Copy to clipboard]View Code PLSQL

SQL> desc numbers;PROCEDURE INS_INTS Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- V1                             NUMBER                  INFUNCTION ODD_EVEN RETURNS VARCHAR2 Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- V1                             NUMBER                  IN

使用：

^?[Copy to clipboard]View Code PLSQL

SQL> execute numbers.ins_ints(5); PL/SQL procedure successfully completed. SQL> select * from integers;         C1 C2---------- -----         1 odd         2 even         3 odd         4 even         5 odd         1 odd         2 even         3 odd         4 even         5 odd 10 rows selected.

3、数据库触发器

常见的触发事件：

• INSERT、UPDATE以及DELETE这些DML命令的执行
• CREATE、DROP、ALTER以及TRUNCATE这些DDL命令的执行
• 会话登入与登出
• 数据库的启动与关闭
• 由于空间问题导致的某条语句的挂起
• 检测到服务器错误

只能通过触发事件来运行触发器。

^?[Copy to clipboard]View Code PLSQL

SQL> create or replace trigger oe_check  2  after insert or update on integers  3  for each row  4  begin  5  if mod(:new.C1, 2) = 0 then  6  dbms_output.put_line(:new.C1 || ' is even ');  7  else  8  dbms_output.put_line(:new.C1 || ' is odd ');  9  end if; 10  end; 11  / Trigger created. SQL> set serveroutput on;SQL> insert into integers values (2, 'odd');2 is even 1 row created. SQL> rollback; Rollback complete. SQL> insert into integers values (3, 'odd');3 is odd 1 row created. SQL> commit; Commit complete.

4、用于PL/SQL的实例参数

PLSQL_V2_COMPATIBILITY（默认FALSE）：为TRUE许可旧版本中合法的异常行为。
PLSQL_DEBUG（默认FALSE）：为TRUE会以某种方式编译PL/SQL代码，有助于调试其他信息。
PLSQL_OPTIMIZE_LEVEL（默认为2）：最高级别为2，支持使用编译器的所有优化功能，提供最佳运行时性能，但可能增加编译时间。低级别提供更快编译，但稍微影响执行性能。大量编译时，可修改。
PLSQL_WARNING（默认DISABLE：ALL）：控制PL/SQL编译器应当显示的消息。
PLSQL_CODE_TYPE（默认INTERPRETED）：默认PL/SQL代码只被编译为字节代码，在调用时被解释。如设置为NATIVE，与后面的PLSQL_NATIVE_LIBRARY_DIR参数一起用，PL/SQL代码预编译为C代码，这种方式可以改善性能。
PLSQL_NATIVE_LIBRARY_DIR（默认NULL）：指定存储本地PL/SQL编译所生成的动态链接库的操作系统路径。

以上参数多数情况下都适用默认设置。