欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-10-19 10:18:20
ORACLE关于存储过程的案例详解 需求 利用存储过程实现:从CDEPDP表中获取旧机构号,然后依据旧机构号查表CDETRADEEX_BAK,获取到新机构号,并把新机构号后缀到...

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;

遇到的问题

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