http://community.csdn.net/Expert/TopicView3.asp?id=5672051 sql server 中 CREATE Procedure bb as SELECT * FROM users return GOCREATE Procedure aa (@userID int) AS SELECT * FROM users WHERE userID=@userID return GOoracle 中 //在oracle 中返回结果集需要使用动态游标create or replace package PKG_Test is type ResultData is ref cursor; procedure bb(Rst out ResultData); procedure aa( auserid in int,Rst out ResultData); end PKG_Test; create or replace package body PKG_Test is procedure bb(Rst out ResultData) is begin open Rst for select * from users; end; procedure aa( auserid in int,Rst out ResultData) is begin open Rst for select * from users where userid=auserid; end; end PKG_Test;
先建个package,留着后面用 Create package x_ref_cursor as type t_RefCur is ref cursor; type charTab is table of varchar2(10000) index by binary_integer; type charField is table of varchar2(100) index by binary_integer; type T_DATE is table of DATE index by binary_integer; end x_ref_cursor;然后: CREATE PROCEDURE PROCEDURE_NAME ( test IN INT DEFAULT NULL p_cursor1 out x_ref_cursor.t_RefCur, p_cursor2 out x_ref_cursor.t_RefCur ) AS BEGIN open p_cursor1 for 'SELECT * FROM table1 '; open p_cursor2 for 'SELECT * FROM table2 where aaa=test '; END;用dataset直接取就好了
sql server 中
CREATE Procedure bb as
SELECT * FROM users
return
GOCREATE Procedure aa (@userID int) AS
SELECT * FROM users WHERE userID=@userID
return
GOoracle 中
//在oracle 中返回结果集需要使用动态游标create or replace package PKG_Test
is
type ResultData is ref cursor;
procedure bb(Rst out ResultData);
procedure aa( auserid in int,Rst out ResultData);
end PKG_Test;
create or replace package body PKG_Test
is
procedure bb(Rst out ResultData)
is
begin
open Rst for select * from users;
end;
procedure aa( auserid in int,Rst out ResultData)
is
begin
open Rst for select * from users where userid=auserid;
end;
end PKG_Test;
Create package x_ref_cursor as
type t_RefCur is ref cursor; type charTab is table of varchar2(10000)
index by binary_integer; type charField is table of varchar2(100)
index by binary_integer; type T_DATE is table of DATE
index by binary_integer;
end x_ref_cursor;然后:
CREATE PROCEDURE PROCEDURE_NAME
(
test IN INT DEFAULT NULL
p_cursor1 out x_ref_cursor.t_RefCur,
p_cursor2 out x_ref_cursor.t_RefCur
)
AS
BEGIN
open p_cursor1 for 'SELECT * FROM table1 ';
open p_cursor2 for 'SELECT * FROM table2 where aaa=test ';
END;用dataset直接取就好了