Oracle分页存储过程
第一次使用oracle,项目需要,写了个分页存储过程,太多不足.
代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--代码风格很像sql server的
create or replace procedure op_page
(
TableName in varchar2,--表名
StrWhere in varchar2,--查询条件,不加where
Fields in varchar2,--查询的字段信息,支持多字段
OrderField in varchar2, --排序字段
OrderBy in varchar2,--排序
PageSize in out number,--页大小
PageIndex in out number,--当前页
TotalPage out number,--总页数
TotalCount out number, --返回记录总数
Value_Cur out pkg_query.cur_query --返回结果集
)
AS
StrSql varchar2(2000):=''; --sql拼接
StartRow number(4);
EndRow number(4);
BEGIN
--获取总记录数
StrSql:='SELECT TO_NUMBER(COUNT(*)) FROM '||TableName; --拼接sql语句
IF StrWhere IS NOT NULL AND StrWhere<>'' THEN
StrSql:=StrSql||' WHERE '||StrWhere;
END IF;
EXECUTE IMMEDIATE StrSql INTO TotalCount; --执行sql语句并返回总记录数
--验证页大小信息
IF PageSize<0 THEN
PageSize:=0;
END IF;
--根据页大小计算总页数
IF MOD(TotalCount,PageSize)=0 THEN
TotalPage:=TotalCount/PageSize;
ELSE
TotalPage:=TotalCount/PageSize+1;
END IF;
--验证页号
IF PageIndex<1 THEN
PageIndex:=1;
END IF;
--实现分页查询
StartRow:=(PageIndex-1)*PageSize+1;
EndRow:=PageIndex*PageSize;
--疑问:当Fields参数传进来是为空或者是NULL时,Fields如何使用默认值呢?
--Default '*'跟:='*'没用啊
IF Fields IS NOT NULL AND Fields<>'' THEN
StrSql:='SELECT '||Fields||' FROM'; --(SELECT A.* ROWNUM R FROM '||')'
ELSE
StrSql:='SELECT * FROM';
END IF;
StrSql:=StrSql||' (SELECT A.*,ROWNUM R FROM ';
StrSql:=StrSql||'(SELECT * FROM '||TableName;
--拼接查询条件
IF StrWhere IS NOT NULL AND StrWhere<>'' THEN
StrSql:=StrSql||' WHRER '||StrWhere;
END IF;
--拼接Order By语句
IF (OrderField IS NOT NULL AND OrderField<>'') OR (OrderBy IS NOT NULL AND OrderBy<>'') THEN
StrSql:=StrSql||' ORDER BY '||OrderField||' '||OrderBy;
END IF;
--拼接查询行数范围
StrSql:=StrSql||') A WHERE ROWNUM<='||EndRow||') B WHERE R>='||StartRow;
DBMS_OUTPUT.put_line(StrSql);
OPEN Value_Cur FOR StrSql;
END op_page;
create or replace procedure op_page
(
TableName in varchar2,--表名
StrWhere in varchar2,--查询条件,不加where
Fields in varchar2,--查询的字段信息,支持多字段
OrderField in varchar2, --排序字段
OrderBy in varchar2,--排序
PageSize in out number,--页大小
PageIndex in out number,--当前页
TotalPage out number,--总页数
TotalCount out number, --返回记录总数
Value_Cur out pkg_query.cur_query --返回结果集
)
AS
StrSql varchar2(2000):=''; --sql拼接
StartRow number(4);
EndRow number(4);
BEGIN
--获取总记录数
StrSql:='SELECT TO_NUMBER(COUNT(*)) FROM '||TableName; --拼接sql语句
IF StrWhere IS NOT NULL AND StrWhere<>'' THEN
StrSql:=StrSql||' WHERE '||StrWhere;
END IF;
EXECUTE IMMEDIATE StrSql INTO TotalCount; --执行sql语句并返回总记录数
--验证页大小信息
IF PageSize<0 THEN
PageSize:=0;
END IF;
--根据页大小计算总页数
IF MOD(TotalCount,PageSize)=0 THEN
TotalPage:=TotalCount/PageSize;
ELSE
TotalPage:=TotalCount/PageSize+1;
END IF;
--验证页号
IF PageIndex<1 THEN
PageIndex:=1;
END IF;
--实现分页查询
StartRow:=(PageIndex-1)*PageSize+1;
EndRow:=PageIndex*PageSize;
--疑问:当Fields参数传进来是为空或者是NULL时,Fields如何使用默认值呢?
--Default '*'跟:='*'没用啊
IF Fields IS NOT NULL AND Fields<>'' THEN
StrSql:='SELECT '||Fields||' FROM'; --(SELECT A.* ROWNUM R FROM '||')'
ELSE
StrSql:='SELECT * FROM';
END IF;
StrSql:=StrSql||' (SELECT A.*,ROWNUM R FROM ';
StrSql:=StrSql||'(SELECT * FROM '||TableName;
--拼接查询条件
IF StrWhere IS NOT NULL AND StrWhere<>'' THEN
StrSql:=StrSql||' WHRER '||StrWhere;
END IF;
--拼接Order By语句
IF (OrderField IS NOT NULL AND OrderField<>'') OR (OrderBy IS NOT NULL AND OrderBy<>'') THEN
StrSql:=StrSql||' ORDER BY '||OrderField||' '||OrderBy;
END IF;
--拼接查询行数范围
StrSql:=StrSql||') A WHERE ROWNUM<='||EndRow||') B WHERE R>='||StartRow;
DBMS_OUTPUT.put_line(StrSql);
OPEN Value_Cur FOR StrSql;
END op_page;
在调试存储过程中,有个小插曲.
我使用PL/SQL连接服务器并调试新写的存储过程,因为拼写Sql语句有错误,在调试到OPEN Value_Cur FOR StrSql;时,PL/SQL就死掉了,改了三次,就死了三次.每一次我都强行把PL/SQL给关掉.当我最后一次把语句改正常时.那个OP_Page就不能再动了,所谓的不能再动是指不管是Create Or Replace也好,drop也好,任何对它的操作都会导致PL/SQL进行死机状态.不知道是啥情况.
后来我估计是因为我前面三次调试未正常退出的情况,导致Oracle服务器挂起了三个调试线程未关而任何对OP_PAGE的操作被DENY或者是啥的,反正偶是不清楚,就是这么猜测.基于这个猜测,然后把Oracle的一个服务给重启了一下(叫OracleService什么的,具体给忘了)就OK了.
到底是怎么回事呢?望有高手能给偶指点一下.3q