oracle游标取第一条数据,oracle通过游标取数据

CREATE OR REPLACE PACKAGE PKG_DNBILL IS

TYPE PC_RESULT IS REF CURSOR; -- 定义结果集返回值类型

/*导入钱清除数据*/

PROCEDURE PRO_CLEARIMPORT(

PS_KEY VARCHAR2,

PS_DN_NO VARCHAR2,

PC_RESULT OUT PC_RESULT

);

END PKG_DNBILL;

CREATE OR REPLACE PACKAGE BODY PKG_DNBILL IS

/*导入钱清除数据*/

PROCEDURE PRO_CLEARIMPORT(

PS_KEY VARCHAR2,

PS_DN_NO VARCHAR2,

PC_RESULT OUT PC_RESULT

)

IS

CT NUMBER;

VS_DN_ID VARCHAR2(100);

BEGIN

/*清除生成的dn数据*/

IF PS_DN_NO IS NOT NULL THEN

SELECT MAX(T.BILL_ID) INTO VS_DN_ID FROM APP_SCMWMS.ST_DN_HEAD T WHERE T.BILL_NO=PS_DN_NO;

DELETE FROM APP_SCMWMS.ST_DN_DETAIL T WHERE T.BILL_ID=VS_DN_ID;

END IF;

/*清除导入数据*/

DELETE FROM APP_SCMWMS.ST_DNIMPORT_MOBILE T WHERE T.KEY_ID=PS_KEY;

IF (SQL%ROWCOUNT = 1) THEN

CT := 1;

ELSIF (SQL%ROWCOUNT != 1) THEN

CT := 0;

end if;

OPEN PC_RESULT FOR SELECT CT FROM DUAL;

END;

END PKG_DNBILL;

///

/// 导入前清除数据

///

///

///

///

public static int ClearData(string cacheKey, string dnNo, OracleTransaction tran){

OracleCommand com = tran.Connection.CreateCommand();

com.Transaction = tran;

com.CommandType = CommandType.StoredProcedure;

com.CommandText = "APP_SCMWMS.PKG_DNBILL.PRO_CLEARIMPORT";

OracleParameter[] Parames=new OracleParameter[3];

Parames[0] = new OracleParameter("PS_KEY",OracleType.VarChar);

Parames[0].Direction = ParameterDirection.Input;

Parames[0].Value = cacheKey;

Parames[1] = new OracleParameter("PS_DN_NO", OracleType.VarChar);

Parames[1].Direction = ParameterDirection.Input;

Parames[1].Value = dnNo;

Parames[2] = new OracleParameter("PC_RESULT", OracleType.Cursor);

Parames[2].Direction = ParameterDirection.Output;

com.Parameters.AddRange(Parames);

DataTable table = new DataTable();

OracleDataAdapter da1 = new OracleDataAdapter(com);//取出数据

da1.Fill(table);

return Convert.ToInt32(table.Rows[0][0]);

}