oracle - 在返回多行的 SQL Plus 中使用绑定(bind)变量？

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 绑定(bind)到值 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 代码中看到的绑定(bind)变量问题。但是，为什么在 SQL*Plus 中执行此操作如此困难？我错过了一些非常明显的事情吗？

数据库是 Oracle 10.2.0.1.0 - 在 Red Hat Enterprise Linux ES 版本 4(Nahant 更新 8)上运行的 64 位

Answer 1

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

variable comment_id number;

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

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 中使用(当作为脚本运行时)。当运行从已经使用 : 绑定(bind)形式的 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)