这将是一个难以回答的问题，这就是为什么3天我一直在研究这个包（我的第一个包装）我一直犹豫不决 .
下面是我的包的规格和正文的布局 . 在你看之前，这就是我想要完成的事情 . 我很接近完成所以没有必要担心这个问题不值得你花时间 .
您也可以在代码中看到我自己的一些个人笔记 .
我的代码是不完整的，目前没有编译，但在它停止编译之前我可以告诉你它也没有用 . DROP和CREATE过程有效 . 不需要接触这些 . 我的主要问题是LOG_PROC，我的EXCEPTIONS，我的ARCHIVE_ALL_TABLES ...据我所知
这是我想要做的：
创建一个包，可用于将新创建的表“归档”为“TEST_TABLE_A_13AUG2012”格式的归档表 . 这个包将使用我创建的名为VW_TEST_TABLES的视图，该视图包含以下数据：
TEST_TABLE_A
TEST_TABLE_B
TEST_TABLE_C
TEST_TABLE_D
此包需要在创建新表之前删除所有先前存档的表 . 因此，我的包需要同时包含DROP_ARCHIVE_TABLES和CREATE_ARCHIVE_TABLES过程 . 除了DROP和CREATE过程之外，我的包还有一个名为ARCHIVE_ALL_TABLES的主过程 . 这是需要调用的过程（例如由调度程序）并进行实际归档 . 我需要在这些过程中加入适当的异常处理 . （例如，当我去掉它时，不关心表是否存在） .
最后，为了正确跟踪每个归档运行，我想构建一个日志记录机制 . 为此，我在我的架构中构建了一个名为TEST_PACKAGE_LOG_TBL的表 . 此表应包含以下列：ARCHIVE_DATE（DATE），TABLE_NAME（VARCHAR2（30）），STATUS_CODE（VARCHAR2（1）），COMMENTS（VARCHAR2（4000）） . 对于我存档的每个表，我想记录日期，表名，成功的'S'或错误的'E'，如果我在删除或创建表时遇到错误，SQLERRM应该是什么显示 .
最后，我的ARCHIVE_ALL_TABLES过程应在完成时检查此日志表，以确定是否有任何表未正确存档 . 我创建了一个函数ERRORS_FOUND（返回布尔值），它接受一个IN参数（今天的日期）并检查日志表中的错误 . 如果此函数返回true，我的ARCHIVE_ALL_TABLES过程应该考虑到这一点，并且“通知管理员”（现在我暂时离开了这个，但最终只会通过注释说明我会通知管理员并放置NULL;如果那么结束块 . ）
总而言之，我的包结构必须包含（至少）以下过程：
ARCHIVE_ALL_TABLES，DROP_ARCHIVE_TABLE，CREATE_ARCHIVE_TABLE，ERRORS_FOUND（函数）
--package specification
CREATE OR REPLACE PACKAGE PKG_TEST_TABLES IS
-- Author :
-- Created : 8/14/2012 8:40:18 AM
-- Purpose : For storing procedures to drop, create, and archive new tables
/* Package specification*/
PROCEDURE ARCHIVE_ALL_TABLES;
PROCEDURE DROP_ARCHIVE_TABLES; --2nd
PROCEDURE CREATE_ARCHIVE_TABLES; --1st and call both from archive tables first assuming it works
PROCEDURE LOG_PROC
(
P_PROCESS_START_TIMESTAMP TIMESTAMP
,P_ARCHIVE_DATE DATE
,P_TABLE_NAME VARCHAR2
,P_STATUS_CODE VARCHAR2
,P_COMMENTS VARCHAR2
);
PROCEDURE W(STR VARCHAR2);
FUNCTION ERRORS_FOUND(P_JOB_RUN_TIMESTAMP TIMESTAMP) RETURN BOOLEAN;
END PKG_TEST_TABLES;
--package body
CREATE OR REPLACE PACKAGE BODY PKG_TEST_TABLES IS
/* Package body*/
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* Procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
PROCEDURE W(STR VARCHAR2) IS
L_STRING VARCHAR2(4000);
BEGIN
L_STRING := STR;
DBMS_OUTPUT.PUT_LINE(STR);
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE DROP_ARCHIVE_TABLES AS
/* Purpose: For dropping previously archived tables so that new ones can be created */
L_NO_TABLES_TO_DROP EXCEPTION;
BEGIN
/* Will drop previously archived tables not current ones*/
FOR STMT IN (SELECT 'DROP TABLE mySchema.' || TABLE_NAME AS STR
FROM VW_TEST_TABLES
WHERE REGEXP_LIKE(TABLE_NAME, '.+[0...9]'))
LOOP
EXECUTE IMMEDIATE STMT.STR; --so that I don't need ';' at the end of each dynamically created SQL
END LOOP;
W('Done'); --put the W back in here when in package scope
EXCEPTION
WHEN L_NO_TABLES_TO_DROP THEN
NULL;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE CREATE_ARCHIVE_TABLES AS
/* purpose: setting variable to equal the creation of my 4 tables. Recreating the archive tables */
L_NO_TABLES_TO_CREATE EXCEPTION;
L_TABLES_NOT_SUCCESSFULLY_CREATED EXCEPTION;
BEGIN
FOR STMT IN (SELECT 'CREATE TABLE ' || TABLE_NAME || '_' || TO_CHAR(SYSDATE, 'ddMONyyyy') || ' AS SELECT * FROM ' || TABLE_NAME AS STR
FROM VW_TEST_TABLES)
--LOG_PROC( ,TO_CHAR(SYSDATE, 'ddMONyyyy') , TABLE_NAME ,'E' ,'TABLE ARCHIVED SUCCESSFULLY')
LOOP
--DBMS_OUTPUT.PUT_LINE(STMT.STR); --want to do a dbms output first before using 'execute immediate'. Hit test, and run it
EXECUTE IMMEDIATE STMT.STR; --so that I don't need ';' at the end of each dynamically created SQL
END LOOP;
-- DBMS_OUTPUT.PUT_LINE('Done'); --put the W back in here when in package scope
EXCEPTION
WHEN L_NO_TABLES_TO_CREATE THEN
NULL; --logging can go here
--can call logging procedure here for dml don't need execute immediate, just use insert into
WHEN L_TABLES_NOT_SUCCESSFULLY_CREATED THEN
NULL; --W('ERROR: ' || SQLERRM);
END;
--PROCEDURE IS NOT CREATING TABLES YET
------------------------------------------------------------------------------------------------- ------------------------------------------------------------------
------------------------------------------------------------------------------------------------- ------------------------------------------------------------------
PROCEDURE LOG_PROC(P_PROCESS_START_TIMESTAMP TIMESTAMP, P_ARCHIVE_DATE DATE, P_TABLE_NAME VARCHAR2, P_STATUS_CODE VARCHAR2, P_COMMENTS VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
/* variables */
L_PROCESS_START_TIMESTAMP TIMESTAMP; L_ARCHIVE_DATE DATE; L_TABLE_NAME VARCHAR2(4000); L_STATUS_CODE VARCHAR2(1); L_COMMENTS VARCHAR2(4000);
BEGIN
L_PROCESS_START_TIMESTAMP := P_PROCESS_START_TIMESTAMP; L_ARCHIVE_DATE := P_ARCHIVE_DATE; L_TABLE_NAME := P_TABLE_NAME; L_STATUS_CODE := P_STATUS_CODE; L_COMMENTS := P_COMMENTS;
INSERT INTO TEST_PACKAGE_LOG_TBL(PROCESS_START_TIMESTAMP, ARCHIVE_DATE, TABLE_NAME, STATUS_CODE, COMMENTS) VALUES(L_PROCESS_START_TIMESTAMP, L_ARCHIVE_DATE, L_TABLE_NAME, L_STATUS_CODE, L_COMMENTS);
RETURN;
END;
------------------------------------------------------------------------------------------------- ------------------------------------------------------------------
------------------------------------------------------------------------------------------------- ------------------------------------------------------------------
FUNCTION ERRORS_FOUND(P_JOB_RUN_TIMESTAMP TIMESTAMP) RETURN BOOLEAN IS
L_JOB_RUN_TIMESTAMP TIMESTAMP; ERROR_COUNT NUMBER; ERROR_BOOL BOOLEAN;
BEGIN
L_JOB_RUN_TIMESTAMP := P_JOB_RUN_TIMESTAMP;
SELECT COUNT(*) INTO ERROR_COUNT FROM TEST_PACKAGE_LOG_TBL WHERE STATUS_CODE = 'E' AND PROCESS_START_TIMESTAMP = L_JOB_RUN_TIMESTAMP; IF ERROR_COUNT > 0 THEN ERROR_BOOL := TRUE; ELSE ERROR_BOOL := FALSE;
END IF;
RETURN ERROR_BOOL;
END;
------------------------------------------------------------------------------------------------- ------------------------------------------------------------------
------------------------------------------------------------------------------------------------- ------------------------------------------------------------------
PROCEDURE ARCHIVE_ALL_TABLES AS
/*
Original Author:
Created Date: 13-Aug-2012
Purpose: To drop all tables before recreating and archiving newly created tables
NOTE: in package - do not use create or replace and 'as' would be alternative to 'is'
*/
/*variables*/
L_DROP_ARCHIVE_TABLES VARCHAR2(4000); L_SQL_CREATE_ARCHIVED_TABLES VARCHAR2(4000); L_PREVENT_SQL_INJECTION
EXCEPTION
;
--L_NOTIFY_ADMINISTRATOR VARCHAR(4000); --TO BE DONE AT A LATER TIME
BEGIN
RETURN;
EXCEPTION
WHEN L_PREVENT_SQL_INJECTION THEN NULL;
WHEN OTHERS THEN W('ERROR: ' || SQLERRM);
END;
------------------------------------------------------------------------------------------------- ------------------------------------------------------------------
------------------------------------------------------------------------------------------------- ------------------------------------------------------------------
BEGIN
-- Initialization
/*archive all tables is like my 'driver' that calls drop then create while logging to the table. Pragma_auto prevents a rollback which would prevent table logging
FIRST: This package will need to drop all previously archived tables before it creates new ones. call drop func first*/
/* calling ARCHIVE_ALL_TABLES */
BEGIN
-- Call the function
NULL;
END;
RETURN;
END PKG_TEST_TABLES;
1 回答
您的
LOG_PROC是一个自治事务，所以你需要一个COMMIT.您定义了许多例外，但不要在代码中的任何位置升级它们 . 例如，我猜你需要这样的东西：