SQL> set serveroutput on; SQL> SQL> declare 2 a int; 3 b int; 4 begin 5 a:=1; 6 dbms_output.put_line(a); 7 select a into b from dual; 8 dbms_output.put_line(b); 9 end; 10 /1 1PL/SQL procedure successfully completed
Oracle不支持存储过程里面直接返回查询类似SQL的create procedure test as begin select * from table end在oracle里面行不通。 用返回游标的方式?
那我掉用Oracle存储过程,返回值怎么取? DECLARE re number; BEGIN re:=fun(); commit; END; -------------------- 不要输出,直接取re值
SQL> create or replace function fntest 2 return nvarchar2 3 is 4 ename nvarchar2(30); 5 begin 6 select first_name into ename from hr.employees where rownum = 1; 7 return ename; 8 end; 9 /Function createdSQL> set serveroutput on; SQL> SQL> declare 2 ename nvarchar2(30); 3 begin 4 select fntest into ename from dual; 5 dbms_output.put_line(ename); 6 end; 7 /StevenPL/SQL procedure successfully completed
SQL> set serveroutput on;
SQL>
SQL> declare
2 a int;
3 b int;
4 begin
5 a:=1;
6 dbms_output.put_line(a);
7 select a into b from dual;
8 dbms_output.put_line(b);
9 end;
10 /1
1PL/SQL procedure successfully completed
as
begin
select * from table
end在oracle里面行不通。
用返回游标的方式?
DECLARE re number;
BEGIN
re:=fun();
commit;
END;
--------------------
不要输出,直接取re值
SQL> create or replace function fntest
2 return nvarchar2
3 is
4 ename nvarchar2(30);
5 begin
6 select first_name into ename from hr.employees where rownum = 1;
7 return ename;
8 end;
9 /Function createdSQL> set serveroutput on;
SQL>
SQL> declare
2 ename nvarchar2(30);
3 begin
4 select fntest into ename from dual;
5 dbms_output.put_line(ename);
6 end;
7 /StevenPL/SQL procedure successfully completed