PL/SQL(五)：存储过程

一、存储过程

        用途
               用于执行某项操作，命名的 PL/SQL 块,能够接受传递的参数,能够被调用。
        简述
               命名的PL/SQL块
               能够接受参数
               能够被重复调用
               用于执行某项操作
               存储在数据库中

二、过程的参数

      有三种
             1、in 模式
                          默认模式
                          可以传递常量或变量
                          在过程内部，不能赋常量
                         允许有默认值,如：p_id in number default 7369
             2、out 模式 
                         调用时，必须用变量，但会忽略变量值
                         调用结束后，对变量赋值
             3、in out 模式
                         传入参数值，并可以返回改变的值
      备注：
             不能限制参数中数据类型的大小
 out和in out 参数不允许有默认值

四、代码示例

1、in 模式
     参数模式说明: in , 输入模式,默认是in .   表示接收值,在过程代码中不能再赋值.

--in 模式
create or replace procedure param_test_in (num int ,num2 in int)
as
begin
  --num := 12; --in模式的参数,接收到值之后变为常量,不能赋值.
  dbms_output.put_line(num+num2);
end;

declare
  n1 int;
  n2 int;
begin  
  param_test_in(10,20); --给in模式参数传常量值.
  n1:=200;
  n2:=300;
  param_test_in(n1,n2); --给in模式参数传变量.
end;

2、out 模式
     参数模式说明:out , 输出模式. 表示输出值,在过程代码中,可以重新赋值.调用时,必须用变量调用.

--in + out 模式 
/*
  写一个存储过程，包含两个参数，p_sal为薪水参数（传入
  参数），p_mes为信息参数（传出参数）当传入的薪水大于所
  有薪水平均值时，将‘薪水太高了’信息传给信息参数;否则传递
  ‘薪水正常’。
*/
create or replace procedure sal_proc(
       p_sal in emp.sal%type,
       p_mes out varchar )
is
  v_sal  emp.sal%type ;
begin
  select avg(sal) into v_sal from emp ;
  if p_sal > v_sal then
         p_mes:=‘薪水太高了‘;
   else
         p_mes:=‘薪水正常‘;
   end if;
end;

3、in out 模式
    1）必须要用变量调用
    2）过程可以获取到输入变量的值,
    3）过程中变量可重新赋值.
    4）过程执行完,也会输出值给调用时的变量.

-in + out + in out 模式
CREATE OR REPLACE PROCEDURE  raise_sal
(p_id   IN     emp.empno%type, 
 p_name  OUT    varchar2,
 p_sal  IN OUT  number)
IS
BEGIN
  UPDATE emp 
  SET sal = sal + p_sal  
  WHERE empno = p_id;
  
  SELECT sal,ename into p_sal,p_name 
  FROM   emp
  WHERE  empno = p_id;
END raise_sal;

DECLARE
  v_sal  emp.sal%type := 500;
  v_name    emp.ename%type;
BEGIN
  raise_sal(7369,v_name,v_sal);
  dbms_output.put_line(‘雇员:‘||v_name||‘现在的薪水是 ‘||v_sal);
END;

4、其他常用语句

--命令行中,查看过程的错误.
show errors PROCEDURE sal_proc;

--删除过程
drop procedure sal_proc;
--授予权限
  --1）直接授权
grant select on emp to system;  -- 表名 emp，用户名 system
  --2）间接授权
grant execute on sal_proc to system;  --存储过程 sal_proc，用户名 system

--查看用户对象中的procedure和function中的对象名、对象类型 和 状态
SELECT object_name, object_type,status
FROM user_objects
WHERE object_type in (‘PROCEDURE‘,‘FUNCTION‘)
ORDER BY object_name;

--查看类型为【过程】的源代码,大写
SELECT *
FROM   user_source
WHERE type = ‘PROCEDURE‘;