Simple paging of procedues in Oracle.

 

Oracle stored procedure:

The Pag cursor outputs the search results based on the conditions .. Totalcount indicates the total number of queries.

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 # Call the stored procedure:

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 = dB. getstoredproccommand ("datagridpages"); // name of the stored procedure dbparameter p_zm = new oracleparameter ("p_zcbm", oracletype. nvarchar, 20); // the field in the stored procedure, that is, the name of the parameter passed above 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 ;}}