oracle_package\packeage body

99g59041q4

　　
　　---Package

CREATE OR REPLACE PACKAGE TEST_PACKAGE is
-- Author  :  
-- Created :  
-- Purpose :  
/*==================================================================================
* 功能描述: 写数据库日志
*=================================================================================*/
PROCEDURE INSERTlOG(log_person     in VARCHAR2,
log_class      in VARCHAR2,
log_method     in VARCHAR2,
log_info       in VARCHAR2,
log_error_code in VARCHAR2);
END TEST_PACKAGE;
/
GRANT EXECUTE ON TEST.TEST_PACKAGE TO R_TEST_EXEC;

　　
　　------Package Body

---Package body的模板
CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE IS
TYPE T_TEST_SEND_INFO IS RECORD(
ID_TEST_SEND_INFO  INF_WECHAT_SEND_INFO.ID_WECHAT_SEND_INFO%TYPE,
ROW_ID             ROWID
);
TYPE TBL_TEST_SEND_INFO IS TABLE OF T_TEST_SEND_INFO INDEX BY BINARY_INTEGER;
----以行插入
PROCEDURE SAVE_TEST_SEND_INFO(V_TEST_SEND_INFO TBL_TEST_SEND_INFO,
O_MSG OUT VARCHAR2) IS
BULK_ERRORS EXCEPTION;
PRAGMA EXCEPTION_INIT(BULK_ERRORS, -24381);
BEGIN
FORALL INDX IN INDICES OF V_TEST_SEND_INFO SAVE EXCEPTIONS
INSERT INTO INF_TEST_SEND_INFO
(ID_TEST_SEND_INFO,
ROWID)
VALUES
(V_TEST_SEND_INFO(INDX).ID_TEST_SEND_INFO,
V_TEST_SEND_INFO(INDX).ROW_ID);
FORALL INDX IN INDICES OF V_TEST_SEND_INFO SAVE EXCEPTIONS
DELETE FROM INF_TEST_SEND_INFO WHERE ROWID=V_TEST_SEND_INFO(INDX).ROW_ID;
EXCEPTION
WHEN BULK_ERRORS THEN
IF O_MSG IS NULL THEN
O_MSG := '插入到微信发送状态流水表异常' || SQLCODE || SUBSTR(SQLERRM, 1, 200);
END IF;
END SAVE_TEST_SEND_INFO;
/************************************
---批量将U数据添加到表中
************************************/
PROCEDURE SAVE_TO_TEST_SEND_INFO(O_MSG OUT VARCHAR2) IS
-- 游标行类型
VTBL_TEST_SEND_INFO TBL_TEST_SEND_INFO;
-- 游标
CURSOR CS_TEST_SEND_INFO IS
SELECT SYS_GUID() ID_TEST_SEND_INFO,
ROWID ROW_ID
FROM TEST_SEND_INFO I
WHERE I.ID_TEST_SEND_INFO IS NOT NULL;
BEGIN
-- 打开游标
OPEN CS_TEST_SEND_INFO;
<<LP_CL_MAIN>>
LOOP
FETCH CS_TEST_SEND_INFO BULK COLLECT INTO VTBL_TEST_SEND_INFO LIMIT 1000;
-- 每1000行保存一次
SAVE_TEST_SEND_INFO(VTBL_TEST_SEND_INFO,O_MSG);
COMMIT;
-- 每1000行提交一次
EXIT LP_CL_MAIN WHEN CS_TEST_SEND_INFO%NOTFOUND;
END LOOP LP_CL_MAIN;
-- 关闭游标
CLOSE CS_TEST_SEND_INFO;
END SAVE_TO_TEST_SEND_INFO;
----插入日志
PROCEDURE INSERTlOG(log_person     in VARCHAR2,
log_class      in VARCHAR2,
log_method     in VARCHAR2,
log_info       in VARCHAR2,
log_error_code in VARCHAR2) is
logPerson varchar2(100);
begin
IF (log_person IS NULL) THEN
logPerson := 'SYSTEM';
ELSE
logPerson := log_person;
END IF;
insert into TEST.TEST_COMM_LOG
(ID_COMM_LOG,
LOG_PERSON,
LOG_CLASS,
LOG_METHOD,
LOG_INFO,
LOG_ERROR_CODE,
LOG_TIME)
values
(SYS_GUID(),
logPerson,
log_class,
log_method,
log_info,
log_error_code,
sysdate);
commit;
end;



END TEST_PACKAGE;
/
　　----triger

--创建数据时CREATED_BY，CREATED_DATE，UPDATED_BY，UPDATED_DATE自动赋值
CREATE OR REPLACE TRIGGER BUSSINESS_BI
BEFORE INSERT ON TEST_BUSSINESS
FOR EACH ROW
DECLARE
V_TRIGGER_USER_CREATED VARCHAR2(100);
V_TRIGGER_USER_UPDATED VARCHAR2(100);
V_TRIGGER_DATE         DATE;
V_STATUS TEST.TR_SWITCH.STATUS%TYPE;
CURSOR C_SWITCH(CP_SWITCH TEST.TR_SWITCH.SWITCH_FOR%TYPE) IS
SELECT STATUS
FROM PADINFODATA.TR_SWITCH
WHERE TRIGGER_NAME = 'INF_BUSSINESS_BI'
AND SWITCH_FOR   = CP_SWITCH;  
V_SQLCODE       VARCHAR2(6);
V_SQLERRM       VARCHAR2(200);
V_ERROR_COMMENT VARCHAR2(300);
BEGIN
V_ERROR_COMMENT := 'BEFORE GET_USER';
--SET CREATED_BY
IF :NEW.CREATED_BY IS NULL THEN
V_TRIGGER_USER_CREATED := USER;
ELSE
V_TRIGGER_USER_CREATED := :NEW.CREATED_BY;
END IF;
--SET UPDATED_BY
IF :NEW.UPDATED_BY IS NULL THEN
V_TRIGGER_USER_UPDATED := V_TRIGGER_USER_CREATED;
ELSE
V_TRIGGER_USER_UPDATED := :NEW.UPDATED_BY;
END IF;
V_TRIGGER_DATE := SYSDATE;
--需求来源：表中记录的审计字段信息的更新
--功能描述：用于保证审计信息的完整性 和一致性
V_ERROR_COMMENT:='BEFORE NETS_INSERT_AUDIT';
OPEN C_SWITCH('NETS_INSERT_AUDIT');       --获取子功能控制信息
FETCH C_SWITCH INTO V_STATUS;
IF C_SWITCH%FOUND AND V_STATUS = '1' THEN
:NEW.CREATED_DATE := V_TRIGGER_DATE;
:NEW.UPDATED_DATE := V_TRIGGER_DATE;
:NEW.CREATED_BY   := V_TRIGGER_USER_CREATED;
:NEW.UPDATED_BY   := V_TRIGGER_USER_UPDATED;
END IF;
CLOSE C_SWITCH;
--出错处理。触发器执行有误，将出错信息插入到tr_error_log表
EXCEPTION
WHEN OTHERS THEN
V_SQLCODE := SQLCODE;
V_SQLERRM := SUBSTR(SQLERRM,1,200);
INSERT INTO TEST.TR_ERROR_LOG
(ERROR_NO          ,         --系统错误代码
ERROR_MESSAGE     ,         --系统错误信息
TRIGGER_NAME      ,         --出错的trigger
TRIGGER_USER      ,         --出错的用户
TRIGGER_DATE      ,         --出错的时间
ERROR_COMMENT               --出错详细信息
)
VALUES
(V_SQLCODE,
V_SQLERRM,
'INF_BUSSINESS_BI',
V_TRIGGER_USER_CREATED,
V_TRIGGER_DATE,
V_ERROR_COMMENT
);
END;
/
　　
　　
　　
　　------
　　
　　------
　　------