[20060407发表于blog.csdn.net，20090929重新编辑]
重新编辑注释：由于太久没有使用PL/SQL，如今再看这篇笔记，几乎完全陌生了，自己还到baidu去搜索了一下PL/SQL和Pro*C才依稀记起，我不知道以后还有没有机会使用PL/SQL，但无疑的是这篇笔记还是有点价值的，或者作为一个纪念吧。PL/SQL（准确的写法就是“PL/SQL”，没有别的，不是Plus SQL，也不是SQL Plus）是Oracle对SQL的扩展，也就是说，PL/SQL只能用于Oracle，所以不要指望代码能移植给MS SQL Server用，另外我印象比较深刻的是PL/SQL的语法很不严谨，bug比较多，用之前请三思，再有一点，这些代码怎么用？就像C语言，要有编译连接器生成可执行文件一样，这些PL/SQL代码也是需要编译器的，不用说，它的编译器一定是Oracle提供的，所以你得先安装Oracle的客户端，利用上面的调试工具调试。我以前是在Pro*C（在C代码中嵌入PL/SQL，利用Oracle提供的预编译生成C，然后才使用C的编译器进行下去）中使用PL/SQL的。
程序不区分大小写
1、SQL*Plus
SQL*Plus（Oracle客户端提供）这个程序可以用来执行脚本，执行脚本前先设置假脱机文件，这样就可以方便地查看出错之处了。或者使用PL/SQL Developer的Command窗口来执行脚本。
2、代码中变量的声明
set serveroutput on; --设置输出
DECLARE
lv_ord_date DATE; --日期类型
lv_last_txt VARCHAR2(25):='Jiang'; --可变字符串类型，并初始化
lv_qty_num CONSTANT NUMBER(2):=20; --2位整数，常量，初始化
lv_shipflag_bln BOOLEAN:=FALSE; --布尔型，初始化为FALSE
lv_name_txt CHAR(10) NOT NULL:='CCC'; --定长字符串类型，不能为空，初始化
BEGIN
dbms_output.put_line (lv_qty_num); --这里一定要写点什么东西才行
END;
3、简单查询
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字段同类型
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、附加说明
要执行declare声明的过程，需要使用“/”。而其它不需要。
在SQL*Plus窗口中直接键入“/”，表示执行最近declare的过程。
6、复合数据类型：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、复合数据类型：%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”能根据表的结构自动创建rec_shopper这种数据类型，十分方便。
8、复合数据类型：记录集
定义成“is table”就想当于一个记录集，存在行数。
另外：声明全局变量：
SQL>VARIABLE g_row NUMBER
注意，不需要“;”，也不需要“/”，使用变量的时候前面加上“:”。
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、隐式游标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、游标的使用
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;
/
游标除了上面的直接使用loop...end loop与fetch的结合来使用记录集之外，还有下面的方式：
declare
cursor cur_prod is
select type, price
from bb_product
where active=1
for update nowait;
--另一会话已经锁定了游标正在检索的行时，不必等待，继续往下。
--另外可写成：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;
上面两个例子都是在declare中声明并定义游标，下面的例子则是：仍然在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;
关于游标的，讲了好多啊……没办法，游标就是很重要的概念。
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处定义的变量只在当块中生效。如果块头没有定义，但块体中使用了，程序就把变量当作外部变量。
12、if/elsif语句
非常有意思，这里用的不是elseif，而是elsif，千万别写错了。
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语句也可以使用or，and，in等运算符。例子这里就不举了。
13、循环 loop，for，while
理所当然了，讲完分支就讲循环，一般的高级语言都这样的。
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;
上面的范例是最简单的loop循环。还可以参照游标的使用这一部分，也使用到了循环。
begin
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end;
上面范例是最简单的for循环。还可以参照游标这部分，有类似循环的使用。
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循环……
14、goto语句
说实在，对goto语句还是有点感情的。因为我最早接触电脑的时候，认为电脑就是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、意外处理
相当于VB中的on error goto ...之类的。下面是范例：
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;
像上面的出错标志no_data_found和too_many_rows是已经定义好的，常见的几种出错是：
NO_DATA_FOUND，TOO_MANY_ROWS，ZERO_DIVIDE，DUP_VAL_ON_INDEX（违反唯一性约束或主键约束）。如果非预定义Oracle错误，得参照下面的例子：
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;
关于出错信息：ORA-02292:integrity constraint violated - child record found。
如果需要手动抛出错误，得参照下面的例子：
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;
手动抛出错误的条件不仅仅是sql%notfound、sql%found和sql%rowcount几个，条件可以是多方面的。如下例：
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;
下面的例子说明内部块的错误如果在块内无法解决的话将传递到块外解决。另外还说明了出错代码和出错信息的获取。
declare
lv_junk1_num number(3):=200;
begin
declare
lv_junk2_num number(3);
begin
lv_junk2_num:='cat'; --引发错误1
exception
when others then
lv_junk2_num:='hat'; --引发错误2
dbms_output.put_line('handler in nested block');
dbms_output.put_line('Error code =' || sqlcode); --错误码
dbms_output.put_line('Error message =' || sqlerrm); --错误信息
end;
lv_junk1_num:=300;
exception
when others then
dbms_output.put_line('handler in outer block');
dbms_output.put_line('Error code =' || sqlcode); --错误码
dbms_output.put_line('Error message =' || sqlerrm); --错误信息
end;
16、过程（Procedure）
过程可以保存起来，类似函数（除了没有返回值），能供别处调用。下面是简单示例：
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;
/
上面的例子是用命令方式来调用过程。下面的例子将说明如何使用过程来调用过程。
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); --过程调用过程
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
这个例子除了说明如何用过程调用过程之外，还教了“print”命令，用来打印单个变量的值，调试的时候非常有用。
另外发现个有意思的现象，过程定义的时候不需要使用declare关键字，使用了也没错，但会产生warning，不好的。
17、复杂度进一步的过程
仔细阅读下面的例子：
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');
这个例子和上个例子其实没有本质的区别。但注意红色字部分，原本是写成“promo_flag:=''”的形式，事实上，null并不等于“''”，这个千万要注意，否则得不到正确的结果。
18、删除过程
一句话，没什么好说的了。
drop procedure procedure_name;
19、函数
其实和过程没什么区别，就像VB，区别就在于一个有返回值，一个没有。
当然，例子很重要，先举个例子啦。
create or replace function ship_calc_sf
(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;
/
函数的使用方法，如下：
select idbasket, shipping actual, ship_calc_sf(quantity) calc,
ship_calc_sf(quantity)-shipping diff
from bb_basket
where orderplaced =1;
在函数中使用函数的示例：
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;
/
上面例子很简单，就不必多说了。
在过程中调用函数，顺便复习一下“过程”吧。
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、删除函数
呵呵，同理的。
drop function function_name;
21、软件包入门
软件包，我理解成过程与函数的集合。下面是简单范例。
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;
/
执行完以上代码之后，就能生成了ordering_pkg的软件包。下面的操作用来测试软件包：
variable cnt number
variable sub number
variable ship number
variable total number
execute ordering_pkg.order_total_pp(12, :cnt, :sub, :ship, :total);
22、触发器
初步了解到触发器的触发条件是：使用insert、update或delete对某个表进行操作。简单范例如下：
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”为主触发条件，“WHEN (OLD.orderplaced <> 1 AND NEW.orderplaced = 1)”为附加条件，条件都成立后，才能触发。OLD为事件发生前的相关记录，NEW为事件发生后的相关记录。测试的时候，试图把bb_basket表中的某条记录的orderplaced字段由0至成1，然后观察表bb_product。
下面的例子说明多条件触发，和条件谓词判断：
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”触发器
利用instead of触发器可以将特定的常规操作取代为相应的DML语句。
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只能是视图，不能是表。建立了上面这么个触发器，如果对view_jgg执行update操作的时候，就会取而代之地执行begin...end之间的语句。