oracle存储过程的创建与调用（实验8.3）

 alter user scott identified by a12345 account unlock;

 grant connect ,resource to scott;

1.创建存储过程，根据职工编号删除Scott.emp表中的相关记录。

（1）以Scott用户连接数据库，然后为system用户授予delete权限。
 conn scott/a12345;

 grant delete on emp to system;

（2）以system 用户连接数据库，创建存储过程。
connect system/a12345;

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/a12345;
execute system.delete_emp(7369);

2.创建存储过程，根据职工编号修改scott.emp表中该职工的其他信息。

（1）   创建新用户，并授予权限。
connect system/a12345;

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/a12345;
  
  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;