SQL> create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type, 2 out_curEmp out SYS_REFCURSOR) as 3 4 begin 5 open out_curEmp for 6 SELECT * FROM emp WHERE deptno = in_deptNo ; 7 EXCEPTION 8 WHEN OTHERS THEN 9 RAISE_APPLICATION_ERROR(-20101, 10 'Error in getEmpByDept' || SQLCODE ); 12 end getEmpByDept; 13 /
CREATE OR REPLACE PROCEDURE aaaa(cur1 out sys_refcursor) IS begin open cur1 for select * from test; end;--调用 declare test_cur sys_reccursor; test_rec test%rowtype; v1 test.col%type; v2 test.col2%type; ...; begin aaaa(test_cur); fetch test_cur into test_rec while test_cur%found loop v1:=test_rec.col; v2:=test_rec.col2; ...; end loop; close test_cur; end;
--这只是调用的简列模版 declare declare test_cur sys_reccursor; test_rec test%rowtype; v1 test.col%type; v2 test.col2%type; ...; begin aaaa(test_cur); fetch test_cur into test_rec while test_cur%found loop v1:=test_rec.col; v2:=test_rec.col2; ...; fetch test_cur into test_rec; end loop; close test_cur; end;
CREATE OR REPLACE PROCEDURE aaaaaaaaaaaaaaa(cur1 out sys_refcursor) IS begin open cur1 for select * from secuser; end;--调用 declare test_cur Sys_Refcursor; test_rec test%rowtype; v1 secuser.logid%type; v2 secuser.u_name%type; --...; begin aaaaaaaaaaaaaaa(test_cur); fetch test_cur into test_rec while test_cur%found loop v1:=test_rec.logid; v2:=test_rec.u_name; --...; end loop; close test_cur; end;这好像也不能调用啊???
declare begin for i in(select col1,col2,col3 from tb) loop dbms_output.put_line(i.col1||','||i.col2||','||i.col3); end loop; end;
CREATE OR REPLACE PROCEDURE aaaaaaaaaaaaaaa(cur1 out sys_refcursor) IS begin open cur1 for select logid from secuser; end;--调用 declare test_cur sys_reccursor; test_rec secuser%rowtype; v1 secuser.logid%type; v2 secuser.u_name%type; begin aaaaaaaaaaaaaaa(test_cur); fetch test_cur into test_rec while test_cur%found loop v1:=test_rec.logid; v2:=test_rec.u_name; end loop; close test_cur; end;调用不了 是不是那个地方写错了SQL codeCREATE OR REPLACE PROCEDURE a…… [/Quote]我在
CREATE OR REPLACE PROCEDURE aaaaaaaaaaaaaaa(cur1 out sys_refcursor) IS begin open cur1 for select * from secuser; end;--调用 declare test_cur sys_reccursor; test_rec secuser%rowtype; v1 secuser.logid%type; v2 secuser.u_name%type; begin aaaaaaaaaaaaaaa(test_cur); fetch test_cur into test_rec while test_cur%found loop v1:=test_rec.logid; v2:=test_rec.u_name; end loop; close test_cur; end;是这个调用不了
CREATE OR REPLACE PROCEDURE aaaaaaaaaaaaaaa(cur1 out sys_refcursor) IS begin open cur1 for select secuser.logid,secuser.u_name,secuser.firstname from secuser; end;像这个我在C#里面怎么调用呢?? sys_refcursor C#怎么接受的啊??
http://topic.csdn.net/u/20100810/10/1e161ac6-ef69-4739-8f7a-3dee85d666d5.html?17851
http://topic.csdn.net/u/20100810/10/65befff0-ebd5-4f5f-8ff0-4da561ffa57d.html?97786
http://topic.csdn.net/u/20100810/10/65befff0-ebd5-4f5f-8ff0-4da561ffa57d.html?79863
我很简单啊 我只想在存储过程里面返回一张表的信息啊! 别无他求 在SQLSERVER里面可以这样写 在PL/SQL里面不能这样写了? 那位能写个列子出来啊 帮忙
2 out_curEmp out SYS_REFCURSOR) as
3
4 begin
5 open out_curEmp for
6 SELECT * FROM emp WHERE deptno = in_deptNo ;
7 EXCEPTION
8 WHEN OTHERS THEN
9 RAISE_APPLICATION_ERROR(-20101,
10 'Error in getEmpByDept' || SQLCODE );
12 end getEmpByDept;
13 /
CREATE OR REPLACE PROCEDURE aaaa(cur1 out sys_refcursor)
IS
begin
open cur1 for select * from test;
end;--调用
declare
test_cur sys_reccursor;
test_rec test%rowtype;
v1 test.col%type;
v2 test.col2%type;
...;
begin
aaaa(test_cur);
fetch test_cur into test_rec
while test_cur%found loop
v1:=test_rec.col;
v2:=test_rec.col2;
...;
end loop;
close test_cur;
end;
declare
declare
test_cur sys_reccursor;
test_rec test%rowtype;
v1 test.col%type;
v2 test.col2%type;
...;
begin
aaaa(test_cur);
fetch test_cur into test_rec
while test_cur%found loop
v1:=test_rec.col;
v2:=test_rec.col2;
...;
fetch test_cur into test_rec;
end loop;
close test_cur;
end;
IS
begin
open cur1 for select * from secuser;
end;--调用
declare
test_cur Sys_Refcursor;
test_rec test%rowtype;
v1 secuser.logid%type;
v2 secuser.u_name%type;
--...;
begin
aaaaaaaaaaaaaaa(test_cur);
fetch test_cur into test_rec
while test_cur%found loop
v1:=test_rec.logid;
v2:=test_rec.u_name;
--...;
end loop;
close test_cur;
end;这好像也不能调用啊???
begin
for i in(select col1,col2,col3 from tb) loop
dbms_output.put_line(i.col1||','||i.col2||','||i.col3);
end loop;
end;
IS
begin
open cur1 for select logid from secuser;
end;--调用
declare
test_cur sys_reccursor;
test_rec secuser%rowtype;
v1 secuser.logid%type;
v2 secuser.u_name%type;
begin
aaaaaaaaaaaaaaa(test_cur);
fetch test_cur into test_rec
while test_cur%found loop
v1:=test_rec.logid;
v2:=test_rec.u_name;
end loop;
close test_cur;
end;调用不了 是不是那个地方写错了SQL codeCREATE OR REPLACE PROCEDURE a……
[/Quote]我在
IS
begin
open cur1 for select * from secuser;
end;--调用
declare
test_cur sys_reccursor;
test_rec secuser%rowtype;
v1 secuser.logid%type;
v2 secuser.u_name%type;
begin
aaaaaaaaaaaaaaa(test_cur);
fetch test_cur into test_rec
while test_cur%found loop
v1:=test_rec.logid;
v2:=test_rec.u_name;
end loop;
close test_cur;
end;是这个调用不了
IS
begin
open cur1 for select secuser.logid,secuser.u_name,secuser.firstname from secuser;
end;像这个我在C#里面怎么调用呢?? sys_refcursor C#怎么接受的啊??