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) isl_sql varchar2(2000);l_countSql varchar2(2000);BEGINl_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) thenl_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) thenl_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) thenl_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) thenl_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) thenl_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) thenl_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;            }        }