开发者社区> jimbuster> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

Oracle 数据库11g新特性之高效 PL/SQL 编码

简介: Oracle 数据库11g新特性之高效 PL/SQL 编码 转自:http://space.itpub.net/1384/viewspace-217959 自推出以来,PL/SQL 就一直是在 Oracle 数据库中编程的首选语言。
+关注继续查看

Oracle 数据库11g新特性之高效 PL/SQL 编码

转自:http://space.itpub.net/1384/viewspace-217959

自推出以来,PL/SQL 就一直是在 Oracle 数据库中编程的首选语言。经过一段时间的发展,我们看到,由于该语言可以实现越来越多需要较少编码的功能,它已经演变为一个综合的开发平台。Oracle 数据库 11g使得 PL/SQL 编码对程序员更加高效。在本文中,您将通过某些示例简单了解这个新功能。

复合触发器

请考虑一个宾馆数据库:宾馆房间的预订记录在名为 BOOKINGS 的表中。您还希望将对该表的更改记录到一个跟踪表 — 有些类似于审计,但稍有不同:您希望该操作是事务性的。在这种情况下,触发器非常适用。

您可以使用一个小型的 after-update 行触发器,将旧值和新值连同更改者一起记录到 BOOKINGS_HIST 表中。到目前为止,一切都没问题。

但这里有一个小问题。after-update 行触发器将针对每一行触发,但某些预订是批量更改的,在一个事务中更新几百行。单独的 after-update 行触发器针对这些行中的每一行触发,并且每个执行在 bookings_hist 表中插入一条记录,因此性能不是最佳的。

更好的方法是批处理这些插入,并将它们批量插入 bookings_hist 表。您可以使用一系列复杂的触发器来完成这个任务。方法是:在行触发器中,将要插入 bookings_hist 表的值放到一个集合中,然后在 after-update-statement 触发器(只触发一次)中,将集合中的数据加载到 bookings_hist 表。由于实际插入只发生一次,因此该过程比在每一行上插入要快。

但它们是不同代码片段中的两个不同的触发器。将集合变量从一个触发器传递到另一个触发器的唯一方式是,在程序包规范中创建一个带有集合变量(如 VARRAY 或 PL/SQL TABLE)的程序包,在 after-update 行触发器上填充该程序包,然后在 after-statement 触发器上读取它 — 这可不是一项简单的任务。相反,如果您将所有触发器都放到一个代码片段中,不是更简单吗?

在 Oracle 数据库 11g中,您可以这么做(使用复合触发器)。复合触发器实际上是作为一个整体定义的四个不同的触发器。例如,UPDATE 复合触发器将 before statement、before row、after statement 和 after row 都合并到一个复合触发器中。这是一个单一代码片段,因此您可以像任何其他单一 PL/SQL 代码一样来传递变量。

下面我们来考虑一个示例。添加了行编号,以帮助说明。
1  create or replace trigger tr_bookings_track
2  for update of booking_dt
3  on bookings
4  compound trigger
5      type ty_bookings_hist is table of bookings_hist%rowtype
6          index by pls_integer;
7      coll_bookings_hist          ty_bookings_hist;
8      ctr                         pls_integer := 0;
9  before statement is
10  begin
11      dbms_output.put_line('In before statement');
12  end before statement;
13  before each row is
14  begin
15      dbms_output.put_line('In before each row');
16  end before each row;
17  after each row is
18  begin
19      ctr := ctr + 1;
20      dbms_output.put_line('In after each row. booking_id='||:new.booking_id);
21      coll_bookings_hist(ctr).booking_id := :new.booking_id;
22      coll_bookings_hist(ctr).mod_dt := sysdate;
23      coll_bookings_hist(ctr).mod_user := user;
24      coll_bookings_hist(ctr).old_booking_dt := :old.booking_dt;
25      coll_bookings_hist(ctr).new_booking_dt := :new.booking_dt;
26  end after each row;
27  after statement is
28  begin
29      dbms_output.put_line('In after statement');
30      forall counter in 1..coll_bookings_hist.count()
31          insert into bookings_hist
32          values coll_bookings_hist(counter);
33  end after statement;
34  end tr_bookings_track;
为了更好地了解触发器的工作方式,我们来执行一个示例更新操作,该操作将更新四行。
update bookings
set booking_dt = sysdate
where booking_id between 100 and 103;
输出如下:
In before statement
In before each row
In after each row. booking_id=100
In before each row
In after each row. booking_id=101
In before each row
In after each row. booking_id=102
In before each row
In after each row. booking_id=103
In after statement
注意复合触发器的操作方式。粗略地说,它具有四个部分:

Before Statement
...在语句前执行一次...
Before Row
...在操作前每行执行一次...
After Row
...在操作后每行执行一次...
After Statement
...每条语句执行一次...

您将看到,该代码是单一代码片段,但每个部分都在不同的点执行。

在前面的示例中,我将 dbms_output 语句放在了不同的点上,以显示每个部分沿着哪些点执行。我更新了四行,其 booking_id 是 100、101、102 和 103,您可以看到,它调用了 before-statement 和 after-statement 触发器(每个一次)以及行触发器(before 和 after,每行一次)。(在前面的示例中,不需要 before-statement 或 before-row 触发器,但我还是将它们放在那里以演示该功能。)

如果您查看 bookings_hist 表,将看到现在有四条记录(每个 booking_id 一条),但这四条记录是在语句末尾批量插入的,而不是针对每一行更新:

BOOKING_ID MOD_DT    MOD_USER                       OLD_BOOKI NEW_BOOKI
---------- --------- ------------------------------ --------- ---------
100 27-SEP-07 ARUP                           28-AUG-07 27-SEP-07
101 27-SEP-07 ARUP                           06-AUG-07 27-SEP-07
102 27-SEP-07 ARUP                           04-SEP-07 27-SEP-07
103 27-SEP-07 ARUP                           15-JUN-07 27-SEP-07

复合触发器的一个真正有用的功能是,PL/SQL 代码中的状态对象(如变量、程序包等)在触发器被触发时进行实例化,而在触发器触发结束后,状态将被清除干净。在上面的示例中,您可以看到我既没有初始化集合,也没有删除集合中的内容。所有这些都是自动完成的,不需要我干预。

触发器中的按序执行

自 Oracle8 以来,您就能够在一个表上定义同一类型的多个触发器 — 例如,两个都是在同一个表的每行后执行插入操作的触发器。触发器的类型决定了执行顺序:before statement、before row、after statement 和 after row。但是,如果您有两个 after-row 触发器(T1 和 T2),应该先触发哪个呢?

同一类型的触发器的执行有些随机,或者至少不保证遵循某个模式。这会引发问题吗?我们来看一个名为 PAYMENTS 的表的示例,如下所示:

Name                                      Null?Type
 ----------------------------------------- -------- ----------------------------
PAY_ID                                             NUMBER(10)
CREDIT_CARD_NO                                     VARCHAR2(16)
AMOUNT                                             NUMBER(13,2)
PAY_MODE                                           VARCHAR2(1)
RISK_RATING                                        VARCHAR2(6)
FOLLOW_UP                                          VARCHAR2(1)
需要根据支付类型和数量来计算风险率,并将其存储在 RISK_RATING 列中。下面这个简单的 before update 行触发器很好地完成了这个任务:
create or replace trigger tr_pay_risk_rating
before update
on payments
for each row
begin
dbms_output.put_line ('This is tr_pay_risk_rating');
if (:new.amount) < 1000 then
:new.risk_rating := 'LOW';
elsif (:new.amount < 10000) then
if (:new.pay_mode ='K') then
:new.risk_rating := 'MEDIUM';
else
:new.risk_rating := 'HIGH';
end if;
else
:new.risk_rating := 'HIGH';
end if;
end;
/
现在,假设某人增加了另一个要求:应该标记某些基于 RISK_RATING、PAY_MODE 等列的项,以便在名为 FOLLOW_UP 的新列中跟踪。您可能已经修改了上述触发器,但保留现有代码不变并创建同一类型的新触发器(before update 行)始终是一个良好的策略,如下所示。(我已经将 dbms_output 语句放在代码中,以演示触发器的触发方式。)
create or replace trigger tr_pay_follow_up
before update
on payments
for each row
begin
dbms_output.put_line ('This is tr_pay_follow_up');
if (
(:new.risk_rating = 'HIGH' and :new.pay_mode = 'C')
or (:new.risk_rating = 'MEDIUM' and :new.pay_mode = 'K')
or (substr(:new.credit_card_no,1,5) = '23456')
) then
:new.follow_up := 'Y';
else
:new.follow_up := 'N';
end if;
end;

/
现在,如果您更新表:
SQL> get upd_pay
1  update payments set
2     credit_card_no = '1234567890123456',
3     amount = 100000,
4*    pay_mode = 'K'

SQL> @upd_pay
This is tr_pay_follow_up

This is tr_pay_risk_rating
 
1 row updated.

SQL> select * from payments;
 
PAY_ID CREDIT_CARD_NO       AMOUNT P RISK_R F
---------- ---------------- ---------- - ------ -
1 1234567890123456     100000 C HIGH   N
发生了什么事?risk_rating 列是 HIGH,pay_mode 列是“C”,这意味着,FOLLOW_UP 列应该是“Y”,但它却是“N”。为什么?要回答这个问题,请看一下触发器的触发顺序:tr_pay_follow_up 在 tr_pay_risk_rating 之前触发。后者将列值设为高风险。这样,当前者触发时,risk_rating 列值为空(或“N”),因此它认为条件满足。

在这种情况下,触发器的执行顺序非常重要。如果 tr_pay_risk_rating 没有在另一个触发器之前触发,就无法设置正确的变量,并且设置将无法正确实现要求。在以前,唯一可能的方法是,将所有逻辑放在一段代码中,并通过在代码中对逻辑进行排序来强制执行。

在 Oracle 数据库 11g中,您可以在触发器创建脚本中放置一条子句,来强制对触发器进行排序。以下是带有该子句的触发器的上半部分:

create or replace trigger tr_pay_follow_up
before update
on payments
for each rowfollows tr_pay_risk_ratingbegin
... and so on ...

该子句 (FOLLOWS <triggerName>) 强制触发器在指定触发器之后触发。您可以通过运行在前面看到的更新脚本来进行测试

SQL> @upd_pay
This is tr_pay_risk_rating
This is tr_pay_follow_up
 
1 row updated.
 
SQL> select * from payments;
 
PAY_ID CREDIT_CARD_NO       AMOUNT P RISK_R F
---------- ---------------- ---------- - ------ -
1 1234567890123456     100000 C HIGH   Y
 
1 row selected.
列按照预期方式正确填充。另外,注意触发器的正确排序,以确定您打算执行的操作。

触发器排序可让您获得模块化代码的优势,同时确保它们能够以正确的顺序执行。

如果什么都不做,使用 CONTINUE

在全部功能中,直到现在,PL/SQL 还缺少一个重要语法:如何指示它什么都不做,转至循环结尾,然后再次循环。

在 Oracle 数据库 11g中,PL/SQL 具有一个名为 CONTINUE 的新结构,可在循环中使用。该语句可将逻辑移到循环结尾,然后再移到循环开头。下面是一个小型示例,演示了当计数器不是 10 的倍数时,控制如何移到循环结尾。

begin
for ctr in 1..100 loop
continue when mod(ctr,10) != 0;
dbms_output.put_line ('ctr='||ctr);
end loop;
end;
/
输出如下:
ctr=10
ctr=20
ctr=30
... and so on ...
CONTINUE 的另一个变体是使用循环名称。
begin
<<OuterLoop>>
for outer in 1..10 loop
dbms_output.put_line ('-> uter='||outer);
for inner in 1..10 loop
continue OuterLoop when mod(inner,3) = 0;
dbms_output.put_line ('..-> inner='||inner);
end loop;
end loop;
end;
/
输出如下:
-> uter=1
..-> inner=1
..-> inner=2
-> uter=2
..-> inner=1
..-> inner=2
-> uter=3
..-> inner=1
..-> inner=2
... and so on ...
如果不使用静态结构,如 mod(inner,3),您还可以使用执行某种计算的函数。
begin
<<OuterLoop>>
for outer in 1..10 loop
dbms_output.put_line ('-> uter='||outer);
for inner in 1..10 loop
continue OuterLoop when (myfunc = 1);
dbms_output.put_line ('..-> inner='||inner);
end loop;
end loop;
end;
/

勿庸置疑,您只能在循环内(它只有在这里才有意义)使用这个结构。如果您尝试在循环外使用它,将出现编译器错误。

井井有条的序列

以前,如果您需要在 PL/SQL 程序中使用序列,则在该版本推出之前,您需要使用 SELECT <Seq>.NEXTVAL INTO <VariableName> FROM DUAL 之类的结构。

declare
trans_id number(10);
begin
select myseq.nextval
into trans_id
from dual;
end;
现在不再需要这样了。您可以将序列的下一个值直接赋值给一个变量:
declare
trans_id number(10);
begin
trans_id := myseq.nextval;
end;
/
这就是我所说的简单性。

When OTHERS Then 执行某些操作

许多 PL/SQL 程序员都采用忽略 OTHERS 异常的危险做法,如下所示:

when OTHERS then
NULL;
这就好像说“当错误发生时,什么也不做;只要忽略或假装它从未发生过,它就不会再发生了。”如果世界有这么简单就好了!这个做法可能会导致漏洞百出、不稳定的代码。

Oracle 数据库 11g在这方面很有帮助。它具有一个名为 PLW-06009 的新警告,可以在编译时向您警告此类问题。下面是一个例子。

create or replace procedure myproc as
l_dummy varchar2(1);
begin
select dummy
into l_dummy
from dual;
exception
when OTHERS then
null;
end;
在您编译该过程时,它编译良好,没有出现任何警告,就像以前使用 10g时一样好。要启用这个警告,您必须设置该会话参数。
SQL> alter session set plsql_warnings = 'enable:all'
  2  /
 
Session altered.
 
SQL> @others1
 
SP2-0804:Procedure created with compilation warnings
 
SQL> show error
Errors for PROCEDURE MYPROC:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/7      PLW-06009:procedure "MYPROC" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR
请注意在编译期间引发的新警告 PLW-06009。注意,这只是一个警告;编译顺利进行。您可以执行该过程,但应考虑警告状况!

禁用触发器

在可用性较高的生产系统中,您经常会看到一个用于应用更改的狭窄的更改窗口。以下是这些环境中常见的、令人沮丧的“Catch-22”案例:您希望在表中添加一个触发器以便执行脚本,但在更改窗口中创建触发器时,会由于某个愚蠢的、可避免的原因(例如,缺少同义词)而出现编译错误。您希望以前已经创建了触发器,但在您创建触发器时,它是启用状态,这无法在更改窗口以外进行操作。您可以做些什么?

在 Oracle 数据库 11g中,这种情况不再是一个问题;您可以创建一个最初为禁用状态的触发器,从而允许您测试所有编译错误。稍后,在更改窗口中,您可以启用它。以下是该触发器的创建方法:

create or replace trigger tr_t
after insert on t
for each rowdisablebegin
insert into t1 (a) values (:new.col_a);
end;
/
现在,如果您检查状态:SQL> select status 2> from user_triggers 3> where trigger_name = 'TR_T' 4> / STATUS -------- DISABLED

即使创建的触发器为禁用状态,它也不能带有错误。因此,如果您尝试创建带有错误的触发器(例如,使用不存在的“M”表):
1  create or replace trigger tr_t
2  after insert on t
3  for each row
4  disable
5  begin
6    insert into m (a) values (:new.col_a);
7* end;
SQL> /
 
Warning:Trigger created with compilation errors.
 
SQL> show error
Errors for TRIGGER TR_T:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3      PL/SQL:SQL Statement ignored
2/15     PL/SQL:ORA-00942:table or view does not exist
对于更改控制过程来说,该特性非常有用。另一个出色的应用是在特定点启用触发器。例如,假设您要使用触发器构建一个审计解决方案,而 audit_table 的旧记录尚未清除。您可以先将触发器创建为禁用状态,并在稍后表就绪时启用它。

函数中的参数名

请考虑以下简单函数:

create or replace function myfunc
(
p_param1        number,
p_param2        number
)
return number
is
begin
return p_param1 + p_param2;
end;
/
该函数执行的操作很简单,但足以说明概念。由于有两个参数,您可以通过两个方法来调用函数:即,将参数作为位置值传递,如:
myfunc (1,2)
或者,作为命名参数传递:
myfunc ( p_param1 => 1, p_param2 => 2)
但是,如果在 select 语句中使用,后者会导致问题出现。在 Oracle 数据库 10g中,如果您执行以下语句:
SQL> select myfunc (p_param1=>1,p_param2=>1) from dual;
将出现错误:
select myfunc (p_param1=>1,p_param2=>1) from dual
                       *
ERROR at line 1:
ORA-00907:missing right parenthesis
在 Oracle 数据库 11g中,您可以随意使用以下表示法:
SQL> select myfunc (p_param1=>1,p_param2=>1) from dual;
 
MYFUNC(P_PARAM1=>1,P_PARAM2=>1)
-------------------------------
                              2
 
1 row selected.
...这非常有效。您可以为后一个指定命名表示法;第一个必须是位置值。例如,以下语句有效,其中,参数 p_param1 设为 1:
select myfunc (1,p_param2=>2) from dual
但是,以下语句无效(位置参数在结尾):
SQL> select myfunc (p_param1=>1,2) from dual;

select myfunc (p_param1=>1,2) from dual
       *
ERROR at line 1:
ORA-06553:PLS-312:a positional parameter association may not follow a named association

动态游标和 REF CURSOR 的可交换性

您一定知道本机动态游标的作用是多么重要,特别是在进行调用之前不知道要查询的确切内容的情况下。您可能还通过 DBMS_SQL 使用过动态 PL/SQL。这两种方法均有其各自的优势。但是,如果您开始使用其中一种方法开发程序,稍后又希望切换到另一种方法,该怎么办呢?

在 Oracle 数据库 11g中,这个过程相当简单。所提供的程序包 DBMS_SQL 具有一个新函数 TO_REFCURSOR,该函数可将 DBMS_SQL 动态游标转换为 ref cursor。以下是此类转换的一个示例:

1  create or replace procedure list_trans_by_store
  2  (
3     p_store_id number
  4  )
5  is
6     type num_tab is table of number index by binary_integer;
7     type type_refcur is ref cursor;
8     c_ref_trans_cur type_refcur;
9     c_trans_cur     number;
10     trans_id        num_tab;
11     trans_amt       num_tab;
12     ret             integer;
13     l_stmt          clob;
14  begin
15     c_trans_cur := dbms_sql.open_cursor;
16     l_stmt :=
17         'select trans_id, trans_amt from trans where store_id = :store_id';
18     dbms_sql.parse(c_trans_cur, l_stmt, dbms_sql.native);
19     dbms_sql.bind_variable(c_trans_cur, 'store_id', p_store_id);
20     ret := dbms_sql.execute(c_trans_cur);
21     c_ref_trans_cur := dbms_sql.to_refcursor(c_trans_cur);
22     fetch c_ref_trans_cur bulk collect into trans_id, trans_amt;
23     for ctr in 1 .. trans_id.count loop
24         dbms_output.put_line(trans_id(ctr) || ' ' || trans_amt(ctr));
25     end loop;
26     close c_ref_trans_cur;
27* end;
假设您希望编写一个通用过程,但您在编译时不知道 select 子句中的列列表。这就是本机动态 SQL 的用武之地;您可以为此定义一个 ref cursor。现在,要使其更有趣,假设您也不知道绑定变量,这样 dbms_sql 将更加适用。如何以最少的代码完成这个复杂要求呢?很简单:只需在开始使用 dbms_sql 完成绑定部分,然后稍后再针对另一部分将其转换为 ref cursor。

同样,如果您希望将本机动态 SQL 转换为 REF CURSOR,需要调用另一个函数 TO_CURSOR_NUMBER:

cur_handle := dbms_sql.to_cursor_number (c_ref_cur);
在进行该调用之前,必须先打开 c_ref_cur 变量指定的 ref cursor。进行该调用之后,ref cursor 的使命就完成了;可以将其仅作为一个 dbms_sql 游标来操纵。

假设您在编译时知道绑定,但不知道选择列表;您可以在开始通过 ref cursor 使用本机动态 sql,稍后将其更改为 dbms_sql,以描述并通过游标获取列。

结论


您可以看到,Oracle 数据库 11g包含了多项改进,可以帮助您编写高效的 PL/SQL 代码。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
ORACLE 10G 新特性(INCREMENTALLY UPDATED BACKUPS)学习
原创 转载请注明出处     10g中添加很多新特性 当然RMAN 也得到了增强,INCREMENTALLY UPDATED BACKUPS就是其中之一。      此特性有别于传统的增量备份,主要是为了节省恢复时间,他通过合并COPY和增量BACKUPSE...
796 0
连接 oracle 数据库|学习笔记
快速学习连接 oracle 数据库
80 0
Oracle 数据库性能优化3日实战(企业培训)
课程名称一: Oracle性能优化及调整 课程时长 1天 课程深度: 高级 上机实验: 10%-30% 授课对象: Oracle开发人员、Oracle数据库管理人员,应用程序开发人员 课程描述: 本课程讲述Oracle数据库物理层规划,系统性能的监控,数据库性能参数调整,统计信息的收集,使用自动化调试工具优化数据库,I/O子系统的配置与设计以及性能优化方法论等。
1819 0
如何连接别人电脑上的ORACLE数据库
如何连接别人电脑上的ORACLE数据库 (以oracle9i版本为例,本机必须安装oralce9i的客户端) 第一步:orahome92-configuration and migration tools- net managers 第二步:在“服...
881 0
[20141024]使用emacs连接使用oracle数据库
[20141024]使用emacs连接使用oracle数据库.txt --前一阵子跟别人学习了使用emacs连接数据库.自己做一个简单记录. 1.首先需要安装emacs.
935 0
C# VS2010中,用微软自带的System.Data.OracleClient来连接Oracle数据库
由于微软在.Net框架4.0中已经决定撤销使用System.Data.OracleClient,造成在VS2010中无法连接Oracle数据库,但它还依旧存在于.Net架构中,我们可以通过自己引用  C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.OracleClient.dll 来获得对Oracle数据库的
1953 0
C#连接Oracle数据库
本文演示了如何在C#中连接Oracle数据库。 string sqlSql = "SELECT * FROM A"; //从配置文件中获取连接字符串信息 OracleConnection conn = new OracleConnection(ConfigurationManager.
849 0
+关注
jimbuster
从事数据库管理和运维
280
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载