没分,往各位指点
create or replace procedure sp_test
(
ID in varchar, /*ID号*/
aResul out types_auto.cursorType, /*返回的结果集*/
aCode out integer, /*返回值 1成功 -1失败
aErrMsg out varchar2 /*出错信息*/
)
请问在.net里如何调这个存储过程
create or replace procedure sp_test
(
ID in varchar, /*ID号*/
aResul out types_auto.cursorType, /*返回的结果集*/
aCode out integer, /*返回值 1成功 -1失败
aErrMsg out varchar2 /*出错信息*/
)
请问在.net里如何调这个存储过程
还是你调用的问题
(
ID in varchar, /*ID号*/
aResul out types_auto.cursorType, /*返回的结果集*/
aCode out integer, /*返回值 1成功 -1失败
aErrMsg out varchar2 /*出错信息*/
)
begin
open aResul for
select * from pats where pats._id = ID;
aCode := 1;
aErrMsg := '查询成功';
return;end sp_test;c#
OracleCommand cmd=new OracleCommand("sp_test",conn);
cmd.CommandType=CommandType.StoredProcedure;OracleParameter p_id=cmd.Parameters.Add("ID",OracleDbType.varchar2);
p_id.Direction=ParameterDirection.Input;OracleParameter p_cursor=cmd.Parameters.Add("aResul",OracleDbType.RefCursor);
p_cursor.Direction=ParameterDirection.Output;OracleParameter p_aCode=cmd.Parameters.Add("aCode",OracleDbType.Int32);
p_aCode.Direction=ParameterDirection.Output;OracleParameter p_aErrMsg =cmd.Parameters.Add("aErrMsg",OracleDbType.varchar2);
p_aErrMsg .Direction=ParameterDirection.Output;cmd.ExecuteScalar();//执行到这就报错
单独调结果就去掉其他几个参数,执行成功,可以返回数据
p_id.Direction=ParameterDirection.Input;这只是参数的名称,类型及参数是否输入输出类型。参数的值呢?
直接cmd.Parameters.Add
存储过程根据DEPTNO获取SCOTT.EMP中的数据,如下
CREATE OR REPLACE PROCEDURE QUERY_EMP(P_DEPTNO EMP.EMPNO%TYPE,
EMP_CURSOR OUT SYS_REFCURSOR) IS
BEGIN
OPEN EMP_CURSOR FOR
SELECT * FROM EMP WHERE DEPTNO = P_DEPTNO;END;调用:
OracleConnection connection = new OracleConnection();
string connectionString = "server=orcl;uid=scott;pwd=admin";
connection.ConnectionString = connectionString;
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleDataAdapter dataAdapter = new OracleDataAdapter();
dataAdapter.SelectCommand = command;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "QUERY_EMP";
OracleCommandBuilder.DeriveParameters(command);
String deptNo = "20";
DataSet ds = new DataSet();
command.Parameters[0].Value = deptNo;
command.Parameters[1].Value = ds;
dataAdapter.Fill(ds);
this.dataGridView1.AutoGenerateColumns = true;
this.dataGridView1.DataSource = ds.Tables[0];
connection.Close();
你说的那个方法我也试了
ORA-06550:?1?,?7?:
PLS-00306:??'sp_test' ?????????
ORA-06550:?1?,?7?:
PL/SQL:Statement ignored