在返回多行的SQL Plus中使用绑定变量

Question

这是一个愚蠢的问题，但我似乎无法解决。我有一个查询在OCI程序中造成了麻烦，所以我想在SQL*Plus中手动运行它，以检查是否有任何差异。这是一个查询：

select e.label as doc_name,
                       e.url,
                       i.item_id,
                       'multi' as form_type
                from cr_items i, cr_extlinks e
                where i.parent_id = :comment_id
                and e.extlink_id = i.item_id
               UNION
                select null as doc_name,
                       utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1))  as url,
                       r.item_id,
                       'single' as form_type
                from cr_revisions r
                where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual);
end;

我想将comment_id绑定到值3052753，所以我做了以下操作：

    DECLARE
     comment_id number := 3052753;
    BEGIN
    select e.label  ,
                           e.url,
                           i.item_id,
                           'multi'  
                    from cr_items i, cr_extlinks e
                    where i.parent_id = :comment_id
                    and e.extlink_id = i.item_id
                   UNION
                    select null  ,
                           utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1))  as url,
                           r.item_id,
                           'single'  
                    from cr_revisions r
                    where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual);
    END;
/

它给出了这个错误：

ORA-06550: line 4, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

现在，我已经很不高兴了，因为我不想从根本上改变这个查询，但无论如何，我还是勇往直前，想出了这个(INTO和UNION配合得不太顺利(：

DECLARE
 comment_id number := 3052753;
 x_label VARCHAR2(50);
 x_url VARCHAR2(500);
 x_item number;
 x_thing VARCHAR2(50);
BEGIN
select label, url, item_id, thing into x_label, x_url, x_item, x_thing from (
select e.label  ,
                       e.url,
                       i.item_id,
                       'multi' as thing  
                from cr_items i, cr_extlinks e
                where i.parent_id = :comment_id
                and e.extlink_id = i.item_id
               UNION
                select null  ,
                       utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1))  as url,
                       r.item_id,
                       'single' as thing 
                from cr_revisions r
                where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual)) ;
END;
/

但现在，当然，因为我返回了超过1行，我得到了完全可预测的

ORA-01422: exact fetch returns more than requested number of rows

现在，我可以继续使用游标等，但我的小查询越来越偏离了原来的样子。我想做的只是检查查询是否在comment_id值的情况下正常运行。当然，我可以将comment_id硬编码到查询中，这很好。但它在OCI中也能很好地工作，所以我将在SQL*PLus中重现我在OCI代码中看到的绑定变量的问题。但是，为什么在SQL*Plus中要做到这一点如此困难呢？我错过了什么显而易见的东西吗？

数据库是运行在Red Hat Enterprise Linux ES release 4(Nahant Update 8(上的Oracle 10.2.0.1.0-64位

类似于@Glenn的方法，但您可以在SQL*Plus中声明绑定变量，并在普通SQL查询中使用它。首先用var[iable]命令声明：

variable comment_id number;

然后用exec[ute]命令设置它，它本质上是一个匿名块：

execute :comment_id := 3052753;

然后使用:comment_id引用运行原始查询，而不使用BEGIN或END:

select e.label as doc_name,
                       e.url,
                       i.item_id,
                       'multi' as form_type
                from cr_items i, cr_extlinks e
                where i.parent_id = :comment_id
                and e.extlink_id = i.item_id
               UNION
                select null as doc_name,
                       utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1))  as url,
                       r.item_id,
                       'single' as form_type
                from cr_revisions r
                where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual);

除了个人偏好之外，我认为这两种方法在功能上没有太大区别，而且两者都适用于SQL Developer(当作为脚本运行时(。当运行从已经使用:绑定形式的Pro*C文件复制的SQL时，我发现这更容易，纯粹是因为您根本不必修改代码。

---

顺便说一句，你可以写：

where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual)

没有额外的select，如：

where r.revision_id = content_item.get_latest_revision(:comment_id)

与其创建匿名块，不如在sqlplus:中定义一个环境变量

DEFINE comment_id = 3052753

然后参考&查询中的comment_id。

select e.label as doc_name,
                       e.url,
                       i.item_id,
                       'multi' as form_type
                from cr_items i, cr_extlinks e
                where i.parent_id = &comment_id
                and e.extlink_id = i.item_id
               UNION
                select null as doc_name,
                       utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1))  as url,
                       r.item_id,
                       'single' as form_type
                from cr_revisions r
                where r.revision_id = ( select content_item.get_latest_revision(&comment_id) from dual);