• <small id='PWNMY'></small><noframes id='PWNMY'>

    <tfoot id='PWNMY'></tfoot>
    <i id='PWNMY'><tr id='PWNMY'><dt id='PWNMY'><q id='PWNMY'><span id='PWNMY'><b id='PWNMY'><form id='PWNMY'><ins id='PWNMY'></ins><ul id='PWNMY'></ul><sub id='PWNMY'></sub></form><legend id='PWNMY'></legend><bdo id='PWNMY'><pre id='PWNMY'><center id='PWNMY'></center></pre></bdo></b><th id='PWNMY'></th></span></q></dt></tr></i><div id='PWNMY'><tfoot id='PWNMY'></tfoot><dl id='PWNMY'><fieldset id='PWNMY'></fieldset></dl></div>
    <legend id='PWNMY'><style id='PWNMY'><dir id='PWNMY'><q id='PWNMY'></q></dir></style></legend>
        <bdo id='PWNMY'></bdo><ul id='PWNMY'></ul>

        如何从执行动态 SQL 的 Oracle PL/SQL 匿名块返回结果集/游标?

        时间:2023-09-18
        <tfoot id='mDjGM'></tfoot>

        <small id='mDjGM'></small><noframes id='mDjGM'>

          <bdo id='mDjGM'></bdo><ul id='mDjGM'></ul>

        • <legend id='mDjGM'><style id='mDjGM'><dir id='mDjGM'><q id='mDjGM'></q></dir></style></legend>
              <i id='mDjGM'><tr id='mDjGM'><dt id='mDjGM'><q id='mDjGM'><span id='mDjGM'><b id='mDjGM'><form id='mDjGM'><ins id='mDjGM'></ins><ul id='mDjGM'></ul><sub id='mDjGM'></sub></form><legend id='mDjGM'></legend><bdo id='mDjGM'><pre id='mDjGM'><center id='mDjGM'></center></pre></bdo></b><th id='mDjGM'></th></span></q></dt></tr></i><div id='mDjGM'><tfoot id='mDjGM'></tfoot><dl id='mDjGM'><fieldset id='mDjGM'></fieldset></dl></div>

                    <tbody id='mDjGM'></tbody>

                  本文介绍了如何从执行动态 SQL 的 Oracle PL/SQL 匿名块返回结果集/游标?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我有这张桌子:

                  ALLITEMS
                  ---------------
                  ItemId  | Areas
                  ---------------
                  1       | EAST
                  2       | EAST
                  3       | SOUTH
                  4       | WEST
                  

                  DDL:

                  drop table allitems;
                  
                  Create Table Allitems(ItemId Int,areas Varchar2(20));
                  Insert Into Allitems(Itemid,Areas) Values(1,'east');
                  Insert Into Allitems(ItemId,areas) Values(2,'east');
                  insert into allitems(ItemId,areas) values(3,'south');
                  insert into allitems(ItemId,areas) values(4,'east');
                  

                  在 MSSQL 中,我可以从动态 SQL 中获取游标:

                  In MSSQL, to get a cursor from a dynamic SQL I can do:

                  DECLARE @v_sqlStatement VARCHAR(2000);
                  SET @v_Sqlstatement = 'SELECT * FROM ALLITEMS';
                  EXEC (@v_sqlStatement); --returns a resultset/cursor, just like calling SELECT 
                  

                  在 Oracle 中,我需要使用 PL/SQL 块:

                  In Oracle, I need to use a PL/SQL Block:

                  SET AUTOPRINT ON;
                  DECLARE
                   V_Sqlstatement Varchar2(2000);
                   outputData SYS_REFCURSOR;
                  BEGIN
                   V_Sqlstatement := 'SELECT * FROM ALLITEMS';
                   OPEN outputData for v_Sqlstatement; 
                  End;
                  --result is : anonymous block completed
                  

                  **但我得到的只是

                  匿名区块已完成".

                  如何让它返回光标?

                  (我知道如果我做 AUTOPRINT,它会打印出 REFCURSOR 中的信息(上面的代码中没有打印,但那是另一个问题))

                  我将从代码(ODBC、C++)中调用这个动态 SQL,我需要它来返回一个游标.怎么样?

                  I will be calling this Dynamic SQL from code (ODBC,C++), and I need it to return a cursor. How?

                  推荐答案

                  您可以编写一个 PL/SQL 函数来返回该游标(或者,如果您有更多与此相关的代码,您可以将该函数放入一个包中):

                  You can write a PL/SQL function to return that cursor (or you could put that function in a package if you have more code related to this):

                  CREATE OR REPLACE FUNCTION get_allitems
                    RETURN SYS_REFCURSOR
                  AS
                    my_cursor SYS_REFCURSOR;
                  BEGIN
                    OPEN my_cursor FOR SELECT * FROM allitems;
                    RETURN my_cursor;
                  END get_allitems;
                  

                  这将返回光标.

                  如果可能,请确保不要将 SELECT-String 放入 PL/SQL 中的引号中.把它放在字符串中意味着在编译时无法检查它,并且每次使用时都必须对其进行解析.

                  Make sure not to put your SELECT-String into quotes in PL/SQL when possible. Putting it in strings means that it can not be checked at compile time, and that it has to be parsed whenever you use it.

                  如果您确实需要使用动态 SQL,您可以将查询放在单引号中:

                  If you really need to use dynamic SQL you can put your query in single quotes:

                    OPEN my_cursor FOR 'SELECT * FROM allitems';
                  

                  每当调用函数时都必须解析此字符串,这通常会变慢并在运行时隐藏查询中的错误.

                  This string has to be parsed whenever the function is called, which will usually be slower and hides errors in your query until runtime.

                  确保尽可能使用绑定变量以避免硬解析:

                  Make sure to use bind-variables where possible to avoid hard parses:

                    OPEN my_cursor FOR 'SELECT * FROM allitems WHERE id = :id' USING my_id;
                  

                  这篇关于如何从执行动态 SQL 的 Oracle PL/SQL 匿名块返回结果集/游标?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:以毫秒为单位的 Oracle 中的日期字符串 下一篇:什么会导致 Oracle ROWID 更改?

                  相关文章

                    • <bdo id='kPVCF'></bdo><ul id='kPVCF'></ul>
                  1. <legend id='kPVCF'><style id='kPVCF'><dir id='kPVCF'><q id='kPVCF'></q></dir></style></legend>
                    <tfoot id='kPVCF'></tfoot>
                    <i id='kPVCF'><tr id='kPVCF'><dt id='kPVCF'><q id='kPVCF'><span id='kPVCF'><b id='kPVCF'><form id='kPVCF'><ins id='kPVCF'></ins><ul id='kPVCF'></ul><sub id='kPVCF'></sub></form><legend id='kPVCF'></legend><bdo id='kPVCF'><pre id='kPVCF'><center id='kPVCF'></center></pre></bdo></b><th id='kPVCF'></th></span></q></dt></tr></i><div id='kPVCF'><tfoot id='kPVCF'></tfoot><dl id='kPVCF'><fieldset id='kPVCF'></fieldset></dl></div>

                    <small id='kPVCF'></small><noframes id='kPVCF'>