oracle 中 Procedues （存储过程） 简单的分页。

 

 oracle存储过程：

pag 这个游标输出的是根据条件搜索出来的结果 。。 totalCount 是查询总数

create or replace procedure DataGridPages(p_zcbm in varchar2,p_fwbm in varchar2,p_zcmc in varchar2,p_fwmc in  varchar2,p_zlwz in  varchar2,p_dwjc in  varchar2
  ,deptcode in varchar2, pageindex in number,pagesize in number,  pag out SYS_REFCURSOR,totalCount out SYS_REFCURSOR) is
l_sql varchar2(2000);
l_countSql varchar2(2000);
BEGIN
l_sql:='select * from (select rownum as pagein , a.fwzc_id,a.zcmc,a.zcbm,a.fwbm,a.DWJC,a.ssqyhz,a.sfdtglhz, b.zlwz,b.fwmc
                       from t_fwzc a inner join t_fwcq b on a.fwbm=b.fwbm WHERE 1=1  and a.deptcode like '''||deptcode||'%''';

l_countSql:='select count(a.fwzc_id)
                       from t_fwzc a inner join t_fwcq b on a.fwbm=b.fwbm WHERE 1=1  and a.deptcode like '''||deptcode||'%''';
                       
if(P_zcbm is not null) then
l_sql:=l_sql||' and a.zcbm like ''%'||p_zcbm||'%'' ';
l_countSql:=l_countSql||' and a.zcbm like ''%'||p_zcbm||'%'' ';
end if;
if(P_fwbm is not null) then
l_sql:=l_sql||' and a.fwbm like ''%'||p_fwbm||'%'' ';
l_countSql:=l_countSql||' and a.fwbm like ''%'||p_fwbm||'%'' ';
end if;
if(P_zcmc is not null) then
l_sql:=l_sql||' and a.zcmc like ''%'||p_zcmc||'%'' ';
l_countSql:=l_countSql||' and a.zcmc like ''%'||p_zcmc||'%'' ';
end if;
if(P_fwmc is not null) then
l_sql:=l_sql||' and b.fwmc like ''%'||p_fwmc||'%'' ';
l_countSql:=l_countSql||' and b.fwmc like ''%'||p_fwmc||'%'' ';
end if;
if(P_zlwz is not null) then
l_sql:=l_sql||' and b.zlwz like ''%'||p_zlwz||'%'' ';
l_countSql:=l_countSql||' and b.zlwz like ''%'||p_zlwz||'%'' ';
end if;
if(P_dwjc is not null) then
l_sql:=l_sql||' and a.dwjc like ''%'||p_dwjc||'%'' ';
l_countSql:=l_countSql||' and a.dwjc like ''%'||p_dwjc||'%'' ';
end if;
l_sql:=l_sql||')where pagein between('||pageindex||'*'||pagesize||')+1 and ('||pageindex||'+1)*'||pagesize||'';
open pag for l_sql;

open totalCount for l_countSql;
end DataGridPages;

c#调用存储过程：

 public string GetCDatas(string p_zcbm, string p_fwbm, string p_zcmc, string p_fwmc, string p_zlwz, string p_dwjc
                         , string deptcode, int pageindex, int pagesize)
        {
            try
            {
                DbCommand dbCommand = db.GetStoredProcCommand("DataGridPages");//存储过程的名字

                DbParameter p_ZM = new OracleParameter("p_zcbm", OracleType.NVarChar, 20); //存储过程中的字段，就是上面传的参数的名字
                p_ZM.Value = p_zcbm;
                dbCommand.Parameters.Add(p_ZM);

                DbParameter p_FM = new OracleParameter("p_fwbm", OracleType.NVarChar, 9);
                p_FM.Value = p_fwbm;
                dbCommand.Parameters.Add(p_FM);

                DbParameter p_ZC = new OracleParameter("p_zcmc", OracleType.NVarChar, 200);
                p_ZC.Value = p_zcmc;
                dbCommand.Parameters.Add(p_ZC);

                DbParameter p_FC = new OracleParameter("p_fwmc", OracleType.NVarChar, 200);
                p_FC.Value = p_fwmc;
                dbCommand.Parameters.Add(p_FC);

                DbParameter p_ZZ = new OracleParameter("p_zlwz", OracleType.NVarChar, 500);
                p_ZZ.Value = p_zlwz;
                dbCommand.Parameters.Add(p_ZZ);

                DbParameter p_DC = new OracleParameter("p_dwjc", OracleType.NVarChar, 100);
                p_DC.Value = p_dwjc;
                dbCommand.Parameters.Add(p_DC);

                DbParameter p_DE = new OracleParameter("deptcode", OracleType.NVarChar, 25);
                p_DE.Value = deptcode;
                dbCommand.Parameters.Add(p_DE);

                DbParameter p_PX = new OracleParameter("pageindex", OracleType.Number);
                p_PX.Value = pageindex;
                dbCommand.Parameters.Add(p_PX);

                DbParameter p_PE = new OracleParameter("pagesize", OracleType.Number);
                p_PE.Value = pagesize;
                dbCommand.Parameters.Add(p_PE);

                DbParameter p_cur = new OracleParameter("pag", OracleType.Cursor);
                p_cur.Direction = ParameterDirection.Output;
                dbCommand.Parameters.Add(p_cur);

                DbParameter p_totalCount = new OracleParameter("totalCount", OracleType.Cursor);
                p_totalCount.Direction = ParameterDirection.Output;
                dbCommand.Parameters.Add(p_totalCount);

                DataSet ds = new DataSet();
                ds=db.ExecuteDataSet(dbCommand);

                //DataTable dt = new DataTable();
                //db.GetDataAdapter().Fill(dt);
                //ds.Tables.Add(p_cur.Value as DataTable);
                //var test = p_cur.Value;

               
                //DataTable ndt = new DataTable();
                //ndt.Columns.Add("count");
                //ndt.NewRow()["count"] = p_totalCount.Value.ToString();
                //var count = Convert.ToInt32(p_totalCount.Value.ToString());
                //ds.Tables.Add(ndt);

                return Connector.ToXml(ds, false);
            }
            catch (Exception err)
            {
                return "err";
                //throw err;
            }

        }