set echo off
set termout on
set serveroutput on size 10000
set lines 120
set pages 20000
set trimspool on
DECLARE
CURSOR C_DR_OPERATION_LOG
IS
SELECT
OPERATION_LOG_NO
,SYS_ENTRY_USER_INFO_CD
FROM
XXXTH_DR_OPERATION_LOG
WHERE
SCREEN_NM = '初回電子署名同意'
AND SCREEN_ID = 'addin-dr-001.0014'
AND (
ACTION = '「同意」ボタン押下'
OR ACTION LIKE '電子署名%'
)
AND CHOSA_CD IS NULL
AND CONTRACT_CD IS NULL;
V_CHOSA_CD_A XXXTH_DR_OPERATION_LOG.CHOSA_CD%TYPE;
V_CONTRACT_CD_A XXXTH_DR_OPERATION_LOG.CONTRACT_CD%TYPE;
ROWCOUNT NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('#------------------------------------ 更新を開始します。 ------------------------------------#');
FOR C_DR_OPERATION_LOG_REC IN C_DR_OPERATION_LOG LOOP
--調査コードを更新
UPDATE
XXXTH_DR_OPERATION_LOG
SET
CHOSA_CD = (SELECT CHOSA_CD FROM XXXTR_CONTRACT_DR_USER WHERE USER_INFO_CD = C_DR_OPERATION_LOG_REC.SYS_ENTRY_USER_INFO_CD)
,SYS_UPDATE_DATETIME = SYSDATE
,SYS_UPDATE_USER_INFO_CD = 'SYSTEM'
WHERE
OPERATION_LOG_NO = C_DR_OPERATION_LOG_REC.OPERATION_LOG_NO;
SELECT CHOSA_CD INTO V_CHOSA_CD_A FROM XXXTH_DR_OPERATION_LOG WHERE OPERATION_LOG_NO = C_DR_OPERATION_LOG_REC.OPERATION_LOG_NO;
--契約コードを更新
UPDATE
XXXTH_DR_OPERATION_LOG
SET
CONTRACT_CD = (SELECT CONTRACT_CD FROM XXXTR_CONTRACT_DR_USER WHERE USER_INFO_CD = C_DR_OPERATION_LOG_REC.SYS_ENTRY_USER_INFO_CD)
,SYS_UPDATE_DATETIME = SYSDATE
,SYS_UPDATE_USER_INFO_CD = 'SYSTEM'
WHERE
OPERATION_LOG_NO = C_DR_OPERATION_LOG_REC.OPERATION_LOG_NO;
ROWCOUNT := C_DR_OPERATION_LOG%ROWCOUNT;
SELECT CONTRACT_CD INTO V_CONTRACT_CD_A FROM XXXTH_DR_OPERATION_LOG WHERE OPERATION_LOG_NO = C_DR_OPERATION_LOG_REC.OPERATION_LOG_NO;
DBMS_OUTPUT.PUT_LINE('オペレーションログ番号 : ' || C_DR_OPERATION_LOG_REC.OPERATION_LOG_NO || ' 更新後 ⇒ ' ||'CHOSA_CD : ' || V_CHOSA_CD_A || ' ,' || 'CONTRACT_CD : ' || V_CONTRACT_CD_A);
END LOOP;
DBMS_OUTPUT.PUT_LINE('#------------------------------------- ' || ROWCOUNT || '行更新されました -------------------------------------#');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ロールバックしました。');
END;
/
plsql 块一个小例子(循环更新,游标使用)
最新推荐文章于 2022-11-23 14:52:27 发布