Oracle基础知识总结【2】

1.查询时，同时使用rownum和order by 排序字段 desc比单独使用rownum速度快
查询语句中使用rownum时，最好也同时使用order by 排序字段 desc
比如:

select receivedmessageid,messageid,length,xcom.convert_blob_to_varchar(bufferdata) as bufferdata,processflag,receivedlogtime,destination
 from XCOM.V_RECEIVED_UNPROCESS t
 where rownum=1 and LENGTH<=4000
的执行速度接近14秒；
select receivedmessageid,messageid,length,xcom.convert_blob_to_varchar(bufferdata) as bufferdata,processflag,receivedlogtime,destination
 from XCOM.V_RECEIVED_UNPROCESS t
 where rownum=1 and LENGTH<=4000
 order by t.receivedlogtime desc
的执行速度不到1秒；
2.oracle分页的存储过程
--包
SQL code

CREATE OR REPLACE PACKAGE dotnet
IS
   TYPE type_cur IS REF CURSOR;                  --定义游标变量用于返回记录集

   PROCEDURE dotnetpagination (
      pindex   IN       NUMBER,                                    --分页索引
      psql     IN       VARCHAR2,                      --产生dataset的sql语句
      psize    IN       NUMBER,                                    --页面大小
      pcount   OUT      NUMBER,                                --返回分页总数
      v_cur    OUT      types.cursortype                         --返回当前页数据记录
   );

   PROCEDURE dotnetpagerecordscount (
      psqlcount   IN       VARCHAR2,                   --产生dataset的sql语句
      prcount     OUT      NUMBER                              --返回记录总数
   );
END dotnet;
/

--包体
SQL code

CREATE OR REPLACE PACKAGE BODY dotnet
IS
--***************************************************************************************
   PROCEDURE dotnetpagination (
      pindex   IN       NUMBER,
      psql     IN       VARCHAR2,
      psize    IN       NUMBER,
      pcount   OUT      NUMBER,
      v_cur    OUT      types.cursortype
   )
   AS
      v_sql     VARCHAR2 (1000);
      v_count   NUMBER;
      v_plow    NUMBER;
      v_phei    NUMBER;
   BEGIN
--***********************************************************取分页总数
      v_sql := 'select count(*) from (' || psql || ')';

      EXECUTE IMMEDIATE v_sql
                   INTO v_count;

      pcount := CEIL (v_count / psize);
--***********************************************************显示任意页内容
      v_phei := (pindex- 1) * psize + psize;
      v_plow := v_phei - psize + 1;
      --Psql := 'select rownum rn,t.* from cd_ssxl t' ;            --要求必须包含rownum字段
      v_sql :=
            'select * from ('
         || psql
         || ') where rn between '
         || v_plow
         || ' and '
         || v_phei;

      OPEN v_cur FOR v_sql;
   END dotnetpagination;

--**************************************************************************************
   PROCEDURE dotnetpagerecordscount (psqlcount IN VARCHAR2, prcount OUT NUMBER)
   AS
      v_sql       VARCHAR2 (1000);
      v_prcount   NUMBER;
   BEGIN
      v_sql := 'select count(*) from (' || psqlcount || ')';

      EXECUTE IMMEDIATE v_sql
                   INTO v_prcount;

      prcount := v_prcount;                                    --返回记录总数
   END dotnetpagerecordscount;
--**************************************************************************************
END dotnet;

3.