创建包名：
1 CREATE OR REPLACE PACKAGE BODY PKG_UTILITY AS 2 3 --字符串转换到索引表 4 PROCEDURE STR_TO_LIST(PI_STR IN VARCHAR2, --字符串 5 PO_LIST OUT VC2000_TABLE, --索引表 6 PO_NUM OUT NUMBER, --单元数 7 PI_DELIMITER IN VARCHAR2 DEFAULT PARAM_INSIDE_DELIMITER --分隔符 8 ) IS 9 V_STR VARCHAR2(32767); 10 V_UNIT VARCHAR2(32767); 11 V_LIST VC2000_TABLE; 12 V_NUM NUMBER; 13 BEGIN 14 V_STR := PI_STR; 15 IF (SUBSTR(V_STR, LENGTH(V_STR) - LENGTH(PI_DELIMITER) + 1)CREATE OR REPLACE PACKAGE pkg_utility AS 16 17 TYPE vc2000_table IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767); 18 --定义常量的方法 19 --定义常量的语法格式 常量名 constant 类型标识符 [not null]:=值; 20 --declare 21 -- pi constant number(9):=3.1415926; 22 -- begin 23 -- commit; 24 -- end; 25 param_inside_delimiter CONSTANT VARCHAR2(1) := CHR(3); 26 27 --SQL类型 28 TYPE rec_sql IS RECORD( 29 sqltext varchar2(2000), --要执行的SQL语句 30 sqltype varchar2(6) , --要执行的SQL语句类型(U,I) 31 tname varchar2(50) --要执行的数据库表名 32 ); 33 TYPE sql_tab IS TABLE OF rec_sql INDEX BY VARCHAR2(32767); 34 35 --字符串转换到索引表 36 PROCEDURE str_to_list( 37 pi_str IN VARCHAR2, --字符串 38 po_list OUT vc2000_table, --索引表 39 po_num OUT NUMBER, --单元数 40 pi_delimiter IN VARCHAR2 DEFAULT param_inside_delimiter --分隔符 41 ); 42 43 --字符串转换到索引表 dengyongbiao 20040412 44 PROCEDURE str_to_namevalue( 45 pi_str IN VARCHAR2, --字符串 46 pi_name_str IN VARCHAR2, --名称串，同时也是返回列表的索引串(全部转换为大写) 47 po_list OUT vc2000_table, --索引表,使用索引串中的字符串作为索引，而不是单元数 48 pi_delimiter IN VARCHAR2 DEFAULT param_inside_delimiter, --分隔符，字符串和索引串相同 49 pi_name_delimiter IN VARCHAR2 default '=' -- 名称和值之间的分隔符 50 ); 51 52 FUNCTION exists_element( 53 pi_list IN vc2000_table, 54 pi_element IN VARCHAR2 55 ) RETURN BOOLEAN; 56 57 FUNCTION list_to_str( 58 pi_list IN vc2000_table, --索引表 59 pi_delimiter IN VARCHAR2 DEFAULT '|' --分隔符 60 )RETURN VARCHAR2; 61 62 --串合并 63 PROCEDURE str_merge( 64 pi_name_str IN VARCHAR2, --名字串 65 pi_value_str IN VARCHAR2, --值串 66 po_merge_str OUT VARCHAR2, --合并串 67 po_fhz OUT VARCHAR2, --返回值 68 po_msg OUT VARCHAR2, --返回消息 69 pi_in_delimiter IN VARCHAR2 default CHR(3), 70 pi_out_delimiter IN VARCHAR2 default '=' 71 ); 72 73 FUNCTION number_months( 74 pi_number IN NUMBER, 75 months IN NUMBER 76 )RETURN NUMBER ; 77 78 --动态执行SQL语句(UPDATE和INSERT)且事务由调用者控制 79 PROCEDURE exec_sql( 80 pi_sqltab IN pkg_utility.sql_tab, --SQL语句索引表 81 po_fhz OUT VARCHAR2, 82 po_msg OUT VARCHAR2 83 ) ; 84 85 --动态执行SQL语句(UPDATE和INSERT)且 86 --动态语句执行及执行完成间的事务由程序控制（独立事务） 87 --要么动态语句全部提交成功，要么就全部不提交 88 PROCEDURE exec_sql_pragma( 89 pi_sqltab IN pkg_utility.sql_tab, --SQL语句索引表 90 po_fhz OUT VARCHAR2, 91 po_msg OUT VARCHAR2 92 ) ; 93 94 95 END pkg_utility; 96 != 97 PI_DELIMITER) THEN 98 V_STR := PI_STR || PI_DELIMITER; 99 END IF; 100 101 V_NUM := 0; 102 103 WHILE (LENGTH(V_STR) > 1 AND V_STR IS NOT NULL) LOOP 104 105 V_UNIT := SUBSTR(V_STR, 1, INSTR(V_STR, PI_DELIMITER) - 1); 106 107 V_NUM := V_NUM + 1; 108 V_LIST(V_NUM) := V_UNIT; 109 110 V_STR := SUBSTR(V_STR, 111 INSTR(V_STR, PI_DELIMITER) + LENGTH(PI_DELIMITER)); 112 113 END LOOP; 114 115 PO_LIST := V_LIST; 116 PO_NUM := V_NUM; 117 118 END STR_TO_LIST; 119 120 --字符串转换到名称值列表 dengyongbiao 20040412 121 PROCEDURE STR_TO_NAMEVALUE(PI_STR IN VARCHAR2, --字符串 122 PI_NAME_STR IN VARCHAR2, --名称串，同时也是返回列表的索引串(全部转换为大写) 123 PO_LIST OUT VC2000_TABLE, --索引表,使用索引串中的字符串作为索引，而不是单元数 124 PI_DELIMITER IN VARCHAR2 DEFAULT PARAM_INSIDE_DELIMITER, --分隔符，字符串和索引串相同 125 PI_NAME_DELIMITER IN VARCHAR2 DEFAULT '=' -- 名称和值之间的分隔符 126 ) IS 127 V_STR VARCHAR2(32767); 128 V_NAME_STR VARCHAR2(32767); 129 V_UNIT VARCHAR2(32767); 130 V_INDEX_UNIT VARCHAR2(32767); 131 V_LIST VC2000_TABLE; 132 V_NAMELIST VC2000_TABLE; 133 V_VALUELIST VC2000_TABLE; 134 V_LIST_RTN VC2000_TABLE; 135 136 V_NUM1 NUMBER; 137 V_NUM2 NUMBER; 138 V_NUM3 NUMBER; 139 140 BEGIN 141 V_STR := PI_STR; 142 V_NAME_STR := PI_NAME_STR; 143 144 STR_TO_LIST(PI_STR, V_LIST, V_NUM1, PI_DELIMITER); 145 STR_TO_LIST(PI_NAME_STR, V_NAMELIST, V_NUM2, PI_DELIMITER); 146 IF V_NUM2 > 0 THEN 147 FOR I IN 1 .. V_NUM2 LOOP 148 V_LIST_RTN(UPPER(V_NAMELIST(I))) := NULL; -- 初始化 149 FOR J IN 1 .. V_NUM1 LOOP 150 STR_TO_LIST(V_LIST(J), V_VALUELIST, V_NUM3, PI_NAME_DELIMITER); 151 IF V_NUM3 = 1 THEN 152 V_VALUELIST(2) := NULL; 153 END IF; 154 IF V_NUM3 > 0 AND UPPER(V_VALUELIST(1)) = UPPER(V_NAMELIST(I)) THEN 155 V_LIST_RTN(UPPER(V_NAMELIST(I))) := V_VALUELIST(2); 156 END IF; 157 END LOOP; 158 END LOOP; 159 END IF; 160 161 PO_LIST := V_LIST_RTN; 162 163 END STR_TO_NAMEVALUE; 164 165 FUNCTION EXISTS_ELEMENT(PI_LIST IN VC2000_TABLE, PI_ELEMENT IN VARCHAR2) 166 RETURN BOOLEAN IS 167 BEGIN 168 IF PI_LIST.COUNT = 0 THEN 169 RETURN FALSE; 170 END IF; 171 172 FOR I IN 1 .. PI_LIST.COUNT LOOP 173 IF PI_ELEMENT = PI_LIST(I) THEN 174 RETURN TRUE; 175 END IF; 176 END LOOP; 177 178 RETURN FALSE; 179 END; 180 181 FUNCTION LIST_TO_STR(PI_LIST IN VC2000_TABLE, --索引表 182 PI_DELIMITER IN VARCHAR2 DEFAULT '|' --分隔符 183 ) RETURN VARCHAR2 IS 184 V_STR VARCHAR2(32767); 185 BEGIN 186 IF PI_LIST.COUNT = 0 THEN 187 RETURN NULL; 188 END IF; 189 190 FOR I IN PI_LIST.FIRST .. PI_LIST.LAST LOOP 191 V_STR := V_STR || PI_LIST(I) || PI_DELIMITER; 192 END LOOP; 193 194 V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - LENGTH(PI_DELIMITER)); 195 RETURN V_STR; 196 END; 197 198 --串合并 199 PROCEDURE STR_MERGE(PI_NAME_STR IN VARCHAR2, --名字串 200 PI_VALUE_STR IN VARCHAR2, --值串 201 PO_MERGE_STR OUT VARCHAR2, --合并串 202 PO_FHZ OUT VARCHAR2, --返回值 203 PO_MSG OUT VARCHAR2, --返回消息 204 PI_IN_DELIMITER IN VARCHAR2 DEFAULT CHR(3), 205 PI_OUT_DELIMITER IN VARCHAR2 DEFAULT '=') IS 206 V_NAME_LIST VC2000_TABLE; 207 V_VALUE_LIST VC2000_TABLE; 208 V_NUM NUMBER; 209 BEGIN 210 STR_TO_LIST(PI_NAME_STR, V_NAME_LIST, V_NUM); 211 STR_TO_LIST(PI_VALUE_STR, V_VALUE_LIST, V_NUM); 212 213 FOR I IN 1 .. V_NUM LOOP 214 IF I = V_NUM THEN 215 PO_MERGE_STR := PO_MERGE_STR || V_NAME_LIST(I) || PI_OUT_DELIMITER || 216 V_VALUE_LIST(I); 217 ELSE 218 PO_MERGE_STR := PO_MERGE_STR || V_NAME_LIST(I) || PI_OUT_DELIMITER || 219 V_VALUE_LIST(I) || PI_IN_DELIMITER; 220 END IF; 221 222 END LOOP; 223 224 END; 225 226 FUNCTION NUMBER_MONTHS(PI_NUMBER IN NUMBER, MONTHS IN NUMBER) RETURN NUMBER IS 227 BEGIN 228 RETURN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR(PI_NUMBER, 1, 6), 229 'yyyymm'), 230 MONTHS), 231 'yyyymm')); 232 EXCEPTION 233 WHEN OTHERS THEN 234 RAISE_APPLICATION_ERROR(-20001, 235 'PKG_UTILITY.number_months_99:' || SQLERRM); 236 237 END; 238 239 --动态执行SQL语句(UPDATE和INSERT)且事务由调用者控制 240 PROCEDURE EXEC_SQL(PI_SQLTAB IN PKG_UTILITY.SQL_TAB, --索引表 241 PO_FHZ OUT VARCHAR2, 242 PO_MSG OUT VARCHAR2) IS 243 V_SQLTEXT VARCHAR2(3000); 244 V_LX_DML VARCHAR2(6); 245 BEGIN 246 FOR I IN 1 .. PI_SQLTAB.COUNT LOOP 247 --获取到SQL内容 248 V_SQLTEXT := PI_SQLTAB(I).SQLTEXT; 249 V_LX_DML := UPPER(SUBSTR(LTRIM(V_SQLTEXT), 1, 6)); 250 IF V_LX_DML <> 'DELETE' AND V_LX_DML <> 'UPDATE' AND 251 V_LX_DML <> 'INSERT' THEN 252 PO_FHZ := 'pkg_utility.exec_sql_050'; 253 PO_MSG := '传入的SQL语句不为DELETE,UPDATE,INSERT.'; 254 RETURN; 255 END IF; 256 257 EXECUTE IMMEDIATE V_SQLTEXT; 258 END LOOP; 259 PO_FHZ := '1'; 260 EXCEPTION 261 WHEN OTHERS THEN 262 PO_FHZ := 'pkg_utility.exec_sql_999'; 263 PO_MSG := '调用pkg_utility.exec_sql出现系统错误.SQLCODE=' || SQLCODE || 264 ',SQLERRM=' || SQLERRM || '，执行语句为:' || V_SQLTEXT; 265 RETURN; 266 END EXEC_SQL; 267 268 --动态执行SQL语句(UPDATE和INSERT)且 269 --动态语句执行及执行完成间的事务由程序控制（独立事务） 270 --要么动态语句全部提交成功，要么就全部不提交 271 PROCEDURE EXEC_SQL_PRAGMA(PI_SQLTAB IN PKG_UTILITY.SQL_TAB, --SQL语句索引表 272 PO_FHZ OUT VARCHAR2, 273 PO_MSG OUT VARCHAR2) IS 274 PRAGMA AUTONOMOUS_TRANSACTION; 275 BEGIN 276 EXEC_SQL(PI_SQLTAB, PO_FHZ, PO_MSG); 277 IF PO_FHZ <> '1' THEN 278 ROLLBACK; 279 RETURN; 280 END IF; 281 COMMIT; 282 PO_FHZ := '1'; 283 EXCEPTION 284 WHEN OTHERS THEN 285 ROLLBACK; 286 PO_FHZ := 'pkg_utility.exec_sql_pragma_999'; 287 PO_MSG := '调用pkg_utility.exec_sql_pragma出现系统错误.SQLCODE=' || SQLCODE || 288 ',SQLERRM=' || SQLERRM; 289 RETURN; 290 END EXEC_SQL_PRAGMA; 291 292 END PKG_UTILITY;
创建包体：
1 CREATE OR REPLACE PACKAGE BODY PKG_UTILITY AS 2 3 --字符串转换到索引表 4 PROCEDURE STR_TO_LIST(PI_STR IN VARCHAR2, --字符串 5 PO_LIST OUT VC2000_TABLE, --索引表 6 PO_NUM OUT NUMBER, --单元数 7 PI_DELIMITER IN VARCHAR2 DEFAULT PARAM_INSIDE_DELIMITER --分隔符 8 ) IS 9 V_STR VARCHAR2(32767); 10 V_UNIT VARCHAR2(32767); 11 V_LIST VC2000_TABLE; 12 V_NUM NUMBER; 13 BEGIN 14 V_STR := PI_STR; 15 IF (SUBSTR(V_STR, LENGTH(V_STR) - LENGTH(PI_DELIMITER) + 1) != 16 PI_DELIMITER) THEN 17 V_STR := PI_STR || PI_DELIMITER; 18 END IF; 19 20 V_NUM := 0; 21 22 WHILE (LENGTH(V_STR) > 1 AND V_STR IS NOT NULL) LOOP 23 24 V_UNIT := SUBSTR(V_STR, 1, INSTR(V_STR, PI_DELIMITER) - 1); 25 26 V_NUM := V_NUM + 1; 27 V_LIST(V_NUM) := V_UNIT; 28 29 V_STR := SUBSTR(V_STR, 30 INSTR(V_STR, PI_DELIMITER) + LENGTH(PI_DELIMITER)); 31 32 END LOOP; 33 34 PO_LIST := V_LIST; 35 PO_NUM := V_NUM; 36 37 END STR_TO_LIST; 38 39 --字符串转换到名称值列表 dengyongbiao 20040412 40 PROCEDURE STR_TO_NAMEVALUE(PI_STR IN VARCHAR2, --字符串 41 PI_NAME_STR IN VARCHAR2, --名称串，同时也是返回列表的索引串(全部转换为大写) 42 PO_LIST OUT VC2000_TABLE, --索引表,使用索引串中的字符串作为索引，而不是单元数 43 PI_DELIMITER IN VARCHAR2 DEFAULT PARAM_INSIDE_DELIMITER, --分隔符，字符串和索引串相同 44 PI_NAME_DELIMITER IN VARCHAR2 DEFAULT '=' -- 名称和值之间的分隔符 45 ) IS 46 V_STR VARCHAR2(32767); 47 V_NAME_STR VARCHAR2(32767); 48 V_UNIT VARCHAR2(32767); 49 V_INDEX_UNIT VARCHAR2(32767); 50 V_LIST VC2000_TABLE; 51 V_NAMELIST VC2000_TABLE; 52 V_VALUELIST VC2000_TABLE; 53 V_LIST_RTN VC2000_TABLE; 54 55 V_NUM1 NUMBER; 56 V_NUM2 NUMBER; 57 V_NUM3 NUMBER; 58 59 BEGIN 60 V_STR := PI_STR; 61 V_NAME_STR := PI_NAME_STR; 62 63 STR_TO_LIST(PI_STR, V_LIST, V_NUM1, PI_DELIMITER); 64 STR_TO_LIST(PI_NAME_STR, V_NAMELIST, V_NUM2, PI_DELIMITER); 65 IF V_NUM2 > 0 THEN 66 FOR I IN 1 .. V_NUM2 LOOP 67 V_LIST_RTN(UPPER(V_NAMELIST(I))) := NULL; -- 初始化 68 FOR J IN 1 .. V_NUM1 LOOP 69 STR_TO_LIST(V_LIST(J), V_VALUELIST, V_NUM3, PI_NAME_DELIMITER); 70 IF V_NUM3 = 1 THEN 71 V_VALUELIST(2) := NULL; 72 END IF; 73 IF V_NUM3 > 0 AND UPPER(V_VALUELIST(1)) = UPPER(V_NAMELIST(I)) THEN 74 V_LIST_RTN(UPPER(V_NAMELIST(I))) := V_VALUELIST(2); 75 END IF; 76 END LOOP; 77 END LOOP; 78 END IF; 79 80 PO_LIST := V_LIST_RTN; 81 82 END STR_TO_NAMEVALUE; 83 84 FUNCTION EXISTS_ELEMENT(PI_LIST IN VC2000_TABLE, PI_ELEMENT IN VARCHAR2) 85 RETURN BOOLEAN IS 86 BEGIN 87 IF PI_LIST.COUNT = 0 THEN 88 RETURN FALSE; 89 END IF; 90 91 FOR I IN 1 .. PI_LIST.COUNT LOOP 92 IF PI_ELEMENT = PI_LIST(I) THEN 93 RETURN TRUE; 94 END IF; 95 END LOOP; 96 97 RETURN FALSE; 98 END; 99 100 FUNCTION LIST_TO_STR(PI_LIST IN VC2000_TABLE, --索引表 101 PI_DELIMITER IN VARCHAR2 DEFAULT '|' --分隔符 102 ) RETURN VARCHAR2 IS 103 V_STR VARCHAR2(32767); 104 BEGIN 105 IF PI_LIST.COUNT = 0 THEN 106 RETURN NULL; 107 END IF; 108 109 FOR I IN PI_LIST.FIRST .. PI_LIST.LAST LOOP 110 V_STR := V_STR || PI_LIST(I) || PI_DELIMITER; 111 END LOOP; 112 113 V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - LENGTH(PI_DELIMITER)); 114 RETURN V_STR; 115 END; 116 117 --串合并 118 PROCEDURE STR_MERGE(PI_NAME_STR IN VARCHAR2, --名字串 119 PI_VALUE_STR IN VARCHAR2, --值串 120 PO_MERGE_STR OUT VARCHAR2, --合并串 121 PO_FHZ OUT VARCHAR2, --返回值 122 PO_MSG OUT VARCHAR2, --返回消息 123 PI_IN_DELIMITER IN VARCHAR2 DEFAULT CHR(3), 124 PI_OUT_DELIMITER IN VARCHAR2 DEFAULT '=') IS 125 V_NAME_LIST VC2000_TABLE; 126 V_VALUE_LIST VC2000_TABLE; 127 V_NUM NUMBER; 128 BEGIN 129 STR_TO_LIST(PI_NAME_STR, V_NAME_LIST, V_NUM); 130 STR_TO_LIST(PI_VALUE_STR, V_VALUE_LIST, V_NUM); 131 132 FOR I IN 1 .. V_NUM LOOP 133 IF I = V_NUM THEN 134 PO_MERGE_STR := PO_MERGE_STR || V_NAME_LIST(I) || PI_OUT_DELIMITER || 135 V_VALUE_LIST(I); 136 ELSE 137 PO_MERGE_STR := PO_MERGE_STR || V_NAME_LIST(I) || PI_OUT_DELIMITER || 138 V_VALUE_LIST(I) || PI_IN_DELIMITER; 139 END IF; 140 141 END LOOP; 142 143 END; 144 145 FUNCTION NUMBER_MONTHS(PI_NUMBER IN NUMBER, MONTHS IN NUMBER) RETURN NUMBER IS 146 BEGIN 147 RETURN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR(PI_NUMBER, 1, 6), 148 'yyyymm'), 149 MONTHS), 150 'yyyymm')); 151 EXCEPTION 152 WHEN OTHERS THEN 153 RAISE_APPLICATION_ERROR(-20001, 154 'PKG_UTILITY.number_months_99:' || SQLERRM); 155 156 END; 157 158 --动态执行SQL语句(UPDATE和INSERT)且事务由调用者控制 159 PROCEDURE EXEC_SQL(PI_SQLTAB IN PKG_UTILITY.SQL_TAB, --索引表 160 PO_FHZ OUT VARCHAR2, 161 PO_MSG OUT VARCHAR2) IS 162 V_SQLTEXT VARCHAR2(3000); 163 V_LX_DML VARCHAR2(6); 164 BEGIN 165 FOR I IN 1 .. PI_SQLTAB.COUNT LOOP 166 --获取到SQL内容 167 V_SQLTEXT := PI_SQLTAB(I).SQLTEXT; 168 V_LX_DML := UPPER(SUBSTR(LTRIM(V_SQLTEXT), 1, 6)); 169 IF V_LX_DML <> 'DELETE' AND V_LX_DML <> 'UPDATE' AND 170 V_LX_DML <> 'INSERT' THEN 171 PO_FHZ := 'pkg_utility.exec_sql_050'; 172 PO_MSG := '传入的SQL语句不为DELETE,UPDATE,INSERT.'; 173 RETURN; 174 END IF; 175 176 EXECUTE IMMEDIATE V_SQLTEXT; 177 END LOOP; 178 PO_FHZ := '1'; 179 EXCEPTION 180 WHEN OTHERS THEN 181 PO_FHZ := 'pkg_utility.exec_sql_999'; 182 PO_MSG := '调用pkg_utility.exec_sql出现系统错误.SQLCODE=' || SQLCODE || 183 ',SQLERRM=' || SQLERRM || '，执行语句为:' || V_SQLTEXT; 184 RETURN; 185 END EXEC_SQL; 186 187 --动态执行SQL语句(UPDATE和INSERT)且 188 --动态语句执行及执行完成间的事务由程序控制（独立事务） 189 --要么动态语句全部提交成功，要么就全部不提交 190 PROCEDURE EXEC_SQL_PRAGMA(PI_SQLTAB IN PKG_UTILITY.SQL_TAB, --SQL语句索引表 191 PO_FHZ OUT VARCHAR2, 192 PO_MSG OUT VARCHAR2) IS 193 PRAGMA AUTONOMOUS_TRANSACTION; 194 BEGIN 195 EXEC_SQL(PI_SQLTAB, PO_FHZ, PO_MSG); 196 IF PO_FHZ <> '1' THEN 197 ROLLBACK; 198 RETURN; 199 END IF; 200 COMMIT; 201 PO_FHZ := '1'; 202 EXCEPTION 203 WHEN OTHERS THEN 204 ROLLBACK; 205 PO_FHZ := 'pkg_utility.exec_sql_pragma_999'; 206 PO_MSG := '调用pkg_utility.exec_sql_pragma出现系统错误.SQLCODE=' || SQLCODE || 207 ',SQLERRM=' || SQLERRM; 208 RETURN; 209 END EXEC_SQL_PRAGMA; 210 211 END PKG_UTILITY;