当前位置: 凤凰彩票登陆 > 编程知识 > 正文

怎么调用存款和储蓄过程和函数,存储进程

时间:2019-09-26 07:45来源:编程知识
ORACLE代码 CREATE OR REPLACE PROCEDURE gd_CURSOR(MYCS1 OUT SYS_REFCURSOR,MYCS2 OUT SYS_REFCURSOR,a out varchar)asBEGIN a:='test'; OPEN MYCS1 FOR SELECT 1 from dual; OPEN MYCS2 FOR SELECT 2 from dual;END; 1.增加操作:         pu

ORACLE代码

CREATE OR REPLACE PROCEDURE gd_CURSOR(MYCS1 OUT SYS_REFCURSOR,MYCS2 OUT SYS_REFCURSOR,a out varchar)asBEGIN  a:='test';  OPEN MYCS1 FOR  SELECT 1 from dual;    OPEN MYCS2 FOR  SELECT 2 from dual;END;

1.增加操作: 
       public static void StockIns(string ps_SupplierName, int pn_ItemQty, string ps_ItemDesc, string ps_ComeDate,
            string ps_AttachedFile, string ps_SupAddress, string ps_SupPhone, string ps_SupFax,
            string ps_SupEmail, string ps_UserNo, ref string ps_HKStockItemNo)
        {
            // 创建参数
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter("ps_SupplierName", OracleType.VarChar, 200),
                new OracleParameter("pn_ItemQty", OracleType.Int32, 10),
                new OracleParameter("ps_ItemDesc", OracleType.VarChar, 400),
                new OracleParameter("ps_ComeDate", OracleType.VarChar, 20),
                new OracleParameter("ps_AttachedFile", OracleType.VarChar, 100),
                new OracleParameter("ps_SupAddress", OracleType.VarChar, 200),
                new OracleParameter("ps_SupPhone", OracleType.VarChar, 60),
                new OracleParameter("ps_SupFax", OracleType.VarChar, 60),
                new OracleParameter("ps_SupEmail", OracleType.VarChar, 100),
                new OracleParameter("ps_UserNo", OracleType.VarChar, 20),
                new OracleParameter("ps_HKStockItemNo", OracleType.VarChar, 20,ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Default, null)
            };

C#代码

            // 给参数赋值
            parms[0].Value = ps_SupplierName;
            parms[1].Value = pn_ItemQty;
            parms[2].Value = ps_ItemDesc;
            parms[3].Value = ps_ComeDate;
            parms[4].Value = ps_AttachedFile;
            parms[5].Value = ps_SupAddress;
            parms[6].Value = ps_SupPhone;
            parms[7].Value = ps_SupFax;
            parms[8].Value = ps_SupEmail;
            parms[9].Value = ps_UserNo;
           
            OraHelper.ExecuteNonQuery(OraHelper.connstr, CommandType.StoredProcedure, "KXSCMIII.PKG_PU_HKSTOCK.Pro_StockIns", parms);
            ps_HKStockItemNo = parms[10].Value.ToString(); // 返回存货单号

  /// <summary>        /// 执行oracle存储过程返回多个结果集        /// </summary>        /// <param name="strProcName">存储过程名称</param>        /// <param name="ResultCount">返回个数</param>        /// <param name="paras">参数</param>        /// <returns>任意对象数组</returns>        public  object[] ExcuteProc_N_Result(string strProcName, int ResultCount, params OracleParameter[] paras)        {            using (OracleConnection conn = new OracleConnection("User ID=用户名;Password=密码;Data Source=数据库;"))            {                OracleCommand cmd = new OracleCommand(strProcName, conn);                if (paras != null && paras.Length > 0)                {                    for (int j = 0; j < paras.Length; j++)                    {                        if (paras[j].Value == null)                        {                            paras[j].Value = DBNull.Value;                        }                    }                }                cmd.Parameters.AddRange;                cmd.CommandType = CommandType.StoredProcedure;                conn.Open();                cmd.ExecuteNonQuery();                int i = 0;                //int nOutputParametersCount = 0;                object[] objResult = new object[ResultCount];                foreach (OracleParameter p in cmd.Parameters)                {                    if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.InputOutput)                    {                        if (p.Value is OracleDataReader)                        {                            OracleDataReader reader = p.Value as OracleDataReader;                            objResult[i++] = ConvertDataReaderToDataTable;                        }                        else                        {                            objResult[i++] = p.Value;                        }                    }                }                return objResult;            }        }        /// <summary>         /// 将DataReader 转为 DataTable         /// </summary>         /// <param name="DataReader">OleDbDataReader</param>         protected  DataTable ConvertDataReaderToDataTable(OracleDataReader reader)        {            DataTable objDataTable = new DataTable("TmpDataTable");            try            {                int intFieldCount = reader.FieldCount;//获取当前行中的列数;                for (int intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)                {                    objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));                }                //populate   datatable                   objDataTable.BeginLoadData();                //object[]   objValues   =   new   object[intFieldCount   -1];                   object[] objValues = new object[intFieldCount];                while (reader.Read                {                    reader.GetValues(objValues);                    objDataTable.LoadDataRow(objValues, true);                }                reader.Close();                objDataTable.EndLoadData();                return objDataTable;            }            catch (Exception ex)            {                throw new Exception("转换出错出错!", ex);            }        }

       }
在前台*.aspx.cs文件里面调用:
                string strBill = "";
                HKStockDataAccess.StockIns(txtSupplierName.Text.Trim(), Int32.Parse(txtItemQty.Text.Trim()), txtItemDesc.Text.Trim(),
                                           txtComeDate.Text.Trim(), strServerFullPath, txtSupAddress.Text.Trim(), txtSupPhone.Text.Trim(),
                                           txtSupFax.Text.Trim(), txtSupEmail.Text.Trim(), Session["userno"].ToString(),ref strBill);

调用方法

2.修改操作:
     public static void ClaimStock(string ps_HKStockItemNo, string ps_Buyer,
                                      string ps_DeliNo, string ps_UserNo)
        {
            // 创建参数
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter("ps_HKStockItemNo",OracleType.VarChar,20),
                new OracleParameter("ps_Buyer",OracleType.VarChar,20),
                new OracleParameter("ps_DeliNo",OracleType.VarChar,20),
                new OracleParameter("ps_UserNo",OracleType.VarChar,20)
            };

OracleParameter[] oracleParameter = new OracleParameter[]{
new OracleParameter("MYCS1",OracleType.Cursor),
new OracleParameter("MYCS2",OracleType.Cursor),
new OracleParameter("a",OracleType.VarChar,200),
};

            // 给参数赋值
            parms[0].Value = ps_HKStockItemNo;
            parms[1].Value = ps_Buyer;
            parms[2].Value = ps_DeliNo;
            parms[3].Value = ps_UserNo;

oracleParameter[0].Direction = ParameterDirection.Output;
oracleParameter[1].Direction = ParameterDirection.Output;
oracleParameter[2].Direction = ParameterDirection.Output;

            OraHelper.ExecuteNonQuery(OraHelper.connstr, CommandType.StoredProcedure, "KXSCMIII.PKG_PU_HKSTOCK.Pro_ClaimStock", parms);
        }

object[] xxx = ExcuteProc_N_Result("gd_CURSOR", 3, oracleParameter);

3.查询操作:
    public static DataTable StockDetailGet(string ps_HKStockItemNo)
        {
            // 创建参数
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter("ps_HKStockItemNo",OracleType.VarChar,20),
                new OracleParameter("returncur", OracleType.Cursor, 2000, ParameterDirection.Output, true,
                0, 0, "", DataRowVersion.Default, null)
            };

            // 给参数赋值
            parms[0].Value = ps_HKStockItemNo;

            // 取记录集
            DataSet ds = OraHelper.ExecuteDataset(OraHelper.connstr, CommandType.StoredProcedure,
                "KXSCMIII.PKG_PU_HKSTOCK.Pro_StockDetailGet", parms);

            return ds.Tables[0];
        }

4.调用函数:
     public static string BuyerNameGet(string ps_CardNo)
        {
            // 创建参数
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter("ps_CardNo", OracleType.VarChar, 20),
                new OracleParameter("ps_FullName", OracleType.VarChar, 80, ParameterDirection.ReturnValue, true, 0, 0, "", DataRowVersion.Default, null)
            };

            // 给参数赋值
            parms[0].Value = ps_CardNo;
           
            OraHelper.ExecuteNonQuery(OraHelper.connstr, CommandType.StoredProcedure, "KXSCMIII.PKG_PU_HKSTOCK.Fun_BuyerNameGet", parms);
            return parms[1].Value.ToString(); // 返回业务员的中文名
        }

编辑:编程知识 本文来源:怎么调用存款和储蓄过程和函数,存储进程

关键词: