CREATE PROCEDURE Page_Pagination
(
@TableName nvarchar(500)='',--表名,如 Pdm_UserID
@PageSize int =10,--每页的记录数,默认为 10
@CurPage int =1,--表示当前页 1
@KeyField nvarchar(30)='ID',--关键字段名,默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段
@KeyAscDesc nvarchar(4)='asc',--关键字的升、降序,默认为升序 ASC , 降序为 DESC
@Fields nvarchar(500)='*',--所选择的列名,默认为全选
@Condition nvarchar(4000)='',--where 条件,默认为空
@Order nvarchar(50)=''--排序条件,默认为空
--@RecordCount int output --RecordCount 传出参数
)
as
declare @SQL nvarchar(4000)
declare @count int
if @TableName = ''
begin
raiserror('请指定表名!',11,1)
return
end if @PageSize <=0 or @CurPage <0
begin
raiserror('当前页数和每页的记录数都必须大于零!',11,1)
return
end
/* if @RecordCount = -1 or @RecordCount is null
begin
set @SQL = 'select @count = Count(1) from ' + @TableName + @Condition
EXECUTE sp_executesql @SQL,N'@count int out',@RecordCount out
end
*/ if @KeyAscDesc = 'desc'
set @KeyAscDesc = '<'
else
set @KeyAscDesc = '>' if @Condition <> ''
set @Condition = ' where ' + @Condition set @SQL = ''
if @CurPage = 1
set @SQL = @SQL + 'select Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' from ' +
@TableName + @Condition + @Order
else
begin
declare @iTopNum int
set @iTopNum = @PageSize * (@CurPage - 1)
set @SQL = @SQL + 'declare @sLastValue nvarchar(100)' + char(13)
set @SQL = @SQL + 'select Top ' + cast(@iTopNum as nvarchar(20)) + ' @sLastValue=' + @KeyField + ' from ' +
@TableName + @Condition + @Order + char(13)
declare @Condition2 nvarchar(200)
if @Condition = ''
set @Condition2 = ' where ' + @KeyField + @KeyAscDesc + '@sLastValue '
else
set @Condition2 = ' and ' + @KeyField + @KeyAscDesc + '@sLastValue '
set @SQL = @SQL + 'select Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' from ' +
@TableName + @Condition + @Condition2 + @Order
end
EXECUTE sp_executesql @SQL
-- print @SQL把上面存储过程改成Oracle版的
(
@TableName nvarchar(500)='',--表名,如 Pdm_UserID
@PageSize int =10,--每页的记录数,默认为 10
@CurPage int =1,--表示当前页 1
@KeyField nvarchar(30)='ID',--关键字段名,默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段
@KeyAscDesc nvarchar(4)='asc',--关键字的升、降序,默认为升序 ASC , 降序为 DESC
@Fields nvarchar(500)='*',--所选择的列名,默认为全选
@Condition nvarchar(4000)='',--where 条件,默认为空
@Order nvarchar(50)=''--排序条件,默认为空
--@RecordCount int output --RecordCount 传出参数
)
as
declare @SQL nvarchar(4000)
declare @count int
if @TableName = ''
begin
raiserror('请指定表名!',11,1)
return
end if @PageSize <=0 or @CurPage <0
begin
raiserror('当前页数和每页的记录数都必须大于零!',11,1)
return
end
/* if @RecordCount = -1 or @RecordCount is null
begin
set @SQL = 'select @count = Count(1) from ' + @TableName + @Condition
EXECUTE sp_executesql @SQL,N'@count int out',@RecordCount out
end
*/ if @KeyAscDesc = 'desc'
set @KeyAscDesc = '<'
else
set @KeyAscDesc = '>' if @Condition <> ''
set @Condition = ' where ' + @Condition set @SQL = ''
if @CurPage = 1
set @SQL = @SQL + 'select Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' from ' +
@TableName + @Condition + @Order
else
begin
declare @iTopNum int
set @iTopNum = @PageSize * (@CurPage - 1)
set @SQL = @SQL + 'declare @sLastValue nvarchar(100)' + char(13)
set @SQL = @SQL + 'select Top ' + cast(@iTopNum as nvarchar(20)) + ' @sLastValue=' + @KeyField + ' from ' +
@TableName + @Condition + @Order + char(13)
declare @Condition2 nvarchar(200)
if @Condition = ''
set @Condition2 = ' where ' + @KeyField + @KeyAscDesc + '@sLastValue '
else
set @Condition2 = ' and ' + @KeyField + @KeyAscDesc + '@sLastValue '
set @SQL = @SQL + 'select Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' from ' +
@TableName + @Condition + @Condition2 + @Order
end
EXECUTE sp_executesql @SQL
-- print @SQL把上面存储过程改成Oracle版的
解决方案 »
- Oracle查询五个表的SQL语句
- 哪位大哥帮忙看下这个ORACLE存储过程 ,急
- 关于oracle9i的clob数据类型!!!
- 为什么我得global_names 改称true 从新启动以后又变成false
- 请教一个问题?怎么取出8月25日之前的数据,在没有时间字段的情况下。
- 使用三层嵌套语句对数据进行分页,在9和8下是支持排序的,到了oracle7下就不支持了?
- 如何查存储过程的更改记录?
- 请教关于插入操作的简单问题
- 谁能解释这两个关键字的用法?
- 请各位高手帮助一下!!!
- 检索控制检索结果中的时间格式
- 我在oracle建表的时候,提示no previliges on tablespace tpl
变量赋值用
sql:=
字符串连接把+改成||
还有错误输出改一下,
输入变量,在调用时输入
CREATE PROCEDURE page_pagination (
tablename IN NVARCHAR2, --表名,如 Pdm_UserID
pagesize IN INT, --每页的记录数,默认为 10
curpage IN INT, --表示当前页 1
keyfield IN NVARCHAR2,
--关键字段名,默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段
keyascdesc IN NVARCHAR2,
--关键字的升、降序,默认为升序 ASC , 降序为 DESC
FIELDS IN NVARCHAR2, --所选择的列名,默认为全选
condition IN NVARCHAR2, --where 条件,默认为空
in_order IN NVARCHAR2 --排序条件,默认为空
--RecordCount int output --RecordCount 传出参数
)
AS
l_sql NVARCHAR2 (4000);
l_count INT;
l_keyascdesc VARCHAR2 (200);
l_condition VARCHAR2 (200);
itopnum INT;
condition2 nvarchar (200);
BEGIN
IF tablename = ''
THEN
--raise '请指定表名!';
RETURN;
END IF; IF pagesize <= 0 OR curpage < 0
THEN
--raise '当前页数和每页的记录数都必须大于零!';
RETURN;
END IF; /* if RecordCount = -1 or RecordCount is null
begin
set SQL = 'select count = Count(1) from ' + TableName + Condition
EXECUTE sp_executesql SQL,N'count int out',RecordCount out
end
*/
IF keyascdesc = 'desc'
THEN
l_keyascdesc := '<';
ELSE
l_keyascdesc := '>';
END IF; IF condition <> ''
THEN
l_condition := ' where ' || condition;
END IF; l_sql := ''; IF curpage = 1
THEN
l_sql :=
l_sql
|| 'select Top '
|| TO_CHAR (pagesize)
|| ' '
|| FIELDS
|| ' from '
|| tablename
|| l_condition
|| in_order;
ELSE
itopnum := pagesize * (curpage - 1);
l_sql := l_sql || 'declare sLastValue nvarchar(100)' || CHAR (13);
l_sql :=
l_sql
|| 'select Top '
|| TO_CHAR (itopnum)
|| ' sLastValue='
|| keyfield
|| ' from '
|| tablename
|| l_condition
|| in_order
|| CHAR (13); IF l_condition = ''
THEN
condition2 := ' where ' || keyfield || l_keyascdesc || 'sLastValue ';
ELSE
condition2 := ' and ' || keyfield || l_keyascdesc || 'sLastValue ';
l_sql :=
l_sql
|| 'select Top '
|| TO_CHAR (pagesize)
|| ' '
|| FIELDS
|| ' from '
|| tablename
|| l_condition
|| condition2
|| in_order;
END IF;
END IF;
END;
==================
你没有改好哦,连Top都有,Oracle不支持吧。
涉及到top的
在oracle全是用rownum代替的
不过有排序的话,一般就要再套层sql然后再取rownum<=分页数