PL/SQL塊
declare
begin
--SQL語(yǔ)句
--直接寫(xiě)的SQL語(yǔ)句(DML/TCL)
--間接寫(xiě)execute immediate <DDL/DCL命令字符串>
--select 語(yǔ)句
<1>必須帶有into子句
select empno into eno from emp
where empno =7369;
<2>只能查到一行**********
<3>字段個(gè)數(shù)必須和變量的個(gè)數(shù)一致
exception
--異常
when <異常名字> then --特定異常
<處理語(yǔ)句>
when others then --所有異常都可捕獲
<處理語(yǔ)句>
end;
<例子>
編寫(xiě)程序 向DEPT表中插入一條記錄，
從鍵盤(pán)輸入數(shù)據(jù)，如果
數(shù)據(jù)類(lèi)型輸入錯(cuò)誤要有提示
無(wú)法插入記錄 也要有提示
只能輸入正數(shù),如果有負(fù)數(shù)提示
declare
n number;
no dept.deptno%type;
nm dept.dname%type;
lc dept.loc%type;
exp exception; --異常的變量
exp1 exception;
num number:=0; --計(jì)數(shù)器
PRagma exception_init(exp,-1); --預(yù)定義語(yǔ)句
--(-1錯(cuò)誤和異常變量關(guān)聯(lián))
pragma exception_init(exp1,-1476);
e1 exception; --自定義異常變量
begin
--輸入值
no := '&編號(hào)';
num := num + 1;
if no < 0 then
raise e1; --自定義異常的引發(fā)
end if;
nm := '&名稱(chēng)';
num := num +1;
lc := '&地址';
num := num +1;
n := 10 /0;
insert into dept values (no,nm,lc);
num := num +1;
commit;
exception
--自定義異常
when e1 then
dbms_output.put_line('編號(hào)不能為負(fù)數(shù)');
--數(shù)據(jù)類(lèi)型不對(duì)
when value_error then
if num =0 then
dbms_output.put_line('編號(hào)數(shù)據(jù)類(lèi)型不對(duì)');
elsif num = 1 then
dbms_output.put_line('名稱(chēng)數(shù)據(jù)類(lèi)型不對(duì)');
elsif num =2 then
dbms_output.put_line('地址數(shù)據(jù)類(lèi)型不對(duì)');
end if;
rollback;
--主鍵沖突
when exp then
--sqlcode全局變量 異常錯(cuò)誤號(hào)
--sqlerrm全局變量 異常的文字信息
--dbms_output.put_line('異常的編號(hào):'||sqlcode);
--dbms_output.put_line('異常的內(nèi)容:'||sqlerrm);
--dbms_output.put_line('編號(hào)已存在') ;
rollback;
--非預(yù)定義異常(關(guān)聯(lián)錯(cuò)誤號(hào))
when exp1 then
--dbms_output.put_line('0做了除數(shù)') ;
raise_application_error(-20001,'0做了除數(shù)'); --引起一個(gè)自定義的錯(cuò)誤
--預(yù)先保留-20001 到 -29999編號(hào)
rollback;
--其他的異常
when others then
dbms_output.put_line('異常的編號(hào):'||sqlcode);
dbms_output.put_line('異常的內(nèi)容:'||sqlerrm);
-- dbms_output.put_line('出現(xiàn)錯(cuò)誤');
rollback;
end;
-- insert into dept values (40,'asdf','asdf');
<簡(jiǎn)單的做法>
--存放異常的
create table save_exp(
bh number,
wz varchar2(1000)
);
declare
no dept.deptno%type;
nm dept.dname%type;
lc dept.loc%type;
errno number;
errtext varchar2(1000);
begin
no := '&編號(hào)';
nm := '&名稱(chēng)';
lc := '&地址';
insert into dept values (no,nm,lc);
commit;
exception
when others then
rollback;
errno := sqlcode;
errtext := sqlerrm;
insert into save_exp values (errno,errtext);
commit;
end;
<游標(biāo)> 內(nèi)存中的一塊區(qū)域,存放的是select 的結(jié)果
1。 隱式游標(biāo)
單條sql語(yǔ)句所產(chǎn)生的結(jié)果集合
用關(guān)鍵字SQL表示隱式游標(biāo)
4個(gè)屬性 %rowcount 影響的記錄的行數(shù) 整數(shù)
%found 影響到了記錄 true
%notfound 沒(méi)有影響到記錄 true
%isopen 是否打開(kāi) 布爾值 永遠(yuǎn)是false
多條sql語(yǔ)句 隱式游標(biāo)SQL永遠(yuǎn)指的是最后一條sql語(yǔ)句的結(jié)果
主要使用在update 和 delete語(yǔ)句上
2。 顯式游標(biāo)
select語(yǔ)句上 使用顯式游標(biāo)
明確能訪問(wèn)結(jié)果集
for循環(huán)游標(biāo)
參數(shù)游標(biāo)
解決多行記錄的查詢問(wèn)題
fetch游標(biāo)
顯示游標(biāo)
需要明確定義
(1)FOR循環(huán)游標(biāo) (常用的一種游標(biāo))
--<1>定義游標(biāo)
--<2>定義游標(biāo)變量
--<3>使用for循環(huán)來(lái)使用這個(gè)游標(biāo)
--前向游標(biāo) 只能往一個(gè)方向走
--效率很高
declare
--類(lèi)型定義
cursor cc is select empno,ename,job,sal
from emp where job = 'MANAGER';
--定義一個(gè)游標(biāo)變量
ccrec cc%rowtype;
begin
--for循環(huán)
for ccrec in cc loop
dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);
end loop;
end;
(2) fetch游標(biāo)
--使用的時(shí)候 必須要明確的打開(kāi)和關(guān)閉
declare
--類(lèi)型定義
cursor cc is select empno,ename,job,sal
from emp where job = 'MANAGER';
--定義一個(gè)游標(biāo)變量
ccrec cc%rowtype;
begin
--打開(kāi)游標(biāo)
open cc;
--loop循環(huán)
loop
--提取一行數(shù)據(jù)到ccrec中
fetch cc into ccrec;
--判斷是否提取到值，沒(méi)取到值就退出
--取到值cc%notfound 是false
--取不到值cc%notfound 是true
exit when cc%notfound;
dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);
end loop;
--關(guān)閉
close cc;
end;
游標(biāo)的屬性4種
%notfound fetch是否提到數(shù)據(jù) 沒(méi)有true 提到false
%found fetch是否提到數(shù)據(jù) 有true 沒(méi)提到false
%rowcount 已經(jīng)取出的記錄的條數(shù)
%isopen 布爾值 游標(biāo)是否打開(kāi)
declare
--類(lèi)型定義
cursor cc is select empno,ename,job,sal
from emp where job = 'MANAGER';
--定義一個(gè)游標(biāo)變量
ccrec cc%rowtype;
begin
--打開(kāi)游標(biāo)
open cc;
--loop循環(huán)
loop
--提取一行數(shù)據(jù)到ccrec中
fetch cc into ccrec;
--判斷是否提取到值，沒(méi)取到值就退出
--取到值cc%notfound 是false
--取不到值cc%notfound 是true
exit when (cc%notfound or cc%rowcount =3);
dbms_output.put_line(cc%rowcount||'-'||ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);
end loop;
--關(guān)閉
close cc;
end;
<例子>
declare
cursor cc is select dept.dname,
emp.ename,emp.sal from
dept,emp where dept.deptno = emp.deptno;
ccrec cc%rowtype;
begin
for ccrec in cc loop
dbms_output.put_line(ccrec.dname||'-'||ccrec.ename||'-'||ccrec.sal);
end loop;
end;
(3)參數(shù)游標(biāo)
按部門(mén)編號(hào)的順序輸出部門(mén)經(jīng)理的名字
declare
--部門(mén)
cursor c1 is select deptno from dept;
--參數(shù)游標(biāo)c2,定義參數(shù)的時(shí)候
--只能指定類(lèi)型,不能指定長(zhǎng)度
--參數(shù)只能出現(xiàn)在select語(yǔ)句=號(hào)的右側(cè)
cursor c2(no number,pjob varchar2) is select emp.* from emp
where deptno = no and job=pjob;
/*
no = 10 pjob = 'MANAGER'
select * from emp where deptno = 10 and job = 'MANAGER';
*/
c1rec c1%rowtype;
c2rec c2%rowtype;
--定義變量的時(shí)候要指定長(zhǎng)度
v_job varchar2(20);
begin
--部門(mén)
for c1rec in c1 loop
--參數(shù)在游標(biāo)中使用
for c2rec in c2(c1rec.deptno,'MANAGER') loop
dbms_output.put_line(c1rec.deptno||'-'||c2rec.ename);
end loop;
end loop;
end;
<綜合例子>
求購(gòu)買(mǎi)的商品包括了顧客"Dennis"所購(gòu)買(mǎi)商品的顧客（姓名）；**************
思路:
Dennis (A,B)
別的顧客 (A,B,C) (A,C) (B,C) C
declare
--Dennis所購(gòu)買(mǎi)的商品
cursor cdennis is select productid
from purcase where customerid=(
select customerid from
customer where name = 'Dennis');
--除Dennis以外的每個(gè)顧客
cursor ccust is select customerid
from customer where name <> 'Dennis';
--每個(gè)顧客購(gòu)買(mǎi)的商品
cursor cprod(id varchar2) is
select productid from purcase
where customerid = id;
j number ;
i number;
c1rec cdennis%rowtype;
c2rec ccust%rowtype;
c3rec cprod%rowtype;
cname varchar2(10);
begin
--顧客循環(huán)
for c2rec in ccust loop
i:=0;
j:=0;
for c1rec in cdennis loop
i := i + 1;
--每個(gè)顧客買(mǎi)的東西
for c3rec in cprod(c2rec.customerid) loop
if (c3rec.productid = c1rec.productid) then
j := j + 1;
end if;
end loop;
end loop;
if (i=j) then
select name into cname from
customer where customerid = c2rec.customerid;
DBMS_output.put_line(cname);
end if;
end loop;
end;
(4)引用游標(biāo)/動(dòng)態(tài)游標(biāo)
select語(yǔ)句是動(dòng)態(tài)的
declare
--定義一個(gè)類(lèi)型(ref cursor)弱類(lèi)型
type cur is ref cursor;
--強(qiáng)類(lèi)型(返回的結(jié)果集有要求)
type cur1 is ref cursor return emp%rowtype;
--定義一個(gè)ref cursor類(lèi)型的變量
cura cur;
--
c1rec emp%rowtype;
c2rec dept%rowtype;
begin
DBMS_output.put_line('輸出員工') ;
open cura for select * from emp;
loop
fetch cura into c1rec;
exit when cura%notfound;
DBMS_output.put_line(c1rec.ename) ;
end loop ;
DBMS_output.put_line('輸出部門(mén)') ;
open cura for select * from dept;
loop
fetch cura into c2rec;
exit when cura%notfound;
DBMS_output.put_line(c2rec.dname) ;
end loop;
close cura;
end;
>>>>>存儲(chǔ)過(guò)程和函數(shù)
沒(méi)有名字的PL/SQL塊(匿名)
有名字的PL/SQL塊(子程序-存儲(chǔ)過(guò)程和函數(shù))
存儲(chǔ)過(guò)程
create or replace procedure p1
as
begin
exception
end;
<最簡(jiǎn)單的存儲(chǔ)過(guò)程>
create or replace procedure p_jd
as
hello varchar2(20);
begin
select 'Hello World' into hello from dual;
dbms_output.put_line(hello);
end;
執(zhí)行存儲(chǔ)過(guò)程的方法
<1> execute p_jd; (SQL*PLUS中SQL>)
<2> begin
p_jd;
end;
帶參數(shù)的存儲(chǔ)過(guò)程
--輸入?yún)?shù)in
--不寫(xiě)in的參數(shù)都是輸入?yún)?shù)
--根據(jù)部門(mén)編號(hào)查員工姓名
create or replace procedure p_getemp(no number)
as
cursor c1 is select * from emp
where deptno = no;
c1rec c1%rowtype;
begin
-- no := 20; 輸入?yún)?shù)是不能賦值的
for c1rec in c1 loop
dbms_output.put_line(c1rec.ename);
end loop;
end;
--輸出參數(shù)out
--根據(jù)部門(mén)編號(hào)查出部門(mén)的平均工資,返回平均工資的值
-- in 輸入 (在procedure中是不能賦值的)
-- out 輸出 (在procedure中是能賦值的)
-- 定義參數(shù)是不能指定長(zhǎng)度的
--定義變量是必須指定長(zhǎng)度的
create or replace procedure p_getavgsal(no number,avgsal out number)
-- no 輸入?yún)?shù)
-- avgsal 輸出參數(shù)
as
aa varchar2(10); --變量
begin
select avg(sal) into avgsal
from emp where deptno = no;
end;
調(diào)用它只能使用PL/SQL塊
declare
av number;
begin
p_getavgsal(10,av);
dbms_output.put_line('平均工資:'||round(av,2));
end;
--一個(gè)參數(shù)同時(shí)可以輸入,也可以輸出
--輸入輸出參數(shù)
create or replace procedure
p_getavgsal(n in out number)
as
begin
select avg(sal) into n
from emp where deptno = n;
end;
declare
av number;
begin
av := 10;
p_getavgsal(av);
dbms_output.put_line('平均工資:'||round(av,2));
end;
--帶多個(gè)參數(shù)的存儲(chǔ)過(guò)程
create or replace procedure
p_getM(no number,pjob varchar2) as
--參數(shù)游標(biāo)c2,定義參數(shù)的時(shí)候
--只能指定類(lèi)型,不能指定長(zhǎng)度
--參數(shù)只能出現(xiàn)在select語(yǔ)句=號(hào)的右側(cè)
cursor c2(no1 number,pjob1 varchar2) is select * from emp
where deptno = no1 and job=pjob1;
c2rec c2%rowtype;
--定義變量的時(shí)候要指定長(zhǎng)度
v_job varchar2(20);
begin
--參數(shù)在游標(biāo)中使用
for c2rec in c2(no,pjob) loop
dbms_output.put_line(c2rec.deptno||'-'||c2rec.ename);
end loop;
end;
調(diào)用方法:execute p_getm(10,'MANAGER'); --按位置
-- no = 10 , pjob = 'MANAGER'
execute p_getm(pjob => 'MANAGER',no => 10);
--按參數(shù)的名字 來(lái)傳值
函數(shù):
必須要有返回值
只能返回一個(gè)值
--根據(jù)部門(mén)編號(hào)查出部門(mén)的平均工資,返回平均工資的值(利用函數(shù))
create or replace function
f_getavgsal(no number)
return number
as
avgsal number(7,2);
begin
select avg(sal) into avgsal
from emp where deptno = no;
--返回值
return avgsal;
end;
--帶輸出參數(shù)
--每個(gè)部門(mén)的平均工資和工資總額
--一個(gè)函數(shù)返回2個(gè)值
create or replace function
f_getavgsal(no number,sumsal out number)
return number
as
avgsal number(7,2);
begin
--平均工資
select avg(sal) into avgsal
from emp where deptno = no;
--工資總額
select sum(sal) into sumsal
from emp where deptno = no;
--返回值
return avgsal;
end;
--調(diào)用方法
<1>PL/SQL塊調(diào)用
declare
aa number;
begin
aa := f_getavgsal(10) ;
dbms_output.put_line(to_char(aa));
end;
<2> SQL語(yǔ)句來(lái)調(diào)用(DML)
select f_getavgsal(10) from dual;
select deptno,f_getavgsal(deptno) from dept;
<3>
create or replace function f1
return number
as
update emp set comm = 1000
where job='CLERK';
return sql%rowcount;
end;
--select語(yǔ)句是無(wú)法調(diào)用它的,因?yàn)槠渲泻行薷恼Z(yǔ)句
本文來(lái)自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處：http://blog.csdn.net/shenghuiping2001/archive/2009/12/30/5105949.aspx
