创建一个简单的存储过程:SQL> create or replace procedure ss(curs out REF CURSOR) is
2 begin
3 open curs for select * from emp;
4 end ss;
5 /Warning: Procedure created with compilation errors.SQL> show err;
Errors for PROCEDURE SS:LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/27 PLS-00201: identifier 'CURSOR' must be declared什么原因呀?
2 begin
3 open curs for select * from emp;
4 end ss;
5 /Warning: Procedure created with compilation errors.SQL> show err;
Errors for PROCEDURE SS:LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/27 PLS-00201: identifier 'CURSOR' must be declared什么原因呀?
type MyCursor is Ref Cursor;然后
create or replace procedure ss(curs out MyCursor) is
begin
open curs for select * from emp;
end ss;
type MyCursor is Ref Cursor;然后
create or replace procedure ss(curs out MyCursor) is
begin
open curs for select * from emp;
end ss;
具体在SQL/PLUS,怎么写呀?
当然返回的是游标;
例:
create or replace package test
is
type mycur is ref cursor ;
procedure get(p out mycur) ;
end test ;
/
create or replace package body test
as
procedure get(p out mycur)
is
begin
open p for select * from emp ;
end get ;
end test ;
/调用的时候 用 "test.get" 就可以了。
SQL> BEGIN
2 TEST.GET;
3 END;
4 /返回错误,PLS-00306: wrong number or types of arguments in call to 'GET'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored到底怎么调用呀
因为get有个输出参数吗?
你可以在程序中调用,当然,调用前你要创建一个输出参数吗,类型是cursor型的.
哪位有返回一个记录集的例子,让我学习一下啥
OracleCommand cmd = new OracleCommand() ;
cmd.Connection = cn ;
// StoreProcedureName 是过程名字 ,类似刚才的test.get
cmd.CommandText = StoreProcedureName ;
cmd.CommandType = CommandType.StoredProcedure ;
OracleParameter[] parameters =
{
new OracleParameter("p_str",OracleType.Char) ,
new OracleParameter("p_cur",OracleType.Cursor,5000,ParameterDirection.Output,true,0,0,"",DataRowVersion.Default,Convert.DBNull)
} ;
foreach(OracleParameter op in parameters)
{
cmd.Parameters.Add(op) ;
} cmd.Parameters["p_str"].Value = sql ; if (cn.State == ConnectionState.Closed)
cn.Open() ;
OracleDataAdapter da = new OracleDataAdapter() ;
da.SelectCommand = cmd ;
da.Fill(ds) ;
cn.Close() ;
return ds ;