不得不感慨逝者如斯夫，就这样，转眼间在公司呆了3年之多。3年前常用的sql proc相关知识，在今天接手离职同事工作时发现突然间无法完全忆起。于是，迫使自己找出3年之前的资料，罗列一下存储过程的常用方式，以此备案！
标准式游标用法和静态sql
CREATE OR REPLACE PROCEDURE p_use_cursor(
i_seq_ids IN VARCHAR2,
o_code OUT INT,
o_msg OUT VARCHAR2
)
AS
/***********************************************************
注释区:采用标准式游标用法和静态sql
***********************************************************/
TYPE t_cur IS REF CURSOR;
v_count INT;
c_check t_cur;
sqlstr VARCHAR2 (5000);
v_code VARCHAR2 (4);
v_sid VARCHAR2 (5);
BEGIN
v_count := 0;
o_code := 0;
o_msg := 'success';
sqlstr :=
' select LOCALNET_CODE,ORG_SID from xtable where seq_id in '
|| i_seq_ids;
OPEN c_check FOR sqlstr;
LOOP
FETCH c_check
INTO v_code, v_sid;
EXIT WHEN c_check%NOTFOUND;
--校验本地网区号
SELECT COUNT (*)
INTO v_count
FROM localnet
WHERE design_code = v_code;
IF (v_count = 0)
THEN
o_code := 1;
o_msg := '代码 ' || v_code || ' 不存在!';
CLOSE c_check;
RETURN;
END IF;
--ORG_SID定长5位
IF (LENGTH (v_sid) <> 5)
THEN
o_code := 2;
o_msg := 'ORG_SID ' || v_sid || ' 的长度必须为5位!';
CLOSE c_check;
RETURN;
END IF;
v_count := 0;
--ORG_SID必须是数字
SELECT NVL2 (TRANSLATE (v_sid, '\1234567890', '\'), 2, 1)
INTO v_count
FROM DUAL;
IF (v_count = 2)
THEN
o_code := 3;
o_msg := 'ORG_SID ' || v_sid || ' 必须为数字!';
CLOSE c_check;
RETURN;
END IF;
END LOOP;
CLOSE c_check;
END p_use_cursor;
/
直接采用游标和静态sql
CREATE OR REPLACE PROCEDURE p_use_cursor (
i_batch_id IN VARCHAR2,
i_user_id IN VARCHAR2,
o_code OUT INT,
o_msg OUT VARCHAR2
)
AS
/***********************************************************
注释区:直接采用游标和静态sql
***********************************************************/
CURSOR cur_adjust
IS
SELECT *
FROM table a, table b
WHERE a.user_id = i_user_id
AND a.status = 1;
l_count INT;
err_count INT;
ok_count INT;
now_date VARCHAR2 (14);
comp_date VARCHAR2 (14);
BEGIN
l_count := 0;
err_count := 0;
ok_count := 0;
now_date := TO_CHAR (SYSDATE, 'YYYYMMDDhh24miss');
comp_date := i_batch_id || '000000';
FOR c_r IN cur_adjust
LOOP
IF (UPPER (c_r.service_code) = 'IVR' OR UPPER (c_r.service_code) = 'PIVR')
THEN
o_code := 9;
err_count := err_count + 1;
o_msg := 'xxxxxx';
INSERT INTO zzyw_adjust_error
(user_id, user_name, error_time, error_desc,
ori_record
)
VALUES (c_r.user_id, c_r.user_name, now_date, o_msg,
c_r.service_code
|| ','
|| c_r.localnet_abb);
DELETE FROM yuy_report
WHERE ROWID = c_r.ROWID;
GOTO CONTINUE;
END IF;
--其它操作
ok_count := ok_count + 1;
<<continue>>
NULL;
END LOOP;
--其它静态sql
o_code := 0;
o_msg :=
'成功提交的记录数为'
|| ok_count
|| '，错单数为'
|| err_count
|| '，用户可以到本期数据查询菜单中查询错单记录';
COMMIT;
END p_use_cursor;
/
静态sql完整事务模式
CREATE OR REPLACE PROCEDURE P_TMP_TP_TRUNK (
i_dealdate In tl_data_audit.data_date%TYPE,
o_returncode OUT INTEGER,
o_returnmsg OUT VARCHAR2
)
--******************************************************************************************************************
-- SQL 存储过程
-- 名称 ： P_TMP_TP_TRUNK
-- 注意 ：
-- 参数 ： i_dealdate:要统计的日期
-- 功能描述 ： 取开始时间最近的trunk_id到临时表
--******************************************************************************************************************
AS
BEGIN
o_returncode := -1;
o_returnmsg := 'TradeOk!';
--/* 取开始时间最近的入中继信息到中继临时表
INSERT INTO table_trunk
(。。。)
SELECT source_id, trunk_code, trunk_side, start_date
FROM (SELECT source_id, trunk_code, trunk_side, start_date ,
rank() over (PARTITION BY source_Id, trunk_code ORDER BY start_date DESC ) rank
FROM (SELECT source_Id, trunk_code, trunk_side, MAX(start_date) start_date
FROM tp_trunk_js
WHERE substr(end_date,1,8) > i_dealdate
GROUP BY source_id, trunk_code , trunk_side)
WHERE trunk_side IN (0,1)
GROUP BY 。。。
)
WHERE rank = 1
;
--/* 取开始时间最近的入中继信息到入中继信息临时表
-- sql语句略
--/*删除中继临时表中的数据
p_sd_truncatetable ('tmp_tp_trunk', o_returncode, o_returnmsg);
--/* 取开始时间最近的入中继信息到入中继信息临时表
--略;
o_returncode := 0;
IF o_returncode = 0
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
o_returncode := -1;
o_returnmsg := SUBSTR ('[01]' || 'tp_trunk没有合适数据 ' || SQLERRM, 1, 255);
ROLLBACK;
WHEN OTHERS
THEN
o_returncode := -1;
o_returnmsg :=
SUBSTR ('[01]' || 'P_TMP_TP_TRUNK错误告警 ' || SQLERRM, 1, 255);
ROLLBACK;
END;
/
CREATE OR REPLACE PROCEDURE P_EXE_MONTHPROC (
O_ReturnCode Out integer,
O_ReturnMsg Out varchar2
)
--*****************************************************************************************
-- SQL 存储过程
-- 名称 ： P_EXE_MONTHPROC
-- 注意 ：
-- 参数 ：
-- 功能描述 ： 调用月存储过程
-- ： 该程序由P_AFTER调用
-- 未完功能 ：
-- 返回值 ： 0：正确，-1：错误
--***********************************************************************************************
as
v_data_flag INT;
v_deal_date number;
cursor cur1 is
select distinct sett_month from table_dealdate;
BEGIN
o_returncode := -1;
o_returnmsg := 'TradeOk!';
select 1 into v_data_flag
from dual
where exists(
select 1 from table_dealdate
);
for c1 in cur1 loop
select (c1.sett_month*100 + 20) into v_deal_date from dual;
p_fact_settle_rule(v_deal_date, O_ReturnCode, O_ReturnMsg);
If O_ReturnCode <> 0 Then
goto ER;
End If;
p_fact_interconnect_month(v_deal_date, O_ReturnCode, O_ReturnMsg);
If O_ReturnCode <> 0 Then
goto ER;
End If;
end loop;
p_sd_truncatetable ('tmp_dealdate', o_returncode, o_returnmsg);
<<ER>>
If O_ReturnCode = 0 Then
commit;
Else
rollback;
END IF;
EXCEPTION
when no_data_found then
O_ReturnCode := -1;
O_ReturnMsg := substr('[01]'||'tmp_dealdate表中没有数据 '||sqlerrm,1,255);
ROLLBACK;
WHEN OTHERS THEN
O_ReturnCode := -1;
O_ReturnMsg := substr('[01]'||'P_EXE_MONTHPROC错误告警 '||sqlerrm,1,255);
ROLLBACK;
END;
/
动态sql完整事务模式
CREATE OR REPLACE PROCEDURE P_TMP_P01(i_dealdate IN TMP_OTHER_TODAY_SETT.statdate%TYPE,
O_ReturnCode OUT INTEGER,
O_ReturnMsg OUT VARCHAR2)
AS
v_Cursor NUMBER;
v_rows NUMBER;
v_SQL VARCHAR2(5000);
v_SQL_data VARCHAR2(400);
v_insert_sql VARCHAR2(1000);
v_select_sql VARCHAR2(2000);
v_from_sql VARCHAR2(200);
v_where_sql VARCHAR2(800);
v_groupby_sql VARCHAR2(1500);
v_partition_name VARCHAR2(5);
v_parm NUMBER;
v_data_flag INT;
v_tablename VARCHAR2(20);
BEGIN
o_returncode := -1;
o_returnmsg := 'TradeOk!';
v_partition_name := 'p1';
--/*从参数表取得结果表中的省份代码
SELECT par_value INTO v_parm FROM xtableWHERE par_code = 1;
--/*获得要处理的清单表名
SELECT 'xtable_' || SUBSTR(i_dealdate, 7) INTO v_tablename FROM dual;
--/*查看接口表是否有当天的数据
v_sql_data := 'select 1 from dual where exists( select 1 from ' ||
v_tablename || ' partition (' || v_partition_name ||
') where end_datetime between to_date(''' || i_dealdate ||
'000000''' || ',''yyyymmddhh24miss'') and to_date(''' ||
i_dealdate || '235959''' || ',''yyyymmddhh24miss''))';
EXECUTE IMMEDIATE v_sql_data
INTO v_data_flag;
--/*向临时表中插入主叫数据
v_insert_sql := 'INSERT INTO TMP_table_SETT (....) ';
v_select_sql := ' select
TO_CHAR(end_datetime,''yyyymmdd'') ,
....,SUM(local_discount_fee) ,
SUM(toll_discount_fee) , SUM(local_discount_fee + toll_discount_fee),
SUM(sett_fee)';
v_from_sql := ' from ' || v_tablename || ' partition (' ||
v_partition_name ||
') a , s_area b , tpw_iden_settle_type c ';
v_where_sql := ' where end_datetime between to_date(''' || i_dealdate ||
'000000''' || ',''yyyymmddhh24miss'') and to_date(''' ||
i_dealdate || '235959''' ||
',''yyyymmddhh24miss'') and called_area_code = b.area_code and b.prov_code = ' ||
v_parm || ' and a.sett_type = c.sett_type ' ||
' and a.called_tsp_code = 11';
v_groupby_sql := ' Group by
TO_CHAR(end_datetime,''yyyymmdd'') ,
a.calling_code ';
v_sql := v_insert_sql || ' ' || v_select_sql || ' ' || v_from_sql || ' ' ||
v_where_sql || ' ' || v_groupby_sql;
BEGIN
-- 开始执行动态SQL
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.v7);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
WHEN OTHERS THEN
o_returnmsg := 'P_TMP_OTHER_TODAY_SETT_'||v_partition_name||'发生未知错误 01@: ' ||
SQLERRM;
ROLLBACK;
RETURN;
END;
o_returncode := 0;
IF o_returncode = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
O_ReturnCode := 1;
O_ReturnMsg := SUBSTR('[01]' ||
'P_TMP_OTHER_TODAY_SETT_'||v_partition_name||'发生未知错误' ||
SQLERRM,
1,
255);
ROLLBACK;
WHEN OTHERS THEN
O_ReturnCode := -1;
O_ReturnMsg := SUBSTR('[01]' ||
'P_TMP_OTHER_TODAY_SETT_'||v_partition_name||'发生未知错误' ||
SQLERRM,
1,
255);
ROLLBACK;
END;
/
分享到：
相关推荐
DotNet操作Oracle存储过程备忘[定义].pdf
Oracle存储过程基础知识 Oracle存储过程的基本语法 Oracle存储过程的若干问题备忘 用Java调用Oracle存储过程总结 在存储过程中做简单动态查询 Oracle存储过程调用Java方法
oracle 存储过程的基本语法，关于oracle存储过程的若干问题备忘，oracle 存储过程语法总结及练习；oracle语法:Oracle触发器语法及实例基础知识（一）。
设计模式之备忘录 和 状态模式精讲 19.1 场景问题 19.1.1 开发仿真系统 考虑这样一个仿真应用，功能是：模拟运行针对某个具体问题的多个解决方案，记录运行过程的各种数据，在模拟运行完成过后，好对这多个解决...
关于 Oracle 存储过程的若干问题备忘...........................................................................................4 1. 在 Oracle 中，数据表别名不能加 as。......................................
微信备忘录小程序源码 作业设计demo 微信备忘录小程序是一种便捷的个人记事应用，它允许用户在微信内快速...- **离线存储**：即使在无网络环境下，也能正常使用，记录的内容会在联网后自动同步。 微信备忘录小程序是日
并用32M的SD卡结合winhex详细的图文步骤揭开存储过程 清楚的对各个关键数字的计算,让你不明白也明白 自己保留做备忘! ******************* lhdzwkk4的温馨提示:下载资源后正确评价资源的方法***********************...
主要介绍了详解备忘录模式及其在Java设计模式编程中的实现,备忘录模式数据的存储过程中应当注意浅拷贝和深拷贝的问题,需要的朋友可以参考下
数据字典包括数据项、数据结构、数据流、数据存储和数据处理过程5个部分 2. 结构化设计方法是一种面向数据流的设计方法，与结构化分析方法衔接。 在需求分析阶段，结构化分析方法产生了数据流图，而在设计阶段，结构...
前言 关于SQL Server基础系列尚未结束，还剩下最后一点内容未写，后面会继续。有园友询问我什么时候开始写SQL Server性能系列，估计还得等一段时间，最近工作也比较忙...存储过程性能优化 禁用受影响函数通过设置SET N
每个备忘单的翻译过程包含两个步骤： 翻译步骤，其中贡献者遵循要翻译的项目模板， 审核步骤，贡献者仔细阅读其同行翻译的每个表达方式，并在其上添加他们的建议和评论。 译者 检查以查看尚未翻译的备忘单。 分叉...
为了使软件的使用更加人性化，对于误操作，我们需要提供一种类似“后悔药”的机制，让软件系统可以回到误操作前的状态，因此需要保存用户每一次操作时系统的状态，一旦出现误操作，可以把存储的历史状态取出即可回到...
Git控制台命令备忘单 一般的 Git是一个版本控制系统（文件）。 类似于在计算机游戏中保存的能力（在Git中，等同于游戏保存就是一次提交）。 重要提示：将文件添加到“保存”是一个两步过程：首先，将文件添加到索引...
11. Memo Links（备忘录连结）：用于存储备忘录之间的关系信息。 12. See Also Links（参照连结）：用于存储节点之间的参照关系信息。 13. Annotations（注解）：用于存储节点的注解信息。 14. Sets（组合）：用于...
知识点备忘 Java SE Thinking in Java [Java I/O](/Thinking-in-Java/Java IO.md) Android Android组件 Drawable 自定义Drawable View View工作过程 View事件分发机制 View滑动 自定义View User Interface ...
应用程序安全性存储库包含所有Web和移动安全性评估清单和备忘单，供安全性分析人员和顾问在其客户的应用程序中使用。 信用： （） 网络安全 重要的Web应用程序安全代码审查评估关键字速查表 Web应用程序安全评估清单...