[20060407發(fā)表于blog.csdn.net，20090929重新編輯]
重新編輯注釋?zhuān)河捎谔脹](méi)有使用PL/SQL，如今再看這篇筆記，幾乎完全陌生了，自己還到baidu去搜索了一下PL/SQL和Pro*C才依稀記起，我不知道以后還有沒(méi)有機(jī)會(huì)使用PL/SQL，但無(wú)疑的是這篇筆記還是有點(diǎn)價(jià)值的，或者作為一個(gè)紀(jì)念吧。PL/SQL（準(zhǔn)確的寫(xiě)法就是“PL/SQL”，沒(méi)有別的，不是Plus SQL，也不是SQL Plus）是Oracle對(duì)SQL的擴(kuò)展，也就是說(shuō)，PL/SQL只能用于Oracle，所以不要指望代碼能移植給MS SQL Server用，另外我印象比較深刻的是PL/SQL的語(yǔ)法很不嚴(yán)謹(jǐn)，bug比較多，用之前請(qǐng)三思，再有一點(diǎn)，這些代碼怎么用？就像C語(yǔ)言，要有編譯連接器生成可執(zhí)行文件一樣，這些PL/SQL代碼也是需要編譯器的，不用說(shuō)，它的編譯器一定是Oracle提供的，所以你得先安裝Oracle的客戶(hù)端，利用上面的調(diào)試工具調(diào)試。我以前是在Pro*C（在C代碼中嵌入PL/SQL，利用Oracle提供的預(yù)編譯生成C，然后才使用C的編譯器進(jìn)行下去）中使用PL/SQL的。
程序不區(qū)分大小寫(xiě)
1、SQL*Plus
SQL*Plus（Oracle客戶(hù)端提供）這個(gè)程序可以用來(lái)執(zhí)行腳本，執(zhí)行腳本前先設(shè)置假脫機(jī)文件，這樣就可以方便地查看出錯(cuò)之處了。或者使用PL/SQL Developer的Command窗口來(lái)執(zhí)行腳本。
2、代碼中變量的聲明
set serveroutput on; --設(shè)置輸出
DECLARE
lv_ord_date DATE; --日期類(lèi)型
lv_last_txt VARCHAR2(25):='Jiang'; --可變字符串類(lèi)型，并初始化
lv_qty_num CONSTANT NUMBER(2):=20; --2位整數(shù)，常量，初始化
lv_shipflag_bln BOOLEAN:=FALSE; --布爾型，初始化為FALSE
lv_name_txt CHAR(10) NOT NULL:='CCC'; --定長(zhǎng)字符串類(lèi)型，不能為空，初始化
BEGIN
dbms_output.put_line (lv_qty_num); --這里一定要寫(xiě)點(diǎn)什么東西才行
END;
3、簡(jiǎn)單查詢(xún)
declare
lv_basket_num number(3);
lv_created_date date;
lv_qty_num number(2);
lv_sub_num number(5,2);
lv_days_num number(3);
lv_shopper_num number(3):=25;
begin
select idbasket, dtcreated, quantity, subtotal
into lv_basket_num, lv_created_date, lv_qty_num, lv_sub_num
from bb_basket
where idshopper = lv_shopper_num
and orderplaced = 0;
lv_days_num:=sysdate-lv_created_date;
dbms_output.put_line(lv_basket_num||' * '||lv_created_date
||' * '||lv_qty_num||' * '||lv_sub_num||' * '||lv_days_num);
end;
4、%type的使用
declare
lv_basket_num bb_basket.idbasket%type; --與bb_basket.idbasket字段同類(lèi)型
lv_created_date bb_basket.dtcreated%type;
lv_qty_num bb_basket.quantity%type;
lv_sub_num bb_basket.subtotal%type;
lv_days_num number(3);
begin
select idbasket, dtcreated, quantity, subtotal
into lv_basket_num, lv_created_date, lv_qty_num,lv_sub_num
from bb_basket
where idshopper=25
and orderplaced = 0;
lv_days_num:= sysdate-lv_created_date;
dbms_output.put_line(lv_basket_num || ' * ' ||
lv_created_date || ' * ' || lv_qty_num || ' * ' ||
lv_sub_num || ' * ' || lv_days_num);
end;
5、附加說(shuō)明
要執(zhí)行declare聲明的過(guò)程，需要使用“/”。而其它不需要。
在SQL*Plus窗口中直接鍵入“/”，表示執(zhí)行最近declare的過(guò)程。
6、復(fù)合數(shù)據(jù)類(lèi)型：RECORD
DECLARE
TYPE type_basket IS RECORD (
basket bb_basket.idBasket%TYPE,
created bb_basket.dtcreated%TYPE,
qty bb_basket.quantity%TYPE,
sub bb_basket.subtotal%TYPE);
rec_basket type_basket;
lv_days_num NUMBER(3);
lv_shopper_num NUMBER(3) := 25;
BEGIN
SELECT idBasket, dtcreated, quantity, subtotal
INTO rec_basket
FROM bb_basket
WHERE idShopper = lv_shopper_num
AND orderplaced = 0;
lv_days_num := SYSDATE - rec_basket.created;
DBMS_OUTPUT.PUT_LINE(rec_basket.basket);
DBMS_OUTPUT.PUT_LINE(rec_basket.created);
DBMS_OUTPUT.PUT_LINE(rec_basket.qty);
DBMS_OUTPUT.PUT_LINE(rec_basket.sub);
DBMS_OUTPUT.PUT_LINE(lv_days_num);
END;
7、復(fù)合數(shù)據(jù)類(lèi)型：%ROWTYPE的使用
DECLARE
rec_shopper bb_shopper%rowtype;
BEGIN
SELECT *
INTO rec_shopper
FROM bb_shopper
WHERE idShopper = 25;
DBMS_OUTPUT.PUT_LINE(rec_shopper.lastname);
DBMS_OUTPUT.PUT_LINE(rec_shopper.address);
DBMS_OUTPUT.PUT_LINE(rec_shopper.email);
END;
“%rowtype”能根據(jù)表的結(jié)構(gòu)自動(dòng)創(chuàng)建rec_shopper這種數(shù)據(jù)類(lèi)型，十分方便。
8、復(fù)合數(shù)據(jù)類(lèi)型：記錄集
定義成“is table”就想當(dāng)于一個(gè)記錄集，存在行數(shù)。
另外：聲明全局變量：
SQL>VARIABLE g_row NUMBER
注意，不需要“;”，也不需要“/”，使用變量的時(shí)候前面加上“:”。
variable g_row number
variable g_prod number
variable g_price number
variable g_qty number
variable g_opt1 number
variable g_opt2 number
begin
:g_row := 1;
:g_prod := 7;
:g_price := 10.8;
:g_qty := 2;
:g_opt1 := 2;
:g_opt2 := 3;
end;
/
declare
type type_basketitem is table of bb_basketitem%rowtype
index by binary_integer;
tbl_basketitems type_basketitem;
begin
tbl_basketitems(:g_row).idproduct := :g_prod;
tbl_basketitems(:g_row).price := :g_price;
tbl_basketitems(:g_row).quantity := :g_qty;
tbl_basketitems(:g_row).option1 := :g_opt1;
tbl_basketitems(:g_row).option2 := :g_opt2;
dbms_output.put_line(:g_row);
:g_row := :g_row + 1;
dbms_output.put_line(:g_row);
dbms_output.put_line(:g_prod);
end;
/
9、隱式游標(biāo)SQL%ROWCOUNT SQL%FOUND SQL%NOTFOUND
begin
update bb_product
set stock = stock + 25
where idProduct in (1, 2, 3);
if sql%notfound then
dbms_output.put_line('Not found.');
end if;
if sql%found then
dbms_output.put_line('Completed '||sql%rowcount||' rows.');
end if;
end;
/
10、游標(biāo)的使用
variable g_basket number
begin
:g_basket:=6;
end;
/
declare
cursor cur_basket is
select bi.idbasket, p.type, bi.price, bi.quantity
from bb_basketitem bi inner join bb_product p on bi.idproduct=p.idproduct
where bi.idbasket = :g_basket;
type type_basket is record
(
basket bb_basketitem.idbasket%type,
type bb_product.type%type,
price bb_basketitem.price%type,
qty bb_basketitem.quantity%type
);
rec_basket type_basket;
lv_rate_num number(2,2);
lv_tax_num number(4,2):=0;
begin
open cur_basket;
loop
fetch cur_basket into rec_basket;
exit when cur_basket%notfound;
dbms_output.put_line('Every record:' || rec_basket.basket||' '||
rec_basket.type||' '||rec_basket.price||' '||rec_basket.qty);
if rec_basket.type = 'E' then
lv_rate_num := .05;
end if;
if rec_basket.type = 'C' then
lv_rate_num := .03;
end if;
lv_tax_num := lv_tax_num +((rec_basket.price * rec_basket.qty) *
lv_rate_num);
end loop;
close cur_basket;
dbms_output.put_line(lv_tax_num);
end;
/
游標(biāo)除了上面的直接使用loop...end loop與fetch的結(jié)合來(lái)使用記錄集之外，還有下面的方式：
declare
cursor cur_prod is
select type, price
from bb_product
where active=1
for update nowait;
--另一會(huì)話已經(jīng)鎖定了游標(biāo)正在檢索的行時(shí)，不必等待，繼續(xù)往下。
--另外可寫(xiě)成：for update of type, price nowait;指定鎖定的列。
lv_sale bb_product.saleprice%type;
begin
for rec_prod in cur_prod loop
if rec_prod.type = 'C' then
lv_sale:=rec_prod.price * 0.8;
end if;
if rec_prod.type = 'E' then
lv_sale := rec_prod.price * 0.85;
end if;
update bb_product
set saleprice = lv_sale
where current of cur_prod;
--dbms_output.put_line('Updated record:' || sql%rowcount);
end loop;
commit;
end;
上面兩個(gè)例子都是在declare中聲明并定義游標(biāo)，下面的例子則是：仍然在declare中聲明，但是定義放在begin...end中。
declare
type type_curvar is ref cursor;
cv_prod type_curvar;
rec_basket bb_basket%rowtype;
rec_shipping bb_shipping%rowtype;
begin
dbms_output.put_line('################ bb_basket ################');
open cv_prod for select * from bb_basket;
loop
fetch cv_prod into rec_basket;
exit when cv_prod%notfound;
dbms_output.put_line(rec_basket.idbasket || ' ' ||
rec_basket.quantity || ' ' || rec_basket.idshopper || ' ' ||
rec_basket.orderplaced);
end loop;
dbms_output.put_line('############### bb_shipping ###############');
open cv_prod for select * from bb_shipping;
loop
fetch cv_prod into rec_shipping;
exit when cv_prod%notfound;
dbms_output.put_line(rec_shipping.idrange || ' ' ||
rec_shipping.low || ' ' || rec_shipping.high || ' ' ||
rec_shipping.fee);
end loop;
end;
關(guān)于游標(biāo)的，講了好多啊……沒(méi)辦法，游標(biāo)就是很重要的概念。
11、變量定義與范圍示例
declare
lv_one number(2):=10;
lv_two number(2):=20;
begin
declare
lv_one number(2):=30;
lv_three number(2):=40;
begin
lv_one:=lv_one+10;
lv_two:=lv_two+10;
dbms_output.put_line('Nested lv_one='||lv_one);
dbms_output.put_line('Nested lv_two='||lv_two);
dbms_output.put_line('Nested lv_three='||lv_three);
end;
lv_one:=lv_one+10;
lv_two:=lv_two+10;
dbms_output.put_line('Enclosing lv_one='||lv_one);
dbms_output.put_line('Enclosing lv_two='||lv_two);
end;
塊頭declare處定義的變量只在當(dāng)塊中生效。如果塊頭沒(méi)有定義，但塊體中使用了，程序就把變量當(dāng)作外部變量。
12、if/elsif語(yǔ)句
非常有意思，這里用的不是elseif，而是elsif，千萬(wàn)別寫(xiě)錯(cuò)了。
declare
type type_order is record(
basket bb_basket.idbasket%type,
sub bb_basket.subtotal%type,
state bb_basket.shipstate%type);
rec_order type_order;
lv_tax_num number(4,2):=0;
begin
select idbasket, subtotal, shipstate
into rec_order
from bb_basket
where idbasket=6;
if rec_order.state='VA' then
lv_tax_num:=rec_order.sub*0.06;
elsif rec_order.state='ME' then
lv_tax_num:=rec_order.sub*0.05;
elsif rec_order.state='NY' then
lv_tax_num:=rec_order.sub*0.07;
else
lv_tax_num:=rec_order.sub*0.04;
end if;
dbms_output.put_line('State='||rec_order.state);
dbms_output.put_line('Subtotal='||rec_order.sub);
dbms_output.put_line('Tax amount='||lv_tax_num);
end;
if語(yǔ)句也可以使用or，and，in等運(yùn)算符。例子這里就不舉了。
13、循環(huán) loop，for，while
理所當(dāng)然了，講完分支就講循環(huán)，一般的高級(jí)語(yǔ)言都這樣的。
declare
lv_cnt_num number(2):=1;
begin
loop
dbms_output.put_line(lv_cnt_num);
exit when lv_cnt_num>5;
lv_cnt_num:=lv_cnt_num+1;
end loop;
end;
上面的范例是最簡(jiǎn)單的loop循環(huán)。還可以參照游標(biāo)的使用這一部分，也使用到了循環(huán)。
begin
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end;
上面范例是最簡(jiǎn)單的for循環(huán)。還可以參照游標(biāo)這部分，有類(lèi)似循環(huán)的使用。
declare
lv_cnt_num number(2):=1;
begin
while lv_cnt_num<=5 loop
dbms_output.put_line(lv_cnt_num);
lv_cnt_num:=lv_cnt_num+1;
end loop;
end;
使用while循環(huán)……
14、goto語(yǔ)句
說(shuō)實(shí)在，對(duì)goto語(yǔ)句還是有點(diǎn)感情的。因?yàn)槲易钤缃佑|電腦的時(shí)候，認(rèn)為電腦就是BASIC。
variable lv_rows_num number
begin
:lv_rows_num:=0;
if :lv_rows_num=0 then
goto insert_row;
end if;
dbms_output.put_line('test point 1');
<<insert_row>>
dbms_output.put_line('test point 2');
end;
15、意外處理
相當(dāng)于VB中的on error goto ...之類(lèi)的。下面是范例：
declare
type type_basket is record(
basket bb_basket.idbasket%type,
created bb_basket.dtcreated%type,
qty bb_basket.quantity%type,
sub bb_basket.subtotal%type);
rec_basket type_basket;
lv_days_num number(3);
lv_shopper_num number(3):=22;
begin
select idbasket, dtcreated, quantity, subtotal
into rec_basket
from bb_basket
where idshopper = lv_shopper_num and orderplaced = 0;
lv_days_num := sysdate - rec_basket.created;
dbms_output.put_line(rec_basket.basket);
dbms_output.put_line(rec_basket.created);
dbms_output.put_line(rec_basket.qty);
dbms_output.put_line(rec_basket.sub);
dbms_output.put_line(lv_days_num);
exception
when no_data_found then
dbms_output.put_line('You have no saved baskets!');
when too_many_rows then
dbms_output.put_line('You don't have enough space!');
end;
像上面的出錯(cuò)標(biāo)志no_data_found和too_many_rows是已經(jīng)定義好的，常見(jiàn)的幾種出錯(cuò)是：
NO_DATA_FOUND，TOO_MANY_ROWS，ZERO_DIVIDE，DUP_VAL_ON_INDEX（違反唯一性約束或主鍵約束）。如果非預(yù)定義Oracle錯(cuò)誤，得參照下面的例子：
declare
ex_basket_fk exception;
pragma exception_init(ex_basket_fk, -2292);
begin
delete from bb_basket where idbasket=4;
exception
when ex_basket_fk then
dbms_output.put_line('Items still in the basket!');
end;
關(guān)于出錯(cuò)信息：ORA-02292:integrity constraint violated - child record found。
如果需要手動(dòng)拋出錯(cuò)誤，得參照下面的例子：
declare
ex_prod_update exception;
begin
update bb_product
set description = 'NO NO NO NO'
where idproduct = 30;
if sql%notfound then
raise ex_prod_update;
end if;
exception
when ex_prod_update then
dbms_output.put_line('Invalid product id entered.');
end;
手動(dòng)拋出錯(cuò)誤的條件不僅僅是sql%notfound、sql%found和sql%rowcount幾個(gè)，條件可以是多方面的。如下例：
declare
lv_ordqty_num number(2):=99;
lv_stock_num number(4);
ex_prod_stk exception;
begin
select stock
into lv_stock_num
from bb_product
where idproduct=2;
if lv_stock_num<lv_ordqty_num then
raise ex_prod_stk;
end if;
exception
when ex_prod_stk then
dbms_output.put_line('request quantity beyond stock level.');
end;
下面的例子說(shuō)明內(nèi)部塊的錯(cuò)誤如果在塊內(nèi)無(wú)法解決的話將傳遞到塊外解決。另外還說(shuō)明了出錯(cuò)代碼和出錯(cuò)信息的獲取。
declare
lv_junk1_num number(3):=200;
begin
declare
lv_junk2_num number(3);
begin
lv_junk2_num:='cat'; --引發(fā)錯(cuò)誤1
exception
when others then
lv_junk2_num:='hat'; --引發(fā)錯(cuò)誤2
dbms_output.put_line('handler in nested block');
dbms_output.put_line('Error code =' || sqlcode); --錯(cuò)誤碼
dbms_output.put_line('Error message =' || sqlerrm); --錯(cuò)誤信息
end;
lv_junk1_num:=300;
exception
when others then
dbms_output.put_line('handler in outer block');
dbms_output.put_line('Error code =' || sqlcode); --錯(cuò)誤碼
dbms_output.put_line('Error message =' || sqlerrm); --錯(cuò)誤信息
end;
16、過(guò)程（Procedure）
過(guò)程可以保存起來(lái)，類(lèi)似函數(shù)（除了沒(méi)有返回值），能供別處調(diào)用。下面是簡(jiǎn)單示例：
create or replace procedure ship_cost_sp
(p_qty in number, p_ship out number)
is
begin
if p_qty>10 then
p_ship:=11.00;
elsif p_qty>5 then
p_ship:=8.00;
else
p_ship:=5.00;
end if;
end;
/
variable g_ship number;
execute ship_cost_sp(7, :g_ship);
begin
dbms_output.put_line('Now g_ship is '|| :g_ship);
end;
/
上面的例子是用命令方式來(lái)調(diào)用過(guò)程。下面的例子將說(shuō)明如何使用過(guò)程來(lái)調(diào)用過(guò)程。
create or replace procedure order_total_sp
(p_bsktid in number,p_cnt out number, p_sub out number,p_ship out number, p_total out number)
is
begin
select sum(quantity), sum(quantity*price)
into p_cnt, p_sub
from bb_basketitem
where idbasket=p_bsktid;
ship_cost_sp(p_cnt, p_ship); --過(guò)程調(diào)用過(guò)程
p_total :=nvl(p_sub,0)+nvl(p_ship, 0);
end;
/
variable g_cnt number
variable g_sub number
variable g_ship number
variable g_total number
execute order_total_sp(12, :g_cnt, :g_sub, :g_ship, :g_total);
print :g_cnt
print :g_sub
print :g_ship
print :g_total
這個(gè)例子除了說(shuō)明如何用過(guò)程調(diào)用過(guò)程之外，還教了“print”命令，用來(lái)打印單個(gè)變量的值，調(diào)試的時(shí)候非常有用。
另外發(fā)現(xiàn)個(gè)有意思的現(xiàn)象，過(guò)程定義的時(shí)候不需要使用declare關(guān)鍵字，使用了也沒(méi)錯(cuò)，但會(huì)產(chǎn)生warning，不好的。
17、復(fù)雜度進(jìn)一步的過(guò)程
仔細(xì)閱讀下面的例子：
create or replace procedure promo_test_sp
(p_mth in char, p_year in char)
is
cursor cur_purch is
select idshopper, sum(subtotal) sub
from bb_basket
where to_char(dtcreated, 'MM')=p_mth
and to_char(dtcreated, 'YYYY')=p_year
and orderplaced=1
group by idshopper;
promo_flag char(1);
begin
for rec_purch in cur_purch loop
if rec_purch.sub>50 then
promo_flag:='A';
elsif rec_purch.sub>25 then
promo_flag:='B';
end if;
if promo_flag is not null then
insert into bb_promolist
values(rec_purch.idshopper, p_mth, p_year, promo_flag, null);
end if;
promo_flag:=null;
end loop;
commit;
end;
/
execute promo_test_sp ('02', '2003');
這個(gè)例子和上個(gè)例子其實(shí)沒(méi)有本質(zhì)的區(qū)別。但注意紅色字部分，原本是寫(xiě)成“promo_flag:=''”的形式，事實(shí)上，null并不等于“''”，這個(gè)千萬(wàn)要注意，否則得不到正確的結(jié)果。
18、刪除過(guò)程
一句話，沒(méi)什么好說(shuō)的了。
drop procedure procedure_name;
19、函數(shù)
其實(shí)和過(guò)程沒(méi)什么區(qū)別，就像VB，區(qū)別就在于一個(gè)有返回值，一個(gè)沒(méi)有。
當(dāng)然，例子很重要，先舉個(gè)例子啦。
create or replace function ship_calc_sf
(p_qty in number) --函數(shù)同樣有輸入輸出參數(shù)
return number
is
lv_ship_num number(5,2);
begin
if p_qty > 10 then
lv_ship_num := 11.00;
elsif p_qty>5 then
lv_ship_num:=8.00;
else
lv_ship_num:=5.00;
end if;
return lv_ship_num;
end;
/
函數(shù)的使用方法，如下：
select idbasket, shipping actual, ship_calc_sf(quantity) calc,
ship_calc_sf(quantity)-shipping diff
from bb_basket
where orderplaced =1;
在函數(shù)中使用函數(shù)的示例：
create or replace function memfmt1_sf(
p_id in number,
p_first in varchar2,
p_last in varchar2)
return varchar2
is
lv_mem_txt varchar2(35);
begin
lv_mem_txt:='Member '||p_id||'-'||p_first||' '||p_last;
return lv_mem_txt;
end;
/
declare
lv_name_txt varchar2(35);
lv_id_num number(4):=25;
lv_first_txt varchar2(15):='Scott';
lv_last_txt varchar2(20):='Savid';
begin
lv_name_txt:=memfmt1_sf(lv_id_num, lv_first_txt, lv_last_txt);
dbms_output.put_line(lv_name_txt);
end;
/
上面例子很簡(jiǎn)單，就不必多說(shuō)了。
在過(guò)程中調(diào)用函數(shù)，順便復(fù)習(xí)一下“過(guò)程”吧。
create or replace procedure login2_sp(
p_user in varchar2,
p_pass in varchar2,
p_id out number,
p_flag out char,
p_mem out varchar2)
is
lv_first_txt bb_shopper.firstname%type;
lv_last_txt bb_shopper.lastname%type;
begin
p_flag := 'N';
select idshopper, firstname, lastname
into p_id, lv_first_txt, lv_last_txt
from bb_shopper
where username=p_user and password=p_pass;
if sql%notfound then
return;
else
p_flag:='Y';
p_mem:=memfmt1_sf(p_id, lv_first_txt, lv_last_txt);
end if;
end;
20、刪除函數(shù)
呵呵，同理的。
drop function function_name;
21、軟件包入門(mén)
軟件包，我理解成過(guò)程與函數(shù)的集合。下面是簡(jiǎn)單范例。
CREATE OR REPLACE PACKAGE ordering_pkg
IS
pv_total_num NUMBER(3,2);
PROCEDURE order_total_pp
(p_bsktid IN NUMBER,
p_cnt OUT NUMBER,
p_sub OUT NUMBER,
p_ship OUT NUMBER,
p_total OUT NUMBER);
FUNCTION ship_calc_pf
(p_qty IN NUMBER)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY ordering_pkg IS
FUNCTION ship_calc_pf
(p_qty IN NUMBER)
RETURN NUMBER
IS
lv_ship_num NUMBER(5,2);
BEGIN
IF p_qty > 10 THEN
lv_ship_num := 11.00;
ELSIF p_qty > 5 THEN
lv_ship_num := 8.00;
ELSE
lv_ship_num := 5.00;
END IF;
RETURN lv_ship_num;
END ship_calc_pf;
PROCEDURE order_total_pp
(p_bsktid IN NUMBER,
p_cnt OUT NUMBER,
p_sub OUT NUMBER,
p_ship OUT NUMBER,
p_total OUT NUMBER)
IS
BEGIN
SELECT SUM(quantity),SUM(quantity*price)
INTO p_cnt, p_sub
FROM bb_basketitem
WHERE idbasket = p_bsktid;
p_ship := ship_calc_pf(p_cnt);
p_total := NVL(p_sub,0) + NVL(p_ship,0);
END order_total_pp;
END;
/
執(zhí)行完以上代碼之后，就能生成了ordering_pkg的軟件包。下面的操作用來(lái)測(cè)試軟件包：
variable cnt number
variable sub number
variable ship number
variable total number
execute ordering_pkg.order_total_pp(12, :cnt, :sub, :ship, :total);
22、觸發(fā)器
初步了解到觸發(fā)器的觸發(fā)條件是：使用insert、update或delete對(duì)某個(gè)表進(jìn)行操作。簡(jiǎn)單范例如下：
CREATE OR REPLACE TRIGGER product_inventory_trg
AFTER UPDATE OF orderplaced ON bb_basket
FOR EACH ROW
WHEN (OLD.orderplaced <> 1 AND NEW.orderplaced = 1)
DECLARE
CURSOR basketitem_cur IS
SELECT idproduct, quantity, option1
FROM bb_basketitem
WHERE idbasket = :NEW.idbasket;
lv_chg_num NUMBER(3,1);
BEGIN
FOR basketitem_rec IN basketitem_cur LOOP
IF basketitem_rec.option1 = 1 THEN
lv_chg_num := (.5 * basketitem_rec.quantity);
ELSE
lv_chg_num := basketitem_rec.quantity;
END IF;
UPDATE bb_product
SET stock = stock - lv_chg_num
WHERE idproduct = basketitem_rec.idproduct;
END LOOP;
END;
/
“AFTER UPDATE OF orderplaced ON bb_basket”為主觸發(fā)條件，“WHEN (OLD.orderplaced <> 1 AND NEW.orderplaced = 1)”為附加條件，條件都成立后，才能觸發(fā)。OLD為事件發(fā)生前的相關(guān)記錄，NEW為事件發(fā)生后的相關(guān)記錄。測(cè)試的時(shí)候，試圖把bb_basket表中的某條記錄的orderplaced字段由0至成1，然后觀察表bb_product。
下面的例子說(shuō)明多條件觸發(fā)，和條件謂詞判斷：
create or replace trigger product_inventory_trg
after delete or update on bb_basket
for each row
declare
cursor basketitem_cur is
select idproduct, quantity
from bb_basketitem
where idbasket=:new.idbasket;
begin
if updating then
for basketitem_rec in basketitem_cur loop
update bb_product
set stock = stock - basketitem_rec.quantity
where idproduct = basketitem_rec.idproduct;
end loop;
end if;
if deleting then
for basketitem_rec in basketitem_cur loop
update bb_product
set stock = stock + basketitem_rec.quantity
where idproduct=basketitem_rec.idproduct;
end loop;
end if;
end;
23、“instead of”觸發(fā)器
利用instead of觸發(fā)器可以將特定的常規(guī)操作取代為相應(yīng)的DML語(yǔ)句。
create or replace trigger jgg_trg
instead of update on view_jgg
for each row
begin
update bb_basket
set orderplaced = :new.orderplaced
where idbasket = :new.idbasket;
insert into bb_basketstatus
values(5, 5, 3, '15-2月-03', 'JGG', null,null);
end;
“instead of xxx on yyy”中的yyy只能是視圖，不能是表。建立了上面這么個(gè)觸發(fā)器，如果對(duì)view_jgg執(zhí)行update操作的時(shí)候，就會(huì)取而代之地執(zhí)行begin...end之間的語(yǔ)句。