數(shù)據(jù)庫培訓(xùn)-PLSQL課件

數(shù)據(jù)庫培訓(xùn)

PL/SQL基礎(chǔ)電信網(wǎng)通BU2007年05月數(shù)據(jù)庫培訓(xùn)-PLSQL培訓(xùn)內(nèi)容PL/SQL程序結(jié)構(gòu)基本語法要素流程控制事務(wù)處理游標(biāo)異常處理存儲(chǔ)過程和函數(shù)程序包觸發(fā)器2數(shù)據(jù)庫培訓(xùn)-PLSQLPL/SQL程序結(jié)構(gòu)

PL/SQL：過程化SQL語言

PL/SQL塊語法

[DECLARE]

---declarationstatements

BEGIN

---executablestatements

[EXCEPTION]

---exceptionstatements

END

3數(shù)據(jù)庫培訓(xùn)-PLSQL基本語法要素常量變量符合數(shù)據(jù)類型變量表達(dá)式函數(shù)ODS變量命名規(guī)范4數(shù)據(jù)庫培訓(xùn)-PLSQL常量語句格式：

常量名CONSTANT類型標(biāo)識(shí)符[notnull]:=值;

例：avc_acct_idCONSTANTVARCHAR2(5)NOTNULL:='AP001';5數(shù)據(jù)庫培訓(xùn)-PLSQL變量語句格式

變量名類型標(biāo)識(shí)符[notnull]:=值;

例：avc_serv_idVARCHAR2(5)NOTNULL:=‘SV001';基本數(shù)據(jù)類型

numberintchar()varchar()varchar2()longdateboolean:turefalsenull

6數(shù)據(jù)庫培訓(xùn)-PLSQL復(fù)合數(shù)據(jù)類型變量使用type%定義變量

avc_nodept.deptno%type;定義記錄類型變量使用rowtype%定義變量一維表類型變量多維表類型變量

7數(shù)據(jù)庫培訓(xùn)-PLSQL表達(dá)式算術(shù)表達(dá)式邏輯表達(dá)式字符表達(dá)式關(guān)系表達(dá)式8數(shù)據(jù)庫培訓(xùn)-PLSQL函數(shù)數(shù)字函數(shù)字符函數(shù)轉(zhuǎn)換函數(shù)日期函數(shù)常規(guī)函數(shù)9數(shù)據(jù)庫培訓(xùn)-PLSQLODS變量命名規(guī)范createorreplaceprocedurep_tf_acct_income_mon(/*

統(tǒng)計(jì)分析_收入情況（月）模塊名稱：p_tf_acct_income_mon

生成周期：按月執(zhí)行數(shù)據(jù)來源：FAS.TF_ACCT_ITEM

數(shù)據(jù)目標(biāo)：統(tǒng)計(jì)分析_收入情況（月）（TF_ACCT_INCOME_MON）*/avc_cycleidvarchar2,--數(shù)據(jù)周期

avc_lastcycleidvarchar2,--上一次成功數(shù)據(jù)周期

an_return out number, --返回值(0:表示成功-1:表示失敗)an_syserr outnumber, --系統(tǒng)錯(cuò)誤號(hào)

an_recordoutnumber,--總記錄數(shù)

avc_syserrtext outvarchar2--系統(tǒng)錯(cuò)誤文本

)isn_pointvarchar2(10);vc_sqlvarchar2(4000);begin.........endp_tf_acct_income_mon;10數(shù)據(jù)庫培訓(xùn)-PLSQL流程控制條件控制循環(huán)控制11數(shù)據(jù)庫培訓(xùn)-PLSQL條件控制If條件1then

語句段1;Elsif條件2thenif(條件4)then

語句段2;endif;Else

語句段3;Endif;12數(shù)據(jù)庫培訓(xùn)-PLSQL循環(huán)控制Loop循環(huán)While循環(huán)For循環(huán)13數(shù)據(jù)庫培訓(xùn)-PLSQLLoop循環(huán)Loop

循環(huán)語句1If條件語句thenexit；

else

語句2endif;Endloop;14數(shù)據(jù)庫培訓(xùn)-PLSQLLoop循環(huán)Loop

循環(huán)語句1

語句2exitwhen條件語句;Endloop;15數(shù)據(jù)庫培訓(xùn)-PLSQLFor循環(huán)for循環(huán)變量in[reverse]循環(huán)上屆..下屆循環(huán)loop

循環(huán)處理語句；

Endloop;16數(shù)據(jù)庫培訓(xùn)-PLSQL事務(wù)處理Commit命令用setautoon[off];來打開，關(guān)閉自動(dòng)提交Rollback命令Savepoint命令17數(shù)據(jù)庫培訓(xùn)-PLSQL游標(biāo)游標(biāo)的作用隱式游標(biāo)顯示游標(biāo)游標(biāo)屬性引用游標(biāo)/動(dòng)態(tài)游標(biāo)18數(shù)據(jù)庫培訓(xùn)-PLSQL游標(biāo)的作用

從數(shù)據(jù)庫中提取出數(shù)據(jù)，以臨時(shí)表的形式放在內(nèi)存中。初始指向首記錄，利用fetch移動(dòng)指針，對(duì)游標(biāo)中的數(shù)據(jù)進(jìn)行處理，然后寫到結(jié)果表中。19數(shù)據(jù)庫培訓(xùn)-PLSQL顯示游標(biāo)select語句上使用顯式游標(biāo)，明確能訪問結(jié)果集FOR循環(huán)游標(biāo)(常用的一種游標(biāo))轉(zhuǎn)換函數(shù)fetch游標(biāo)參數(shù)游標(biāo)20數(shù)據(jù)庫培訓(xùn)-PLSQLFOR循環(huán)游標(biāo)定義游標(biāo)定義游標(biāo)變量使用for循環(huán)來使用這個(gè)游標(biāo)前向游標(biāo)只能往一個(gè)方向走效率很高declare--類型定義

cursorccisselectempno,ename,job,salfromempwherejob='MANAGER';--定義一個(gè)游標(biāo)變量

ccreccc%rowtype;begin--for循環(huán)

forccrecinccloopdbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);endloop;

end;21數(shù)據(jù)庫培訓(xùn)-PLSQLfetch游標(biāo)使用的時(shí)候必須要明確的打開和關(guān)閉declare--類型定義

cursorccisselectempno,ename,job,salfromempwherejob='MANAGER';--定義一個(gè)游標(biāo)變量

ccreccc%rowtype;begin--打開游標(biāo)

opencc;--loop循環(huán)

loop--提取一行數(shù)據(jù)到ccrec中

fetchccintoccrec;--判斷是否提取到值，沒取到值就退出

--取到值cc%notfound是false--取不到值cc%notfound是trueexitwhencc%notfound;dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);endloop;--關(guān)閉

closecc;

end;22數(shù)據(jù)庫培訓(xùn)-PLSQL游標(biāo)屬性游標(biāo)的屬性4種%notfoundfetch是否提到數(shù)據(jù)沒有true提到false%foundfetch是否提到數(shù)據(jù)有true沒提到false%rowcount已經(jīng)取出的記錄的條數(shù)%isopen布爾值游標(biāo)是否打開declare--類型定義

cursorccisselectempno,ename,job,salfromempwherejob='MANAGER';--定義一個(gè)游標(biāo)變量

ccreccc%rowtype;begin--打開游標(biāo)

opencc;--loop循環(huán)

loop--提取一行數(shù)據(jù)到ccrec中

fetchccintoccrec;--判斷是否提取到值，沒取到值就退出

--取到值cc%notfound是false--取不到值cc%notfound是trueexitwhen(cc%notfoundorcc%rowcount=3);dbms_output.put_line(cc%rowcount||'-'||ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);endloop;--關(guān)閉

closecc;end;23數(shù)據(jù)庫培訓(xùn)-PLSQL參數(shù)游標(biāo)按部門編號(hào)的順序輸出部門經(jīng)理的名字declare--部門cursorc1isselectdeptnofromdept;--參數(shù)游標(biāo)c2,定義參數(shù)的時(shí)候--只能指定類型,不能指定長(zhǎng)度--參數(shù)只能出現(xiàn)在select語句=號(hào)的右側(cè)cursorc2(nonumber,pjobvarchar2)isselectemp.*fromempwheredeptno=noandjob=pjob;

c1recc1%rowtype;c2recc2%rowtype;--定義變量的時(shí)候要指定長(zhǎng)度v_jobvarchar2(20);begin--部門forc1recinc1loop--參數(shù)在游標(biāo)中使用forc2recinc2(c1rec.deptno,'MANAGER')loopdbms_output.put_line(c1rec.deptno||'-'||c2rec.ename);endloop;endloop;end;24數(shù)據(jù)庫培訓(xùn)-PLSQL綜合例子題目求購買的商品包括了顧客“Dennis”所購買商品的顧客（姓名）；

createtablepurcase(productidnumber,customeridnumber)；

createtablecustomer(customeridnumber,namevarchar(30))；思路：

Dennis(A,B)

別的顧客(A,B,C)(A,C)(B,C)C

25數(shù)據(jù)庫培訓(xùn)-PLSQLdeclare--Dennis所購買的商品

cursorcur_dennisisselectproductidfrompurcasewherecustomerid=(selectcustomeridfromcustomerwherename='Dennis');

--除Dennis以外的每個(gè)顧客

cursorcur_custisselectcustomeridfromcustomerwherename<>'Dennis';--每個(gè)顧客購買的商品

cursorcur_prod(idvarchar2)isselectproductidfrompurcasewherecustomerid=id;jnumber;inumber;rec_denniscur_dennis%rowtype;rec_custcur_cust%rowtype;rec_prodcur_prod%rowtype;avc_namevarchar2(10);begin--顧客循環(huán)

forrec_custincur_custloopi:=0;j:=0;forrec_dennisincur_dennisloopi:=i+1;--每個(gè)顧客買的東西

forrec_prodincur_prod(rec_cust.customerid)loopif(rec_ductid=rec_ductid)thenj:=j+1;endif;endloop;endloop;if(i=j)thenselectnameintoavc_namefromcustomerwherecustomerid=rec_cust.customerid;DBMS_output.put_line(avc_name);endif;endloop;end;26數(shù)據(jù)庫培訓(xùn)-PLSQL隱式游標(biāo)

隱式游標(biāo)也叫sql游標(biāo)，是用來處理所有sql語句的環(huán)境區(qū)域指針；不能通過專門的語句來打開，PL/SQL隱式的打開sql游標(biāo)處理完后自動(dòng)關(guān)閉。單條sql語句所產(chǎn)生的結(jié)果集合用關(guān)鍵字SQL表示隱式游標(biāo)4個(gè)屬性%rowcount影響的記錄的行數(shù)整數(shù)

%found影響到了記錄true%notfound沒有影響到記錄true%isopen是否打開布爾值永遠(yuǎn)是false多條sql語句隱式游標(biāo)SQL永遠(yuǎn)指的是最后一條sql語句的結(jié)果主要使用在update和delete語句上27數(shù)據(jù)庫培訓(xùn)-PLSQL游標(biāo)變量/動(dòng)態(tài)游標(biāo)select語句是動(dòng)態(tài)的

declare--定義一個(gè)類型(refcursor)弱類型

typecurisrefcursor;--定義一個(gè)refcursor類型的變量

curacur;c1recemp%rowtype;c2recdept%rowtype;beginDBMS_output.put_line('輸出員工');opencuraforselect*fromemp;loopfetchcuraintoc1rec;exitwhencura%notfound;DBMS_output.put_line(c1rec.ename);endloop;DBMS_output.put_line('輸出部門');opencuraforselect*fromdept;loopfetchcuraintoc2rec;exitwhencura%notfound;DBMS_output.put_line(c2rec.dname);endloop;closecura;end;28數(shù)據(jù)庫培訓(xùn)-PLSQL異常處理系統(tǒng)預(yù)定義的異常自定義異常聲明：異常名EXCEPION；產(chǎn)生異常：raise語句異常處理

29數(shù)據(jù)庫培訓(xùn)-PLSQL存儲(chǔ)過程和函數(shù)沒有名字的PL/SQL塊(匿名)有名字的PL/SQL塊(子程序-存儲(chǔ)過程和函數(shù))存儲(chǔ)過程

createorreplaceprocedurep1asbeginexceptionend;<最簡(jiǎn)單的存儲(chǔ)過程>createorreplaceprocedurep_jdashellovarchar2(20);beginselect'HelloWorld'intohellofromdual;dbms_output.put_line(hello);end;

執(zhí)行存儲(chǔ)過程的方法

<1>executep_jd;(SQL*PLUS中SQL>)<2>beginp_jd;end;30數(shù)據(jù)庫培訓(xùn)-PLSQL帶參數(shù)的存儲(chǔ)過程--輸入?yún)?shù)in--不寫in的參數(shù)都是輸入?yún)?shù)

--根據(jù)部門編號(hào)查員工姓名

createorreplaceprocedurep_getemp(nonumber)ascursorc1isselect*fromempwheredeptno=no;c1recc1%rowtype;begin--no:=20;輸入?yún)?shù)是不能賦值的

forc1recinc1loopdbms_output.put_line(c1rec.ename);endloop;end;31數(shù)據(jù)庫培訓(xùn)-PLSQL帶參數(shù)的存儲(chǔ)過程--輸出參數(shù)out--根據(jù)部門編號(hào)查出部門的平均工資,返回平均工資的值--in輸入(在procedure中是不能賦值的)--out輸出(在procedure中是能賦值的)--定義參數(shù)是不能指定長(zhǎng)度的

--定義變量是必須指定長(zhǎng)度的

createorreplaceprocedurep_getavgsal(nonumber,avgsaloutnumber)--no輸入?yún)?shù)

--avgsal輸出參數(shù)

asaavarchar2(10);--變量

beginselectavg(sal)intoavgsalfromempwheredeptno=no;end;調(diào)用它只能使用PL/SQL塊

declareavnumber;beginp_getavgsal(10,av);dbms_output.put_line('平均工資:'||round(av,2));end;32數(shù)據(jù)庫培訓(xùn)-PLSQL帶參數(shù)的存儲(chǔ)過程--一個(gè)參數(shù)同時(shí)可以輸入,也可以輸出--輸入輸出參數(shù)

createorreplaceprocedurep_getavgsal(ninoutnumber)asbeginselectavg(sal)intonfromempwheredeptno=n;end;

declareavnumber;beginav:=10;p_getavgsal(av);dbms_output.put_line('平均工資:'||round(av,2));end;33數(shù)據(jù)庫培訓(xùn)-PLSQL帶多個(gè)參數(shù)的存儲(chǔ)過程--帶多個(gè)參數(shù)的存儲(chǔ)過程

createorreplaceprocedurep_getM(nonumber,pjobvarchar2)as--參數(shù)游標(biāo)c2,定義參數(shù)的時(shí)候

--只能指定類型,不能指定長(zhǎng)度

--參數(shù)只能出現(xiàn)在select語句=號(hào)的右側(cè)

cursorc2(no1number,pjob1varchar2)isselect*fromempwheredeptno=no1andjob=pjob1;c2recc2%rowtype;--定義變量的時(shí)候要指定長(zhǎng)度

v_jobvarchar2(20);begin--參數(shù)在游標(biāo)中使用

forc2recinc2(no,pjob)loopdbms_output.put_line(c2rec.deptno||'-'||c2rec.ename);endloop;end;

調(diào)用方法:executep_getm(10,'MANAGER');--按位置

--no=10,pjob='MANAGER'

executep_getm(pjob=>'MANAGER',no=>10);--按參數(shù)的名字來傳值34數(shù)據(jù)庫培訓(xùn)-PLSQL

函數(shù)

必須要有返回值只能返回一個(gè)值35數(shù)據(jù)庫培訓(xùn)-PLSQL函數(shù)例子--根據(jù)部門編號(hào)查出部門的平均工資,返回平均工資的值(利用函數(shù))createorreplacefunctionf_getavgsal(nonumber)returnnumberasavgsalnumber(7,2);beginselectavg(sal)intoavgsalfromempwheredeptno=no;--返回值

returnavgsal;end;36數(shù)據(jù)庫培訓(xùn)-PLSQL一個(gè)函數(shù)返回2個(gè)值--帶輸出參數(shù)

--每個(gè)部門的平均工資和工資總額

--一個(gè)函數(shù)返回2個(gè)值createorreplacefunctionf_getavgsal(nonumber,sumsaloutnumber)returnnumberasavgsalnumber(7,2);begin--平均工資

selectavg(sal)intoavgsalfromempwheredeptno=no;--工資總額

selectsum(sal)intosumsalfromempwheredeptno=no;--返回值

returnavgsal;end;37數(shù)據(jù)庫培訓(xùn)-PLSQL函數(shù)的調(diào)用方法塊調(diào)用

declareaanumber;beginaa:=f_getavgsal(10);dbms_output.put_line(to_char(aa));end;SQL語句來調(diào)用(DML)selectf_getavgsal(10)fromdual;selectdeptno,f_getavgsal(deptno)fromdept;含有修改語句，select語句是無法調(diào)用

createorreplacefunctionf1returnnumberasupdateempsetcomm=1000wherejob='CLERK';returnsql%rowcount;end;--select語句是無法調(diào)用它的,因?yàn)槠渲泻行薷恼Z句38數(shù)據(jù)庫培訓(xùn)-PLSQL程序包PACKAGE用途:模塊化<例子>--公司的員工的管理

1.增加一個(gè)員工

2.員工離職包中的變量是全局變量返回結(jié)果集合39數(shù)據(jù)庫培訓(xùn)-PLSQLPACKAGE的例子

createorreplacepackagetest_pack--包頭

asproceduregetname(enonumber,enmoutvarchar2);functionf_get_name(enonumber)returnvarchar2;end;createorreplacepackagebodytest_pack--包體

asproceduregetname(enonumber,enmoutvarchar2)asbeginselectenameintoenmfromempwhereempno=eno;end;