Oracle中创建SELECT查询存储过程报PLS-00428错误如何解决

PLS-00428是Oracle PL/SQL的标准编译错误，核心规则是：在PL/SQL存储过程/函数的执行块中，不允许直接执行无目标的SELECT语句，所有查询返回的结果必须指定存储位置，也就是报错提示要求的INTO子句。你当前的代码直接在BEGIN和END之间写了多行查询语句，没有指定结果输出/存储方式，因此触发报错。

根据你使用这个存储过程的目的，有三种常见的解决方式：

场景1：需要存储过程返回查询结果集供外部调用（最常用场景） #

可以给存储过程增加一个SYS_REFCURSOR类型的输出参数，将查询结果绑定到游标上返回，修改后的代码如下：

Create or replace procedure Sanction_test(
    -- 增加输出游标参数
    p_result OUT SYS_REFCURSOR
)
as 
begin
-- 将查询结果打开到输出游标中
open p_result for
select 
ROWNUM, 
cadid, CADRE,PAYSCALE, POST, PostId, deptid, Dept,grp,gazetted, Per_Normal_Cur, Per_Encd_Cur, Temp_Normal_Cur, Temp_Encd_Cur,Sup_Cur,(Per_Normal_Cur + Per_Encd_Cur + Temp_Normal_Cur + Temp_Encd_Cur + Sup_Cur)as TOTAL_CUR, Per_Normal_Up,Per_Encd_Up,Temp_Normal_Up,Temp_Encd_Up ,Sup_Up,(Per_Normal_Up + Per_Encd_Up + Temp_Normal_Up + Temp_Encd_Up + Sup_Up) as TOTAL_UPCOM, Per_Normal_Delta,Per_Encd_Delta,Temp_Normal_Delta,Temp_Encd_Delta,Sup_Delta ,(Per_Normal_Delta + Per_Encd_Delta + Temp_Normal_Delta + Temp_Encd_Delta + Sup_Delta) TOTAL_DELTA from(SELECT rownum , (SELECT CADRE_NAME FROM REF_CADRE c WHERE C.CADRE_ID = P.CADRE_ID) CADRE ,FD_PS_STR PAYSCALE  ,(SELECT POST_NAME FROM REF_POST WHERE POST_ID = P.POST_ID) POST,
(SELECT COUNT(1) FROM ref_post_code_details  WHERE    department_id = '116' and permanant_flag = 'Y' and witheffectivedate < '01-04-22' and POST_ID = P.POST_ID  ) as Per_Normal_Cur  , 
(SELECT COUNT(1) FROM ref_post_code_details  WHERE  department_id = 116  and Encadre_from_flag = 'Y' and witheffectivedate < '01-04-22' and POST_ID = P.POST_ID  ) as Per_Encd_Cur,
(SELECT COUNT(1) FROM ref_post_code_details  WHERE department_id =116 and temporary_flag = 'Y'  and witheffectivedate < '01-04-22' and POST_ID = P.POST_ID  ) as Temp_Normal_Cur, 
(SELECT COUNT(1) FROM ref_post_code_details  WHERE   department_id = 116 and temporary_enc_flag = 'Y' and witheffectivedate < '01-04-22' and POST_ID = P.POST_ID  ) as Temp_Encd_Cur,
(SELECT COUNT(1) FROM ref_post_code_details  WHERE department_id = 116 and supernumerary_flag = 'Y'and witheffectivedate < '01-04-22' and POST_ID = P.POST_ID  ) as Sup_Cur,
(SELECT COUNT(1) FROM ref_post_code_details  WHERE department_id = 116  and permanant_flag = 'Y' and POST_ID = P.POST_ID  ) as Per_Normal_Up,
(SELECT COUNT(1) FROM ref_post_code_details  WHERE department_id = 116  and Encadre_from_flag = 'Y' and POST_ID = P.POST_ID  ) as Per_Encd_Up,
(SELECT COUNT(1) FROM ref_post_code_details  WHERE department_id = 116  and temporary_flag = 'Y' and POST_ID = P.POST_ID  ) as Temp_Normal_Up,
(SELECT COUNT(1) FROM ref_post_code_details  WHERE department_id = 116 and temporary_enc_flag = 'Y' and POST_ID = P.POST_ID  ) as Temp_Encd_Up,
(SELECT COUNT(1) FROM ref_post_code_details  WHERE department_id = 116  and supernumerary_flag = 'Y' and POST_ID = P.POST_ID  ) as Sup_Up,  
(SELECT NVL(sum(per_normal), 0)  nrml FROM test_delta1 WHERE  dept_code =116  and POST_ID = P.POST_ID  ) as Per_Normal_Delta, 
(SELECT NVL(sum(per_ENCADRED), 0)  nrml FROM test_delta1 WHERE  dept_code = 116 and POST_ID = P.POST_ID  ) as Per_Encd_Delta, 
(SELECT NVL(sum(TEMP_NORMAL), 0)  nrml FROM test_delta1 WHERE  dept_code = 116  and POST_ID = P.POST_ID  ) as Temp_Normal_Delta, 
(SELECT NVL(sum(TEMP_ENCADRED), 0)  nrml FROM test_delta1 WHERE  dept_code = 116  and POST_ID = P.POST_ID  ) as Temp_Encd_Delta, 
(SELECT NVL(sum(SUPERNUMERARY), 0)  nrml FROM test_delta1 WHERE  dept_code = 116  and POST_ID = P.POST_ID  ) as Sup_Delta,  p.cadre_id cadid, P.Post_id as PostId, p.groups grp ,
DECODE(P.GROUPS,'A','Yes','B','Yes','No') gazetted,  P.DEPARTMENT_ID deptid, D.department_name dept, P.old_dept_cd FROM REF_POST P, REF_DEPARTMENT D  
WHERE P.DEPARTMENT_ID = D.DEPARTMENT_ID    and P.DEPARTMENT_ID = 116  GROUP BY p.cadre_id,P.POST_ID,p.old_dept_cd,P.DEPARTMENT_ID,d.department_name,p.groups,FD_PS_STR,rownum ORDER BY ROWNUM)
a order by rownum;
end;
/

调用方式示例（PL/SQL环境下）：

var res refcursor;
exec Sanction_test(:res);
print res;

场景2：查询仅返回1行，需要在存储过程内部处理数据 #

可以声明和查询字段一一对应的变量，用INTO子句将结果存入变量后处理，示例结构如下：

Create or replace procedure Sanction_test
as 
-- 声明和查询字段类型匹配的变量，示例仅列部分字段，需补全所有
v_rownum number;
v_cadid number;
v_cadre varchar2(100);
-- 剩余其他字段变量依次声明
begin
select 
ROWNUM, 
cadid, CADRE,
-- 其他字段
INTO v_rownum, v_cadid, v_cadre
-- 此处放你原来的查询语句，确保仅返回1行
from ......;
-- 后续写变量处理逻辑
end;
/

注意：该方式仅适用于查询结果固定为1行的场景，返回0行或多行都会触发运行时错误。

场景3：需要在存储过程内部逐行处理多行查询结果 #

使用显式游标+循环的方式遍历查询结果，示例结构如下：

Create or replace procedure Sanction_test
as 
cursor cur_data is
-- 此处放你原来的完整查询语句
select ...... from ......;
begin
for row_data in cur_data loop
    -- 逐行处理逻辑，可通过row_data.字段名访问每行数据
    dbms_output.put_line(row_data.cadre);
end loop;
end;
/

内容的提问来源于stack exchange，提问作者Rahul Tunga