ORACLE进阶（八）存储过程案例详解
【摘要】 写在前面的话
2017.7.12入职以来，自己处于不断学习状态。在工作过程中，积累工作经验十分重要。尤其是一些业务知识自己需要不断充电。之前接到一个写批量小程序的任务，由于自己之前并未接触过Oracle，更别提什么PL/SQL，存储过程了，自己刚开始也是不知道从何处着手，但接到了任务，自己也只能硬...
写在前面的话
2017.7.12入职以来，自己处于不断学习状态。在工作过程中，积累工作经验十分重要。尤其是一些业务知识自己需要不断充电。之前接到一个写批量小程序的任务，由于自己之前并未接触过Oracle，更别提什么PL/SQL，存储过程了，自己刚开始也是不知道从何处着手，但接到了任务，自己也只能硬着头皮上了，要不然呢？！
首先需要了解存储过程是什么？如何在PL/SQL中执行？存储过程、函数、包之间的关系是怎样的？
有关存储过程的学习，请参考博文《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;
- 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
遇到的问题
- 正则表达式；
- 正确表示两数值相等使用“ =”；
- 包、存储过程、函数之间的关系？如何相互调用？
优化
在存储过程中新增传参，为达到模块化开发，更合理的方式是在入参中定义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）