创建包名：
CREATE OR REPLACE PACKAGE BODY PKG_UTILITY AS --字符串转换到索引表
PROCEDURE STR_TO_LIST(PI_STR IN VARCHAR2, --字符串
PO_LIST OUT VC2000_TABLE, --索引表
PO_NUM OUT NUMBER, --单元数
PI_DELIMITER IN VARCHAR2 DEFAULT PARAM_INSIDE_DELIMITER --分隔符
) IS
V_STR VARCHAR2(32767);
V_UNIT VARCHAR2(32767);
V_LIST VC2000_TABLE;
V_NUM NUMBER;
BEGIN
V_STR := PI_STR;
IF (SUBSTR(V_STR, LENGTH(V_STR) - LENGTH(PI_DELIMITER) + 1)CREATE OR REPLACE PACKAGE pkg_utility AS TYPE vc2000_table IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767);
--定义常量的方法
--定义常量的语法格式 常量名 constant 类型标识符 [not null]:=值;
--declare
-- pi constant number(9):=3.1415926;
-- begin
-- commit;
-- end;
param_inside_delimiter CONSTANT VARCHAR2(1) := CHR(3); --SQL类型
TYPE rec_sql IS RECORD(
sqltext varchar2(2000), --要执行的SQL语句
sqltype varchar2(6) , --要执行的SQL语句类型(U,I)
tname varchar2(50) --要执行的数据库表名
);
TYPE sql_tab IS TABLE OF rec_sql INDEX BY VARCHAR2(32767); --字符串转换到索引表
PROCEDURE str_to_list(
pi_str IN VARCHAR2, --字符串
po_list OUT vc2000_table, --索引表
po_num OUT NUMBER, --单元数
pi_delimiter IN VARCHAR2 DEFAULT param_inside_delimiter --分隔符
); --字符串转换到索引表 dengyongbiao 20040412
PROCEDURE str_to_namevalue(
pi_str IN VARCHAR2, --字符串
pi_name_str IN VARCHAR2, --名称串，同时也是返回列表的索引串(全部转换为大写)
po_list OUT vc2000_table, --索引表,使用索引串中的字符串作为索引，而不是单元数
pi_delimiter IN VARCHAR2 DEFAULT param_inside_delimiter, --分隔符，字符串和索引串相同
pi_name_delimiter IN VARCHAR2 default '=' -- 名称和值之间的分隔符
); FUNCTION exists_element(
pi_list IN vc2000_table,
pi_element IN VARCHAR2
) RETURN BOOLEAN; FUNCTION list_to_str(
pi_list IN vc2000_table, --索引表
pi_delimiter IN VARCHAR2 DEFAULT '|' --分隔符
)RETURN VARCHAR2; --串合并
PROCEDURE str_merge(
pi_name_str IN VARCHAR2, --名字串
pi_value_str IN VARCHAR2, --值串
po_merge_str OUT VARCHAR2, --合并串
po_fhz OUT VARCHAR2, --返回值
po_msg OUT VARCHAR2, --返回消息
pi_in_delimiter IN VARCHAR2 default CHR(3),
pi_out_delimiter IN VARCHAR2 default '='
); FUNCTION number_months(
pi_number IN NUMBER,
months IN NUMBER
)RETURN NUMBER ; --动态执行SQL语句(UPDATE和INSERT)且事务由调用者控制
PROCEDURE exec_sql(
pi_sqltab IN pkg_utility.sql_tab, --SQL语句索引表
po_fhz OUT VARCHAR2,
po_msg OUT VARCHAR2
) ; --动态执行SQL语句(UPDATE和INSERT)且
--动态语句执行及执行完成间的事务由程序控制（独立事务）
--要么动态语句全部提交成功，要么就全部不提交
PROCEDURE exec_sql_pragma(
pi_sqltab IN pkg_utility.sql_tab, --SQL语句索引表
po_fhz OUT VARCHAR2,
po_msg OUT VARCHAR2
) ; END pkg_utility;
!=
PI_DELIMITER) THEN
V_STR := PI_STR || PI_DELIMITER;
END IF; V_NUM := 0; WHILE (LENGTH(V_STR) > 1 AND V_STR IS NOT NULL) LOOP V_UNIT := SUBSTR(V_STR, 1, INSTR(V_STR, PI_DELIMITER) - 1); V_NUM := V_NUM + 1;
V_LIST(V_NUM) := V_UNIT; V_STR := SUBSTR(V_STR,
INSTR(V_STR, PI_DELIMITER) + LENGTH(PI_DELIMITER)); END LOOP; PO_LIST := V_LIST;
PO_NUM := V_NUM; END STR_TO_LIST; --字符串转换到名称值列表 dengyongbiao 20040412
PROCEDURE STR_TO_NAMEVALUE(PI_STR IN VARCHAR2, --字符串
PI_NAME_STR IN VARCHAR2, --名称串，同时也是返回列表的索引串(全部转换为大写)
PO_LIST OUT VC2000_TABLE, --索引表,使用索引串中的字符串作为索引，而不是单元数
PI_DELIMITER IN VARCHAR2 DEFAULT PARAM_INSIDE_DELIMITER, --分隔符，字符串和索引串相同
PI_NAME_DELIMITER IN VARCHAR2 DEFAULT '=' -- 名称和值之间的分隔符
) IS
V_STR VARCHAR2(32767);
V_NAME_STR VARCHAR2(32767);
V_UNIT VARCHAR2(32767);
V_INDEX_UNIT VARCHAR2(32767);
V_LIST VC2000_TABLE;
V_NAMELIST VC2000_TABLE;
V_VALUELIST VC2000_TABLE;
V_LIST_RTN VC2000_TABLE; V_NUM1 NUMBER;
V_NUM2 NUMBER;
V_NUM3 NUMBER; BEGIN
V_STR := PI_STR;
V_NAME_STR := PI_NAME_STR; STR_TO_LIST(PI_STR, V_LIST, V_NUM1, PI_DELIMITER);
STR_TO_LIST(PI_NAME_STR, V_NAMELIST, V_NUM2, PI_DELIMITER);
IF V_NUM2 > 0 THEN
FOR I IN 1 .. V_NUM2 LOOP
V_LIST_RTN(UPPER(V_NAMELIST(I))) := NULL; -- 初始化
FOR J IN 1 .. V_NUM1 LOOP
STR_TO_LIST(V_LIST(J), V_VALUELIST, V_NUM3, PI_NAME_DELIMITER);
IF V_NUM3 = 1 THEN
V_VALUELIST(2) := NULL;
END IF;
IF V_NUM3 > 0 AND UPPER(V_VALUELIST(1)) = UPPER(V_NAMELIST(I)) THEN
V_LIST_RTN(UPPER(V_NAMELIST(I))) := V_VALUELIST(2);
END IF;
END LOOP;
END LOOP;
END IF; PO_LIST := V_LIST_RTN; END STR_TO_NAMEVALUE; FUNCTION EXISTS_ELEMENT(PI_LIST IN VC2000_TABLE, PI_ELEMENT IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
IF PI_LIST.COUNT = 0 THEN
RETURN FALSE;
END IF; FOR I IN 1 .. PI_LIST.COUNT LOOP
IF PI_ELEMENT = PI_LIST(I) THEN
RETURN TRUE;
END IF;
END LOOP; RETURN FALSE;
END; FUNCTION LIST_TO_STR(PI_LIST IN VC2000_TABLE, --索引表
PI_DELIMITER IN VARCHAR2 DEFAULT '|' --分隔符
) RETURN VARCHAR2 IS
V_STR VARCHAR2(32767);
BEGIN
IF PI_LIST.COUNT = 0 THEN
RETURN NULL;
END IF; FOR I IN PI_LIST.FIRST .. PI_LIST.LAST LOOP
V_STR := V_STR || PI_LIST(I) || PI_DELIMITER;
END LOOP; V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - LENGTH(PI_DELIMITER));
RETURN V_STR;
END; --串合并
PROCEDURE STR_MERGE(PI_NAME_STR IN VARCHAR2, --名字串
PI_VALUE_STR IN VARCHAR2, --值串
PO_MERGE_STR OUT VARCHAR2, --合并串
PO_FHZ OUT VARCHAR2, --返回值
PO_MSG OUT VARCHAR2, --返回消息
PI_IN_DELIMITER IN VARCHAR2 DEFAULT CHR(3),
PI_OUT_DELIMITER IN VARCHAR2 DEFAULT '=') IS
V_NAME_LIST VC2000_TABLE;
V_VALUE_LIST VC2000_TABLE;
V_NUM NUMBER;
BEGIN
STR_TO_LIST(PI_NAME_STR, V_NAME_LIST, V_NUM);
STR_TO_LIST(PI_VALUE_STR, V_VALUE_LIST, V_NUM); FOR I IN 1 .. V_NUM LOOP
IF I = V_NUM THEN
PO_MERGE_STR := PO_MERGE_STR || V_NAME_LIST(I) || PI_OUT_DELIMITER ||
V_VALUE_LIST(I);
ELSE
PO_MERGE_STR := PO_MERGE_STR || V_NAME_LIST(I) || PI_OUT_DELIMITER ||
V_VALUE_LIST(I) || PI_IN_DELIMITER;
END IF; END LOOP; END; FUNCTION NUMBER_MONTHS(PI_NUMBER IN NUMBER, MONTHS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR(PI_NUMBER, 1, 6),
'yyyymm'),
MONTHS),
'yyyymm'));
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,
'PKG_UTILITY.number_months_99:' || SQLERRM); END; --动态执行SQL语句(UPDATE和INSERT)且事务由调用者控制
PROCEDURE EXEC_SQL(PI_SQLTAB IN PKG_UTILITY.SQL_TAB, --索引表
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_SQLTEXT VARCHAR2(3000);
V_LX_DML VARCHAR2(6);
BEGIN
FOR I IN 1 .. PI_SQLTAB.COUNT LOOP
--获取到SQL内容
V_SQLTEXT := PI_SQLTAB(I).SQLTEXT;
V_LX_DML := UPPER(SUBSTR(LTRIM(V_SQLTEXT), 1, 6));
IF V_LX_DML <> 'DELETE' AND V_LX_DML <> 'UPDATE' AND
V_LX_DML <> 'INSERT' THEN
PO_FHZ := 'pkg_utility.exec_sql_050';
PO_MSG := '传入的SQL语句不为DELETE,UPDATE,INSERT.';
RETURN;
END IF; EXECUTE IMMEDIATE V_SQLTEXT;
END LOOP;
PO_FHZ := '';
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := 'pkg_utility.exec_sql_999';
PO_MSG := '调用pkg_utility.exec_sql出现系统错误.SQLCODE=' || SQLCODE ||
',SQLERRM=' || SQLERRM || '，执行语句为:' || V_SQLTEXT;
RETURN;
END EXEC_SQL; --动态执行SQL语句(UPDATE和INSERT)且
--动态语句执行及执行完成间的事务由程序控制（独立事务）
--要么动态语句全部提交成功，要么就全部不提交
PROCEDURE EXEC_SQL_PRAGMA(PI_SQLTAB IN PKG_UTILITY.SQL_TAB, --SQL语句索引表
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXEC_SQL(PI_SQLTAB, PO_FHZ, PO_MSG);
IF PO_FHZ <> '' THEN
ROLLBACK;
RETURN;
END IF;
COMMIT;
PO_FHZ := '';
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
PO_FHZ := 'pkg_utility.exec_sql_pragma_999';
PO_MSG := '调用pkg_utility.exec_sql_pragma出现系统错误.SQLCODE=' || SQLCODE ||
',SQLERRM=' || SQLERRM;
RETURN;
END EXEC_SQL_PRAGMA; END PKG_UTILITY;
创建包体：
CREATE OR REPLACE PACKAGE BODY PKG_UTILITY AS --字符串转换到索引表
PROCEDURE STR_TO_LIST(PI_STR IN VARCHAR2, --字符串
PO_LIST OUT VC2000_TABLE, --索引表
PO_NUM OUT NUMBER, --单元数
PI_DELIMITER IN VARCHAR2 DEFAULT PARAM_INSIDE_DELIMITER --分隔符
) IS
V_STR VARCHAR2(32767);
V_UNIT VARCHAR2(32767);
V_LIST VC2000_TABLE;
V_NUM NUMBER;
BEGIN
V_STR := PI_STR;
IF (SUBSTR(V_STR, LENGTH(V_STR) - LENGTH(PI_DELIMITER) + 1) !=
PI_DELIMITER) THEN
V_STR := PI_STR || PI_DELIMITER;
END IF; V_NUM := 0; WHILE (LENGTH(V_STR) > 1 AND V_STR IS NOT NULL) LOOP V_UNIT := SUBSTR(V_STR, 1, INSTR(V_STR, PI_DELIMITER) - 1); V_NUM := V_NUM + 1;
V_LIST(V_NUM) := V_UNIT; V_STR := SUBSTR(V_STR,
INSTR(V_STR, PI_DELIMITER) + LENGTH(PI_DELIMITER)); END LOOP; PO_LIST := V_LIST;
PO_NUM := V_NUM; END STR_TO_LIST; --字符串转换到名称值列表 dengyongbiao 20040412
PROCEDURE STR_TO_NAMEVALUE(PI_STR IN VARCHAR2, --字符串
PI_NAME_STR IN VARCHAR2, --名称串，同时也是返回列表的索引串(全部转换为大写)
PO_LIST OUT VC2000_TABLE, --索引表,使用索引串中的字符串作为索引，而不是单元数
PI_DELIMITER IN VARCHAR2 DEFAULT PARAM_INSIDE_DELIMITER, --分隔符，字符串和索引串相同
PI_NAME_DELIMITER IN VARCHAR2 DEFAULT '=' -- 名称和值之间的分隔符
) IS
V_STR VARCHAR2(32767);
V_NAME_STR VARCHAR2(32767);
V_UNIT VARCHAR2(32767);
V_INDEX_UNIT VARCHAR2(32767);
V_LIST VC2000_TABLE;
V_NAMELIST VC2000_TABLE;
V_VALUELIST VC2000_TABLE;
V_LIST_RTN VC2000_TABLE; V_NUM1 NUMBER;
V_NUM2 NUMBER;
V_NUM3 NUMBER; BEGIN
V_STR := PI_STR;
V_NAME_STR := PI_NAME_STR; STR_TO_LIST(PI_STR, V_LIST, V_NUM1, PI_DELIMITER);
STR_TO_LIST(PI_NAME_STR, V_NAMELIST, V_NUM2, PI_DELIMITER);
IF V_NUM2 > 0 THEN
FOR I IN 1 .. V_NUM2 LOOP
V_LIST_RTN(UPPER(V_NAMELIST(I))) := NULL; -- 初始化
FOR J IN 1 .. V_NUM1 LOOP
STR_TO_LIST(V_LIST(J), V_VALUELIST, V_NUM3, PI_NAME_DELIMITER);
IF V_NUM3 = 1 THEN
V_VALUELIST(2) := NULL;
END IF;
IF V_NUM3 > 0 AND UPPER(V_VALUELIST(1)) = UPPER(V_NAMELIST(I)) THEN
V_LIST_RTN(UPPER(V_NAMELIST(I))) := V_VALUELIST(2);
END IF;
END LOOP;
END LOOP;
END IF; PO_LIST := V_LIST_RTN; END STR_TO_NAMEVALUE; FUNCTION EXISTS_ELEMENT(PI_LIST IN VC2000_TABLE, PI_ELEMENT IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
IF PI_LIST.COUNT = 0 THEN
RETURN FALSE;
END IF; FOR I IN 1 .. PI_LIST.COUNT LOOP
IF PI_ELEMENT = PI_LIST(I) THEN
RETURN TRUE;
END IF;
END LOOP; RETURN FALSE;
END; FUNCTION LIST_TO_STR(PI_LIST IN VC2000_TABLE, --索引表
PI_DELIMITER IN VARCHAR2 DEFAULT '|' --分隔符
) RETURN VARCHAR2 IS
V_STR VARCHAR2(32767);
BEGIN
IF PI_LIST.COUNT = 0 THEN
RETURN NULL;
END IF; FOR I IN PI_LIST.FIRST .. PI_LIST.LAST LOOP
V_STR := V_STR || PI_LIST(I) || PI_DELIMITER;
END LOOP; V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - LENGTH(PI_DELIMITER));
RETURN V_STR;
END; --串合并
PROCEDURE STR_MERGE(PI_NAME_STR IN VARCHAR2, --名字串
PI_VALUE_STR IN VARCHAR2, --值串
PO_MERGE_STR OUT VARCHAR2, --合并串
PO_FHZ OUT VARCHAR2, --返回值
PO_MSG OUT VARCHAR2, --返回消息
PI_IN_DELIMITER IN VARCHAR2 DEFAULT CHR(3),
PI_OUT_DELIMITER IN VARCHAR2 DEFAULT '=') IS
V_NAME_LIST VC2000_TABLE;
V_VALUE_LIST VC2000_TABLE;
V_NUM NUMBER;
BEGIN
STR_TO_LIST(PI_NAME_STR, V_NAME_LIST, V_NUM);
STR_TO_LIST(PI_VALUE_STR, V_VALUE_LIST, V_NUM); FOR I IN 1 .. V_NUM LOOP
IF I = V_NUM THEN
PO_MERGE_STR := PO_MERGE_STR || V_NAME_LIST(I) || PI_OUT_DELIMITER ||
V_VALUE_LIST(I);
ELSE
PO_MERGE_STR := PO_MERGE_STR || V_NAME_LIST(I) || PI_OUT_DELIMITER ||
V_VALUE_LIST(I) || PI_IN_DELIMITER;
END IF; END LOOP; END; FUNCTION NUMBER_MONTHS(PI_NUMBER IN NUMBER, MONTHS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR(PI_NUMBER, 1, 6),
'yyyymm'),
MONTHS),
'yyyymm'));
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,
'PKG_UTILITY.number_months_99:' || SQLERRM); END; --动态执行SQL语句(UPDATE和INSERT)且事务由调用者控制
PROCEDURE EXEC_SQL(PI_SQLTAB IN PKG_UTILITY.SQL_TAB, --索引表
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
V_SQLTEXT VARCHAR2(3000);
V_LX_DML VARCHAR2(6);
BEGIN
FOR I IN 1 .. PI_SQLTAB.COUNT LOOP
--获取到SQL内容
V_SQLTEXT := PI_SQLTAB(I).SQLTEXT;
V_LX_DML := UPPER(SUBSTR(LTRIM(V_SQLTEXT), 1, 6));
IF V_LX_DML <> 'DELETE' AND V_LX_DML <> 'UPDATE' AND
V_LX_DML <> 'INSERT' THEN
PO_FHZ := 'pkg_utility.exec_sql_050';
PO_MSG := '传入的SQL语句不为DELETE,UPDATE,INSERT.';
RETURN;
END IF; EXECUTE IMMEDIATE V_SQLTEXT;
END LOOP;
PO_FHZ := '';
EXCEPTION
WHEN OTHERS THEN
PO_FHZ := 'pkg_utility.exec_sql_999';
PO_MSG := '调用pkg_utility.exec_sql出现系统错误.SQLCODE=' || SQLCODE ||
',SQLERRM=' || SQLERRM || '，执行语句为:' || V_SQLTEXT;
RETURN;
END EXEC_SQL; --动态执行SQL语句(UPDATE和INSERT)且
--动态语句执行及执行完成间的事务由程序控制（独立事务）
--要么动态语句全部提交成功，要么就全部不提交
PROCEDURE EXEC_SQL_PRAGMA(PI_SQLTAB IN PKG_UTILITY.SQL_TAB, --SQL语句索引表
PO_FHZ OUT VARCHAR2,
PO_MSG OUT VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXEC_SQL(PI_SQLTAB, PO_FHZ, PO_MSG);
IF PO_FHZ <> '' THEN
ROLLBACK;
RETURN;
END IF;
COMMIT;
PO_FHZ := '';
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
PO_FHZ := 'pkg_utility.exec_sql_pragma_999';
PO_MSG := '调用pkg_utility.exec_sql_pragma出现系统错误.SQLCODE=' || SQLCODE ||
',SQLERRM=' || SQLERRM;
RETURN;
END EXEC_SQL_PRAGMA; END PKG_UTILITY;