第8章_2 Oracle存儲過程

1、八八 數(shù)據(jù)庫編程數(shù)據(jù)庫編程 存儲過程存儲過程 預(yù)編譯，優(yōu)化預(yù)編譯，優(yōu)化 存儲過程是預(yù)編譯過的，并且經(jīng)優(yōu)化后存儲于SQL內(nèi) 存中，使用時無需再次編譯，提高了工作效率； 代碼存放于數(shù)據(jù)庫代碼存放于數(shù)據(jù)庫 存儲過程的代碼直接存放于服務(wù)器數(shù)據(jù)庫中，一般由客戶端直接通過存儲過程的名字進(jìn)行調(diào)用，減少了網(wǎng)絡(luò)流量，加快了系統(tǒng)執(zhí)行速度； 安全性安全性 執(zhí)行存儲過程的用戶要具有一定的權(quán)限才能使用存儲過程； 存儲過程的優(yōu)點(diǎn)存儲過程的優(yōu)點(diǎn)CREATE 【OR REPLACE】 PROCEDURE procedure_name(parameter1 【model】 datatype1, parameter2 【mode

2、2】 datatype2 .)IS AS 變量聲明部分；變量聲明部分；-存儲過程中的聲明不使用存儲過程中的聲明不使用DECLARE關(guān)鍵字關(guān)鍵字 BEGIN PL/SQL Block;END procedure_name; 存儲過程的定義存儲過程的定義create or replace procedure p_select_sasbegin for vr_s in (select sname,age from s) loop dbms_output.put_line(姓名姓名:|vr_s.sname| 年齡年齡:|vr_s.age); end loop;end;存儲過程示例存儲過程示例creat

3、e or replace procedure p_insert_s(p_s# in char, p_sname in nvarchar2, p_sex in nvarchar2 ) as begin insert into s(s#,sname,sex) values(p_s#,p_sname,p_sex); commit;end; in 可以省略不能定義形參的長度存儲過程示例存儲過程示例create or replace procedure p_s(p_s# in char, p_sname out nvarchar2, p_D# out nvarchar2 ) is begin selec

4、t sname,D# into p_sname,p_D# from s where s#=p_s#; end;存儲過程示例存儲過程示例 在SQL*PLUS中，需要使用CALL或EXECUTE命令， exec p_select_s; call p_select_s(); 注意：注意：set serveroutput onset serveroutput on后可以顯示輸出結(jié)果。后可以顯示輸出結(jié)果。在在PL/SQL塊中，可以直接引用 begin p_select_s; end;存儲過程的調(diào)用存儲過程的調(diào)用有參存儲過程的調(diào)用begin p_insert_S (sg2,王明,男);end;或：或：ca

5、ll p_insert_S (sg2,王明,男);存儲過程的調(diào)用存儲過程的調(diào)用有輸出參數(shù)的存儲過程的調(diào)用有輸出參數(shù)的存儲過程的調(diào)用Declare v_sname s.sname%TYPE; v_D# s.D#%TYPE;Begin p_s(S3,v_sname, v_D#); dbms_output.put_line(姓名:|v_sname); dbms_output.put_line(部門:|v_D#);end;存儲過程的調(diào)用存儲過程的調(diào)用 示例：輸入教師編號，根據(jù)表profprof中D#字段的值，修改教師工資；若部門號為D1，則工資加100；若部門號為D2，則工資加300；否則工資加400

6、。 create or replace procedure p_changesal(p_p# in char)asv_increment prof.sal%type;v_pdept prof.D#%type;begin select D# into v_pdept from prof where p#=p_p#; if v_pdept= D1 then v_increment:=200; elsif v_pdept= D2 then v_increment:=300; else v_increment:=400; end if; update prof set sal=sal+v_increm

7、ent where p#=p_p#; commit;end;刪除存儲過程刪除存儲過程DROP PROCEDURE procedure_name;查看存儲過程的代碼查看存儲過程的代碼SELECT TEXT FROM USER_SOURCE WHERE NAME= procedure_name其中：其中： procedure_name是存儲過程的名字是存儲過程的名字 存儲過程的維護(hù)存儲過程的維護(hù) 存儲過程中的異常處理存儲過程中的異常處理示例:輸入教師姓名，教師不存在，則輸出There is not such an employee。如果有多個，則輸出每個的編號和工資。create or repla

8、ce procedure p_info(p_pname in char) asv_sal prof.sal%type;begin select sal into v_sal from prof where pname=p_pname; dbms_output.put_line(p_pname|:|v_sal);exception when NO_DATA_FOUND then dbms_output.put_line(There is not such an employee!); when TOO_MANY_ROWS then for v_prof in(select p#,sal from

9、 prof where pname=p_pname) loop dbms_output.put_line(v_prof.p#|:|v_prof.sal); end loop;end;COMMIT （提交事務(wù)）確認(rèn)事務(wù)變化，結(jié)束當(dāng)前事務(wù)、刪除保存點(diǎn)，釋放鎖，使得當(dāng)前事務(wù)中所有未決的數(shù)據(jù)永久改變。SAVEPOINT（保存點(diǎn)）在當(dāng)前事務(wù)中，標(biāo)記事務(wù)的保存點(diǎn)。ROLLBACK （回滾事務(wù)）回滾整個事務(wù)，刪除該事務(wù)所定義的所有保存點(diǎn)，釋放鎖，丟棄所有未決的數(shù)據(jù)改變。ROLLBACK TO SAVEPOINT （回滾事務(wù)到指定的保存點(diǎn))回滾當(dāng)前事務(wù)到指定的保存點(diǎn)，丟棄該保存點(diǎn)創(chuàng)建后的任何改變，釋放鎖。 存

10、儲過程中的事務(wù)處理存儲過程中的事務(wù)處理存儲過程中的事務(wù)處理存儲過程中的事務(wù)處理create or replace procedure p_insert_s(p_s# in char, p_sname in varchar2, p_sex in char ) as begin insert into s(s#,sname,sex) values(p_s#,p_sname,p_sex); commit; exception when others then rollback; raise_application_error(-20001,數(shù)據(jù)插入失敗數(shù)據(jù)插入失敗!);end;create or r

11、eplace procedure p_transasbegin insert into c values(666,英語1,null); SAVEPOINT savepoint1; insert into c values(667,化學(xué)1,null); SAVEPOINT savepoint2; insert into c values(667,物理1,null); commit; 存儲過程中的事務(wù)處理存儲過程中的事務(wù)處理exception when dup_val_on_index then rollback to savepoint1; commit; raise_application_e

12、rror(-20001,違反唯一性約束!); when others then rollback;end;CREATE 【OR REPLACE】 FUNCTION function_name(parameter1 【model】 datatype1,parameter2 【mode2】 datatype2 .) RETURN return_datatypeIS ASBEGIN PL/SQL Block;END function_name; 函數(shù)函數(shù)示例：建立一個存儲函數(shù)，統(tǒng)計指定部門的人數(shù)。CREATE OR REPLACE FUNCTION COUNTNUM (p_D# CHAR) RETU

13、RN NUMBER ISV_SUM NUMBER;BEGINSELECT COUNT(*) INTO V_SUM FROM prof WHERE D#=p_D#;RETURN V_SUM;END;函數(shù)的調(diào)用函數(shù)的調(diào)用 在SQL*PLUS中，需要使用CALL或EXECUTE命令， exec dbms_output.put_line(countnum(D1); call dbms_output.put_line(countnum(D1); 注意：注意：set serveroutput onset serveroutput on后可以顯示輸出結(jié)果。后可以顯示輸出結(jié)果。 在在PL/SQL塊中，可以直接

14、引用 begin dbms_output.put_line(countnum(D1); end; 練習(xí)練習(xí)建立存儲過程分別完成圖書管理系統(tǒng)的功能：1借書2預(yù)約3 還書練習(xí)練習(xí) 1、建立存儲過程完成圖書管理系統(tǒng)中的借書功能。 （1）借書時要求輸入借閱流水號，借書證號，圖書編號。（即該函數(shù)有3個輸入?yún)?shù)） （2）借書時，借書日期為系統(tǒng)時間。 （3）圖書的是否借出改為是create or replace procedure p_borrow_book(v_jylsh in number,v_jszh in number,v_tsbh in number)asv_sfjc 圖書.是否借出%type;b

15、egin select 是否借出 into v_sfjc from 圖書 where 圖書編號=v_tsbh; if v_sfjc=否 then insert into 借閱(借閱流水號,借書證號,圖書編號,借書日期) values(v_jylsh,v_jszh,v_tsbh,to_date(to_char(sysdate,YYYY/MM/DD),YYYY/MM/DD); update 圖書 set 是否借出=是 where 圖書編號=v_tsbh; else dbms_output.put_line(該圖書已經(jīng)借出！); end if;end;練習(xí)練習(xí) 1、建立存儲過程完成圖書管理系統(tǒng)中的借

16、書功能。 （1）借書時要求輸入借閱流水號，借書證號，圖書編號。（即該函數(shù)有3個輸入?yún)?shù)） （2）借書時，借書日期為系統(tǒng)時間。 （3）圖書的是否借出改為是create or replace procedure p_borrow_book(v_jylsh in number,v_jszh in number,v_tsbh in number)asv_sfjc 圖書.是否借出%type;begin select 是否借出 into v_sfjc from 圖書 where 圖書編號=v_tsbh; if v_sfjc=否 then insert into 借閱(借閱流水號,借書證號,圖書編號,借書日

17、期) values(v_jylsh,v_jszh,v_tsbh,to_date(to_char(sysdate,YYYY/MM/DD),YYYY/MM/DD); update 圖書 set 是否借出=是 where 圖書編號=v_tsbh; else dbms_output.put_line(該圖書已經(jīng)借出！); end if;end;練習(xí)練習(xí) 2、建立存儲過程完成圖書管理系統(tǒng)中的預(yù)約功能。、建立存儲過程完成圖書管理系統(tǒng)中的預(yù)約功能。 （1）預(yù)約時要求輸入預(yù)約流水號，借書證號，預(yù)約流水號，借書證號，ISBN。（即該函數(shù)有3個輸入?yún)?shù)） (2)存儲過程先檢查輸入的ISBN版本的圖書是否都已借出，

18、如果是則進(jìn)行預(yù)約，否則提示“該書目有可借圖書，請查找”。 （3）預(yù)約時間為系統(tǒng)時間。create or replace procedure p_order_book(v_yylsh in number,v_jszh in number,v_ISBN in number)Asv_sfjc 圖書.是否借出%type;Begin select 是否借出 into v_sfjc from 圖書 where 圖書編號= v_ISBN;if v_sfjc=是 then insert into 預(yù)約(預(yù)約流水號，借書證號，ISBN，預(yù)約時間) values(v_yylsh , v_jszh v_ISBN ,

19、to_date(to_char(sysdate,YYYY/MM/DD),YYYY/MM/DD);else dbms_output.put_line(該書目有可借圖書，請查找！); end if;end;練習(xí)練習(xí)3、建立存儲過程完成圖書管理系統(tǒng)中的還書還書功能。（1）還書時要求輸入借書證號，圖書編號，罰款分類號借書證號，圖書編號，罰款分類號。（即該函數(shù)有3個輸入?yún)?shù)）（3）還書日期為系統(tǒng)日期 （3）圖書的是否借出改為否create or replace procedure p_return_book(v_jszh in number,v_tsbh in number,v_fkflh in number)Asv_sfjc 圖書.是否借出%type;Beginselect 是否借出 into v_sfjc from 圖書 where 圖書編號= v_tsbh ;if v_sfjc=是 thenupdate 借閱 set 歸還日期= to_date(to_char(sysdate,YYYY/MM/DD),YYYY/MM/DD) where 借書證號 = v_jszh and 圖書編號= v_tsbh ;update 借閱 set 罰款分類號= v_fkflh whe