oracle实验8 存储过程与函数的创建

1、我一定要骄傲的走下去，大家每天都要开心向上哦！.编写存储过程 QueryEmp，查询指定员工记录；输入参数为员工编号，输出参数是员工的姓名和工资。如果找到该员工，在屏幕显示该员工已经查到。如果没找到，则捕获异常并处理。存储过程定义成功后，调用该存储过程查看结果。三、实验环境Windows 10， Oracle 11g四、实验步骤1.创建存储过程，根据职工编号删除 scott.emp 表中的相关记录。（1）以 scott 用户连接数据库，然后为 system 用户授予 delete 权限。语句：connect scott/tiger;grant delete on emp to system;截

2、图：oracle 实验 8 存储过程与函数的创建一、实验目的1. 掌握存储过程与函数的概念2. 能够熟练创建和调用存储过程与函数。二、实验内容教材：第八章实验和练习题（全做）1.补充练习题：2.编写函数 get_salary，根据emp 表中的员工编号，获取他的工资。输入参数为员工编号，如果找到该员工，屏幕显示已找到的信息，函数返回值为该员工的工资。如果找不到，捕获并处理异常，函数返回值为 0。函数创建成功后，调用该函数查看效果。.编写函数 get_cnt，根据输入参数部门编号，输出参数输出该部门的人数，返回值是该部门的工资总和。如果如果找不到，捕获并处理异常，函数返回值为 0。函数创建成功后

3、，调用该函数查看效果。.编写存储过程 DelEmp，删除 emp 表中指定员工记录。输入参数为员工编号。如果找到该员工，则删除他的记录，并在屏幕显示该员工被删除。如果没找到，则使用异常处理。存储过程定义成功后，调用该存储过程查看结果。.第 4 页 共 18 页（2） 以 system 用户连接数据库，创建存储过程。语句：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 用户调用 delete_emp 存储过程。语句：execute delete_emp(7369);截图：（4） scott 用户调用 delete_emp 存储过程。语句：grant execute on delete_emp to scott; connect scott/tiger;execute system.delete_emp(7369);截图：2. 创建存储过程，根据职工编号修改 scott.emp 表中该职工的其他信息。（1）

5、 创建新用户，并授予权限。语句：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） 以新用户连接数据库，创建存储过程。语句：connect u1/abcdef;CREATE OR REPLACE PROCEDURE update_emp(no IN scott.emp.empno%TYPE,-引用 emp 表中的某字段的数据类型，必须对该表具有se

6、lect 权限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. -引用的向 中的某字段润数据类型， 必须对该衰具信也l 釭屯 响n 屯R令 I H :;” “ . C"l' Jl'. en;i" 吐r 习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卫pIEt dlt1色5IUpt n3”顺 烂n-七EHS 七飞-1 c-卢OS11g3g 435863“卫 P'd 吐 C 5 CQ 七七屯nP 竺 t conIll叮 准 阿 ” “ hem C叮 9 - ne;五lQS 謹（3） u1 调用 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、 创建存储过程，根据指定的职工编号查询该职工的详细信息。我一定要骄傲的走下去，大家每天都要开心向上哦！（1）创建存储过程。语句：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）调用存储过程。语句：set serveroutput on declareinfo varchar2(50);第 15 页 共

16、 18 页beginselect_emp(7369,info); dbms_output.put_line(info);end;/截图：4. 创建函数，根据给定的部门编号计算该部门所有职工的平均工资。（1） 创建函数。语句：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、-因为上面的语句不触发异常，因此用 if 语句判断是否查询成功return avgsal; elseavgsal:=-1; return avgsal;end if;endavg_sal;/截图：（2） 调用函数。语句：begindbms_output.put_line(avg_sal(&deptno); end;截图：（选择题）1.以下哪种程序单元必须返回数据？（ A ）A.函数 B.存储过程 C.触发器 D.包2. 当建立存储过程时，以下哪个关键字用来定义输出型参数？（ C ）A.INB.PROCEDURE C.OUTD.FUNCTION3.下列哪个语句可以在 SQL*Plus 中

18、直接调用一个存储过程？（ B ）A.RETURN B.EXEC C.SETD.IN4.下面哪些不是存储过程中参数的有效模式？（ D ）A.INB.OUT C.IN OUTD.OUT IN5.函数头部中的 RETURN 语句的作用是什么？（ A ）A.声明返回的数据类型B.调用函数C. 调用过程D. 函数头部不能使用 RETURN 语句（编程题）1. 根据以下要求编写存储过程：输入部门编号，输出scott.emp 表中该部门所有职工的职工编号、姓名、工作岗位。（1） 授予 system 用户对 scott.emp 具有显示的查询权限。（2） 创建存储过程语句：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） 执行存储过程语句： execute pro_depart(20);截图：

20、2. 根据以下要求编写函数：将 scott.emp 表中工资低于平均工资的职工工资加上 200，并返回修改了工资的总人数。（1） 授予 system 用户对 scott.emp 具有修改的权限。（2） 创建函数语句：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） 调用函数语句：begindbms_output.put_line('修改了工资的总人数是： '|fun_sal); end;截图：（简答题）创建与调用存储过程或函数时，应事先授予哪些权限？答：1.首先创建存储过程自身需要的权限，即应授予

22、create procedure 系统权限。2. 用户调用其他用户所创建的存储过程时，应事先授予对该过程的 execute 权限。3. 如果对某表进行增、删、查、改的操作时，应授予 insert、delete、update、select 的显示权限。（补充练习题）1. 编写函数 get_salary，根据 emp 表中的员工编号，获取他的工资。输入参数为员工编号，如果找到该员工，屏幕显示已找到的信息，函数返回值为该员工的工资。如果找不到，捕获并处理异常，函 数返回值为 0。函数创建成功后，调用该函数查看效果。（1） 创建函数语句：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函数已创建。begindbms_output.put_line('该员工工资是：'|get_salary(7369); end;（2） 调用函数语句：截图：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. 编写函数 get_cnt，根据输入参数部门编号，输出参数输出该部门的人数，返回值是该部门的工资总和。如果如果找不到，捕获并处理异常，函数返回值为0。函数创建成功后，调用该函数查看效果。（1） 创建函数语句：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） 调用函数语句：var salary_sum number

27、; var cnt number;exec :salary_sum:=get_cnt(30,:cnt);截图：3. 编写存储过程 DelEmp，删除 emp 表中指定员工记录。输入参数为员工编号。如果找到该员工， 则删除他的记录，并在屏幕显示该员工被删除。如果没找到，则使用自定义异常处理。存储过程定义 成功后，调用该存储过程查看结果。（1） 以 scott 用户连接数据库，然后为 system 用户授予 delete 权限。语句：connect scott/tiger;grant delete on emp to system;截图：（2） 以 system 用户连接数据库，创建存储过程。语句

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|'号员工已经被删除完毕！');except