Oracle SQl从保存在表字段中的sql语句中填充过程中的游标-Java 学习之路

我们希望在从表中的select语句填充的过程中填充游标 .

我们创建了一个名为stored_sql_statments的表，其中包含2列Created_date和Sql_statement . 在该表中，我们将插入一个select语句，该语句根据客户端的需要从数据库中选择其他数据 . 示例：插入到stored_sql_statments（Created_date，Sql_statement）值（'2/1/2011'，'选择Client_idn，来自cool_table的something_neat，其中animal ='dog''）

然后在这个过程中，我们有一堆代码可以完成它需要做的事情，它永远不会改变，但是我们在游标中的select语句会定期更改 . 我们总是需要返回2个字段，但select语句的其余部分会更改 .

所以现在我们需要在过程中使用select语句填充表格中的游标 . 如果它只返回1行我们有：声明x varchar2（600）; rec1号码（10）; rec2 varchar2（15）;开始执行立即从stored_sql_statments中选择Sql_statement为x，其中created_date ='2/1/2011';执行立即x到rec1，rec2; ...

这是有效的，但我们不需要它进入我需要它进入游标的2个变量 . 真正的select语句（上面的代码只是我们需要做的一个简单例子）带回了数千条记录，因此我们需要使用游标 .

希望这一切都有意义

所以，如果有人知道如何做到这一点，将不胜感激 .

2 回答

你想尝试dynamically populate a ref cursor吗？如果这样做并不难：

set serveroutput on
declare


 sql1 varchar2(500);
 sql2 varchar2(500);
 procedure runProcess(sqlstatement IN varchar2)
 AS
     refcrs sys_refcursor;
     DTE DATE;
     LEVELB NUMBER;
 BEGIN
  dbms_output.put_line(sqlstatement);
     open refcrs for
      sqlstatement; -- use 'using' to bind those variables
        loop
          fetch refcrs into DTE, LEVELB;
          exit when refcrs%notfound;
          dbms_output.put_line(TO_CHAR(DTE,'MMDDYYYY') || '/' || LEVELB);
        end loop;     
    CLOSE REFCRS;     
 END runProcess;

begin
    sql1 := 'select (sysdate - level) a, level b from dual connect by level < 5';
    sql2:= 'select (sysdate + level) a, -level b from dual connect by level < 5';

    runProcess(SQL1);
    runProcess(SQL2);


end ;

/**
select (sysdate - level) a, level b from dual connect by level < 5
02102011/1
02092011/2
02082011/3
02072011/4
select (sysdate + level) a, -level b from dual connect by level < 5
02122011/-1
02132011/-2
02142011/-3
02152011/-4
**/

如您所见，我在同一过程中动态执行两个不同的select语句并输出结果 .

回复于 2024-04-30T04:07:12+08:00

SQL> drop table stack_overflow;

Table dropped.

SQL> create table stack_overflow (created_date date constraint stack_overflow_pk primary key
  2      , sql_statement varchar2(4000) not null)
  3  /

Table created.

SQL> drop table source_data;

Table dropped.

SQL> create table source_data (vc varchar2(10) null
  2      , n number);

Table created.

SQL> insert into source_data values ('a', 100);

1 row created.

SQL> insert into source_data values ('a', 0);

1 row created.

SQL> insert into source_data values ('b', 50);

1 row created.

SQL> insert into source_data values ('c', null);

1 row created.

SQL> insert into stack_overflow values (sysdate - 3/24, 'select vc, sum(n)
  2      from source_data
  3      group by vc
  4      order by vc asc');

1 row created.

SQL> insert into stack_overflow values (sysdate - 2/24 , 'select vc, avg(n)
  2      from source_data
  3      group by vc
  4      order by vc desc');

1 row created.

SQL> insert into stack_overflow values (sysdate - 1/24 , 'select vc, count(*)
  2      from source_data
  3      group by vc');

1 row created.

SQL> insert into stack_overflow values (sysdate, 'select vc, count(n)
  2      from source_data
  3      group by vc');

1 row created.

SQL> commit;

Commit complete.

SQL> declare
  2      type stack_overflow_type is record (col_1 varchar2(10), col_2 number);
  3      type stack_overflow_cur_type is ref cursor return stack_overflow_type;
  4      cursor sql_statement_cur is select sql_statement
  5          from stack_overflow
  6          order by created_date;
  7      --
  8      function get_cursor_by_date (i_created_date in date) return stack_overflow_cur_type is
  9          l_return_cur sys_refcursor; -- stack_overflow_cur_type;
 10          l_sql_statement stack_overflow.sql_statement%TYPE;
 11      begin
 12          select sql_statement into l_sql_statement
 13          from stack_overflow
 14          where created_date = i_created_date;
 15          --
 16          open l_return_cur for l_sql_statement;
 17          return l_return_cur;
 18      end get_cursor_by_date;
 19      --
 20      procedure process_and_close_cursor (i_cursor in stack_overflow_cur_type) is
 21          l_current_rec stack_overflow_type;
 22      begin
 23          loop
 24              fetch i_cursor into l_current_rec;
 25              exit when i_cursor%NOTFOUND;
 26              dbms_output.put_line('col_1: ' || l_current_rec.col_1
 27                  || ' col_2: ' || to_number(l_current_rec.col_2));
 28          end loop;
 29          --
 30          close i_cursor;
 31      end;
 32      --
 33  begin
 34      for l_row in (select created_date
 35          from stack_overflow
 36          order by created_date)
 37      loop
 38          dbms_output.put_line('Processing the SQL statement created on: '
 39             || to_char(l_row.created_date, 'YYYY-MM-DD HH24:Mi:SS'));
 40          --
 41          process_and_close_cursor(get_cursor_by_date(l_row.created_date));
 42          --
 43          dbms_output.new_line;
 44      end loop;
 45  end;
 46  /
Processing the SQL statement created on: 2011-02-11 10:01:16
col_1: a col_2: 100
col_1: b col_2: 50
col_1: c col_2:

Processing the SQL statement created on: 2011-02-11 11:01:16
col_1: c col_2:
col_1: b col_2: 50
col_1: a col_2: 50

Processing the SQL statement created on: 2011-02-11 12:01:17
col_1: a col_2: 2
col_1: b col_2: 1
col_1: c col_2: 1

Processing the SQL statement created on: 2011-02-11 13:01:17
col_1: a col_2: 2
col_1: b col_2: 1
col_1: c col_2: 0


PL/SQL procedure successfully completed.

回复于 2024-04-30T04:07:12+08:00

Oracle SQl从保存在表字段中的sql语句中填充过程中的游标

2 回答

相关问题