oracle存储过程使用

包： 

 is
    Type Com_CurSor is Ref Cursor;      
    PROCEDURE GetOrg(SelectCur out Com_CurSor);
    procedure I_abc(aa in varchar2,bb in varchar2);
END Test;

包体：

 is
 PROCEDURE GetOrg(SelectCur out Com_CurSor)
 is
BEGIN
     Open SelectCur for select * from org;
END GetOrg;

procedure I_abc(aa in varchar2,bb in varchar2)
is
begin
    insert into abc(aa,bb) values(aa,bb);
END I_abc;
end test;

.net(c#调用)

           public static string LoadAllDepInfo()
        {
            OracleConnection con = GetOracleConnection();
            OracleCommand loadsCommand = new OracleCommand("test.GetOrg", con);
            loadsCommand.CommandType = CommandType.StoredProcedure;

            OracleParameter parm_cursor = new OracleParameter("SelectCur", OracleType.Cursor);
            parm_cursor.Direction = ParameterDirection.Output;
            loadsCommand.Parameters.Add(parm_cursor);
            OracleDataAdapter dsCommand = new OracleDataAdapter(loadsCommand);
            if (dsCommand == null)
            {
            }
            DataSet dd = new DataSet();
            try
            {
                dsCommand.Fill(dd,"org");

            }
            catch(OracleException e)
            {
               return e.Message.ToString();
              
            }

            cmd.Connection.Close();
            return "ok";
        }

        public static string InsertABC(string aa,string bb)
        {
            OracleConnection con = GetOracleConnection();
            OracleCommand cmd = new OracleCommand("test.I_abc",con);
            cmd.CommandType = CommandType.StoredProcedure;

            OracleParameter p_aa = new OracleParameter("aa",OracleType.Char);
            p_aa.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(p_aa);

            OracleParameter p_bb = new OracleParameter("bb",OracleType.Char);
            p_bb.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(p_bb);

            cmd.Parameters["aa"].Value = aa;
            cmd.Parameters["bb"].Value = bb;
            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (OracleException e)
            {
                return e.Message.ToString();
            }
            cmd.Connection.Close();
            return "ok!";
        }