Oracle 积累
时间:2022-03-14 02:58
1、知识点:
1)ORACLE 一维数组或者多维数组 定义、初始化以及使用
2)ORACLE PIPELINED 的使用
3)ORACLE利用INSTR和SUBSTR的字符串操作函数,实现SPLIT函数方法
2、实例:
TYPE para_array IS TABLE OF varchar2(10000) INDEX BY BINARY_INTEGER; --一维字符数组 TYPE para_array_double IS TABLE OF para_array INDEX BY BINARY_INTEGER; --二维数组 --根据分隔符P_SEP 分割字符串P_LIST 返回数组 PARA_ARRAY FUNCTION F_SPLIT_STR(P_LIST VARCHAR2, P_SEP VARCHAR2) RETURN PARA_ARRAY PIPELINED IS L_IDX PLS_INTEGER; V_LIST VARCHAR2(50) := P_LIST; BEGIN LOOP L_IDX := INSTR(V_LIST, P_SEP); IF L_IDX > 0 THEN PIPE ROW(SUBSTR(V_LIST, 1, L_IDX - 1)); V_LIST := SUBSTR(V_LIST, L_IDX + LENGTH(P_SEP)); ELSE PIPE ROW(V_LIST); EXIT; END IF; END LOOP; RETURN; END F_SPLIT_STR; --对字符串以I_SPLIT 分隔符进行分割,返回一维数组para_array FUNCTION F_GET_ARR(I_VALUE IN VARCHAR2,I_SPLIT IN VARCHAR2) RETURN para_array IS V_ARR para_array; V_VALUE VARCHAR2(1000); V_COUNT NUMBER; V_NUM NUMBER; CURSOR C_VALUE IS SELECT * FROM TABLE(F_SPLIT_STR(I_VALUE, I_SPLIT)); BEGIN V_ARR := para_array(); V_VALUE := ‘‘; V_NUM := 1; FOR V_CUR_VALUE IN C_VALUE LOOP V_VALUE := V_CUR_VALUE.COLUMN_VALUE; IF V_VALUE IS NOT NULL THEN V_ARR.EXTEND(); V_ARR(V_NUM) := V_VALUE ; V_NUM := V_NUM + 1 ; END IF; END LOOP; RETURN V_ARR; END F_GET_ARR; --对多次分割的字符串进行分割操作,并返回二维数组para_array_double FUNCTION F_GET_ARR_DOUBLE(I_VALUE_STR IN varchar2) RETURN para_array_double IS V_VALUE_STR_ARR para_array; V_VALUE_ARR para_array; V_VALUE_ARR_DOUBLE para_array_double; V_VALUE VARCHAR2(1000); V_NUM NUMBER; BEGIN V_VALUE_STR_ARR := para_array(); V_VALUE_ARR := para_array(); V_VALUE_STR_ARR := F_GET_BENF_INSURED_ARR(I_VALUE_STR,‘;‘); for V_NUM IN 1..V_VALUE_STR_ARR.COUNT loop V_VALUE_ARR := F_GET_ARR(V_VALUE_STR_ARR(V_NUM),‘,‘); V_VALUE_ARR_DOUBLE(V_NUM) := V_VALUE_ARR; end loop; RETURN V_VALUE_ARR_DOUBLE; END F_GET_ARR_DOUBLE; --测试用例 PROCEDURE test_arr(I_TEST_STR IN VARCHAR2, O_MSG OUT VARCHAR2) IS V_ARR_DOUBLE para_array_double; BEGIN V_ARR_DOUBLE := F_GET_ARR_DOUBLE(I_TEST_STR); FOR I IN 1..V_ARR_DOUBLE.COUNT LOOP FOR J IN 1..V_ARR_DOUBLE(I).COUNT LOOP DBMS_OUTPUT.PUT_LINE(V_ARR_DOUBLE(I)(J)); END LOOP; END LOOP; O_MSG := ‘TRUE‘; END TEST_ARR;