-
see follows:
SQL> create or replace procedure sp_try
2 as
3 begin
4 select * from try;
5 end;
6 /
¾¯¸æ: ´´½¨µÄ¹ý³Ì´øÓб*Òë´*Îó¡£
(warning:the procedure has a compiled error.)
SQL> show errors
PROCEDURE SP_TRY ³öÏÖ´*Îó:
LINE/COL ERROR
-------- -------------------------------------------------
4/4 PLS-00428: ÔÚ´Ë SELECT Óï¾äÖÐȱÉÙ INTO ×Ó¾ä
4/4 PL/SQL: SQL Statement ignored
I want this procedure return a multirows recordset. the table of try have many rows. I want these all rows can return. How should I do?
Please help
thanks
ocean
-
-
You have to get the rows into a variable one by one in a loop. Use a cursor:
Code:
Create or replace procedure sp_try
is
cursor my_cursor is select * from try;
my_try try%rowtype;
Begin
open my_cursor;
loop
fetch my_cursor into my_try;
exit when my_cursor%notfound;
dbms_output.put_line(my_try.column1||' '||my_try.column2);
end loop;
end;
/
show errors
/
-
what about in a procedure?
pando, your example is a function, return a cursor. Please understand me.
I use jsp and oracle to build web application. In jsp , I use java.sql.CallableStatement class to call a oracle procedure or function. there is a executeQuery method where return a ResultSet calss. So I want the procedure return a resultSet like java.
For example in sql server, I can only build a procedure(there is not function in sql server) as follows:
Create procedure sp_try
begin
select * from try
end
In asp, I use this statement as follows
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open 'this is a connection string'
set rs = conn.Execute("exec sp_try")
'then rs have a recordset, this is a class "ADODB.RecordSet"
%>
The java.sql.ResultSet in java is equal to ADODB.Recordset in asp. But the oracle procedure is not like sql server procedure
if I use a funciton which returns a cursor type, I will use which type in java?
Hope you understand me.
Please help
thanks
ocean
-
thank you, I know all
Pando,thank you very much. I read the article you gived. Now I know how to do. Thank you very much.
Could you tell me how to you find this article?
ocean
-
Tom Kyte is pretty popular guy, if you read Oracle Magazine online you will see a link to his site
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|