SQL> create or replace procedure sp_try
4 select * from try;
(warning:the procedure has a compiled error.)
SQL> show errors
PROCEDURE SP_TRY 出现错误:
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?
You have to get the rows into a variable one by one in a loop. Use a cursor:
Create or replace procedure sp_try
cursor my_cursor is select * from try;
fetch my_cursor into my_try;
exit when my_cursor%notfound;
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
select * from try
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.
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?
Tom Kyte is pretty popular guy, if you read Oracle Magazine online you will see a link to his site
Click Here to Expand Forum to Full Width