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,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;

        /// <summary>
        /// 导入前清除数据
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        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]);
        }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!