Oracle存储过程应用实例

引子

记录工作过程中特耗时间的一个问题的优化过程。核心生产环境，有专门的系统有专人维护基础配置数据，如销管数据。而测试环境无维护，这些数据都有有效期，经常遇到过期，导致系统无法正常使用。

以往的解决办法：
1 首先要联系到外围管理系统（销售管理）相关责任人，协助在测试环境同步做配置（这涉及到跨团队的沟通）；2 配置完审核通过后，待定时同步核心，确认无误后可使用（需等待，特耗时间）。

新解决方案：
由于不需要监管报备的配置信息，生产配置信息更加准确，所以确定写数据库存过，迁移生产环境配置数据到测试。

方案1

工具：数据库存过

思路：数据库新建导入表T_IMPORT_TABLE，保存待操作表。按顺序遍历这些表，在遍历过程中先删表中符合条件的数据，然后将生产环境符合条件的数据迁入到当前环境中

   /*****************************************************************************\
   * Name: P_IMPORT_CHA_DATA
   * PROCEDURE: 导生产环境基础数据（代理人信息）
   * Paramater: IC_C_CHA_CDE 代理人代码
   * Programmer: lyt
   * Date: 2019/10/12
   * Update:
   * 需导入表配置：T_IMPORT_TABLE WHERE C_MARK = '2';
   * 维护dbquery中间库表结构：SELECT 'CREATE TABLE T_' || C_TABLE || ' AS SELECT * FROM ZSSYS.' || C_TABLE || ' WHERE ROWNUM = 0;',A.* FROM T_IMPORT_TABLE A；
   * 如果手续费打包时提示：算税平台找不到该代理人，需联系算税平台（沈一栋）邵先路，将算税生产环境信息同步到对应环境
  \*****************************************************************************/
 
  PROCEDURE P_IMPORT_CHA_DATA(IC_C_CHA_CDE VARCHAR2) AS
    LB_SQL      CLOB;
    LB_COLS     CLOB;
    LB_COND     CLOB;
    LN_ROWS     NUMBER;
    CURSOR CUR_TABLE IS
      SELECT * FROM T_IMPORT_TABLE WHERE C_MARK = '2';
  BEGIN     
    FOR LR_TABLE IN CUR_TABLE LOOP
    
      SELECT WM_CONCAT(COLUMN_NAME)
        INTO LB_COLS
        FROM USER_TAB_COLS@LINK_CORE
       WHERE TABLE_NAME = 'T_' || UPPER(LR_TABLE.C_TABLE)
       ORDER BY COLUMN_ID;
    
      LB_COND := ' WHERE ';
      
      IF (UPPER(LR_TABLE.C_TABLE) = 'WEB_CUS_COM_ACCT') THEN
        LB_COND := LB_COND || 'C_REL_CDE ';
      ELSIF (UPPER(LR_TABLE.C_TABLE) = 'WEB_CUS_CONFER') THEN
        LB_COND := LB_COND || 'C_CLNT_CDE ';
      ELSIF (UPPER(LR_TABLE.C_TABLE) = 'WEB_CUS_CONFER_DTL' OR UPPER(LR_TABLE.C_TABLE) = 'WEB_AUTH_CONFER') THEN
        LB_COND := LB_COND || 'C_AGT_AGR_NO IN (SELECT C_AGT_AGR_NO FROM ZSSYS.WEB_CUS_CONFER  WHERE C_CLNT_CDE';
      ELSIF (UPPER(LR_TABLE.C_TABLE) = 'WEB_ORG_SALES') THEN
        LB_COND := LB_COND || 'C_SLS_CDE IN(SELECT C_SLS_CDE FROM ZSSYS.WEB_CUS_CHA WHERE C_CHA_CDE';
      ELSE
        LB_COND := LB_COND || 'C_CHA_CDE ';
      END IF;
              
      IF (UPPER(LR_TABLE.C_TABLE) = 'WEB_CUS_CONFER_DTL' OR UPPER(LR_TABLE.C_TABLE) = 'WEB_AUTH_CONFER' OR UPPER(LR_TABLE.C_TABLE) = 'WEB_ORG_SALES') THEN
        LB_COND := LB_COND || '= ''' || IC_C_CHA_CDE || ''')';
      ELSE
        LB_COND := LB_COND || '= ''' || IC_C_CHA_CDE || '''';
      END IF;
      
      LB_SQL := 'DELETE FROM ZSSYS.' || LR_TABLE.C_TABLE || LB_COND;
      
      EXECUTE IMMEDIATE LB_SQL;

      LB_SQL := 'INSERT INTO ZSSYS.' || LR_TABLE.C_TABLE || '(' || LB_COLS ||
      ')  SELECT ' || LB_COLS || ' FROM ' || 'ZSSYS.' ||
                LR_TABLE.C_TABLE || '@LINK_CORE '|| LB_COND;
                
      EXECUTE IMMEDIATE LB_SQL;
    END IF;
    END LOOP;
    --COMMIT;
  END P_IMPORT_CHA_DATA;

方案2

版本1.1存在问题：由于一些表之间存在外键约束，外键约束要求必须先删除子表的记录才能够删主表的记录，而插入数据时必须先插入主表的记录再插入子表的记录，一个循环中同时完成删除和插入这个设计不合理，即如果原始记录已存在删除操作会失败。

解决思路：

将外键设为无效，然后再对表进行删除插入操作（需DBA权限，且可能对数据有效性产生影响）
首先反向遍历表完成全部删除操作（删除符合条件的数据），然后正向遍历表完成插入操作，这里需用到动态索引

  /*****************************************************************************\
   * Name: P_IMPORT_CHA_DATA
   * PROCEDURE: 导生产环境基础数据（代理人信息）
   * Paramater: IC_C_CHA_CDE 代理人代码
   * Programmer: lyt
   * Date: 2019/10/12
   * Update:
   * 需导入表配置：T_IMPORT_TABLE WHERE C_MARK = '2';
   * 如果手续费打包时提示：算税平台找不到该代理人，需联系算税平台，将算税生产环境信息同步到对应环境
  \*****************************************************************************/

  PROCEDURE P_IMPORT_CHA_DATA(IC_C_CHA_CDE VARCHAR2) AS
    TYPE ref_cursor_type IS REF CURSOR;
    CUR_TABLE ref_cursor_type;
    LR_TABLE  ZSSYS.T_IMPORT_TABLE%ROWTYPE;
    LB_SQL    CLOB;
    LB_COLS   CLOB;
    LB_COND   CLOB;
  BEGIN
    FOR i IN 1 .. 2 LOOP
    
      IF (i = 1) THEN
        OPEN CUR_TABLE FOR
          SELECT *
            FROM ZSSYS.T_IMPORT_TABLE
           WHERE C_MARK = '2'
           ORDER BY C_TABLE_NO DESC;
      ELSE
        OPEN CUR_TABLE FOR
          SELECT *
            FROM ZSSYS.T_IMPORT_TABLE
           WHERE C_MARK = '2'
           ORDER BY C_TABLE_NO;
      END IF;
    
      LOOP
        FETCH CUR_TABLE
          INTO LR_TABLE;
        EXIT WHEN CUR_TABLE%NOTFOUND;
      
        SELECT WM_CONCAT(COLUMN_NAME)
          INTO LB_COLS
          FROM DBA_TAB_COLS@LINK_CORE
         WHERE TABLE_NAME = 'T_' || UPPER(LR_TABLE.C_TABLE)
           AND OWNER = 'ZSSYS'
         ORDER BY COLUMN_ID;
      
        LB_COND := ' WHERE ';
      
        IF (UPPER(LR_TABLE.C_TABLE) = 'WEB_CUS_COM_ACCT') THEN
          LB_COND := LB_COND || 'C_REL_CDE ';
        ELSIF (UPPER(LR_TABLE.C_TABLE) = 'WEB_CUS_CONFER') THEN
          LB_COND := LB_COND || 'C_CLNT_CDE ';
        ELSIF (UPPER(LR_TABLE.C_TABLE) = 'WEB_CUS_CONFER_DTL' OR
              UPPER(LR_TABLE.C_TABLE) = 'WEB_AUTH_CONFER') THEN
          LB_COND := LB_COND ||
                     'C_AGT_AGR_NO IN (SELECT C_AGT_AGR_NO FROM ZSSYS.WEB_CUS_CONFER@LINK_CORE  WHERE C_CLNT_CDE';
        ELSIF (UPPER(LR_TABLE.C_TABLE) = 'WEB_ORG_SALES') THEN
          LB_COND := LB_COND ||
                     'C_SLS_CDE IN(SELECT C_SLS_CDE FROM ZSSYS.WEB_CUS_CHA@LINK_CORE WHERE C_CHA_CDE';
        ELSE
          LB_COND := LB_COND || 'C_CHA_CDE ';
        END IF;
      
        IF (UPPER(LR_TABLE.C_TABLE) = 'WEB_CUS_CONFER_DTL' OR
           UPPER(LR_TABLE.C_TABLE) = 'WEB_AUTH_CONFER' OR
           UPPER(LR_TABLE.C_TABLE) = 'WEB_ORG_SALES') THEN
          LB_COND := LB_COND || '= ''' || IC_C_CHA_CDE || ''')';
        ELSE
          LB_COND := LB_COND || '= ''' || IC_C_CHA_CDE || '''';
        END IF;
      
        IF (i = 1) THEN
          LB_SQL := 'DELETE FROM ZSSYS.' || LR_TABLE.C_TABLE || LB_COND;
        ELSE
          LB_SQL := 'INSERT INTO ZSSYS.' || LR_TABLE.C_TABLE || '(' ||
                    LB_COLS || ')  SELECT ' || LB_COLS || ' FROM ' ||
                    'ZSSYS.' || LR_TABLE.C_TABLE || '@LINK_CORE' || LB_COND;
        END IF;
        EXECUTE IMMEDIATE LB_SQL;
      
      END LOOP;
      IF CUR_TABLE%ISOPEN THEN
        --close cursor
        CLOSE CUR_TABLE;
      END IF;
    END LOOP;
  END P_IMPORT_CHA_DATA;

方案3

思路同1.2，区别是将拼装sql where 条件部分逻辑抽取为配置项，放在数据库中

  /*****************************************************************************\
   * Name: P_IMPORT_CORE_CHA_NEW
   * PROCEDURE: 导生产环境基础数据（代理人信息）
   * Paramater: IC_C_CHA_CDE 代理人代码
   * Programmer: lyt
   * Date: 2019/10/12
   * Update:
   * 需导入表配置：T_IMPORT_TABLE WHERE C_MARK = '2';
   * 如果手续费打包时提示：算税平台找不到该代理人，需联系算税平台，将算税生产环境信息同步到对应环境
  \*****************************************************************************/

  PROCEDURE P_IMPORT_CORE_CHA_NEW(IC_C_CHA_CDE VARCHAR2) AS
    TYPE ref_cursor_type IS REF CURSOR;
    CUR_TABLE ref_cursor_type;
    LR_TABLE  ZSSYS.T_IMPORT_TABLE%ROWTYPE;
    LB_SQL    CLOB;
    LB_COLS   CLOB;
    LB_COND   CLOB;
    x         number;
  BEGIN
    FOR i IN 1 .. 2 LOOP
    
      IF (i = 1) THEN
        OPEN CUR_TABLE FOR
          SELECT *
            FROM ZSSYS.T_IMPORT_TABLE
           WHERE C_MARK = '2'
           ORDER BY C_TABLE_NO DESC;
      ELSE
        OPEN CUR_TABLE FOR
          SELECT *
            FROM ZSSYS.T_IMPORT_TABLE
           WHERE C_MARK = '2'
           ORDER BY C_TABLE_NO;
      END IF;
    
      LOOP
        FETCH CUR_TABLE
          INTO LR_TABLE;
        EXIT WHEN CUR_TABLE%NOTFOUND;
      
        SELECT WM_CONCAT(COLUMN_NAME)
          INTO LB_COLS
          FROM DBA_TAB_COLS@LINK_CORE
         WHERE TABLE_NAME = UPPER(LR_TABLE.C_TABLE)
           AND OWNER = 'ZSSYS'
         ORDER BY COLUMN_ID;
      
        LB_COND := ' WHERE ' || UPPER(LR_TABLE.C_COND) || ' = ''' || IC_C_CHA_CDE || '''';
      
        x := LENGTH(LR_TABLE.C_COND) -
             LENGTH(REPLACE(LR_TABLE.C_COND, '(', ''));
        WHILE x > 0 LOOP
          LB_COND := LB_COND || ')';
          x       := x + 1;
        END LOOP;
      
        IF (i = 1) THEN
          LB_SQL := 'DELETE FROM ZSSYS.' || LR_TABLE.C_TABLE || LB_COND;
        ELSE
          LB_SQL := 'INSERT INTO ZSSYS.' || LR_TABLE.C_TABLE || '(' ||
                    LB_COLS || ')  SELECT ' || LB_COLS || ' FROM ' ||
                    'ZSSYS.' || LR_TABLE.C_TABLE || '@LINK_CORE' || LB_COND;
        END IF;
        EXECUTE IMMEDIATE LB_SQL;
      
      END LOOP;
      IF CUR_TABLE%ISOPEN THEN
        --close cursor
        CLOSE CUR_TABLE;
      END IF;
    END LOOP;
  END P_IMPORT_CORE_CHA_NEW;

补充 T_IMPORT_TABLE建表语句，以及表结构

-- Create table
create table T_IMPORT_TABLE
(
  T_CRT_TM     DATE default SYSDATE,
  C_TABLE      VARCHAR2(100),
  C_TABLE_NAME VARCHAR2(100),
  C_MARK       CHAR(1),
  C_TABLE_NO   NUMBER,
  C_COND       VARCHAR2(200)
)

知识点补充

【知识点1】 oracle 中的for循环

#例子
FOR LR_TABLE IN CUR_TABLE LOOP
END LOOP;

【知识点2】 oracle 中的条件分支语句

#例子
IF () THEN
...;
ELSIF () THEN
...;
ELSE
...;
END IF;

【知识点3】游标 cursor

Cursor类型包含三种: 隐式Cursor，显式Cursor和Ref Cursor（动态Cursor）。

1). 隐式游标：无需定义，Select /Update / Insert/Delete操作，就是隐式Cursor

2). 显式游标：

cursor [(<param_list>)]is <select_statement>;

3). 动态游标：

Type [Cursor type name] is ref cursor

游标的属性（4个）1.found　　2.notfound　　3.rowcount　　4.isopen

【知识点4】关于存过调试

1 如何打断点
鼠标切到行号上，右键，点击 Set Breakpoint

2 如何调试
在plsql左侧存过列表中选择要调试的存过，然后再点Test进入调试页，如下：

最上面一排是debug相关按键区域，中间显示存过代码，左下为变量值跟踪显示区域（填写变量名称，执行过程中查看变量值）

posted @ 2019-10-24 18:17 雨燕阅读(14059) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

雨燕

管理学之父彼得德鲁克说：“如果你不能度量它，你就无法改进它”

Oracle存储过程应用实例

引子

方案1

方案2

方案3

知识点补充

【知识点1】 oracle 中的for循环

【知识点2】 oracle 中的条件分支语句

【知识点3】游标 cursor

【知识点4】关于存过调试

公告

雨 燕

管理学之父彼得德鲁克说：“如果你不能度量它，你就无法改进它”

Oracle存储过程应用实例

引子

方案1

方案2

方案3

知识点补充

【知识点1】 oracle 中的for循环

【知识点2】 oracle 中的条件分支语句

【知识点3】 游标 cursor

【知识点4】 关于存过调试

公告

雨燕

【知识点3】游标 cursor

【知识点4】关于存过调试