plsql 块一个小例子(循环更新,游标使用)

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;
/



 
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值