正在尝试将查询中的单个值赋给变量...我做错了什么？
正在尝试将查询中的单个值赋给变量...我做错了什么？
提问于 2018-06-08 03:24:52
我正在尝试将一个变量(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 回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-08 03:41:11
使用以下内容：
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
- take
- 游标的select。因此，删除两列的两个变量
l_rhr和l_sub_vl_id. - I将表
value的名称替换为value_，因为它是oracle的保留关键字。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50748816
复制相关文章
相似问题