试图将查询中的单个值分配给变量......有什么错误呢?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (53)

我试图将变量(v_flag_id)设置为查询的结果。我一直在网上查看示例,看起来我的格式/语法是正确的。我究竟做错了什么?提前致谢。

create or replace PROCEDURE RUN_AGG
is
declare
    v_Flag_id Number := select flag_id from flag where flag_tx = 'Processed / Calculated';

CURSOR hours IS
  SELECT distinct(HR) as RHR
  , submission_value_id
  from (
  select  
      v.DATA_DATE,
      v.HR,
      sv.submission_value_id
   from value v
   inner join submission_value sv on sv.value_id = v.value_id
   where sv.SUBMISSION_VALUE_ID NOT IN (
      SELECT SUBMISSION_VALUE_ID FROM VALUE_FLAG WHERE VALUE_FLAG.FLAG_ID = v_Flag_id 
   );
BEGIN
OPEN hours;
 LOOP

FETCH hours into l_hr;
EXIT WHEN hours%NOTFOUND;
  AGG_HOURLY_REG_FINAL(l_hr.RHR);
END LOOP;
CLOSE hours;
END RUN_AGG;

我收到的错误如下:

Error(6,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one 
of the following:     begin function pragma procedure subtype type <an 
identifier>    <a double-quoted delimited-identifier> current cursor delete    
 exists prior external language 
提问于
用户回答回答于

使用以下内容:

CREATE OR REPLACE PROCEDURE RUN_AGG IS
 l_rhr       VARCHAR2 (100);
 l_sub_vl_id VARCHAR2 (100);

 CURSOR hours is
  SELECT distinct (HR) as RHR, submission_value_id
    FROM (SELECT v.DATA_DATE, v.HR, sv.submission_value_id
            FROM value_ v
           INNER JOIN submission_value sv
              ON (sv.value_id = v.value_id)
           WHERE sv.SUBMISSION_VALUE_ID NOT IN
                 (SELECT SUBMISSION_VALUE_ID
                    FROM VALUE_FLAG
                   WHERE VALUE_FLAG.FLAG_ID in
                         (SELECT flag_id
                            FROM flag
                           WHERE flag_tx = 'Processed / Calculated')));
BEGIN
 OPEN hours;
 LOOP
    FETCH hours INTO l_rhr, l_sub_vl_id;
  EXIT WHEN hours%NOTFOUND;
  AGG_HOURLY_REG_FINAL(l_rhr);
 END LOOP;
 CLOSE hours;
END RUN_AGG;
  • 去掉 declare
  • select flag_id into v_Flag_id from flag where flag_tx = 'Processed / Calculated';在小时光标的选择中取sql。所以,删除v_Flag_id变量。
  • 返回两个变量两列l_rhrl_sub_vl_id

扫码关注云+社区

领取腾讯云代金券