记录Oracle一段函数

根据查询出来的所有账户,合并到另一个账户去,并更新当前账户为空

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE
V_COUNT NUMBER(10);
BEGIN
FOR TEMP_CURSOR IN (SELECT ACCOUNT_ID,USER_ID,TOTAL_BALANCE,AVAILABLE_BALANCE FROM ACCOUNTS WHERE SYMBOL = 'VTX' AND TYPE = 2 AND TOTAL_BALANCE > 0) LOOP
SELECT COUNT(*) INTO V_COUNT FROM ACCOUNTS WHERE USER_ID = TEMP_CURSOR.USER_ID AND SYMBOL = 'VTC' AND TYPE = 2;
IF V_COUNT > 0 THEN
UPDATE ACCOUNTS
SET UPDATED_AT = SYSDATE,TOTAL_BALANCE = TOTAL_BALANCE + TEMP_CURSOR.TOTAL_BALANCE,AVAILABLE_BALANCE = AVAILABLE_BALANCE + TEMP_CURSOR.AVAILABLE_BALANCE
WHERE USER_ID = TEMP_CURSOR.USER_ID AND SYMBOL = 'VTC' AND TYPE = 2 ;
UPDATE ACCOUNTS
SET UPDATED_AT = SYSDATE,TOTAL_BALANCE = 0,AVAILABLE_BALANCE = 0
WHERE ACCOUNT_ID = TEMP_CURSOR.ACCOUNT_ID;
END IF;
END LOOP;
END;

--COMMIT; -- 提交所有数据,把这个去掉,可以查看是否是自己想要的效果,再决定是否提交