Oracle：使用游标的IN子句的动态查询 [英] Oracle: Dynamic query with IN clause using cursor

问题描述

有人可以帮我创建一个例子，实现一个动态查询，使用IN子句和填充结果到游标。
输入参数可以是数组或字符串连接。

Could some one help me with creating a example, to implement a dynamic query that uses IN clause and populate the results to cursor. The input parameter could be array or string concatenated.

我一直在尝试很多，但没有成功。

I have been trying a lot but no successful.

谢谢..

推荐答案

您可以基于您的数组或某些动态形成一个查询字符串。并用作OPEN CURSOR。 。

Clasic situation everyone has. You can form a Query string dynamically based on your array or sometthing. And use as OPEN CURSOR. .

  DECLARE
    v_mystring VARCHAR(50);
    v_my_ref_cursor sys_refcursor;
    in_string varchar2='''abc'',''bcd''';
    id2 varchar2(10):='123';
        myrecord tablename%rowtype;
  BEGIN

    v_mystring := 'SELECT a.*... from tablename a where name= :id2 and 
                    id in('||in_string||')';

    OPEN v_my_ref_cursor FOR v_mystring USING id2;

    LOOP
      FETCH v_my_ref_cursor INTO myrecord;
      EXIT WHEN v_my_ref_cursor%NOTFOUND;
        ..
      -- your processing
    END LOOP;
    CLOSE v_my_ref_cursor;

  END;

IN子句最多支持1000个项目。您可以随时使用表来加入。
该表可能是一个全局临时表（GTT），其数据对于特定会话是可见的。

IN clause supports maximum of 1000 items. You can always use a table to join instead. That table might be a Global Temporary Table(GTT) whose data is visible to thats particular session.

仍然可以使用嵌套表（如PL / SQL表）

Still you can use a nested table also for it(like PL/SQL table)

TABLE（）会将PL / Sql表转换为SQL可理解的表对象（实际上是一个对象）

TABLE() will convert a PL/Sql table as a SQL understandable table object(an object actually)

下面是一个简单的例子。

A simple example of it below.

CREATE TYPE pr AS OBJECT
           (pr  NUMBER);
/
CREATE TYPE prList AS TABLE OF pr;
/

declare
  myPrList prList := prList ();
  cursor lc is 
    select * 
      from (select a.*
              from yourtable a
                   TABLE(CAST(myPrList as prList)) my_list
             where 
                   a.pr = my_list.pr
             order by a.pr desc) ;
  rec lc%ROWTYPE;

BEGIN 
  /*Populate the Nested Table, with whatever collection you have */
  myPrList := prList ( pr(91),
                       pr(80));
  /*
     Sample code: for populating from your TABLE OF NUMBER type 

     FOR I IN 1..your_input_array.COUNT
     LOOP
          myPrList.EXTEND;
          myPrList(I) := pr(your_input_array(I));
     END LOOP;
  */
  open lc;
  loop 
    FETCH lc into rec;
    exit when lc%NOTFOUND; -- Your Exit WHEN condition should be checked afte FETCH iyself!
    dbms_output.put_line(rec.pr);
  end loop;
  close lc;
END;
/

这篇关于Oracle：使用游标的IN子句的动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！