oracle實驗8 存儲過程與函數(shù)的創(chuàng)建

1、我一定要驕傲的走下去，大家每天都要開心向上哦！.編寫存儲過程 QueryEmp，查詢指定員工記錄；輸入?yún)?shù)為員工編號，輸出參數(shù)是員工的姓名和工資。如果找到該員工，在屏幕顯示該員工已經(jīng)查到。如果沒找到，則捕獲異常并處理。存儲過程定義成功后，調(diào)用該存儲過程查看結(jié)果。三、實驗環(huán)境Windows 10， Oracle 11g四、實驗步驟1.創(chuàng)建存儲過程，根據(jù)職工編號刪除 scott.emp 表中的相關(guān)記錄。（1）以 scott 用戶連接數(shù)據(jù)庫，然后為 system 用戶授予 delete 權(quán)限。語句：connect scott/tiger;grant delete on emp to system;截

2、圖：oracle 實驗 8 存儲過程與函數(shù)的創(chuàng)建一、實驗?zāi)康?. 掌握存儲過程與函數(shù)的概念2. 能夠熟練創(chuàng)建和調(diào)用存儲過程與函數(shù)。二、實驗內(nèi)容教材：第八章實驗和練習(xí)題（全做）1.補充練習(xí)題：2.編寫函數(shù) get_salary，根據(jù)emp 表中的員工編號，獲取他的工資。輸入?yún)?shù)為員工編號，如果找到該員工，屏幕顯示已找到的信息，函數(shù)返回值為該員工的工資。如果找不到，捕獲并處理異常，函數(shù)返回值為 0。函數(shù)創(chuàng)建成功后，調(diào)用該函數(shù)查看效果。.編寫函數(shù) get_cnt，根據(jù)輸入?yún)?shù)部門編號，輸出參數(shù)輸出該部門的人數(shù)，返回值是該部門的工資總和。如果如果找不到，捕獲并處理異常，函數(shù)返回值為 0。函數(shù)創(chuàng)建成功后

3、，調(diào)用該函數(shù)查看效果。.編寫存儲過程 DelEmp，刪除 emp 表中指定員工記錄。輸入?yún)?shù)為員工編號。如果找到該員工，則刪除他的記錄，并在屏幕顯示該員工被刪除。如果沒找到，則使用異常處理。存儲過程定義成功后，調(diào)用該存儲過程查看結(jié)果。.第 4 頁 共 18 頁（2） 以 system 用戶連接數(shù)據(jù)庫，創(chuàng)建存儲過程。語句：connect system/orcl1234；create or replace procedure delete_emp (id scott.emp.empno%type)is begindelete from scott.emp where empno=id; excep

4、tionwhen others then dbms_output.put_line('errors');end；截圖：（3） system 用戶調(diào)用 delete_emp 存儲過程。語句：execute delete_emp(7369);截圖：（4） scott 用戶調(diào)用 delete_emp 存儲過程。語句：grant execute on delete_emp to scott; connect scott/tiger;execute system.delete_emp(7369);截圖：2. 創(chuàng)建存儲過程，根據(jù)職工編號修改 scott.emp 表中該職工的其他信息。（1）

5、 創(chuàng)建新用戶，并授予權(quán)限。語句：connect system/orcl1234;create user u1 identified by abcdef;grant create session, create procedure to u1;grant select,update on scott.emp to u1;截圖：（2） 以新用戶連接數(shù)據(jù)庫，創(chuàng)建存儲過程。語句：connect u1/abcdef;CREATE OR REPLACE PROCEDURE update_emp(no IN scott.emp.empno%TYPE,-引用 emp 表中的某字段的數(shù)據(jù)類型，必須對該表具有se

6、lect 權(quán)限name IN scott.emp.ename%TYPE DEFAULT NULL,job1 IN scott.emp.job%TYPE DEFAULT NULL, mgr1 IN scott.emp.mgr%TYPE DEFAULT NULL,hiredate1 scott.emp.hiredate%TYPE DEFAULT NULL, salary scott.emp.sal%TYPE DEFAULT NULL,comm1 m%TYPE DEFAULT NULL, deptno1 scott.emp.deptno%TYPE DEFAULT NULL) ISBEGINif na

7、me is not null thenupdate scott.emp set ename=name where empno=no; end if;if job1 is not null thenupdate scott.emp set job=job1 where empno=no; end if;if mgr1 is not null thenupdate scott.emp set mgr=mgr1 where empno=no; end if;if hiredate1 is not null thenupdate scott.emp set hiredate=hiredate1 whe

8、re empno=no; end if;if salary is not null thenupdate scott.emp set sal=salary where empno=no; end if;if comm1 is not null thenupdate scott.emp set comm=comm1 where empno=no; end if;if deptno1 is not null thenupdate scott.emp set deptno=deptno1 where empno=no; end if;EXCEPTIONWHEN others THEN rollbac

9、k;END;/截圖：我一定要驕傲的走下去，大家每天都要開心向上哦！第 5 頁 共 18 頁討;:n n e ct. u 11 a b cd e;f- |12 3 詛56 7 110.n1 2 3 ”5 6 70511冒圃貯干凰，L- 訂.”“”“亙屯,1,u u Lno, IH !: Cot t 貯 p .iernp n心l l"PE. -引用的向 中的某字段潤數(shù)據(jù)類型， 必須對該衰具信也l 釭屯 響n 屯R令 I H :;” “ . C"l' Jl'. en;i" 吐r 習(xí)PE PEF的 LJ 即 ll. L., j 吵 1 “ gc ot 芘

10、一 作 叩 j ob l l"Pf DH' 血 LT HIILIL, 叩 1 比 :5Got 已 印 :p,. og心1r PE DE F血 L T HULIL心 ed 3t e1 s c吐 t 吧l'lp. h, ir 心 K t r ” PE DEFAULT 叩 L'-過 1 茹 µ scot t 叩 . H l" l ''ri"E DEli'IIUL1 HULL" .寸| 5c吐 t 乓叩 心 中 西llEFhULT HULL .1 叫·1 寸1g11 1 1 12 2 22 22

11、 2 2 22扣 pt nu1 5cot 比J 叩 dept n吐l VP E II EFAULi IHULsGiltl讓 na”:lis not n111l l 忙1飛 n叩 吐 貯 S EoEt 記mp s吧t ena 順 叩 呻咖 曠E 叩 叩 ”“;口 nd if;if j的 1 釭 not n11111t lh,f!in叩,;i t 倫 5 Cu 讓 叮 lp 5吐 j Q忙 j 叩咖 r e 盯 凹 pn 忙 叩 ；叩 ， 江 ：if n gr 1 年 not 叩 11 t i 心 n1 23 115 iI7 110.叩 中 比 s c吐 t 吧口 p s吐 叩 于一“ 9曠1 如

12、 r e 巳'mpnQ - no;盯 Id i.F;” : hi" ，心 丘 1 杠 Illot mu1旦C壓 n叩 cl.i t e s eot:t 尺np s t h立l"eclat, ehi r-作d,at e1 h 巳r e e, p pno啊 nol:叩年干 的 l 扣 ， 江 的 t iiuU t 阮 n叩 (lat e s eottt . 記np 過5心 5吐ar l,J Nher 貯 口可 叩 噩no:印 d if;亡f C° ” “ 1 i 5 叩 t IiiUl 1 也 印一，nn-­i幾­ 幾叩乒el尸色叩1n&#

13、173;，n配色d九一nlitlh衛(wèi)pIEt dlt1色5IUpt n3”順 爛n-七EHS 七飛-1 c-盧OS11g3g 435863“衛(wèi) P'd 吐 C 5 CQ 七七屯nP 竺 t conIll叮 準 阿 ” “ hem C叮 9 - ne;五lQS 謹（3） u1 調(diào)用 update_emp 過程。語句： exec update_emp(7369,salary=>2000);截圖：葉 e x e c u p d a t e_ e mp (7 3 的 ，s .al .ar .!J = 2 0的 ）；PL / S QL 過程已成功完成。.,.·- '|3.

14、 創(chuàng)建存儲過程，根據(jù)指定的職工編號查詢該職工的詳細信息。我一定要驕傲的走下去，大家每天都要開心向上哦?。?）創(chuàng)建存儲過程。語句：connect scott/tiger;create or replace procedure select_emp (no in scott.emp.empno%type, emp_information out varchar2)isr scott.emp%ROWTYPE; beginselect * into r from scott.emp where empno=no; emp_information:=emp_information|r.ename|

15、9; '|r.job|' '|r.sal|''|r.mgr|''|r.hiredate|''|m|''|r.deptno; exceptionwhen no_data_found then emp_information:='No person!'when others then emp_information:='Error!'End;/截圖：（2）調(diào)用存儲過程。語句：set serveroutput on declareinfo varchar2(50);第 15 頁 共

16、 18 頁beginselect_emp(7369,info); dbms_output.put_line(info);end;/截圖：4. 創(chuàng)建函數(shù)，根據(jù)給定的部門編號計算該部門所有職工的平均工資。（1） 創(chuàng)建函數(shù)。語句：create or replace function avg_sal (no scott.emp.deptno%type) return numberisavgsal number(7,2); beginselect avg(sal) into avgsal from scott.emp where deptno=no;if avgsal is not null then

17、-因為上面的語句不觸發(fā)異常，因此用 if 語句判斷是否查詢成功return avgsal; elseavgsal:=-1; return avgsal;end if;endavg_sal;/截圖：（2） 調(diào)用函數(shù)。語句：begindbms_output.put_line(avg_sal(&deptno); end;截圖：（選擇題）1.以下哪種程序單元必須返回數(shù)據(jù)？（ A ）A.函數(shù) B.存儲過程 C.觸發(fā)器 D.包2. 當(dāng)建立存儲過程時，以下哪個關(guān)鍵字用來定義輸出型參數(shù)？（ C ）A.INB.PROCEDURE C.OUTD.FUNCTION3.下列哪個語句可以在 SQL*Plus 中

18、直接調(diào)用一個存儲過程？（ B ）A.RETURN B.EXEC C.SETD.IN4.下面哪些不是存儲過程中參數(shù)的有效模式？（ D ）A.INB.OUT C.IN OUTD.OUT IN5.函數(shù)頭部中的 RETURN 語句的作用是什么？（ A ）A.聲明返回的數(shù)據(jù)類型B.調(diào)用函數(shù)C. 調(diào)用過程D. 函數(shù)頭部不能使用 RETURN 語句（編程題）1. 根據(jù)以下要求編寫存儲過程：輸入部門編號，輸出scott.emp 表中該部門所有職工的職工編號、姓名、工作崗位。（1） 授予 system 用戶對 scott.emp 具有顯示的查詢權(quán)限。（2） 創(chuàng)建存儲過程語句：create or replace

19、procedure pro_depart (no in scott.emp.deptno%type)iscursor c1 is select * from scott.emp where deptno=no; begindbms_output.put_line('編號 姓名工作崗位'); for rec in c1loopdbms_output.put_line(rec.empno|''|rec.ename|''|rec.job); end loop;end;截圖：（3） 執(zhí)行存儲過程語句： execute pro_depart(20);截圖：

20、2. 根據(jù)以下要求編寫函數(shù)：將 scott.emp 表中工資低于平均工資的職工工資加上 200，并返回修改了工資的總?cè)藬?shù)。（1） 授予 system 用戶對 scott.emp 具有修改的權(quán)限。（2） 創(chuàng)建函數(shù)語句：conn system/orcl1234;create or replace function fun_sal return numberiscursor c2 is select * from scott.emp for update; rows number default 0;avg_sal number(7,2); beginselect avg(sal) into avg

21、_sal from scott.emp; for rec in c2loopif rec.sal< avg_sal thenupdate scott.emp set sal=sal+200 where current of c2; rows:=rows+1;end if; end loop;return rows; end;截圖：（3） 調(diào)用函數(shù)語句：begindbms_output.put_line('修改了工資的總?cè)藬?shù)是： '|fun_sal); end;截圖：（簡答題）創(chuàng)建與調(diào)用存儲過程或函數(shù)時，應(yīng)事先授予哪些權(quán)限？答：1.首先創(chuàng)建存儲過程自身需要的權(quán)限，即應(yīng)授予

22、create procedure 系統(tǒng)權(quán)限。2. 用戶調(diào)用其他用戶所創(chuàng)建的存儲過程時，應(yīng)事先授予對該過程的 execute 權(quán)限。3. 如果對某表進行增、刪、查、改的操作時，應(yīng)授予 insert、delete、update、select 的顯示權(quán)限。（補充練習(xí)題）1. 編寫函數(shù) get_salary，根據(jù) emp 表中的員工編號，獲取他的工資。輸入?yún)?shù)為員工編號，如果找到該員工，屏幕顯示已找到的信息，函數(shù)返回值為該員工的工資。如果找不到，捕獲并處理異常，函 數(shù)返回值為 0。函數(shù)創(chuàng)建成功后，調(diào)用該函數(shù)查看效果。（1） 創(chuàng)建函數(shù)語句：create or replace function get_s

23、alary (no in scott.emp.empno%type)return number issalary scott.emp.sal%type; beginselect sal into salary from scott.emp where empno=no; return salary;exceptionwhen others then return 0;end;截圖：“2eraltt o c sa5一tge)e.，ernppf0 ”二U­ht%1a 5-pn e-tt0c 5i1tt % C-unn up f me e - cpa m 1 e rop-eet brtm-

24、uurc n 05n” t了芒蘭王ter乒L、，2 3 4 5 6 7 8 9 0 1 2 3-UsI L ， 雙 L旦 平 王 L., )圾襯）兀）扭io。nn。peerh" ep.llIouJra1a snehtt-·nyi1 ra 11aa 555r-.e0hntnnDorrc riu1 1 1 1函數(shù)已創(chuàng)建。begindbms_output.put_line('該員工工資是：'|get_salary(7369); end;（2） 調(diào)用函數(shù)語句：截圖：begind b111s_ out put . put _ l i n e ' 該員工工資是；

25、l l ge t _ s a l a t-""9( 73 69 ; end;123SQL陔員工工 資是16 00PL/ SQL 過程已成功完成。語句：begindbms_output.put_line('該員工工資是：'|get_salary(2000); end;截圖：2. 編寫函數(shù) get_cnt，根據(jù)輸入?yún)?shù)部門編號，輸出參數(shù)輸出該部門的人數(shù)，返回值是該部門的工資總和。如果如果找不到，捕獲并處理異常，函數(shù)返回值為0。函數(shù)創(chuàng)建成功后，調(diào)用該函數(shù)查看效果。（1） 創(chuàng)建函數(shù)語句：create or replace function get_cnt (no i

26、n scott.dept.deptno%type, cnt out number )return number issalary_sum number(7,2); beginselect sum(sal) into salary_sum from scott.emp where deptno=no; select count(*) into cnt from scott.emp where deptno=no; return salary_sum;exceptionwhen others then return 0;end;截圖：（2） 調(diào)用函數(shù)語句：var salary_sum number

27、; var cnt number;exec :salary_sum:=get_cnt(30,:cnt);截圖：3. 編寫存儲過程 DelEmp，刪除 emp 表中指定員工記錄。輸入?yún)?shù)為員工編號。如果找到該員工， 則刪除他的記錄，并在屏幕顯示該員工被刪除。如果沒找到，則使用自定義異常處理。存儲過程定義 成功后，調(diào)用該存儲過程查看結(jié)果。（1） 以 scott 用戶連接數(shù)據(jù)庫，然后為 system 用戶授予 delete 權(quán)限。語句：connect scott/tiger;grant delete on emp to system;截圖：（2） 以 system 用戶連接數(shù)據(jù)庫，創(chuàng)建存儲過程。語句

28、：connect system/orcl1234；create or replace procedure DelEmp (no scott.emp.empno%type)isno_emp exception; cnt number;beginselect count(*) into cnt from scott.emp where empno=no;我一定要驕傲的走下去，大家每天都要開心向上哦！if cnt=0 then raise no_emp;end if;delete from scott.emp where empno=no; dbms_output.put_line(no|'號員工已經(jīng)被刪除完畢！');except