SQLPLUS：SELECT INTO 提示輸入變數值

知道為什麼這會促使我追求價值嗎？我通過批處理文件呼叫腳本：

DECLARE
  precount   NUMBER;
  nowcount   NUMBER;
BEGIN
   SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
   SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
END;
/

-- NUMBER OF MEC/MVP RECORDS
SPOOL logs\MVP_MEC_UPDATED.log APPEND
PROMPT &&CLIENTID   &&precount  &&nowcount
SPOOL OFF;

謝謝！

預設情況下，與字元 ( &) 會在執行時間之前提示輸入值。這不是您顯示變數值的方式。請嘗試以下方法。

set serveroutput on

SPOOL logs\MVP_MEC_UPDATED.log APPEND
DECLARE
  precount   NUMBER;
  nowcount   NUMBER;
BEGIN
   SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
   SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';

   dbms_output.put_line('precount: ' || precount || ',  nowcount:' || nowcount );
END;
/

SPOOL OFF

將變數傳遞給 SQL*Plus 中的腳本。比方說，腳本1.sql：

set serveroutput on feedback off verify off
define CLIENTID=&1

SPOOL logs\MVP_MEC_UPDATED.log APPEND
DECLARE
  precount   NUMBER;
  nowcount   NUMBER;
BEGIN
   SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
   SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';

   dbms_output.put_line('CLIENTID: ' || '&CLIENTID' || ', precount: ' || precount || ',  nowcount:' || nowcount );
END;
/

SPOOL OFF
exit

呼叫它：

sqlplus -s user/password @1.sql 101

其中 101 是CLIENTID. 要麼：

sqlplus -s user/password @1.sql %CLIENTID%

%CLIENTID%批處理腳本中的變數在哪裡。

引用自：https://dba.stackexchange.com/questions/132304