通用的分页就不必了 各位大神进来看看
CREATE OR REPLACE PROCEDURE CaseMessagePager
(
pagesize int,--页大小
pageindex int:=1,--页索引
orderfield in varchar2,--排序字段
ordertype in varchar2,--排序类型
tabname in varchar2,--表名
strwhere in varchar2,--条件
returnColunm in varchar2,--返回的列
tolrecord out number,--总记录
tolpage out number --总页数
)
asBEGIN
declare firstsql nvarchar(500)
if(strwhere='')
set firstsql='select mytolrecord=count(*) from '+tabname
else
set firstsql='select mytolrecord=count(*) from '+tabname+' where '+strwhere
exec sp_executesql firstsql,N'mytolrecord int output', tolrecord output
set tolpage=ceiling(tolrecord*1.0/pagesize)
declare sql varchar(1000)
if(strwhere='')
set sql='select top '+convert(varchar(10),pagesize)+' '+returnColunm+' from '
+tabname+' where policycode not in(select top '+convert(varchar(10),(pageindex-1)*pagesize)
+' policycode from '+tabname+' order by policycode asc) order by '+orderfield+' '+ordertypeelse
set sql='select top '+convert(varchar(10),pagesize)+' '+returnColunm+' from '
+tabname+' where ('+strwhere+') and (policycode not in(select top '+convert(varchar(10),(pageindex-1)*pagesize)
+' policycode from '+tabname+' order by policycode asc)) order by '+orderfield+' '+ordertype
print(sql)
exec(sql)
END CaseMessagePager;PROCEDURE ZXDRB.CASEMESSAGEPAGER 编译错误错误:PLS-00103: 出现符号 "IF"在需要下列之一时:
:= ; not null default
character
行:19
文本:if(strwhere='')错误:PLS-00103: 出现符号 "SET"在需要下列之一时:
*
PROCEDURE ZXDRB.CASEMESSAGEPAGER 编译错误错误:PLS-00103: 出现符号 "IF"在需要下列之一时:
:= ; not null default
character
行:19
文本:if(strwhere='')错误:PLS-00103: 出现符号 "SET"在需要下列之一时:
*
PROCEDURE ZXDRB.CASEMESSAGEPAGER 编译错误错误:PLS-00103: 出现符号 "IF"在需要下列之一时:
:= ; not null default
character
行:19
文本:if(strwhere='')错误:PLS-00103: 出现符号 "SET"在需要下列之一时:
*
CREATE OR REPLACE PROCEDURE CaseMessagePager
(
pagesize int,--页大小
pageindex int:=1,--页索引
orderfield in varchar2,--排序字段
ordertype in varchar2,--排序类型
tabname in varchar2,--表名
strwhere in varchar2,--条件
returnColunm in varchar2,--返回的列
tolrecord out number,--总记录
tolpage out number --总页数
)
asBEGIN
declare firstsql nvarchar(500)
if(strwhere='')
set firstsql='select mytolrecord=count(*) from '+tabname
else
set firstsql='select mytolrecord=count(*) from '+tabname+' where '+strwhere
exec sp_executesql firstsql,N'mytolrecord int output', tolrecord output
set tolpage=ceiling(tolrecord*1.0/pagesize)
declare sql varchar(1000)
if(strwhere='')
set sql='select top '+convert(varchar(10),pagesize)+' '+returnColunm+' from '
+tabname+' where policycode not in(select top '+convert(varchar(10),(pageindex-1)*pagesize)
+' policycode from '+tabname+' order by policycode asc) order by '+orderfield+' '+ordertypeelse
set sql='select top '+convert(varchar(10),pagesize)+' '+returnColunm+' from '
+tabname+' where ('+strwhere+') and (policycode not in(select top '+convert(varchar(10),(pageindex-1)*pagesize)
+' policycode from '+tabname+' order by policycode asc)) order by '+orderfield+' '+ordertype
print(sql)
exec(sql)
END CaseMessagePager;PROCEDURE ZXDRB.CASEMESSAGEPAGER 编译错误错误:PLS-00103: 出现符号 "IF"在需要下列之一时:
:= ; not null default
character
行:19
文本:if(strwhere='')错误:PLS-00103: 出现符号 "SET"在需要下列之一时:
*
PROCEDURE ZXDRB.CASEMESSAGEPAGER 编译错误错误:PLS-00103: 出现符号 "IF"在需要下列之一时:
:= ; not null default
character
行:19
文本:if(strwhere='')错误:PLS-00103: 出现符号 "SET"在需要下列之一时:
*
PROCEDURE ZXDRB.CASEMESSAGEPAGER 编译错误错误:PLS-00103: 出现符号 "IF"在需要下列之一时:
:= ; not null default
character
行:19
文本:if(strwhere='')错误:PLS-00103: 出现符号 "SET"在需要下列之一时:
*
解决方案 »
- SQL Loader的两个问题
- 请教一个ORACLE_HOME的配置问题
- 以下mssql建表,用oracle该怎么写?
- [急求] 同一个oracle服务器 上有两个数据库, 请问怎么同时操作两个数据库. 也就是说可以用一条sql 操作两个数据库
- 如何把另一个数据库的冷备份文件恢复成另一个相同的数据库
- 请问怎样向clob类型的字段里插入数据,在线等!
- 谁可以给小弟我提供几个好的ORACLE论坛。谢谢
- 感谢各位大虾来帮帮小弟---------怎样连接到oracle服务器
- 谁能解决这个问题?谢谢了!
- 请问高手在oracle中有没有数据仓库的建模工具啊!!
- 初学oracle job很多不清楚
- select查询问题
(
pagesize INTEGER, --页大小
pageindex INTEGER :=1,--页索引
orderfield IN VARCHAR2,--排序字段
ordertype IN VARCHAR2,--排序类型
tabname IN VARCHAR2,--表名
strwhere IN VARCHAR2,--条件
returnColunm IN VARCHAR2,--返回的列
tolrecord OUT NUMBER,--总记录
tolpage OUT NUMBER --总页数
)
AS
firstsql NVARCHAR2(500);
sqlstr VARCHAR2(1000);
BEGINIF strwhere = '' THEN
firstsql :='select mytolrecord=count(*) from ' || tabname;
ELSE
firstsql :='select mytolrecord=count(*) from ' || tabname || ' where ' || strwhere;
-- exec sp_executesql firstsql,N'mytolrecord int output', tolrecord output
tolpage :=ceiling(tolrecord*1.0/pagesize);
END IF;
IF strwhere='' THEN
sqlstr :='select top '+convert(varchar(10),pagesize)+' '+returnColunm+' from ' ||
tabname || ' where policycode not in(select top ' || convert(varchar(10),(pageindex-1)*pagesize)
|| ' policycode from ' || tabname ||' order by policycode asc) order by '
||orderfield || ' ' ||ordertype;ELSE
sqlstr :='select top ' || convert(varchar(10),pagesize) || ' ' || returnColunm || ' from '
|| tabname || ' where ('+strwhere+') and (policycode not in(select top '
|| convert(varchar(10),(pageindex-1)*pagesize)
|| ' policycode from ' || tabname || ' order by policycode asc)) order by '
|| orderfield || ' ' || ordertype;
END IF;
DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
END
(
pagesize INTEGER, --页大小
pageindex INTEGER :=1,--页索引
orderfield IN VARCHAR2,--排序字段
ordertype IN VARCHAR2,--排序类型
tabname IN VARCHAR2,--表名
strwhere IN VARCHAR2,--条件
returnColunm IN VARCHAR2,--返回的列
tolrecord OUT NUMBER,--总记录
tolpage OUT NUMBER --总页数
)
AS
firstsql NVARCHAR2(500);
sqlstr VARCHAR2(1000);
BEGINIF strwhere is null THEN
firstsql :='select mytolrecord=count(*) from ' || tabname;
ELSE
firstsql :='select mytolrecord=count(*) from ' || tabname || ' where ' || strwhere;
-- exec sp_executesql firstsql,N'mytolrecord int output', tolrecord output
tolpage :=ceil(tolrecord*1.0/pagesize);
END IF;
IF strwhere is null THEN
sqlstr :='select top '+'pagesize'+' '+returnColunm+' from ' ||
tabname || ' where policycode not in(select top ' || (pageindex-1)*pagesize
|| ' policycode from ' || tabname ||' order by policycode asc) order by '
||orderfield || ' ' ||ordertype;ELSE
sqlstr :='select top ' || pagesize || ' ' || returnColunm || ' from '
|| tabname || ' where ('+strwhere+') and (policycode not in(select top '
|| (pageindex-1)*pagesize
|| ' policycode from ' || tabname || ' order by policycode asc)) order by '
|| orderfield || ' ' || ordertype;
END IF;
DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
END;
(
pagesize INTEGER, --页大小
pageindex INTEGER :=1,--页索引
orderfield IN VARCHAR2,--排序字段
ordertype IN VARCHAR2,--排序类型
tabname IN VARCHAR2,--表名
strwhere IN VARCHAR2,--条件
returnColunm IN VARCHAR2,--返回的列
tolrecord OUT NUMBER,--总记录
tolpage OUT NUMBER --总页数
)
AS
firstsql NVARCHAR2(500);
sqlstr VARCHAR2(1000);
vcid INTEGER;
BEGINIF strwhere = '' THEN
firstsql :='select count(*) from ' || tabname;
ELSE
-- 注意这块的写法,其它的参考来写
firstsql :='select count(*) from ' || tabname || ' where ' || strwhere;
vcid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(vcid, firstsql, DBMS_SQL.V7);
tolrecord := DBMS_SQL.execute(vcid);
tolpage :=ceiling(tolrecord*1.0/pagesize);
END IF;
IF strwhere='' THEN
sqlstr :='select top '+convert(varchar(10),pagesize)+' '+returnColunm+' from ' ||
tabname || ' where policycode not in(select top ' || convert(varchar(10),(pageindex-1)*pagesize)
|| ' policycode from ' || tabname ||' order by policycode asc) order by '
||orderfield || ' ' ||ordertype;ELSE
sqlstr :='select top ' || convert(varchar(10),pagesize) || ' ' || returnColunm || ' from '
|| tabname || ' where ('+strwhere+') and (policycode not in(select top '
|| convert(varchar(10),(pageindex-1)*pagesize)
|| ' policycode from ' || tabname || ' order by policycode asc)) order by '
|| orderfield || ' ' || ordertype;
END IF;
DBMS_OUTPUT.PUT_LINE(sqlstr);
EXECUTE IMMEDIATE sqlstr;
END