CREATE OR REPLACE PACKAGE PKG_DGP_TEST
IS
TYPE my_cursor IS REF CURSOR;
PROCEDURE SP_DGP_TEST
(
pageIndex in number,---当前索引页
pageSize in number,----每页行数
tableName in varchar2,-----表名称
tableColumns in varchar2 default '*',-----查询的列名称
orderByColumn in varchar2,-----排序字段和排序类型
strWhere in varchar2,-------where条件
totalPages out number,------总页数
recordCount out number,------返回总记录数
returnRecord out my_cursor----返回的结果集
);
END PKG_DGP_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_DGP_TEST
IS
PROCEDURE SP_DGP_TEST
(
pageIndex in number,---当前索引页
pageSize in number,----每页行数
tableName in varchar2,-----表名称
tableColumns in varchar2 default '*',-----查询的列名称
orderByColumn in varchar2,-----排序字段和排序类型
strWhere in varchar2,-------where条件
totalPages out number,------总页数
recordCount out number,------返回总记录数
returnRecord out pkg_dgp_test----返回的结果集
)
IS
selectSql varchar2(4000);
pageStart number;
pageEnd number;
tempSql varchar2(2000);
BEGIN
IF strWhere IS NOT NULL OR strWhere<>''
THEN
tempSql:='SELECT COUNT(1) FROM '|| tableName ||' WHERE '||strWhere;
ELSE
tempSql:='SELECT COUNT(1) FROM '|| tableName;
END IF;
EXECUTE IMMEDIATE tempSql INTO recordCount;
IF pageSize<0
THEN
pageSize:=0;
END IF;
IF MOD(recordCount,pageSize)=0
THEN
totalPages:=trunc(recordCount,pageSize);
ELSE
totalPages:=trunc(recordCount,pageSize)+1;
END IF;
IF pageIndex<1
THEN
pageIndex:=1;
END IF;
IF pageIndex>recordCount
THEN
pageIndex:=recordCount;
END IF;
pageStart:=pageSize*(pageIndex-1)+1;
pageEnd:=pageSize*pageIndex;
selectSql:='SELECT '|| tableColumns ||' FROM ( SELECT '|| tableColumns ||',ROWNUM RM FROM ( SELECT * FROM '|| tableName ;
IF strWhere IS NOT NULL OR strWhere<>''
THEN
selectSql:=selectSql || ' WHERE ' ||strWhere;
END IF;
IF orderByColumn IS NOT NULL OR orderByColumn<>''
THEN
selectSql:=selectSql|| 'ORDER BY ' ||orderByColumn;
END IF;
selectSql:=selectSql||') T WHERE ROWNUM <='|| pageEnd ||') WHERE RM>=' || pageStart;
OPEN returnRecord FOR selectSql;
END SP_DGP_TEST;
END PKG_DGP_TEST;
-----在PLSQL中调用
BEGIN
PKG_DGP_TEST.SP_DGP_TEST( pageIndex => 2, pageSize => 10, tableName =>'DGP_OBJECTS', tableColumns => '', orderByColumn => '',strWhere => '' );
END报错“出现符号end-of-file”在需要下列之一时符号“;”被替换为end-of-line后继续。大家麻烦帮我看看这个存储过程错在哪里?第一次用写oracle存储过程,弄了好长时间。谢谢!
IS
TYPE my_cursor IS REF CURSOR;
PROCEDURE SP_DGP_TEST
(
pageIndex in out number,---当前索引页
pageSize in out number,----每页行数
tableName in varchar2,-----表名称
tableColumns in varchar2 default '*',-----查询的列名称
orderByColumn in varchar2,-----排序字段和排序类型
strWhere in varchar2,-------where条件
totalPages out number,------总页数
recordCount out number,------返回总记录数
returnRecord out my_cursor----返回的结果集
);
END PKG_DGP_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_DGP_TEST
IS
PROCEDURE SP_DGP_TEST
(
pageIndex in out number,---当前索引页
pageSize in out number,----每页行数
tableName in varchar2,-----表名称
tableColumns in varchar2 default '*',-----查询的列名称
orderByColumn in varchar2,-----排序字段和排序类型
strWhere in varchar2,-------where条件
totalPages out number,------总页数
recordCount out number,------返回总记录数
returnRecord out my_cursor----返回的结果集,上面知道用my_cursor,下面不知道用my_cursor )
IS
selectSql varchar2(4000);
pageStart number;
pageEnd number;
tempSql varchar2(2000);
BEGIN
IF strWhere IS NOT NULL OR strWhere<>''
THEN
tempSql:='SELECT COUNT(1) FROM '|| tableName ||' WHERE '||strWhere;
ELSE
tempSql:='SELECT COUNT(1) FROM '|| tableName;
END IF;
EXECUTE IMMEDIATE tempSql INTO recordCount;
IF pageSize<0
THEN
pageSize:=0;
END IF;
IF MOD(recordCount,pageSize)=0
THEN
totalPages:=trunc(recordCount/pageSize);
ELSE
totalPages:=trunc(recordCount/pageSize)+1;
END IF;
IF pageIndex<1
THEN
pageIndex:=1;
END IF;
IF pageIndex>recordCount
THEN
pageIndex:=recordCount;
END IF;
pageStart:=pageSize*(pageIndex-1)+1;
pageEnd:=pageSize*pageIndex;
selectSql:='SELECT '|| tableColumns ||' FROM ( SELECT '|| tableColumns ||',ROWNUM RM FROM ( SELECT * FROM '|| tableName ;
IF strWhere IS NOT NULL OR strWhere<>''
THEN
selectSql:=selectSql || ' WHERE ' ||strWhere;
END IF;
IF orderByColumn IS NOT NULL OR orderByColumn<>''
THEN
selectSql:=selectSql|| 'ORDER BY ' ||orderByColumn;
END IF;
selectSql:=selectSql||') T WHERE ROWNUM <='|| pageEnd ||') WHERE RM>=' || pageStart;
OPEN returnRecord FOR selectSql;
END SP_DGP_TEST;
END PKG_DGP_TEST;
pageSize in out number,----每页行数
这两个地方为什么要用out呢?不是输入参数吗?
谢谢呀
pageSize in out number,----每页行数
这两个地方为什么要用out呢?不是输入参数吗?
你存储过程有对这两个参数赋值操作。
IF pageSize<0 THEN
pageSize:=0;
END IF;
in out既是输入 又是输出。你的问题是,你知道不知道这段代码是分三次执行的,你要是一起执行,中间加"/"