oracle实验8 存储过程与函数的创建
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
oracle实验8 存储过程与函数的创建
一、实验目的
1.掌握存储过程与函数的概念
2.能够熟练创建和调用存储过程与函数。
二、实验内容
1.教材：第八章实验和练习题（全做）
2.补充练习题：
.编写函数get_salary，根据emp表中的员工编号，获取他的工资。输入参数为员工编号，如果找到该员工，屏幕显示已找到的信息，函数返回值为该员工的工资。如果找不到，捕获并处理异常，函数返回值为0。函数创建成功后，调用该函数查看效果。
.编写函数get_cnt，根据输入参数部门编号，输出参数输出该部门的人数，返回值是该部门的工资总和。如果如果找不到，捕获并处理异常，函数返回值为0。函数创建成功后，调用该函数查看效果。
.编写存储过程DelEmp，删除emp表中指定员工记录。输入参数为员工编号。如果找到该员工，则删除他的记录，并在屏幕显示该员工被删除。如果没找到，则使用异常处理。
存储过程定义成功后，调用该存储过程查看结果。
.编写存储过程QueryEmp，查询指定员工记录；输入参数为员工编号，输出参数是员工的姓名和工资。如果找到该员工，在屏幕显示该员工已经查到。如果没找到，则捕获异常并处理。存储过程定义成功后，调用该存储过程查看结果。
三、实验环境
Windows 10，Oracle 11g
四、实验步骤
1.创建存储过程，根据职工编号删除scott.emp表中的相关记录。
（1）以scott 用户连接数据库，然后为system 用户授予delete 权限。
语句：
connect scott/tiger;
grant delete on emp to system;
截图：
（2）以system 用户连接数据库，创建存储过程。语句：
connect system/orcl1234；
create or replace procedure delete_emp
(id scott.emp.empno%type)
is
begin
delete from scott.emp where empno=id;
exception
when 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）创建新用户，并授予权限。
语句：
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表中的某字段的数据类型，必须对该表具有select权限 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
)
IS
BEGIN
if name is not null then
update scott.emp set ename=name where empno=no;
end if;
if job1 is not null then
update scott.emp set job=job1 where empno=no;
end if;
if mgr1 is not null then
update scott.emp set mgr=mgr1 where empno=no;
end if;
if hiredate1 is not null then
update scott.emp set hiredate=hiredate1 where empno=no;
end if;
if salary is not null then
update scott.emp set sal=salary where empno=no;
end if;
if comm1 is not null then
update scott.emp set comm=comm1 where empno=no;
end if;
if deptno1 is not null then
update scott.emp set deptno=deptno1 where empno=no;
end if;
EXCEPTION
WHEN others THEN
rollback;
END;
/
截图：