本文介绍Oracle数据库中存储过程和包的编写规范及示例。包括存储过程的注释标准、变量定义、异常处理等，同时展示了如何创建包及其主体，并提供了具体的SQL语句和函数调用实例。
一、存储过程格式：
1）首先在存储过程开始阶段加要注释：若详尽一些，可以参考下面的列表。正常情况一定要加的是描述，创建人，创建时间。如果后期有修改要加上修改人，修改时间，修改内容及原因
/****************************************************/
/* 指标: INDEX_01
/* 指标描述：毛收入实际值
/* 传入参数-1：END_DATE_IN VARCHAR2
/* 传入参数-2：
/* 传入参数....(如有需要，可以继续添加)
/* 参数格式：YYYYMMDD
/* 作者：LJQ
/* 创建时间：2014-9-16 18:43
/* 修改人：
/* 修改时间：
/****************************************************/
2)一般存储过程中有两种：一种是带参数的存储过程，一种是不带参数的存储过程。
不带参数的存储过程示例：
CREATE OR REPLACE PROCEDURE PRO_CUSTBD_ODS_MONITOR IS
BEGIN
/*
执行语句
*/
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
带参数的存储过程示例：
PROCEDURE PRO_INFOLOC_YHL_SJ(END_DATE_IN VARCHAR2) IS
BEGIN
/*
带有参数END_DATE_IN的语句
*/
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
3）存储过程中可以定义一些变量并给这些变量赋值。例如：
PROCEDURE PRO_INFOLOC_YHL_SJ(END_DATE_IN VARCHAR2) IS
C_ZB_CODE CONSTANT VARCHAR2(30) := 'PRO_INFOLOC_YHL_SJ';
--常量：定义常量时需要使用CONSTANT关键字，并且必须在声明时就为该常量赋值。而且在程序其他部分不能修改该常量的值。
--常量的语法格式为：constant_name CONSTANT data_type {:=| default } value; 赋值号是 :=
L_JOB_ID NUMBER;
L_VER_MONTH VARCHAR2(8);
BEGIN
L_JOB_ID := PAC_INFOLOC_UTIL.LOG_JOB_INTERFACE_START(G_PKG_NAME,
C_ZB_CODE,
END_DATE_IN);
L_VER_MONTH := substr(END_DATE_IN, 1, 6);
/*语句
delete from LOC_T_ODS_PIC_EMP t where t.ver_month = L_VER_MONTH;
*/
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
4）存储过程中一些特殊语句的解释：
PRAGMA AUTONOMOUS_TRANSACTION
当前的存储过程作为已有事务的子事务运行，子事务的commit，rollback操作不影响父事务的状态
oracle中的sqlcode和sqlerrm函数
sqlcode函数用来返回pl/sql执行内部异常代码。
sqlerrm函数返回指定错误代码的错误信息。
sqlcode和sqlerrm是不能直接在sql语句中使用，必须先将其赋值给变量后，才能在sql语句中使用。
sqlerrm用的最多的是在pl/sql编程中来捕获异常的详细信息，特别是在when other中使用非常有用。
dbms_utility.format_error_backtrace 返回当前异常相应的描述。系统为最近一次生成的异常设置了一个栈，并跟踪它的传递过程，而这个函数使用这个栈，然后返回该异常的整个传递过程。这个函数对错误的定位和实施下一步处理起着至关重要的作用。
execute immediate 解析并马上执行动态的SQL语句
execute immediate,它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块
SQL%ROWCOUNT 用于记录修改的条数
sql%rowcount用于记录修改的条数，就如你在sqlplus下执行delete from之后提示已删除xx行一样，
这个参数必须要在一个修改语句和commit之间放置，否则你就得不到正确的修改行数。
例如：l_logmemo := substr(sqlcode || ';' || Sqlerrm ||
dbms_utility.format_error_backtrace,
1,
500);
execute immediate 'alter table dm_custbd_index_month truncate partition part_cusind_cas012';
二.包与存储过程示例
CREATE OR REPLACE PACKAGE PAC_INFOLOC_STATION_MAIN IS
--包头 可以加注释
PROCEDURE PRO_INFOLOC_YHL_SJ(END_DATE_IN VARCHAR2);
END PAC_INFOLOC_STATION_MAIN;
CREATE OR REPLACE PACKAGE BODY PAC_INFOLOC_STATION_MAIN IS
--包体
/****************************************************/
/* 指标: PRO_INFOLOC_YHL_SJ
/* 指标描述：实际值
/* 传入参数-1：END_DATE_IN VARCHAR2
/* 传入参数-2：
/* 传入参数....(如有需要，可以继续添加)
/* 参数格式：YYYYMMDD
/* 作者：LJQ
/* 创建时间：2014-9-16 18:43
/* 修改人：
/* 修改时间：
/****************************************************/
PROCEDURE PRO_INFOLOC_YHL_SJ(END_DATE_IN VARCHAR2) IS
C_ZB_CODE CONSTANT VARCHAR2(30) := 'PRO_INFOLOC_YHL_SJ'; --指标编码
L_JOB_ID NUMBER; --JOB ID
L_VER_MONTH VARCHAR2(8); --月份YYYYMM
BEGIN
L_JOB_ID := PAC_INFOLOC_UTIL.LOG_JOB_INTERFACE_START(G_PKG_NAME,
C_ZB_CODE,
END_DATE_IN);
L_VER_MONTH := substr(END_DATE_IN, 1, 6);
delete from LOC_T_ODS_PIC_EMP t where t.ver_month = L_VER_MONTH;
insert into LOC_T_ODS_PIC_EMP
(VER_MONTH,
VER_DATE,
PRODUCT_CAT,
XTJYT_NAME,
REPORT_DATE,
RESP_NO,
SEG1,
PERSON_NAME,
PERSON_NO,
CHECK_RESP_TYPE,
PERSON_TYPE,
BL_NUM,
INSERT_TIME,
PRODUCT_CODE)
select TO_CHAR(T.REPORT_DATE, 'YYYYMM'),
TO_CHAR(T.REPORT_DATE, 'YYYYMMDD'),
T.PRODUCT_CAT,
T.XTJYT_NAME,
to_char(T.Report_Date, 'YYYYMMDD'),
T.RESP_NO,
T.SEG1,
T.PERSON_NAME,
LPAD(T.PERSON_NO, 8, 0) AS PERSON_NO,
DECODE(T.CHECK_RESP_TYPE, '部件', '采购', T.CHECK_RESP_TYPE),
T.PERSON_TYPE,
count(*),
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),
T.PRODUCT_CODE
from infoloc_pic_itf_temp t
where to_char(t.report_date, 'YYYYMM') = L_VER_MONTH
and trim(t.check_resp_type) in ('部件', '设计')
and t.person_no is not null
and t.person_no || t.row_id not in
(select substr(t2.appeal_id, 0, length(t2.appeal_id) - 14)
from APP.CEI_CHECK_RESULT_FCT t2
where t2.check_results = 'SU04'
and t2.appeal_id is not null)
group by T.PRODUCT_CAT,
T.XTJYT_NAME,
T.report_date,
T.RESP_NO,
T.SEG1,
T.PERSON_NAME,
T.PERSON_NO,
T.CHECK_RESP_TYPE,
T.PERSON_TYPE,
T.PRODUCT_CODE;
COMMIT; --提交
PAC_INFOLOC_UTIL.LOG_JOB_INTERFACE_END(L_JOB_ID);
EXCEPTION
WHEN OTHERS THEN
PAC_INFOLOC_UTIL.LOG_ERROR(G_PKG_NAME, C_ZB_CODE, SQLERRM); --错误监控日志
ROLLBACK;
END;
END PAC_INFOLOC_STATION_MAIN;
CREATE OR REPLACE PACKAGE PAC_INFOLOC_UTIL IS
-- Author : 创建者
-- Created : 创建时间（格式：YYYY-MM-DD HH:MI）
-- Purpose : INFOLOC
G_PKG_NAME CONSTANT VARCHAR2(40) := 'PAC_INFOLOC_UTIL';
FUNCTION LOG_JOB_INTERFACE_START(PKG_NAME VARCHAR2,
SUB_NAME VARCHAR2,
YESTERDAY VARCHAR2) RETURN NUMBER;
PROCEDURE LOG_JOB_INTERFACE_END(JOB_SEQ NUMBER);
PROCEDURE LOG_ERROR(PKG_NAME IN VARCHAR2,
ZB_CODE IN VARCHAR2,
ERROR IN VARCHAR2);
END PAC_INFOLOC_UTIL;
CREATE OR REPLACE PACKAGE BODY PAC_INFOLOC_UTIL IS
PROCEDURE MAIN(YESTERDAY VARCHAR2) IS
JOB_ID NUMBER;
/****************************************************/
/* 指标: LOG_JOB_SHOW
/* 指标描述：指标计算任务日志(前台显示用）
/* 传入参数-1：PKG_NAME 任务主标题
/* 传入参数-2：SUB_NAME 任务副标题
/* 传入参数-3 TYPE 任务类型
/* 传入参数-4 YESTERDAY（YYYYMMDD） 计算日期
/* 作者：XIAODB
/* 创建时间：2013-4-8 14:00
/* 修改人：
/* 修改时间：
/****************************************************/
FUNCTION LOG_JOB_INTERFACE_START(PKG_NAME VARCHAR2,
SUB_NAME VARCHAR2,
YESTERDAY VARCHAR2) RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
JOB_SEQ NUMBER;
BEGIN
SELECT SEQ_LOCSYS_JOB_MONITOR.NEXTVAL INTO JOB_SEQ FROM DUAL;
INSERT INTO LOC_T_SYS_JOB_MONITOR_ZB
(JOB_ID, PKG_NAME, SUB_NAME, START_DATE, VERSION_DATE)
VALUES
(JOB_SEQ, PKG_NAME, SUB_NAME, SYSDATE, YESTERDAY);
COMMIT;
RETURN JOB_SEQ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/****************************************************/
/* 指标: LOG_JOB_END
/* 指标描述：指标计算任务日志
/* 传入参数-1：JOB_SEQ 任务序列号
/* 作者：XIAODB
/* 创建时间：2013-4-8 14:00
/* 修改人：
/* 修改时间：
/****************************************************/
PROCEDURE LOG_JOB_INTERFACE_END(JOB_SEQ NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE LOC_T_SYS_JOB_MONITOR_ZB
SET END_DATE = SYSDATE,
HS_TIME =
(SYSDATE - START_DATE) * 24 * 60 * 60 --此处单位为秒，可以自由设定
WHERE JOB_ID = JOB_SEQ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/****************************************************/
/* 指标: LOG_JOB_END
/* 指标描述：指标计算任务日志
/* 传入参数-1：JOB_SEQ 任务序列号
/* 作者：XIAODB
/* 创建时间：2013-4-8 14:00
/* 修改人：
/* 修改时间：
/****************************************************/
PROCEDURE LOG_ERROR(PKG_NAME IN VARCHAR2,
ZB_CODE IN VARCHAR2,
ERROR IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO LOC_T_SYS_ERROR_LOG
(PKG_NAME, ZB_CODE, ERROR, INSERT_TIME)
VALUES
(PKG_NAME, ZB_CODE, ERROR, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
END PAC_INFOLOC_UTIL;
示例二：
Procedure sp_custbd_kpi_cusind_ca is
--============================================
--= 过程功能: ***数量
--= 作者: LJQ
--= 创建时间: 2016-06-24
--=========================================
begin
------清空客户****分区表
execute immediate 'alter table dm_custbd_index_month truncate partition part_cusind_ca';
-----------客户******
insert into /*+append*/
dm_custbd_index_month --nologging
select t.c_month,
t.c_soto,
'CUSIND_CA',
t.kpi_value,
'0',
'0',
SYSDATE,
'LJQ',
SYSDATE,
'LJQ'
from (select a.c_month,a.c_soto,a.kpi_value
from DM_CUSTBD_INDEX_MONTH a
where a.index_id = 'CUSIND_CAS1002') t;
--将上一步的SQL的执行结果条目数记录到变量中
ROWLINE := SQL%ROWCOUNT;
commit;
--insert成功将结果写入日志表
insert into dm_custbd_t_log
values
(sysdate,
'PKG_CUSTBD_KPI_MONTH',
'sp_custbd_kpi_cusind_cas007',
'dm_custbd_index_month',
ROWLINE,
'success');
commit;
exception
when others then
-------异常处理
l_logmemo := substr(sqlcode || ';' || Sqlerrm ||
dbms_utility.format_error_backtrace,
1,
500);
-------异常日志
insert into dm_custbd_t_log
values
(sysdate,
'PKG_CUSTBD_KPI_MONTH',
'sp_custbd_kpi_cusind_cas007',
'dm_custbd_index_month',
1,
l_logmemo);
commit;
end sp_custbd_kpi_cusind_ca;
扫一扫
682
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
