Oracle 存储过程整理及常用几种示例

参考了以下文章，对 Oracle 存储过程常用语法的一个总结，和需要注意的一些问题【下面的存储过程不是按照参考文章的顺序，网页展示的存储过程可能会有点乱，需要拷到plsql 中看】：

一：ORACLE 存储过程

二：简述%TYPE 和 %ROWTYPE 的用法

三：Oracle 基本数据类型

四：Oracle存储过程in、out、in out 模式参数

五：oracle 存储过程的基本语法

六：Oracle 中游标详细用法

七：oracle 游标三种循环

文章包括以下语法的使用：

1、创建【有参】存储过程

2、【有参/无参】游标的使用

3、如何在存储过程中操作 DDL 语句（如truncate，alter，create ....）

4、if、elsif、else 条件判断，for 循环，while 循环，loop 循环的使用

5、存储过程异常处理

 

存储过程的创建

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

CREATE OR REPLACE PROCEDURE TEST_PRO(EMPNO EMP.EMPNO%TYPE,
                                     
                                     DEPTNO VARCHAR, --  定义参数，不可以给长度，给长度会报错
                                     FLAG   OUT CHAR) -- 加 OUT 可以将该参数作为返回值输出，关于out，in，in out 详细参考三
 AS
  --  IS 或者 AS 没有区别

  /* 注意： AS 后的参数和括号中的参数名不能相同，相同会报错*/
  /* %TYPE  使 ENO 的数据类型同 EMP 表的 EMPNO 的数据类型一致，详细参考 二 */
  ENO EMP.EMPNO%TYPE;
  /* 也可以使用Oracle 的数据类型*/
  DNO     CHAR(5);
  DML_SQL VARCHAR2(4000);
  ENAME   EMP.ENAME%TYPE;

  V_NSRXX T_SDS_NSRXX%ROWTYPE;

  TYPE NSRXX_TYPE IS TABLE OF T_SDS_NSRXX%ROWTYPE; /* 表行类型 */

  NO   EMP.EMPNO%TYPE;
  NAME EMP.ENAME%TYPE;

  v_ename VARCHAR2(10);
  v_job   VARCHAR2(10);

  CURSOR EMP_CUR IS
    SELECT ENAME, JOB FROM EMP ORDER BY SAL; -- 声明游标（无参）

  CURSOR EMP_PARAM(EJOB EMP.JOB%TYPE) IS
    SELECT ENAME, JOB FROM EMP WHERE JOB = EJOB ORDER BY SAL; -- 声明游标（有参）

BEGIN
  -- DML 语句
  /*DELETE EMP2 WHERE EMPNO = '7369';
  COMMIT;*/

  -- DDL(数据定义语言) 必须使用 EXECUTE IMMEDIATE
  /*EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP2';*/

  /* 注意：1、使用 EXECUTE IMMEDIATE 操作 DML (数据操纵语言) 默认是不提交的；
  2、如果sql中存在单引号，需要 再加一个单引号转译；
  3、SQL%ROWCOUNT 记录影响行数
        必须放在一个更新或者删除等修改类语句后面执行，select语句用于查询的话无法使用，
        必须放在 COMMIT 上面，否则检查不到影响行数
        当你执行多条修改语句时，按照sql%rowcount 之前执行的最后一条语句修改数为准；*/

  ENAME   := '张三';
  DML_SQL := 'UPDATE EMP2 SET ENAME = ''' || ENAME ||
             ''' WHERE EMPNO = ''7499''';
  EXECUTE IMMEDIATE DML_SQL;
  FLAG := SQL%ROWCOUNT;
  COMMIT;
  /*IF 条件判断，注意 sql 中的 else if  是 elsif  这么写的*/
  IF FLAG > 0 THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE('修改成功');
    END;
  ELSIF FLAG = 0 AND 1 = 1 OR 1 != 2 THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE('修改失败');
    END;
  ELSE
    BEGIN
      DBMS_OUTPUT.PUT_LINE('出现问题');
    END;
  END IF;
  /*SELECT INTO STATEMENT：将SELECT查询的结果存入到变量中，必须只能有一条记录*/
  SELECT EMPNO, ENAME INTO NO, NAME FROM EMP WHERE EMPNO = '7788';
  DBMS_OUTPUT.PUT_LINE(NO || '----' || NAME);

  /* Oracle 游标，详细请看 六，无参*/
  OPEN EMP_CUR;

  DBMS_OUTPUT.PUT_LINE('=====================');

  /* for 循环*/
  FOR I IN REVERSE 1 .. 3 LOOP
    -- REVERSE 大到小 , I 只是单纯的变量名
    FETCH EMP_CUR
      INTO V_ENAME, V_JOB;
    DBMS_OUTPUT.PUT_LINE(V_ENAME || '----' || V_JOB);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('=====================');

  /* loop 循环*/
  LOOP
    FETCH EMP_CUR
      INTO V_ENAME, V_JOB;
    EXIT WHEN EMP_CUR%NOTFOUND; -- 退出条件
    DBMS_OUTPUT.PUT_LINE(V_ENAME || '----' || V_JOB);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('=====================');

  CLOSE EMP_CUR;

  FLAG := 'Y';
  WHILE FLAG = 'Y' LOOP
  
    IF TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
               'YYYY-MM-DD HH24:MI:SS') =
       TO_DATE('2019/4/23 14:53:00', 'YYYY-MM-DD HH24:MI:SS') THEN
      BEGIN
        FLAG := 'N';
      END;
    END IF;
  
  END LOOP;

  /* Oracle 游标，详细请看 六，有参*/
  OPEN EMP_PARAM('MANAGER');

  /*while 循环，里面用到了 两个FETCH*/
  FETCH EMP_PARAM /*第一个fetch语句是把游标的第一行记录赋值给相关变量*/
    INTO V_ENAME, V_JOB;

  WHILE EMP_PARAM%FOUND LOOP
  
    FETCH EMP_PARAM /*第二个fetch语句是让游标指向下一条记录。*/
      INTO V_ENAME, V_JOB;
  
    DBMS_OUTPUT.PUT_LINE(V_ENAME || '----' || V_JOB);
  
  END LOOP; /* 如果没有第二个语句的话，即为死循环，不停地执行loop内的代码。总之，使用while来循环处理游标是最复杂的方法*/

  CLOSE EMP_PARAM;

  -- 处理报错问题  SQLCODE：错误编号   SQLERRM：错误信息
EXCEPTION
  WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误代码是：' || SQLCODE || '，错误信息是：' || SQLERRM ||
                         '，错误行数是：' ||
                         DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
END TEST_PRO;

 

如果本文章对您有所帮助，您可以动一动您的金手指点个赞吧！！！！