首先获取数据总数:
execute immediate 'Select Count(*) from (Select [id] From [table] where [Condition])' into [iNum];
计算分页数据上下限:
[iLow] := [iPage] * [iPageSize];
[iHigh] := [ilow] + [iPageSize];
获取分页数据,游标返回:
sSql := 'Select * From [table] where id between [ilow] and [ihigh]';
open cur for sSql;
其中游标定义(在package中):
TYPE type_cur IS REF CURSOR;
函数中参数说明:out cur type_cur;
全文搜索分页存储过程:/*================================================*//*=过程开始*//*================================================*/CREATE OR REPLACE PACKAGE BODY "SEARCHTEST"."PAGE1" as -- 返回指定分页的数据集PROCEDURE PageSearch( sKey in varchar2, --搜索关键字 iIndex in number, --分页页码 iSize in number, --页面大小 iCount out number, --返回记录总数 records out type_cur --返回当前页数据记录 ) AS v_sql VARCHAR2(1000); sSql varchar2(1000); --PageRecordsCount 计算记录条目用Sql语句 ilow number; ihei number; sTable Varchar2(1000); --表名 sField Varchar2(1000); --待搜索内容所在字段名 Begin sTable := 'TEXT1'; sField := 'SB01' ; -- 按匹配程度 降序排列的 返回所有模糊数据的SQL sSql := 'SELECT * FROM ' || sTable || ' Where Contains(' || sField || ',''About(' || sKey || ')'' , 1) > 0 Order By Score(1) DESC'; --取分页总数 v_sql := 'select count(*) from (' || sSql || ')'; execute immediate v_sql into iCount; --显示任意页内容 ihei := iIndex * iSize ; ilow := ihei - iSize + 1; -- 对所有模糊数据 数据集 获取列号的SQL v_sql := 'SELECT RowNum rn , t.* From (' || sSql || ') t'; --获取分页数据 v_sql := 'select * from (' || v_sql || ') where rn between ' || ilow || ' and ' || ihei; open records for v_sql; End PageSearch; end Page1;