Oracle存储过程

  存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数，来执行它，存储过程是数据库中的一个重要对象，在数据量特别庞大的情况下利用存储过程能达到倍速的效果提升。

一、 存储过程的好处

1. 由于数据库执行动作时，是现编译后执行的，然而存储过程是一个编译过的代码块，所以执行速率要比SQL语句高。

2. 一个存储过程在程序在网络中交互时可以替代大堆的SQL语句，所以也能降低网络的通信量，提高通信速率。

3. 通过存储过程能够使没有权限的用户在控制之下间接的存取数据库，从而确保数据的安全。

二、 定义简单的存储过程

create or replace procedure p_emp is
BEGIN
  -- 存储过程中至少要有一行有效代码
  dbms_output.put_line('hello world');
end p_emp;

三、 带参数的存储过程

  带参数的存储过程在定义参数时要指定参数名称 参数类型in/out入参还是出参，in可以不写，默认是入参，参数的数据类型

create or replace procedure p_emp(v_deptno IN emp.deptno%TYPE) is
CURSOR emp_c IS SELECT * FROM emp WHERE deptno=v_deptno;
v_emp_row emp%ROWTYPE;
BEGIN
  FOR v_emp_row IN emp_c LOOP
    dbms_output.put_line(v_emp_row.empno);
  END LOOP;   
end p_emp;

四、 带出参的存储过程

create or replace procedure p_emp(
v_deptno IN emp.deptno%TYPE,
v_num OUT INTEGER) is
BEGIN
  SELECT COUNT(*) INTO v_num FROM emp WHERE deptno=v_deptno;   
end p_emp;

  黄色部分的参数是存储过程的出参，通过这个出参可以看到存储过程执行的结果，存储过程中不建议处理事务，提交和回滚都不建议操作，如果存储过程有嵌套调用的话，让最外层程序来决定是否提交事务。

五、 例题

给一个字符串，'7788,5566,2233'员工编号，传入存储过程，删除这些员工 记录，并给出这些员工删除的结果'true,false,true'删除成功true,否则false，以字符串形式返回。

CREATE OR REPLACE PROCEDURE p_delete(ids VARCHAR,-- 要删除的ids
                                     rs OUT VARCHAR) -- 删除的结果
IS
v_ids VARCHAR(50) :=ids;-- 赋值给变量，因为入参无法改变
v_id VARCHAR(5);-- 截取出的id
n NUMBER;-- 逗号的位置
BEGIN
  LOOP
    EXIT WHEN v_ids IS NULL;-- ids为null时退出循环
    n:=INSTR(v_ids,',');-- 找逗号的位置
    IF n=0 THEN -- 没有逗号
      v_id:=v_ids;
      v_ids:=NULL;-- ids变为null，标识着准备退出循环
    ELSE
      v_id:=SUBSTR(v_ids,1,n-1);-- 找逗号前的id
      v_ids:=SUBSTR(v_ids,n+1);-- ids更改为剩下的id信息
    END IF;
    DELETE FROM emp WHERE empno=v_id;-- 删除记录
    IF SQL%ROWCOUNT=1 THEN -- 判断结果
      IF rs IS NULL THEN-- 拼接结果
        rs:='true';
      ELSE
        rs:=rs||',true';
      END IF;
    ELSE
      IF rs IS NULL THEN
        rs:='false';
      ELSE
        rs:=rs||',false';
      END IF;
    END IF;
  END LOOP;
END;


-- 调用存储过程
declare 
  v_result VARCHAR2(50);
BEGIN
  
  p_delete('7788,5566,2233',v_result);
  COMMIT;
  dbms_output.put_line('execute result:'||v_result); 
  -- execute result:true,false,false
end;