ORACLE存储过程案例详解

一、前言

2017.7.12入职以来，自己处于不断学习状态。在工作过程中，积累工作经验十分重要。尤其是一些业务知识自己需要不断充电。之前接到一个写批量小程序的任务，由于自己之前并未接触过Oracle，更别提什么PL/SQL，存储过程了，自己刚开始也是不知道从何处着手，但接到了任务，自己也只能硬着头皮上了，要不然呢？！

首先需要了解存储过程是什么？如何在PL/SQL中执行？存储过程、函数、包之间的关系是怎样的？

有关存储过程的学习，请参考博文《ORACLE进阶（七）存储过程详解》。

下面结合实际应用场景，讲解下存储过程的具体应用。

二、需求

利用存储过程实现：从CDEPDP表中获取旧机构号，然后依据旧机构号查表CDETRADEEX_BAK，获取到新机构号，并把新机构号后缀到旧机构号之后。

三、思路

1. 从CDEPDP表中获取旧机构号字符串，形式为“1K7040,1K7090,1K7019”；然后利用正则表达式组装成数组形式；
2. 依据旧机构号查表CDETRADEEX_BAK，获取到新机构号；
3. 把新机构号后缀到旧机构号之后。

SPERIOD

CREATE OR REPLACE PROCEDURE SPERIOD IS

  /* 创建游标cur_CDEPDP，用于提取CDEPDP中的trades数据 */
  Cursor cur_CDEPDP IS
    SELECT TRADES FROM CDEPDP;

  v_count      NUMBER;
  v_sum        NUMBER;
  loop_counter NUMBER;
  v_sum2       NUMBER;
  v_result     CDEPDP.TRADES%TYPE;
  oldOrg_var   CDEPDP.TRADES%TYPE;
  oldOrg       CDETRADEEX_BAK.OLDTRADENO%TYPE;
  invalid_old_orgno EXCEPTION;
BEGIN
  v_count      := 0;
  v_sum        := 0;
  v_sum2       := 0;
  loop_counter := 1;
  /* 使用游标for循环，按行获取CDEPDP中的数据 */
  FOR trades in cur_CDEPDP LOOP
    v_result := '';
    DBMS_OUTPUT.PUT_LINE('oldOrg Items:' || trades.trades);
    oldOrg_var := trades.trades;
    v_count    := v_count + 1;
    v_sum      := REGEXP_COUNT(oldOrg_var, ',') + 1;
    DBMS_OUTPUT.PUT_LINE('Total Items:' || v_sum);
    /* 若只存在一个数据元素 */
    IF v_sum < 2 THEN
      DBMS_OUTPUT.PUT_LINE(RETRIEVEORGNO(oldOrg_var));
      DBMS_OUTPUT.PUT_LINE('Result:' || oldOrg_var || ',' ||
                           RETRIEVEORGNO(oldOrg_var));
    ELSE
      FOR loop_counter IN 1 .. v_sum LOOP
        oldOrg := LTRIM(RTRIM(REGEXP_SUBSTR(oldOrg_var,
                                            '[^,]+',
                                            1,
                                            loop_counter),
                              ','),
                        ' ');
        --DBMS_OUTPUT.PUT_LINE(oldOrg);
        --DBMS_OUTPUT.PUT_LINE(RETRIEVEORGNO(oldOrg));
        IF RETRIEVEORGNO(oldOrg) IS NOT NULL THEN
          /* 去重并拼接结果（新机构号） */
          IF REGEXP_LIKE(v_result, RETRIEVEORGNO(oldOrg)) THEN
            CONTINUE;
          ELSE
            v_result := v_result || ',' || RETRIEVEORGNO(oldOrg);
          END IF;
          /* ELSE RAISE invalid_old_orgno; CONTINUE; */
        END IF;
      END LOOP;
      /* 拼接结果（旧机构号 + 新机构号） */
      v_result := oldOrg_var || ',' || LTRIM(v_result, ',');
      DBMS_OUTPUT.PUT_LINE('Result:' || v_result);
      v_sum2 := REGEXP_COUNT(v_result, ',') + 1;
      DBMS_OUTPUT.PUT_LINE('Total Items After Processed:' || v_sum2);
    END IF;
  END LOOP;
  /* 输出测试信息 */
  DBMS_OUTPUT.PUT_LINE('Sum Items:' || v_count);
  /* 异常捕捉-数据信息不存在 */
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('数据库中不存在新机构号对应该旧机构号');
  WHEN invalid_old_orgno THEN
    DBMS_OUTPUT.PUT_LINE('数据库中不存在新机构号对应该旧机构号2');
END SPERIOD;

RETRIEVEORGNO

create or replace function RETRIEVEORGNO(oldOrg IN CDETRADEEX_BAK.OLDTRADENO%TYPE)
  return CDETRADEEX_BAK.NEWTRADENO%TYPE is
  return_value CDETRADEEX_BAK.NEWTRADENO%TYPE;
begin
  SELECT NEWTRADENO INTO return_value FROM CDETRADEEX_BAK WHERE OLDTRADENO = oldOrg;
  /*DBMS_OUTPUT.PUT_LINE('return_value:' || return_value);*/
  return(return_value);
  /* 当旧机构号不存在对应的新机构号 */
EXCEPTION
  WHEN NO_DATA_FOUND
    THEN RETURN NULL;
end RETRIEVEORGNO;

select * from CDETRADEEX_BAK;
select * from cdepdp;

四、遇到的问题

1. 正则表达式；
2. 正确表示两数值相等使用“ =”；
3. 包、存储过程、函数之间的关系？如何相互调用？

五、优化

在存储过程中新增传参，为达到模块化开发，更合理的方式是在入参中定义OUT参数，其他模块在调用时可直接使用处理结果。在获取到新参数后，新增更新表结构语句。
SPERIOD

CREATE OR REPLACE PROCEDURE SPERIOD_CDEPDP(p_BeginFlag IN VARCHAR2) IS

  /* 创建游标cur_CDEPDP，用于提取CDEPDP中的trades数据 */
  Cursor cur_CDEPDP IS
    SELECT pdid, trades FROM CDEPDP;

  v_sum        NUMBER;
  v_sumnew     NUMBER;
  loop_counter NUMBER;
  v_result     CDEPDP.TRADES%TYPE;
  v_trades     CDEPDP.TRADES%TYPE;
  v_pdid       CDEPDP.PDID%TYPE;
  v_oldOrg     CDETRADE.TRADENO%TYPE;
  v_newOrg     CDETRADE.TRADENO%TYPE;

  invalid_old_orgno EXCEPTION;
BEGIN
  IF p_BeginFlag = 'TRUE' THEN
    v_sum        := 0;
    v_sumnew     := 0;
    loop_counter := 1;
    /* 使用游标for循环，按行获取CDEPDP中的数据 */
    FOR curloop in cur_CDEPDP LOOP
      v_pdid   := curloop.pdid;
      v_trades := curloop.trades;
      v_result := v_trades;
      IF v_trades IS NULL THEN
        CONTINUE;
      ELSE
        v_sum := REGEXP_COUNT(v_trades, ',') + 1;
        --DBMS_OUTPUT.PUT_LINE('pdId:' || v_pdid);
        --DBMS_OUTPUT.PUT_LINE('oldTotal Num:' || v_sum);
        --DBMS_OUTPUT.PUT_LINE('oldOrg Items:' || v_trades);
      
        /* 若只存在一个数据元素 */
        IF v_sum < 2 THEN
          v_result := v_result || ',' || RETRIEVEORGNO(v_trades);
          v_sumnew := REGEXP_COUNT(v_result, ',') + 1;
          --DBMS_OUTPUT.PUT_LINE('newTotal Num:' || v_sumnew);
          --DBMS_OUTPUT.PUT_LINE('newOrg Items:' || v_result);
        ELSE
          FOR loop_counter IN 1 .. v_sum LOOP
            v_oldOrg := LTRIM(RTRIM(REGEXP_SUBSTR(v_trades,
                                                  '[^,]+',
                                                  1,
                                                  loop_counter),
                                    ','),
                              ' ');
            v_newOrg := RETRIEVEORGNO(v_oldOrg);
            IF v_newOrg IS NOT NULL THEN
              /* 去重并拼接结果（新机构号） */
              IF REGEXP_LIKE(v_result, v_newOrg) THEN
                CONTINUE;
              ELSE
                v_result := v_result || ',' || v_newOrg;
              END IF;
              /* ELSE RAISE invalid_old_orgno; CONTINUE; */
            END IF;
            --DBMS_OUTPUT.PUT_LINE('newOrg Items:' || v_result);
          END LOOP;
          /* 拼接结果（旧机构号 + 新机构号） */
          v_sumnew := REGEXP_COUNT(v_result, ',') + 1;
          --DBMS_OUTPUT.PUT_LINE('newTotal Num:' || v_sumnew);
          --DBMS_OUTPUT.PUT_LINE('newOrg Items:' || v_result);
        END IF;
        /* 更新数据 */
        update CDEPDP set trades = v_result where pdid = v_pdid;
        COMMIT;
      END IF;
    END LOOP;
    /* 异常捕捉-数据信息不存在 */
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('数据库中不存在新机构号对应该旧机构号');
  WHEN invalid_old_orgno THEN
    DBMS_OUTPUT.PUT_LINE('数据库中不存在新机构号对应该旧机构号2');
END SPERIOD_CDEPDP;

RETRIEVEORGNO

create or replace function RETRIEVEORGNO(oldOrg IN cdetradeDataMap.TRADENOS%TYPE)
  return cdetradeDataMap.TRADENOT%TYPE is
  return_value cdetradeDataMap.TRADENOT%TYPE;
begin
  SELECT TRADENOT INTO return_value FROM cdetradeDataMap WHERE TRADENOS = oldOrg;
  /*DBMS_OUTPUT.PUT_LINE('return_value:' || return_value);*/
  return(return_value);
  /* 当旧机构号不存在对应的新机构号 */
EXCEPTION
  WHEN NO_DATA_FOUND
    THEN RETURN NULL;
end RETRIEVEORGNO;