当前位置: 代码迷 >> SQL >> plsql 块一个小事例(循环更新,游标使用)
  详细解决方案

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

热度:127   发布时间:2016-05-05 15:04:51.0
plsql 块一个小例子(循环更新,游标使用)
set echo offset termout onset serveroutput on size 10000set lines 120set pages 20000set trimspool onDECLARE 	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;/
?
  相关解决方案