PL/SQL快速参考

最新推荐文章于 2021-05-13 21:18:40 发布

guogangj

最新推荐文章于 2021-05-13 21:18:40 发布

阅读量2.5k

点赞数

分类专栏： Windows编程文章标签： exception date sql nested insert function

本文链接：https://blog.csdn.net/guogangj/article/details/654198

版权

Windows编程专栏收录该内容

44 篇文章 4 订阅

订阅专栏

PL/SQL笔记

程序不区分大小写

1、SQL*Plus这个程序可以用来执行脚本，执行脚本前先设置假脱机文件，这样就可以方便地查看出错之处了。或者使用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)

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)

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)

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

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

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)

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

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

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)

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之间的语句。

guogangj

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL快速参考

PL/SQL笔记程序不区分大小写 1、SQL*Plus这个程序可以用来执行脚本，执行脚本前先设置假脱机文件，这样就可以方便地查看出错之处了。或者使用PL/SQL Developer的Command窗口来执行脚本。2、代码中变量的声明： set serveroutput on;
复制链接

扫一扫