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)isr 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);

截图：