时间戳

首页 > 解决方案 > 尝试创建 proc 但出现错误

oracle - 尝试创建 proc 但出现错误

问题描述

我正在尝试创建一个返回连接值但出现错误的过程。

我创建了一个过程名称“GetMultiVal”，并在其中创建了一个游标“stage_val”用于获取值。

CREATE OR REPLACE PROCEDURE GetMultiVal
(v_var_value out varchar2,v_hr_stk_out out Sys_Refcursor)
is
var_value varchar2(200);
x varchar2   (200);
cursor age_val is
 SELECT AGE_CD,
 decode(AGE_CD,'07','CLNE(RW','05','CS ','4A',NULL,AGE_DESC) AGE_DESC ,
 AGE_SEQ
 FROM DBPROD.PROD_AGE_MST
 WHERE AGE_SEQ < 15
 AND AGE_CD NOT IN ('6A','05')
 ORDER BY 3;
BEGIN
OPEN v_hr_stk_out For
    FOR i IN age_val LOOP
      SELECT To_Char(Round(NVL(SUM(NVL(ROD_WT, 0)), 0), 0))
      INTO X
      FROM DBPROD.Prod_age_fgs_cur
    WHERE WF_DATE BETWEEN sysdate AND  sysdate+1
       AND WF_AGE_CD=i.AGE_CD;
    var_value :=var_value || X||'~';
       END LOOP;
    select var_value as v_var_value from dual;
end;
end;

预期：应该在 v_var_value 变量中获得连接值。

实际：收到错误 PROCEDURE DBPROD.GETMULTIVAL 的编译错误

错误：PLS-00103：在预期以下情况之一时遇到符号“FOR”：

标签： oracleplsql

解决方案

you don't Need to open a Cursor if you use it in a for Loop.

try this one:

CREATE OR REPLACE PROCEDURE getmultival(v_var_value  out varchar2
                                       ,v_hr_stk_out out Sys_Refcursor) is
  var_value varchar2(200);
  x         varchar2(200);
  cursor age_val is
    SELECT age_cd
          ,decode(age_cd, '07', 'CLNE(RW', '05', 'CS ', '4A', NULL, age_desc) age_desc
          ,age_seq
      FROM dbprod.prod_age_mst
     WHERE age_seq < 15
       AND age_cd NOT IN ('6A', '05')
     ORDER BY 3;
BEGIN

  open v_hr_stk_out  for
   SELECT age_cd
          ,decode(age_cd, '07', 'CLNE(RW', '05', 'CS ', '4A', NULL, age_desc) age_desc
          ,age_seq
      FROM dbprod.prod_age_mst
     WHERE age_seq < 15
       AND age_cd NOT IN ('6A', '05')
     ORDER BY 3;



  FOR i IN age_val LOOP
    SELECT to_char(round(nvl(SUM(nvl(rod_wt, 0)), 0), 0))
      INTO x
      FROM dbprod.prod_age_fgs_cur
     WHERE wf_date BETWEEN sysdate AND sysdate + 1
       AND wf_age_cd = i.age_cd;
    var_value := var_value || x || '~';
  END LOOP; 

  v_var_value := var_value ;

end;

推荐阅读