调用:
conn.Open();
OracleCommand cmd = new OracleCommand("MyPackage1.MyProcedure", conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter("cid", OracleType.VarChar,20);
OracleParameter p2 = new OracleParameter("c_st", OracleType.Cursor,20);
OracleParameter p3 = new OracleParameter("num1",OracleType.Number);
OracleParameter p4 = new OracleParameter("num2",OracleType.Number);
OracleParameter p5 = new OracleParameter("res_name",OracleType.VarChar,50);
p1.Value = "8001";
p3.Value = "5";
p4.Value = "8";
p1.Direction = ParameterDirection.Input;
p2.Direction = ParameterDirection.Output;
p5.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.ExecuteNonQuery();
return p2.Value.ToString();
包内容
create or replace
PACKAGE MyPackage1 AS
TYPE re_stu IS RECORD
(
rname emp.ename%type,
rage emp.empno%type
);
type c_stu is ref cursor;
FUNCTION MyFunction(num1 NUMBER,num2 NUMBER) RETURN NUMBER;
PROCEDURE MyProcedure(cid in varchar2,c_st out c_stu);
END MyPackage1;
create or replace
PACKAGE BODY MYPACKAGE1 AS
FUNCTION MyFunction(num1 NUMBER,num2 NUMBER) RETURN NUMBER
AS
num NUMBER(10,2):=0;
BEGIN
num:=num1+num2;
return num ;
END;
PROCEDURE MyProcedure(cid in varchar2,c_st out c_stu)
AS
r_stu re_stu;
BEGIN
open c_st for select ename , empno from emp where empno = cid;
loop
fetch c_st into r_stu;
exit when c_st%notfound;
dbms_output.put_line('姓名='||r_stu.rname);
end loop;
END;
END MYPACKAGE1;