在用户自定义函数中,我有一个下面的语句:
v_sql:='select mm.a,mm.b from (
select a,b from table1
union
select a,b from table2
) mm '
execute immediate v_sql请问各位大侠,这个执行可以通过吗?
我试了几次都不能通过,是不是 execute immediate不支持子语句的查询? 是不是不能用union?
v_sql:='select mm.a,mm.b from (
select a,b from table1
union
select a,b from table2
) mm '
execute immediate v_sql请问各位大侠,这个执行可以通过吗?
我试了几次都不能通过,是不是 execute immediate不支持子语句的查询? 是不是不能用union?
是因为过程中不能像单纯的sql语句那样进行查询
如果该查询语句返回有且只有1条记录,可以定义变量,execute immediate v_sql into v1,v2...
或者使用游标传出结果集
create or replace procedure proc(cur out sys_refcursor)
as
begin
open cur for 'select mm.a,mm.b from (
select a,b from table1
union
select a,b from table2
) mm ' ;
end;
declare
cur sys_refcursor;
v1 table1.a%type;
v2 table1.b%type;
begin
open cur for 'select mm.a,mm.b from (
select a,b from table1
union
select a,b from table2
) mm ' ;
loop
fetch cur into v1,v2;
exit when cur%notfound;
dbms_output.put_line(v1||'--'||v2);
end loop;
end;
楼主你想想呀
平常在SQL*PLUS里你SELECT的话直接可以把数据返回在屏幕上给你看到
可是在procedure里你去select,数据怎么返回呢?所以要通过变量来接收SELECT语句返回的值
完了在通过输出函数dbms_output.put_line来把接收到的数据打印到屏幕上
SQL> select * from table1; A B
---------- ----------
1 1
2 2SQL> select * from table2; A B
---------- ----------
3 3
4 4
SQL> create or replace procedure proc(cur out sys_refcursor)
2 as
3 begin
4 open cur for 'select mm.a,mm.b from (
5
6 select a,b from table1
7 union
8 select a,b from table2
9 ) mm ' ;
10 end;
11 /Procedure created.SQL> declare
2 cur sys_refcursor;
3 v1 table1.a%type;
4 v2 table1.b%type;
5 begin
6 open cur for 'select mm.a,mm.b from (
7
8 select a,b from table1
9 union
10 select a,b from table2
11 ) mm ' ;
12 loop
13 fetch cur into v1,v2;
14 exit when cur%notfound;
15 dbms_output.put_line(v1||'--'||v2);
16 end loop;
17 end;
18 /PL/SQL procedure successfully completed.SQL> set serveroutput on --楼主注意一定要加这句 不然看不到返回的结果的
SQL> /
1--1
2--2
3--3
4--4PL/SQL procedure successfully completed.