CREATE OR REPLACE PROCEDURE prc_query
(p_tableName in varchar2, --表名
p_strWhere in varchar2, --查詢條件
p_orderColumn in varchar2, --排序的列
p_orderStyle in varchar2, --排序方式
p_curPage in out Number, --當前頁
p_pageSize in out Number, --每頁顯示記錄條數
p_totalRecords out Number, --總記錄數
p_totalPages out Number, --總頁數
v_cur out pkg_query.cur_query) --返回的結果集
IS
v_sql VARCHAR2(1000) := ''; --sql語句
v_startRecord Number(4); --開始顯示的記錄條數
v_endRecord Number(4); --結束顯示的記錄條件
BEGIN
--記錄中總記錄條數
v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || p_tableName || ' WHERE 1=1';
IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
v_sql := v_sql || p_strWhere;
END IF;
EXECUTE IMMEDIATE v_sql INTO p_totalRecords;
--驗證頁面記錄大小
IF p_pageSize < 0 THEN
p_pageSize := 0;
END IF;
--根據頁大小計算總頁數
IF MOD(p_totalRecords,p_pageSize) = 0 THEN
p_totalPages := p_totalRecords / p_pageSize;
ELSE
p_totalPages := p_totalRecords / p_pageSize + 1;
END IF;
--驗證頁號
IF p_curPage < 1 THEN
p_curPage := 1;
END IF;
IF p_curPage > p_totalPages THEN
p_curPage := p_totalPages;
END IF;
--實現分頁查詢
v_startRecord := (p_curPage - 1) * p_pageSize + 1;
v_endRecord := p_curPage * p_pageSize;
v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM ' ||'(SELECT * FROM ' || p_tableName;
IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
v_sql := v_sql || ' WHERE 1=1' || p_strWhere;
END IF;
IF p_orderColumn IS NOT NULL or p_orderColumn <> '' THEN
--在Oracle存儲過程實現分頁
v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle;
END IF;
v_sql := v_sql || ') A WHERE rownum <= ' || v_endRecord || ') B WHERE r >= '
|| v_startRecord;
DBMS_OUTPUT.put_line(v_sql);
OPEN v_cur FOR v_sql;
END prc_query;
2、JAVA代码里取出结果集
String sql= "{ call prc_query(?,?,?,?,?,?,?,?,?) }";
CallableStatement call = con.prepareCall(sql);
3.自己的例子
procedure prc_query(--p_tableName in varchar2,
p_curPage in out Number,
p_begin_time in varchar2,
p_end_time in varchar2,
p_pageSize in out Number,
p_totalRecords out Number,
p_totalPages out Number,
v_cur out cur_query)
is
v_sql varchar2(10000):='';--SQL language
v_startRecord Number(4); ---開始顯示的記錄數
v_endRecord Number(4);--結束顯示的記錄條件
p_tableName varchar2(10000):='select distinct (select p.family_name from product_family_info p) product_family_name,rownum,
'' '' product_family_desc,decode(rsg.group_level, ''ROA'', ''Main Board'', ''Core Product'') product_type_name,
'' '' product_type_desc,rh.product_name,
rh.product_rstate as product_revision,
decode(rsg.group_level,
''ROA'',
''ROA Products'',
''KRH and 1/KRH Products'') product_desc,
''1'' product_status,
''0'' product_clf_switch,
'' '' ProductFamily_Id,
'' '' ProductBase_Id,
'' '' ProductType_Id,
'' '' Product_Id
from routing_history rh, routing_station_group rsg
where rh.station_group = rsg.group_id and rsg.table_name is not null
and rh.time_end >= to_date('''||p_begin_time||''', ''yyyy/mm/dd HH24:MI:SS'')
and rh.time_end <= to_date('''||p_end_time||''', ''yyyy/mm/dd HH24:MI:SS'')';
begin
v_sql :='select to_number(count(*)) from ' || '('||p_tableName ||')'|| 'where 1=1';
execute immediate v_sql into p_totalRecords;
if p_pageSize <0 then
p_pageSize :=0;
end if;
if mod(p_totalRecords,p_pageSize)=0 then
p_totalPages:=p_totalRecords/p_pageSize;
else
p_totalPages:=p_totalRecords/p_pageSize+1;
end if;
if p_curPage<1 then
p_curPage:=1;
end if;
if p_curPage>p_totalPages then
p_curPage:=p_totalPages;
end if;
v_startRecord:=(p_curPage-1)* p_pageSize+1;
v_endRecord:=p_curPage*p_pageSize;
v_sql:='select * from (select A.*, rownum r from ' || '(select * from '|| '('||p_tableName ||')'|| 'where 1=1';
v_sql:=v_sql ||')A where rownum<=' || v_endRecord || ') B where r>=' || v_startRecord;
DBMS_OUTPUT.put_line(v_sql);
open v_cur for v_sql;
end prc_query