這個 SQL 運行良好，它可以做我想做的所有事情，除了我要求它只輸出查詢結果。所以，我在它周圍包裝了一些代碼，目的是這樣做。看下一個區塊...
SELECT
'NO_PROBLEM'
FROM
DUAL
WHERE
NOT EXISTS (
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)= 'O7_DICTIONARY_ACCESSIBILITY'
AND UPPER(VALUE) != 'FALSE'
)
UNION ALL
SELECT
'PROBLEM'
FROM
DUAL
WHERE
EXISTS (
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)= 'O7_DICTIONARY_ACCESSIBILITY'
AND UPPER(VALUE) != 'FALSE'
)
這是我添加了一些包裝的地方
DECLARE
v_07_check VARCHAR2(10);
CURSOR c_07_check IS
SELECT 'NO_PROBLEM'
FROM dual
WHERE NOT EXISTS
(
SELECT upper(value)
FROM v$system_parameter
WHERE upper(name)='O7_DICTIONARY_ACCESSIBILITY'
AND upper(value) != 'FALSE' )
UNION ALL
SELECT 'PROBLEM'
FROM dual
WHERE EXISTS
(
SELECT upper(value)
FROM v$system_parameter
WHERE upper(name)='O7_DICTIONARY_ACCESSIBILITY'
AND upper(value) != 'FALSE' )
BEGIN OPEN c_07_check;
LOOP
FETCH c_07_check
INTO v_07_check;
EXIT
WHEN c_07_check%NOTFOUND;
IF v_07_check = 'PROBLEM' THEN
dbms_output.put_line('PROBLEM') )
ELSIF v_07_check = 'NO_PROBLEM' THEN
dbms_output.put_line('NO_PROBLEM')
END IF;
END LOOP;
CLOSE C_07_CHECK;
END;
/
代碼輸出的錯誤資訊是：
PLS-00103: Encountered the symbol "END" when expecting one of the following:
:= . ( % ;
The symbol ";" was substituted for "END" to continue.
ORA-06550: line 50, column 5:
PLS-00103: Encountered the symbol "CLOSE"
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
它抱怨的行是：
CLOSE c_07_check;
我已經檢查了 oracle 的檔案，但我不確定這里有什么問題。我嘗試將 close 陳述句移動到塊中的不同點，但它不能解決問題。任何人都可以就失敗的地方提出任何建議嗎？
uj5u.com熱心網友回復：
3 個位置缺少分號（在游標宣告的末尾，dbms_output.put_line呼叫后面），多余的右括號。
DECLARE
v_07_check VARCHAR2 (10);
CURSOR c_07_check IS
SELECT 'NO_PROBLEM'
FROM DUAL
WHERE NOT EXISTS
(SELECT UPPER (VALUE)
FROM v$system_parameter
WHERE UPPER (name) = 'O7_DICTIONARY_ACCESSIBILITY'
AND UPPER (VALUE) != 'FALSE')
UNION ALL
SELECT 'PROBLEM'
FROM DUAL
WHERE EXISTS
(SELECT UPPER (VALUE)
FROM v$system_parameter
WHERE UPPER (name) = 'O7_DICTIONARY_ACCESSIBILITY'
AND UPPER (VALUE) != 'FALSE');
BEGIN
OPEN c_07_check;
LOOP
FETCH c_07_check INTO v_07_check;
EXIT WHEN c_07_check%NOTFOUND;
IF v_07_check = 'PROBLEM'
THEN
DBMS_OUTPUT.put_line ('PROBLEM');
ELSIF v_07_check = 'NO_PROBLEM'
THEN
DBMS_OUTPUT.put_line ('NO_PROBLEM');
END IF;
END LOOP;
CLOSE C_07_CHECK;
END;
/
請注意，如果您使用游標回圈，您可以縮短和簡化它，例如FOR
BEGIN
FOR cur_r
IN (SELECT 'NO_PROBLEM' v_07_check
FROM DUAL
WHERE NOT EXISTS
(SELECT UPPER (VALUE)
FROM v$system_parameter
WHERE UPPER (name) = 'O7_DICTIONARY_ACCESSIBILITY'
AND UPPER (VALUE) != 'FALSE')
UNION ALL
SELECT 'PROBLEM'
FROM DUAL
WHERE EXISTS
(SELECT UPPER (VALUE)
FROM v$system_parameter
WHERE UPPER (name) = 'O7_DICTIONARY_ACCESSIBILITY'
AND UPPER (VALUE) != 'FALSE'))
LOOP
DBMS_OUTPUT.put_line (cur_r.v_07_check);
END LOOP;
END;
因為 - 這樣做 - 您不必宣告游標變數，打開游標，從中獲取，注意退出回圈并關閉游標 - Oracle 為您完成。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/435868.html