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