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;
--根據部門編號查出部門的平均工資,返回平均工資的值(利用函數)
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語句是無法調用它的,因為其中含有修改語句
