oracle 最简单的存储过程

CREATE OR REPLACE  PROCEDURE "MAPINFO"."INPUT_VALUE"  (name in
    varchar2)
as
begin
insert into EMPLOYEE values(name,20);
end; 

 

select t.* from employee t where rownum between 1 and 3

程序包体并返回datareador

程序包

CREATE OR REPLACE  PACKAGE "NMS_GIS"."NMS_GIS_TEST"  as
  TYPE c_CURSOR IS REF CURSOR;
  PROCEDURE OPEN_TABLE_CURSOR (c_Result   OUT c_CURSOR,
  p_Out      OUT VARCHAR2);

end;

程序包体

CREATE OR REPLACE  PACKAGE BODY "NMS_GIS"."NMS_GIS_TEST"  is
 PROCEDURE OPEN_TABLE_CURSOR(
    c_Result   OUT c_CURSOR,  --结果集
    p_Out      OUT VARCHAR2)  --错误信息
IS
BEGIN
    OPEN c_Result FOR
    SELECT * from TEST;
EXCEPTION
    WHEN OTHERS THEN
      p_Out := SQLERRM;  --存储过程错误
END;

end NMS_GIS_TEST;

 调用时

                OracleCommand cmd = Conn().CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "nms_gis.NMS_GIS_TEST.OPEN_TABLE_CURSOR";
                cmd.Parameters.Add("c_Result", OracleType.Cursor);
                cmd.Parameters.Add("p_Out", OracleType.VarChar, 50);
                cmd.Parameters[0].Direction = ParameterDirection.Output;
                cmd.Parameters[1].Direction = ParameterDirection.Output;
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                MessageBox.Show(ds.Tables[0].Rows.Count.ToString());