Oracle数据库——存储过程与函数的创建
⼀、涉及内容
1.掌握存储过程与函数的概念。
2.能够熟练创建和调⽤存储过程与函数。⼆、具体操作
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 scott.emp.comm%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; /
截图：
（3） u1调⽤update_emp 过程。
语句： exec update_emp(7369,salary=>2000); 截图：
3.创建存储过程，根据指定的职⼯编号查询该职⼯的详细信息。（1）创建存储过程。语句：
connect scott/tiger;
create or replace procedure select_emp (no in scott.emp.empno%type, emp_information out varchar2)is
r scott.emp%ROWTYPE;begin
select * into r from scott.emp where empno=no;
emp_information:=emp_information||r.ename||' '||r.job||' '||r.sal||' '||r.mgr||' '||r.hiredate||' '||r.comm||' '||r.deptno;exception
when no_data_found then
emp_information:='No person!'; when others then
emp_information:='Error!';End;/
截图：
（2）调⽤存储过程。语句：
set serveroutput on declare
info varchar2(50); begin
select_emp(7369,info); dbms_output.put_line(info); end; /
截图：
4.创建函数，根据给定的部门编号计算该部门所有职⼯的平均⼯资。（1）创建函数。语句：
create or replace function avg_sal (no scott.emp.deptno%type) return number is
avgsal number(7,2); begin
select avg(sal) into avgsal from scott.emp where deptno=no;
if avgsal is not null then --因为上⾯的语句不触发异常，因此⽤if语句判断是否查询成功 return avgsal; else
avgsal:=-1; return avgsal; end if;
end avg_sal; /
截图：
（2）调⽤函数。语句：
begin
dbms_output.put_line(avg_sal(&deptno)); end;
截图：
（选择题）
1. 以下哪种程序单元必须返回数据？（ A ） A.函数 B.存储过程 C.触发器 D.包
2.当建⽴存储过程时，以下哪个关键字⽤来定义输出型参数？（ C ） A.IN B.PROCEDURE C.OUT D.FUNCTION
3.下列哪个语句可以在SQL*Plus中直接调⽤⼀个存储过程？（ B ） A.RETURN B.EXEC C.SET D.IN
4.下⾯哪些不是存储过程中参数的有效模式？（ D ） A.IN B.OUT C.IN OUT D.OUT IN
5.函数头部中的RETURN语句的作⽤是什么？（ A ） A.声明返回的数据类型 B.调⽤函数 C.调⽤过程
D.函数头部不能使⽤RETURN语句
（编程题）
1. 根据以下要求编写存储过程：输⼊部门编号，输出scott.emp 表中该部门所有职⼯的职⼯编号、姓名、⼯作岗位。（1）授予system⽤户对scott.emp具有显⽰的查询权限。
（2）创建存储过程 语句：
create or replace procedure pro_depart (no in scott.emp.deptno%type) is
cursor c1 is select * from scott.emp where deptno=no; begin
dbms_output.put_line('编号 姓名 ⼯作岗位'); for rec in c1 loop
dbms_output.put_line(rec.empno||' '||rec.ename||' '||rec.job); end loop; end;
截图：
（3）执⾏存储过程
语句： execute pro_depart(20);截图：
2.根据以下要求编写函数：将scott.emp 表中⼯资低于平均⼯资的职⼯⼯资加上200，并返回修改了⼯资的总⼈数。（1）授予system⽤户对scott.emp具有修改的权限。
（2）创建函数语句：
conn system/orcl1234;
create or replace function fun_sal return number is
cursor c2 is select * from scott.emp for update; rows number default 0; avg_sal number(7,2); begin
select avg(sal) into avg_sal from scott.emp; for rec in c2 loop
if rec.sal< avg_sal then
update scott.emp set sal=sal+200 where current of c2; rows:=rows+1; end if; end loop; return rows; end;
截图：
（3）调⽤函数语句：
begin
dbms_output.put_line('修改了⼯资的总⼈数是： '||fun_sal); end;
截图：
（简答题）
创建与调⽤存储过程或函数时，应事先授予哪些权限？
答：1.⾸先创建存储过程⾃⾝需要的权限，即应授予create procedure系统权限。 2.⽤户调⽤其他⽤户所创建的存储过程时，应事先授予对该过程的execute权限。
3.如果对某表进⾏增、删、查、改的操作时，应授予insert、delete、update、select的显⽰权限。
（补充练习题）
1. 编写函数get_salary，根据emp表中的员⼯编号，获取他的⼯资。输⼊参数为员⼯编号，如果找到该员⼯，屏幕显⽰已找到的信息，函数返回值为该员⼯的⼯资。如果找不到，捕获并处理异常，函数返回值为0。函数创建成功后，调⽤该函数查看效果。
（1）创建函数语句：
create or replace function get_salary (no in scott.emp.empno%type) return number is
salary scott.emp.sal%type; begin
select sal into salary from scott.emp where empno=no; return salary; exception
when others then return 0; end;
截图：
（2）调⽤函数语句：
begin
dbms_output.put_line('该员⼯⼯资是：'||get_salary(7369)); end;
截图：
语句：
begin
dbms_output.put_line('该员⼯⼯资是：'||get_salary(2000)); end;
截图：
2. 编写函数get_cnt，根据输⼊参数部门编号，输出参数输出该部门的⼈数，返回值是该部门的⼯资总和。如果如果找不到，捕获并处理异常，函数返回值为0。函数创建成功后，调⽤该函数查看效果。（1）创建函数语句：
create or replace function get_cnt (no in scott.dept.deptno%type,
cnt out number ) return number is
salary_sum number(7,2); begin
select sum(sal) into salary_sum from scott.emp where deptno=no; select count(*) into cnt from scott.emp where deptno=no; return salary_sum; exception
when others then return 0; end;
截图：
（2）调⽤函数语句：
var salary_sum number; 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 ⽤户连接数据库，创建存储过程。
语句：
connect system/orcl1234；
create or replace procedure DelEmp (no scott.emp.empno%type) is
no_emp exception; cnt number; begin
select 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||'号员⼯已经被删除完毕！'); exception
when no_emp then
dbms_output.put_line('抱歉！没有找到'||no||'号员⼯！'); end; /
截图：
（3）调⽤存储过程。语句：exec DelEmp(2000);截图：
4. 编写存储过程QueryEmp，查询指定员⼯记录；输⼊参数为员⼯编号，输出参数是员⼯的姓名和⼯资。如果找到该员⼯，在屏幕显⽰该员⼯已经查到。如果没找到，则捕获异常并处理。存储过程定义成功后，调⽤该存储过程查看结果。（1）创建过程语句：
CREATE OR REPLACE PROCEDURE QueryEmp (no IN scott.emp.empno%TYPE,
name OUT scott.emp.ename%TYPE, salary OUT scott.emp.sal%TYPE) IS
BEGIN
SELECT ename,sal into name,salary FROM scott.emp WHERE empno=no; dbms_output.put_line('找到员⼯！'); EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('该职⼯不存在！'); END; /
截图：
（2）执⾏过程语句：
DECLARE
emp_name scott.emp.ename%TYPE; emp_salary scott.emp.sal%TYPE; BEGIN
QueryEmp(7788,emp_name,emp_salary); --调⽤存储过程 IF emp_name IS NOT NULL THEN --如果该职⼯存在，则输出
dbms_output.put_line('姓名是：'||emp_name|| ' ⼯资是：'||emp_salary); END IF; END;
亦可：exec QueryEmp(7788,:ename,:sal);截图：
