-- Public type declarations type RecTypeTerm is record( eng_name term.eng_name%type, eng_abbr_name term.eng_abbr_name%type, chn_name term.chn_name%type ); type CurTypeTerm is ref cursor;
-- Public constant declarations Copy_Right constant varchar2(11) := 'Dream.Zhang'; -- Public variable declarations Call_Time number:=0; cursor curTerm(engAbbrName varchar2) return term%rowtype;
-- Public function and procedure declarations function ex_get_call_time return number; procedure ex_add_call_time(times number); -- 测试output cursor参数的过程 procedure ex_cursor_output_parameter(pKind varchar2,pCurTerm out CurTypeTerm);
function ex_cursor_return_value return CurTypeTerm; end;
TYPE TYPE_CURSOR IS REF CURSOR; PROCEDURE test(RET_CURSOR OUT TYPE_CURSOR); IS BEGIN OPEN RET_CURSOR FOR select ID,GAGA from T_test order by ID; END;调试: set serverout on; var csr_name refcursor; packagename.test(:csr_name); print csr_name;
private void btnQueryOutputParameter_Click(object sender, System.EventArgs e) {
OracleConnection conn=null;
OracleCommand command=null;
OracleTransaction trans=null;
try{
conn=new OracleConnection(connectString);
conn.Open();
trans=conn.BeginTransaction();
command=new OracleCommand("ex_plsql.ex_cursor_output_parameter",conn,trans);
command.CommandType=CommandType.StoredProcedure;
command.Parameters.Add("pKind", "Oracle");
command.Parameters.Add("pCurTerm", OracleType.Cursor);
command.Parameters[1].Direction=ParameterDirection.Output;
OracleDataAdapter oda=new OracleDataAdapter(command);
DataTable dt=new DataTable();
oda.Fill(dt); Console.WriteLine("The result rows number: "+dt.Rows.Count);
for(int i=0;i<dt.Rows.Count;i++){
for(int j=0;j<dt.Columns.Count;j++){
Console.Write(dt.Rows[i][j].ToString()+" ");
}
Console.WriteLine();
}
Console.WriteLine("Success."); trans.Commit();
}
catch(Exception ex){
trans.Rollback();
Console.WriteLine(ex.ToString());
}
finally{
conn.Close();
}
}包的声明:(见ex_cursor_output_parameter)
create or replace package ex_plsql is
-- Author : DREAM
-- Created : 2006-6-30 12:29:34
-- Purpose : Test PLSQL
-- Public type declarations
type RecTypeTerm is record(
eng_name term.eng_name%type,
eng_abbr_name term.eng_abbr_name%type,
chn_name term.chn_name%type
);
type CurTypeTerm is ref cursor;
-- Public constant declarations
Copy_Right constant varchar2(11) := 'Dream.Zhang'; -- Public variable declarations
Call_Time number:=0;
cursor curTerm(engAbbrName varchar2) return term%rowtype;
-- Public function and procedure declarations
function ex_get_call_time return number;
procedure ex_add_call_time(times number);
-- 测试output cursor参数的过程
procedure ex_cursor_output_parameter(pKind varchar2,pCurTerm out CurTypeTerm);
function ex_cursor_return_value return CurTypeTerm;
end;
PROCEDURE test(RET_CURSOR OUT TYPE_CURSOR);
IS
BEGIN
OPEN RET_CURSOR FOR
select ID,GAGA from T_test order by ID;
END;调试:
set serverout on;
var csr_name refcursor;
packagename.test(:csr_name);
print csr_name;