SET SERVEROUTPUT ON SIZE UNL;
DECLARE
CURSOR C_NAME_LIST IS
SELECT NAME_ID, UDFN21
FROM NAME_VIEW
WHERE NVL(UDFN21, 0) >= 18;
V_TEMP_CT NUMBER := 0;
V_RECORD_CT NUMBER := 0;
V_FILE_CONTEN CLOB := '';
V_TEMP_LOG CLOB := '';
V_PATH VARCHAR2(1000) := '&1';
V_RESORT_NAME VARCHAR2(30) := '&2';
V_COMMIT_CT NUMBER := 100;
V_LOG_TIME_FORMAT VARCHAR2(30) := 'dd-Mon-yyyy hh24:mi:ss';
V_START_TIME VARCHAR2(30);
BEGIN
V_START_TIME := TO_CHAR(SYSDATE, V_LOG_TIME_FORMAT);
DBMS_OUTPUT.PUT_LINE(V_START_TIME || ' - Start patch');
BEGIN
SELECT RESORT INTO V_RESORT_NAME from DATABASE_RESORTS WHERE DATABASE_ID = 'ORS';
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;
V_FILE_CONTEN := 'Resort Name: ' || V_RESORT_NAME || CHR(13) || CHR(10) || 'NAME_ID,UDFN21' || CHR(13) || CHR(10);
BEGIN
FOR REC IN C_NAME_LIST LOOP
UPDATE NAME_VIEW SET UDFN21 = 0 WHERE NAME_ID = REC.NAME_ID;
V_TEMP_LOG := V_TEMP_LOG || '[' || REC.NAME_ID || ',' || REC.UDFN21 || ']' || CHR(13) || CHR(10);
V_TEMP_CT := V_TEMP_CT + 1;
V_RECORD_CT := V_RECORD_CT + 1;
IF V_TEMP_CT >= V_COMMIT_CT THEN
V_TEMP_CT := 0;
V_FILE_CONTEN := V_FILE_CONTEN || V_TEMP_LOG;
V_TEMP_LOG := '';
COMMIT;
END IF;
END LOOP;
V_FILE_CONTEN := V_FILE_CONTEN || V_TEMP_LOG;
COMMIT;
V_FILE_CONTEN := V_FILE_CONTEN || ' ************** ' || V_RECORD_CT || ' records has been changed.' || ' ************** ' || CHR(13) || CHR(10);
V_FILE_CONTEN := V_FILE_CONTEN || 'End Of File';
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, V_LOG_TIME_FORMAT) || ' - End patch');
EXCEPTION WHEN OTHERS THEN
V_FILE_CONTEN := V_FILE_CONTEN || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || CHR(13) || CHR(10);
V_FILE_CONTEN := V_FILE_CONTEN || DBMS_UTILITY.FORMAT_ERROR_STACK || CHR(13) || CHR(10);
V_FILE_CONTEN := V_FILE_CONTEN || '/***************************/' || CHR(13) || CHR(10) || '/* Completed with error. */' || CHR(13) || CHR(10) || '/***************************/';
END;
V_FILE_CONTEN := 'Start time: ' || V_START_TIME || ' End Time: ' || TO_CHAR(SYSDATE, V_LOG_TIME_FORMAT) || CHR(13) || CHR(10) || V_FILE_CONTEN;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, V_LOG_TIME_FORMAT) || ' - Start export file');
DBMS_XSLPROCESSOR.CLOB2FILE(V_FILE_CONTEN, V_PATH, 'UPD_UDFN21_RESULT_' || V_RESORT_NAME || '.txt', 0);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, V_LOG_TIME_FORMAT) || ' - End of export file');
END;
/
EXIT;
uj5u.com熱心網友回復：
既有修改檔案，也有檔案抓取！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/107637.html
標籤：基礎和管理
上一篇：在使用exp匯出Oracle資料庫表時，部分欄位的默認值和非空約束沒有匯出來
下一篇：如何更好地學習oracle資料庫