Oracle存储过程实用案例

存储过程并不是是应用程序的首选，但在一些其他场景如造数，批量处理数据的时候还用的上，分享一波脚本，当做工具就好。

1、获取一个表的所有列名

-- Created on 2014/5/5 by ADMIN1 
declare 
  -- Local variables here
  i NUMBER := 0;
  CURSOR cur IS SELECT column_name FROM USER_COL_COMMENTS WHERE table_name = 'TABLE';
  cols CLOB;
begin
  -- Test statements here
  FOR col IN cur LOOP
    IF i <> 0 THEN 
      cols := cols ||',';
    END IF;
    i := 1;
    cols := cols || col.COLUMN_NAME;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(cols);
end;

2、循环的几种方式

CREATE OR REPLACE PROCEDURE P_LOOP
AS
V_COUNT NUMBER DEFAULT 0;
CURSOR V_CURSOR IS SELECT * FROM JOBS;
V_JOB JOBS%ROWTYPE;

BEGIN
  --------------------------------------LOOP
  LOOP
    V_COUNT := V_COUNT +1;
    EXIT WHEN V_COUNT>=5;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(V_COUNT);

  -------------------------------------CURSOR
  ---------------%FOUND 可从游标中取出一条记录
  ---------------%NOTFOUND 不能从游标中再取出记录
  ---------------%ISOPEN 游标已经打开
  ---------------%ROWCOUNT 迄今为止从游标中取出的行数
  OPEN V_CURSOR;
  LOOP
    FETCH V_CURSOR INTO V_JOB;
    EXIT WHEN V_CURSOR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(V_JOB.JOB_ID || ','||V_JOB.JOB_TITLE||V_CURSOR%ROWCOUNT );
  END LOOP;
  CLOSE V_CURSOR;

  ----------------------------------FOR
  FOR I IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('FOR...................'||I);
  END LOOP;

  -------------------------------FOR + CURSOR
  FOR ITEM IN V_CURSOR LOOP
     DBMS_OUTPUT.PUT_LINE(ITEM.JOB_ID || ','||ITEM.JOB_TITLE);
  END LOOP;

  -------------------------------WHILE
  V_COUNT:=0;
  WHILE V_COUNT <5
    LOOP
      V_COUNT := V_COUNT +1;
      DBMS_OUTPUT.PUT_LINE(V_COUNT);
    END LOOP;


END;
/

3、大数据量插入

CREATE OR REPLACE PROCEDURE P_BIGDATA_INSERT(P_TABLE_NAME   IN VARCHAR2, --目标表
                                             P_TABLE_COLUMN IN VARCHAR2, --目标字段
                                             P_TABLE_SELECT IN VARCHAR2, --SELECT 查询语句
                                             RETURN_RESULT   OUT NUMBER --返回的结果1,表示成功，０表示失败
                                             ) AS
  /** 
   对大数据量的表进行分批提交
  */
  RUNTIME NUMBER;
  I       NUMBER;
  AMOUNT  NUMBER;
  S_SQL   VARCHAR2(5000);
  P_SIZE NUMBER := 5000;
  Q_SQL CLOB;
BEGIN
  RETURN_RESULT := 0; --开始初始化为0
  --核必逻辑内容，可根据具体的业务逻辑来自定义
  S_SQL := 'select count(1) from (' || P_TABLE_SELECT || ')';
  EXECUTE IMMEDIATE S_SQL INTO AMOUNT;

  --每P_SIZE提交一次
  RUNTIME := AMOUNT MOD P_SIZE;
  IF (RUNTIME > 0) THEN
    RUNTIME := 1 + TRUNC(AMOUNT / P_SIZE);
  ELSIF (RUNTIME = 0) THEN
    RUNTIME := 0 + TRUNC(AMOUNT / P_SIZE);
  END IF;


 --核必逻辑内容，可根据具体的业务逻辑来自定义
  FOR I IN 1 .. RUNTIME LOOP
  Q_SQL:= 'insert into ' || P_TABLE_NAME || ' (' ||
                      P_TABLE_COLUMN || ')  
     select ' || P_TABLE_COLUMN || ' from (select selectSec.*, rownum rownumType  
          from (' || P_TABLE_SELECT ||') selectSec  
         WHERE ROWNUM <= ' || I * P_SIZE || ')  
          WHERE rownumType > ' || (I - 1) * P_SIZE;
--    DBMS_OUTPUT.PUT_LINE(Q_SQL);
    EXECUTE IMMEDIATE Q_SQL;
    --提交
    COMMIT;
  END LOOP;
  RETURN_RESULT := 1;
  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    RETURN_RESULT := 0;
    ROLLBACK;
    --dbms_output.put_line('执行出现异常,错误码='|| SQLCODE || ',错误描述=' || SUBSTR(SQLERRM, 1, 100));
    RAISE;

    RETURN;
END;

4、大数据量删除

CREATE OR REPLACE PROCEDURE P_DEL_BIGDATA --分批提交删除
(P_SQL IN VARCHAR2 --SQL语句，示例：P_DEL_BIGDATA('delete from table where rownum <= 10');
 ) AS
BEGIN

  /** 循环执行*/
  WHILE 1 = 1 LOOP
    EXECUTE IMMEDIATE P_SQL;
    IF SQL%NOTFOUND THEN
      EXIT;
    END IF;
    COMMIT;
  END LOOP;
  COMMIT;
END;

5、存储过程中的异常处理

CREATE OR REPLACE PROCEDURE P_EXCEPTION
/**
1、预定义异常
2、自定义异常

自定义异常编号 –20,000 到 –20,999 
自定义异常消息  2048 byte
*/
AS
v_name employees.first_name%TYPE;
exp1 EXCEPTION;
--PRAGMA EXCEPTION_INIT(exp1, -20000);
BEGIN

  SELECT first_name INTO v_name FROM employees e WHERE e.employee_id = 100;
  IF SQL%FOUND THEN
    --抛出异常 方式一
    --RAISE exp1;

    --方式二
    RAISE_APPLICATION_ERROR(-20000,'异常');
  END IF;

EXCEPTION
  WHEN exp1 THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE||'------>'||SQLERRM);
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE||'->'||SQLERRM);
END;
/