ORACLE关于存储过程的案例详解

需求

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

思路

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

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;

遇到的问题

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