ORACLE进阶(八)存储过程案例详解

举报
SHQ5785 发表于 2020/12/30 00:15:51 2020/12/30
【摘要】 写在前面的话       2017.7.12入职以来,自己处于不断学习状态。在工作过程中,积累工作经验十分重要。尤其是一些业务知识自己需要不断充电。之前接到一个写批量小程序的任务,由于自己之前并未接触过Oracle,更别提什么PL/SQL,存储过程了,自己刚开始也是不知道从何处着手,但接到了任务,自己也只能硬...

写在前面的话

      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;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68

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
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

遇到的问题

  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;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78

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;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

文章来源: shq5785.blog.csdn.net,作者:No Silver Bullet,版权归原作者所有,如需转载,请联系作者。

原文链接:shq5785.blog.csdn.net/article/details/79371979

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。