简单的oracle存储过程,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());