MS SQL分页语句转为oracle语句~

[MattHgh]

MS SQL写的分页语句，例如：
select * from (select top 4 * from (select top 19 a.id,a.title,a.ctime,a.doshow,b.cnname from apw_infoems a,apw_ad b where a.types='1' and a.active='1' and a.creater=b.userid order by a.ctime desc) derivedtbl order by ctime) derivedtbl order by ctime desc
和
select * from (select top 19 * from (select top 19 * from apw_infoems where active='1' and types='1' order by createtime desc) derivedtbl order by createtime) derivedtbl order by createtime desc

这2个语句转为oracle的应该怎么写？
谢谢

[hongqi162]

分页你可以参考这个存储过程

procedure sp_Page(p_PageSize int, --每页记录数
p_PageNo int, --当前页码,从 1 开始
p_SqlSelect varchar2, --查询语句,含排序部分
p_SqlCount varchar2, --获取记录总数的查询语句
p_OutRecordCount out int,--返回总记录数
p_OutCursor out ResultData)
as
v_sql varchar2(3000);
v_count int;
v_heiRownum int;
v_lowRownum int;
begin
----取记录总数
execute immediate p_SqlCount into v_count;
p_OutRecordCount := v_count;
----执行分页查询
v_heiRownum := p_PageNo * p_PageSize;
v_lowRownum := v_heiRownum - p_PageSize +1;

v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| p_SqlSelect ||') A
WHERE rownum <= '|| to_char(v_heiRownum) || '
) B
WHERE rn >= ' || to_char(v_lowRownum) ;
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn

OPEN p_OutCursor FOR v_sql;

end sp_Page;

[MattHgh]

不好意思..
能说下那个MSSQL的语句怎么改吗
这里现在不用存储过程..

[hongqi162]

select * from (select * from (select * from apw_infoems where active='1' and types='1' and rownum<20 order by createtime desc) derivedtbl where rownum<20 order by createtime) derivedtbl order by createtime desc