oracle 存储过程 多表操作

CREATE OR REPLACE Procedure P_WEB_MEMBERINFOR
(
      p_memberid In Varchar2,
      member_Resultset In Out Pkg_Return_Resultset.Resultset, --返回结果集
      money_Resultset In Out Pkg_Return_Resultset.Resultset, --返回结果集
      point_Resultset In Out Pkg_Return_Resultset.Resultset, --返回结果集
      email_Resultset In Out Pkg_Return_Resultset.Resultset --返回结果集
)
As

Begin
  Open member_Resultset For
       Select * from T_WEB_BUYERMEMBERS Where MEMBERID = p_memberid;
  Open money_Resultset For
       Select * From DD_YWXT.T_JS_YW_CZJL Where MEMBERID = p_memberid;
  Open point_Resultset For
       Select * From T_WEB_POINT Where MEMBERID = p_memberid;
  Open email_Resultset For
       Select * From T_WEB_EMAIL Where EMAILTO = p_memberid;
End;

　　

C#调用

public DataSet GetUserInfor(string memberid)
        {
            OracleParameter[] parameters = {
new OracleParameter("p_memberid", OracleType.VarChar, 255),
new OracleParameter("member_Resultset", OracleType.Cursor),
new OracleParameter("money_Resultset", OracleType.Cursor),
new OracleParameter("point_Resultset", OracleType.Cursor),
new OracleParameter("email_Resultset", OracleType.Cursor),
                    };
            parameters[0].Value = memberid;
            parameters[1].Direction = ParameterDirection.Output;
            parameters[2].Direction = ParameterDirection.Output;
            parameters[3].Direction = ParameterDirection.Output;
            parameters[4].Direction = ParameterDirection.Output;
return DbHelperOra.RunProcedureCXF("P_WEB_MEMBERINFOR", parameters, "ds");
        }



public static DataSet RunProcedureCXF(string storedProcName, IDataParameter[] parameters, string tableName)
        {
using (OracleConnection connection = new OracleConnection(connectionString)) {
                DataSet dataSet = new DataSet();
                connection.Open();
                OracleDataAdapter sqlDA = new OracleDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet);
                connection.Close();
return dataSet;
            }
        }

　　

编辑器加载中...

转载于:https://www.cnblogs.com/superchen/archive/2011/08/05/2128971.html