oracle以逗号分隔并循环,ORACLE存储过程逗号分隔循环

原文：https://blog.csdn.net/apple_HYYYY/article/details/84889650

创建分割函数

CREATE OR REPLACE FUNCTION SPLITSTR(P_STRING IN VARCHAR2, P_DELIMITER IN VARCHAR2)

RETURN STR_SPLIT PIPELINED

AS

V_LENGTH NUMBER := LENGTH(P_STRING);

V_START NUMBER := 1;

V_INDEX NUMBER;

BEGIN

WHILE(V_START <= V_LENGTH)

LOOP

V_INDEX := INSTR(P_STRING, P_DELIMITER, V_START);

IF V_INDEX = 0

THEN

PIPE ROW(SUBSTR(P_STRING, V_START));

V_START := V_LENGTH + 1;

ELSE

PIPE ROW(SUBSTR(P_STRING, V_START, V_INDEX - V_START));

V_START := V_INDEX + 1;

END IF;

END LOOP;

RETURN;

END SPLITSTR;

使用：

CREATE OR REPLACE PROCEDURE "SP_FUNDREPORT" (

ACCOUNTID IN VARCHAR2, -- 账簿ID

BEGIN_DATE VARCHAR2, -- 查询日期

OUT_RESULT OUT SYS_REFCURSOR -- 游标

) AS

TMP_BALANCE FLOAT := 0;-- 期初变量

-- 声明变量

TEMP_COUNT number(10);

TEMP_USER_ID VARCHAR2(36);

v_rlt NUMBER(1);

cursor TEMP_ACCOUNT_CURSOR IS select * from table(splitstr(ACCOUNTID, ','));

BEGIN

DELETE FROM TEMP_FUNDREPORT_S;

v_rlt := 1;

select count(*) into TEMP_COUNT from table(splitstr(ACCOUNTID, ','));

open TEMP_ACCOUNT_CURSOR;

loop fetch TEMP_ACCOUNT_CURSOR INTO TEMP_USER_ID;

dbms_output.put_line(TEMP_USER_ID);

-- 查询期初

select (BALANCE+(select nvl(sum(b.DEBIT_AMOUNT),0) from CU_JOURNAL b where b.account_id = TEMP_USER_ID and to_char(b.TRANS_DATE,'yyyy-mm-dd') < BEGIN_DATE)-(select nvl(sum(b.CREDIT_AMOUNT),0) from CU_JOURNAL b where b.account_id = TEMP_USER_ID and to_char(b.TRANS_DATE,'yyyy-mm-dd') < BEGIN_DATE)) INTO TMP_BALANCE from CU_ACCOUNTBOOK a where a.id = TEMP_USER_ID;

-- 删除临时表数据

DELETE FROM TEMP_FUNDREPORT;

-- 添加数据到临时表

INSERT INTO TEMP_FUNDREPORT (

ROWNUMBER, ID, SUMMARY, DEBIT_AMOUNT, CREDIT_AMOUNT, ACCOUNT_BOOK

)

SELECT ROWNUM AS ROWNUMER, A.* FROM (

SELECT A.ID, A.SUMMARY, A.DEBIT_AMOUNT, A.CREDIT_AMOUNT, Z.ACCOUNT_BOOK FROM CU_JOURNAL A

LEFT JOIN CU_ACCOUNTBOOK Z ON Z.ID = A.ACCOUNT_ID

WHERE A.ACCOUNT_ID = TEMP_USER_ID AND TO_CHAR( TRANS_DATE, 'YYYY-MM-DD' ) = BEGIN_DATE

) A;

-- 添加数据

INSERT INTO TEMP_FUNDREPORT_S(

ACCOUNTNAME, SUMMARY, FRONTAMOUNT, DEBITAMOUNT, CREDITAMOUNT, AFTERAMOUNT

) SELECT B.* FROM (

-- 期初余额

SELECT ACCOUNT_BOOK AS ACCOUNTNAME, '期初' AS SUMMARY, NULL AS FRONTAMOUNT, NULL AS DEBITAMOUNT, NULL AS CREDITAMOUNT, TMP_BALANCE AS AFTERAMOUNT

FROM TEMP_FUNDREPORT GROUP BY ACCOUNT_BOOK

-- 每天的数据

UNION ALL

SELECT ACCOUNT_BOOK AS ACCOUNTNAME, TO_CHAR(SUMMARY) AS SUMMARY, NULL AS FRONTAMOUNT, DEBIT_AMOUNT AS DEBITAMOUNT, CREDIT_AMOUNT AS CREDITAMOUNT, NULL AS AFTERAMOUNT

FROM TEMP_FUNDREPORT A

-- 小计

UNION ALL

SELECT ACCOUNT_BOOK AS ACCOUNTNAME, '小计' AS SUMMARY, TMP_BALANCE AS FRONTAMOUNT, SUM( DEBIT_AMOUNT ) DEBITAMOUNT, SUM( CREDIT_AMOUNT ) AS CREDITAMOUNT,

(SELECT TMP_BALANCE+SUM(DEBIT_AMOUNT )-SUM(CREDIT_AMOUNT) FROM TEMP_FUNDREPORT) AS AFTERAMOUNT

FROM TEMP_FUNDREPORT A GROUP BY ACCOUNT_BOOK

) B;

v_rlt := v_rlt + 1;

exit when v_rlt = TEMP_COUNT + 1;

end loop;

-- 查询数据

OPEN OUT_RESULT FOR

SELECT * FROM TEMP_FUNDREPORT_S;

END;