不得不感慨逝者如斯夫，就这样，转眼间在公司呆了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;
/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
