Oracle存储过程示例

CREATE OR REPLACE PROCEDURE AAA.bb_cc_dd_xxxx (p_data_date IN VARCHAR2,
p_result OUT VARCHAR2)/*创建存储过程*/
IS
v_data_date VARCHAR2 (8);/*定义变量*/
v_yesterday VARCHAR2 (8);
v_date_days NUMBER;
v_success VARCHAR2 (10) := sa.sa_admin.succeed;
v_failed VARCHAR2 (10) := sa.sa_admin.failed;
v_step VARCHAR2 (100);
v_start_time VARCHAR2 (100);
v_end_time VARCHAR2 (100);
v_proc_name VARCHAR2 (100) := 'bb_cc_dd_xxxx';
v_table_name VARCHAR2 (100) := 'cc_dd_xxxx';
/******************************************************************
Author:/*作者*/
Functions Description:/*功能说明*/
Parameter Description:
SUCCESS：成功
FAILED： 失败
******************************************************************/
BEGIN
---程序开始
v_step := '1';/*变量赋值*/
p_result := v_success;
v_data_date := p_data_date;
v_yesterday := TO_CHAR (TO_DATE (v_data_date, 'YYYYMMDD') - 1, 'YYYYMMDD');/*v_data_date前一天*/
v_date_days := TO_NUMBER (TO_CHAR (TO_DATE (v_data_date, 'YYYYMMDD'), 'DDD'));/*v_data_date是一年中的第几天*/
v_proc_name := 'bb_cc_dd_xxxx';
v_table_name := 'cc_dd_xxxx';
v_start_time := TO_CHAR (SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF');
v_step := '2';

DELETE FROM cc_dd_xxxx
WHERE data_date = v_data_date;/*清表*/

COMMIT;
v_step := '3';/*逻辑部分*/

--*****
INSERT INTO AAA.cc_dd_xxxx (data_date,
branch,
branch_desc,
ccy,
ccy_desc,
date_num,
sum_actual)/*最后一个没有“,”*/
SELECT /*+ INDEX(ra) *//*hint*/
ra.fdate,
branch,
branch_desc,
ccy,
ccy_desc,
v_date_days,
SUM (DECODE (ra.zzzz, 'C02', 0, ra.nnnn))
sum_actual/*最后一个没有“,”*/
FROM AAA.yyyy ra
WHERE ra.fdate = v_data_date
AND ra.zzzz IN ('C02')
AND ra.mmmm <> 'C'
AND ra.nnnn <> 0
GROUP BY ra.fdate,
ra.branch,
ra.ccy,
ra.branch_desc,
ra.ccy_desc;

v_step := '4';

--*****
IF SUBSTR (v_data_date, 5, 8) <> '0101'/*判断v_data_date是不是当年的第一天*/
THEN
MERGE INTO AAA.cc_dd_xxxx d
USING (SELECT tp.branch,
tp.ccy,
tp.sum_actual
FROM AAA.cc_dd_xxxx tp
WHERE tp.data_date = v_yesterday) s
ON ( d.branch = s.branch
AND d.ccy = s.ccy
AND d.data_date = v_data_date)
WHEN MATCHED
THEN
UPDATE SET
d.sum_actual = d.sum_actual + s.sum_actual;/*中间用","隔开，最后一个没有*/
END IF;

COMMIT;

v_step := 'N';/*写日志*/
v_end_time := TO_CHAR (SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF');
sa.sa_log.write_trace_log (v_proc_name,
v_start_time,
v_end_time,
p_result);
EXCEPTION
WHEN OTHERS
THEN
p_result := v_failed;
sa.sa_log.write_error_log (v_proc_name,
v_step,
'ERROR OCURR:' || SQLERRM,
p_result);
raise_application_error (-20001, v_proc_name);
END bb_cc_dd_xxxx ;