在SQL Plus中使用绑定变量并返回多行? [英] Using bind variables in SQL Plus with more than one row returned?

问题描述

这是一个愚蠢的问题，但我似乎无法解决.我有一个查询会在OCI程序中引起麻烦，因此我想在SQL * Plus中手动运行它以检查是否有任何区别.这是查询:

This is a stupid problem, but I can't seem to get around it. I have a query that's causing trouble in an OCI program, so I want to run it manually in SQL*Plus to check if there is any difference there. This is the query:

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，所以我执行了以下操作:

I wanted to bind the comment_id to the value 3052753, so I did the following:

    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并不太顺利):

Now, I'm already unhappy because I don't want to be radically changing this query, but anyway I forge ahead and come up with this (INTO and UNIONs don't go together so smoothly):

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行，所以我完全可以预测

but now, of course because I'm returning more than 1 row, I get the utterly predictable

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

现在，我可以继续使用游标等了，但是我的小查询越来越偏离了它的原始自我.我要做的就是检查查询是否以该comment_id的值正常运行.当然，我可以将comment_id硬编码到查询中，并且工作正常.但是它在OCI中也能正常工作，因此我将在SQL * PL中重现问题，并在OCI代码中看到绑定变量的问题.但是，为什么要在SQL * Plus中做到这一点却如此艰难呢?我错过了真正明显的东西吗?

Now, I can go ahead and start using cursors etc, but my little query is getting more and more distorted from its original self. All I wanted to do was to check if the query ran ok with that value of comment_id. Of course, I can just hardcode the comment_id into the query, and that works fine. But it also works fine in the OCI so I'm to reproduce in SQL*PLus the issue with bind variables that I'm seeing in the OCI code. But why is it such a struggle to do this in SQL*Plus? Have I missed something really obvious?

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

Database is Oracle 10.2.0.1.0 - 64bit running on Red Hat Enterprise Linux ES release 4 (Nahant Update 8)

推荐答案

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

Similar to @Glenn's approach, but you can declare a bind variable in SQL*Plus and use it in a plain SQL query. First declare it with the var[iable] command:

variable comment_id number;

然后使用 exec[ute] 进行设置命令，本质上是一个匿名块:

Then set it with the exec[ute] command, which is essentially an anonymous block:

execute :comment_id := 3052753;

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

Then run your original query with the :comment_id references, and no BEGIN or 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时，这很容易，这纯粹是因为您根本不需要修改代码.

I don't think there's much functional difference between the two approaches beyond personal preference, and both also work in SQL Developer (when run as a script). I find this easier when running SQL copied from a Pro*C file which already uses the : bind form, purely because you don't have to modify the code at all.

顺便说一句，您可以编写:

Incidentally, you can write:

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)

这篇关于在SQL Plus中使用绑定变量并返回多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！