存储过程分页

create or replace package P_Separate_page as

-- 该包用于对sql语句的分页处理，返回数据集。

--定义一个引用游标类型 t_cur.

type t_cur is ref cursor;

--声明过程get_separate_page，参数的类型为t_cur的引用游标cur.

procedure get_Separate_Page(cur out t_cur,str in varchar2,minrn in number,maxrn in number) ;

--声明函数fget_separate_page,返回类型为游标.

function fget_Separate_Page(str in varchar2,minrn in number,maxrn in number)

return t_cur ;

end P_Separate_page;

/

create or replace package body p_separate_page as

procedure get_Separate_Page(cur out t_cur,str in varchar2,minrn in number,maxrn in number)

is

--** 该过程提供基本SQL的查询分页功能。

--** cur 参数为游标，用于返回结果集，使用了一个定义好的游标类型t_cur，在包P_Sparate_Page中定义。

--** str 为所要分页的SQL语句。

--** minrn 参数为所要分页的初始页码范围。

--** maxrn 参数为所要分页的最大页码范围。

--** 初始版本V1.0,编写者:zhaorupeng,mail:[e-mail]

strstr varchar2(2000) ;

str1 varchar2(2000) ;

condition1 number ;

condition2 number ;

begin

--引用原SQL语句，进行必要的分析处理。

str1 := str ;

-- condition1 为 0 ，说明不包含 *，非0则包含*

condition1 := (instr(str1,'*',1,1)) ;

-- condition2 为 0 ，说明无where,非0则包含where

condition2 := (instr(str1,'where',1,1)) ;

-----------该过程处理以下几种可能会出现的SQL语句------------

-- 1. 指定了字段名，无where条件查询的SQL

-- select uniqueid from table ; |condition1 = 0 ,condition2 = 0

-- 2. 查询所有字段，使用*，无where条件查询的句型

-- select * from table; |condition2 != 0 ,condition2 = 0

-- 3. 指定了字段名，且有where条件查询的句型

-- select uniqueid from table where uniqueid = '20' ;|condition1 = 0,condition2 != 0

-- 4. 查询所有字段，使用*，且有where条件查询的句型

-- select * from table where uniqueid = '20' ; |condition1 != 0,conition2 != 0

if condition1 = 0 and condition2 = 0 then

str1 := replace(str1,'from',',rownum rn from ') ;

elsif condition1 != 0 and condition2 = 0 then

str1 := replace(str1,'*','c.*,rownum rn ') ;

str1 := str1||' c' ;

elsif condition1 = 0 and condition2 != 0 then

str1 := replace(str1,'from',',rownum rn from ') ;

elsif condition1 !=0 and condition2 !=0 then

str1 := replace(str1,'*','c.*,rownum rn ') ;

str1 := replace(str1,'where','c where') ;

end if ;

--dbms_output.put_line(str1) ;

strstr := 'select * from ('|| str1||') where rn between '|| minrn ||' and '|| maxrn;

--dbms_output.put_line(strstr) ;

open cur for strstr ;

-- open cur for select * from (select a.*,rownum rn from t_hie_d_indexinfo a) where rn between minrn and maxrn ;

end get_Separate_Page; --过程get_Separate_Page结束。

-- 定义函数fget_Separate_Page ，返回数据集。

function fget_Separate_Page(str in varchar2,minrn in number,maxrn in number) return t_cur

is

cur t_cur;

strstr varchar2(2000) ;

str1 varchar2(2000) ;

condition1 number ;

condition2 number ;

begin

--引用原SQL语句，进行必要的分析处理。

str1 := str ;

-- condition1 为 0 ，说明不包含 *，非0则包含*

condition1 := (instr(str1,'*',1,1)) ;

-- condition2 为 0 ，说明无where,非0则包含where

condition2 := (instr(str1,'where',1,1)) ;

-----------该过程处理以下几种可能会出现的SQL语句------------

-- 1. 指定了字段名，无where条件查询的SQL

-- select uniqueid from table ; |condition1 = 0 ,condition2 = 0

-- 2. 查询所有字段，使用*，无where条件查询的句型

-- select * from table; |condition1 != 0 ,condition2 = 0

-- 3. 指定了字段名，且有where条件查询的句型

-- select uniqueid from table where uniqueid = '20' ;|condition1 = 0,condition2 != 0

-- 4. 查询所有字段，使用*，且有where条件查询的句型

-- select * from table where uniqueid = '20' ; |condition1 != 0,conition2 != 0

if condition1 = 0 and condition2 = 0 then

str1 := replace(str1,'from',',rownum rn from ') ;

elsif condition1 != 0 and condition2 = 0 then

str1 := replace(str1,'*','c.*,rownum rn ') ;

str1 := str1||' c' ;

elsif condition1 = 0 and condition2 != 0 then

str1 := replace(str1,'from',',rownum rn from ') ;

elsif condition1 !=0 and condition2 !=0 then

str1 := replace(str1,'*','c.*,rownum rn ') ;

str1 := replace(str1,'where','c where') ;

end if ;

--dbms_output.put_line(str1) ;

strstr := 'select * from ('|| str1||') where rn between '|| minrn ||' and '|| maxrn;

--dbms_output.put_line(strstr) ;

open cur for strstr ;

-- open cur for select * from (select a.*,rownum rn from t_hie_d_indexinfo a) where rn between minrn and maxrn ;

return cur ;

end fget_Separate_Page;

end P_Separate_Page;

/

-- 正式版本分页查询过程

create or replace procedure Separate_Page(cur out p_separate_page.t_cur,str in varchar2,minrn in number,maxrn in number)

as

-- 正式版本分页查询过程 --

--** 该过程提供基本SQL的查询分页功能。

--** cur 参数为游标，用于返回结果集，使用了一个定义好的游标类型，在pkg_t_hie_d_indexinfo包中定义。

--** str 为所要分页的SQL语句。

--** minrn 参数为所要分页的初始页码范围。

--** maxrn 参数为所要分页的最大页码范围。

--** 初始版本V1.0,编写者:zhaorupeng,mail:[e-mail]

strstr varchar2(2000) ;

str1 varchar2(2000) ;

condition1 number ;

condition2 number ;

begin

--引用原SQL语句，进行必要的分析处理。

str1 := str ;

-- condition1 为 0 ，说明不包含 *，非0则包含*

condition1 := (instr(str1,'*',1,1)) ;

-- condition2 为 0 ，说明无where,非0则包含where

condition2 := (instr(str1,'where',1,1)) ;

-----------该过程处理以下几种可能会使用的SQL语句------------

-- 1. 指定了字段名，无where条件查询的SQL

-- select uniqueid from table ; |condition1 = 0 ,condition2 = 0

-- 2. 查询所有字段，使用*，无where条件查询的句型

-- select * from table; |condition1 != 0 ,condition2 = 0

-- 3. 指定了字段名，且有where条件查询的句型

-- select uniqueid from table where uniqueid = '20' ;|condition1 = 0,condition2 != 0

-- 4. 查询所有字段，使用*，且有where条件查询的句型

-- select * from table where uniqueid = '20' ; |condition1 != 0,conition2 != 0

if condition1 = 0 and condition2 = 0 then

str1 := replace(str1,'from',',rownum rn from ') ;

elsif condition1 != 0 and condition2 = 0 then

str1 := replace(str1,'*','c.*,rownum rn ') ;

str1 := str1||' c' ;

elsif condition1 = 0 and condition2 != 0 then

str1 := replace(str1,'from',',rownum rn from ') ;

elsif condition1 !=0 and condition2 !=0 then

str1 := replace(str1,'*','c.*,rownum rn ') ;

str1 := replace(str1,'where','c where') ;

end if ;

--dbms_output.put_line(str1) ;

strstr := 'select * from ('|| str1||') where rn between '|| minrn ||' and '|| maxrn;

--dbms_output.put_line(strstr) ;

open cur for strstr ;

-- open cur for select * from (select a.*,rownum rn from t_hie_d_indexinfo a) where rn between minrn and maxrn ;

end Separate_Page;

/

create or replace package pkg_t_hie_d_indexinfo as

--定义一个返回类型为表table_test的记录结构的引用游标类型t_test

--type t_test is ref cursor return t_hie_d_indexinfo%rowtype;

--声明过程，参数是类型为t_test的引用游标cur，传出参数

type t_test is ref cursor;

procedure t_hie_d_indexinfocur(str in varchar2,minrn in number,maxrn in number,cur out t_test) ;

end pkg_t_hie_d_indexinfo;

/

create or replace procedure t_hie_d_indexinfo_t2(cur out pkg_t_hie_d_indexinfo.t_test,str in varchar2,minrn in number,maxrn in number)

as

--** 该过程提供基本SQL的查询分页功能。

--** cur 参数为游标，用于返回结果集，使用了一个定义好的游标类型，在pkg_t_hie_d_indexinfo包中定义。

--** str 为所要分页的SQL语句。

--** minrn 参数为所要分页的初始页码范围。

--** maxrn 参数为所要分页的最大页码范围。

--** 初始版本V1.0,编写者:zhaorupeng,mail:[e-mail]

strstr varchar2(2000) ;

str1 varchar2(2000) ;

condition1 number ;

condition2 number ;

begin

--引用原SQL语句，进行必要的分析处理。

str1 := str ;

-- condition1 为 0 ，说明不包含 *，非0则包含*

condition1 := (instr(str1,'*',1,1)) ;

-- condition2 为 0 ，说明无where,非0则包含where

condition2 := (instr(str1,'where',1,1)) ;

-----------该过程处理以下几种可能会使用的SQL语句------------

-- 1. 指定了字段名，无where条件查询的SQL

-- select uniqueid from table ; |condition1 = 0 ,condition2 = 0

-- 2. 查询所有字段，使用*，无where条件查询的句型

-- select * from table; |condition1 != 0 ,condition2 = 0

-- 3. 指定了字段名，且有where条件查询的句型

-- select uniqueid from table where uniqueid = '20' ;|condition1 = 0,condition2 != 0

-- 4. 查询所有字段，使用*，且有where条件查询的句型

-- select * from table where uniqueid = '20' ; |condition1 != 0,conition2 != 0

if condition1 = 0 and condition2 = 0 then

str1 := replace(str1,'from',',rownum rn from ') ;

elsif condition1 != 0 and condition2 = 0 then

str1 := replace(str1,'*','c.*,rownum rn ') ;

str1 := str1||' c' ;

elsif condition1 = 0 and condition2 != 0 then

str1 := replace(str1,'from',',rownum rn from ') ;

elsif condition1 !=0 and condition2 !=0 then

str1 := replace(str1,'*','c.*,rownum rn ') ;

str1 := replace(str1,'where','c where') ;

end if ;

--dbms_output.put_line(str1) ;

strstr := 'select * from ('|| str1||') where rn between '|| minrn ||' and '|| maxrn;

--dbms_output.put_line(strstr) ;

open cur for strstr ;

-- open cur for select * from (select a.*,rownum rn from t_hie_d_indexinfo a) where rn between minrn and maxrn ;

end t_hie_d_indexinfo_t2;

/