隨手筆記

    public string Procedure(OracleConnection Conn, string funName, string[] pararr, string[] parvalue, string p_out)//有輸出變數+回傳值 參數分別為  DB link , package function name , sql變數名稱 , sql變數值 , sql輸出變數
    {
        using (OracleCommand sqlcom = new OracleCommand(funName, Conn))
        {
            Conn.Open();
            sqlcom.CommandType = CommandType.StoredProcedure;

            OracleParameter[] param = new OracleParameter[pararr.Length + 1];//參數變數+輸出變數

            for (int i = 0; i <= pararr.Length - 1; i++)//加入變數名稱
            {
                param[i] = new OracleParameter(pararr[i], OracleType.NVarChar, 255);
                param[i].Direction = ParameterDirection.Input;
                param[i].Value = parvalue[i];
            }
            param[pararr.Length] = new OracleParameter(p_out, OracleType.NVarChar, 255);//加入輸出名稱
            param[pararr.Length].Direction = ParameterDirection.Output;


            OracleParameter par;
            for (int i = 0; i < param.Length; i++)
            {
                par = (OracleParameter)param[i];
                sqlcom.Parameters.Add(par);
            }

            sqlcom.ExecuteNonQuery();
            return Convert.ToString(param[pararr.Length].Value.ToString());
        }
    }
    public void voidProcedure(OracleConnection Conn, string funName, string[] pararr, string[] parvalue)//無輸出變數 參數分別為  DB link , package function name , sql變數名稱 , sql變數值
    {
        using (OracleCommand sqlcom = new OracleCommand(funName, Conn))
        {
            Conn.Open();
            sqlcom.CommandType = CommandType.StoredProcedure;

            OracleParameter[] param = new OracleParameter[pararr.Length];//參數變數+輸出變數

            for (int i = 0; i <= pararr.Length - 1; i++)//加入變數名稱
            {
                param[i] = new OracleParameter(pararr[i], OracleType.NVarChar, 255);
                param[i].Direction = ParameterDirection.Input;
                param[i].Value = parvalue[i];
            }

            OracleParameter par;
            for (int i = 0; i < param.Length; i++)
            {
                par = (OracleParameter)param[i];
                sqlcom.Parameters.Add(par);
            }

            sqlcom.ExecuteNonQuery();
        }
    }
    public DataSet curProcedure(OracleConnection Conn, string funName, string[] pararr, string[] parvalue, string p_out)//cursor使用
    {
        OracleCommand sqlcom = new OracleCommand(funName, Conn);

        sqlcom.CommandType = CommandType.StoredProcedure;

        OracleParameter[] param = new OracleParameter[pararr.Length];//參數變數+輸出變數

        for (int i = 0; i < pararr.Length; i++)//加入變數名稱
        {
            param[i] = new OracleParameter(pararr[i], OracleType.NVarChar, 255);
            param[i].Direction = ParameterDirection.Input;
            param[i].Value = parvalue[i];
        }

        OracleParameter par;
        for (int i = 0; i < param.Length; i++)
        {
            par = (OracleParameter)param[i];
            sqlcom.Parameters.Add(par);
        }

        OracleParameter param1 = sqlcom.Parameters.Add(p_out, OracleType.Cursor);
        param1.Direction = ParameterDirection.Output;

        OracleDataAdapter da = new OracleDataAdapter(sqlcom);

        DataSet ds = new DataSet();
        da.Fill(ds, "dtDataList");

        return ds;
    }