oracle_package\packeage body

 

---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;
/ 

 

 

 

 ------

 

 ------

 ------