由于客户对速度和性能上的要求越来越变态,而数据量一天天的庞大,因此本人产生了数据的查询和分页完全由客户端回调来实现。想法看上去复杂，实现起来也不难。废话不多说，看程序吧。

一、存储过程

包头：

create or replace package H_QUERYPACK is

  -- Author  : Evorul
  -- Created : 2007-3-29
  -- Purpose : 查询机构表
 
  -- Public type declarations
  type MYCURSOR is REF CURSOR;
   PROCEDURE QUERYLOG (RET_CURSOR OUT MYCURSOR,ERRORCODE OUT INT,p_logID int,p_StartTime Date,p_EndTime Date,p_Operator varchar2 ,p_OrderField varchar2 ,
      p_Desc int,p_PageSize Int,p_PageIndex Int,p_RecordCount Out Int);

end H_QUERYPACK;
 

包体：

   create or replace package body H_QUERYPACK Is
  -- Author  : Evorul
  -- Created : 2007-3-29
  -- Purpose : 查询
 
  -- 查询公司,分页用
   PROCEDURE QUERYLOG (RET_CURSOR OUT MYCURSOR,ERRORCODE OUT INT,p_logID int,p_StartTime Date,p_EndTime Date,p_Operator varchar2 ,p_OrderField varchar2 ,
      p_Desc int,p_PageSize Int,p_PageIndex Int,p_RecordCount Out Int)
      AS
     v_sql varchar2(3000);
    v_sqlcount varchar2(3000);
    v_orderfield varchar2(100);
    v_order VARCHAR2(5); --顺序
    v_count int;
    v_heiRownum int;
    v_lowRownum int;
   BEGIN
        ERRORCODE:=0;

        v_sql:='select * from LOG Where 1=1 ';

        if(p_logID <> 0)then
            v_sql := v_sql || '  and id = ' || TO_CHAR(p_logID);
        end if;

         IF p_Operator Is Not Null Then then
                v_sql := v_sql || 'And operator LIKE ''%' || RTRIM(LTRIM(p_Operator))||'%''';       
          end if;

          v_sql := v_sql ||' and (TO_CHAR(time,''YYYYMMDD'') between ''' || to_char(p_StartTime, 'YYYYMMDD') ||''' and ''' || to_char(p_EndTime, 'YYYYMMDD') ||''')';

         
       ----取记录总数
      v_sqlcount := 'select count(*) from (' || v_sql || ')';
      execute immediate v_sqlcount into v_count;
      p_RecordCount := v_count;

        --排序字段
        IF p_OrderField IS NOT NULL THEN
           v_orderfield:=p_OrderField;
           Else
           v_orderfield:='ID';
        END IF;
        --是否降序
        IF p_Desc <>0 THEN
          v_order:=' ASC';
          Else
          v_order:=' DESC';
        END IF;
       
        v_sql:=v_sql || 'ORDER BY '|| v_orderfield || v_order;
      ----执行分页查询
      v_heiRownum := p_PageIndex * p_PageSize;
  &nb