初學者在 SQL*Plus 創建存儲過程時,頻繁遇到 PLS-00428: an INTO clause is expected in this SELECT statement。本文通過逐步修正錯誤示範,講清 PL/SQL 中 SELECT 必須配合 INTO 將結果賦給變量 的強制規則。
-- 1. 帶參數、無 INTO
CREATE OR REPLACE PROCEDURE get_news(aid IN VARCHAR2, atitle IN VARCHAR2) AS
BEGIN
SELECT * FROM cf_news;
END;
-- PLS-00428
-- 2. 修正參數語法、仍無 INTO
CREATE OR REPLACE PROCEDURE get_news(aid IN VARCHAR2, atitle IN VARCHAR2) AS
BEGIN
SELECT * FROM cf_news;
END;
-- PLS-00428
-- 3. 單參數、仍無 INTO
CREATE OR REPLACE PROCEDURE get_news(aid IN VARCHAR2) AS
BEGIN
SELECT * FROM cf_news;
END;
-- PLS-00428
-- 4. 無參數、仍無 INTO
CREATE OR REPLACE PROCEDURE get_news AS
BEGIN
SELECT * FROM cf_news;
END;
-- PLS-00428
SQL> SHOW ERRORS;
PROCEDURE GET_NEWS 錯誤:
LINE/COL ERROR
-------- -------------------------------------
4/1 PLS-00428: 在此 SELECT 語句中缺少 INTO 子句
CREATE OR REPLACE PROCEDURE get_news AS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO v_cnt FROM cf_news;
-- 此處可處理 v_cnt 邏輯
END;
/
-- Procedure created.
SELECT **強制** 使用 INTO variable_listSELECT 列表一一對應NO_DATA_FOUND,>1 行 → TOO_MANY_ROWSEXECUTE IMMEDIATE 'SELECT ...' INTO vars USING bindsCREATE OR REPLACE PROCEDURE get_news_list(p_rc OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_rc FOR SELECT * FROM cf_news;
END;
/
調用端(Java/.NET/PL/SQL)遍歷 SYS_REFCURSOR 獲取結果集。