现在有一个sql server的存储过程,要把它转换成Oracle存储进程,哪位大侠知道,最好给出原码,谢谢
SQL存储过程如下:
CREATE PROCEDURE PAGINATION_JHGL
@SQL text, @Curpage int = 1, @PageSize int
AS
SET nocount ON
DECLARE @h int
SET @CurPage = (@CurPage-1)*@PageSize + 1
EXEC sp_cursoropen @h output, @sql, 1
EXEC sp_cursorfetch @h, 16, @CurPage, @PageSize
EXEC sp_cursorclose @h
SET nocount OFF
GO
SQL存储过程如下:
CREATE PROCEDURE PAGINATION_JHGL
@SQL text, @Curpage int = 1, @PageSize int
AS
SET nocount ON
DECLARE @h int
SET @CurPage = (@CurPage-1)*@PageSize + 1
EXEC sp_cursoropen @h output, @sql, 1
EXEC sp_cursorfetch @h, 16, @CurPage, @PageSize
EXEC sp_cursorclose @h
SET nocount OFF
GO
-----因改是个分页
CREATE or replace PROCEDURE PAGINATION_JHGL(sqltext in varchar2,
curpage in number,
pageSize in number) AS
v_sql varchar2(2000);
v_cur sys_refcursor;
v_type youtable%type;
begin
v_sql := 'SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (' ||
sqltext || ') row_ WHERE rownum <= ' || pageSize * curpage ||
') WHERE rownum_ >' pageSize * (curpage - 1));
open cur for v_sql;
loop
fetch cur
into v_type;
exit when cur%notfound;
dbms_output.put_line('ccccc');
end loop;
close cur;
end;
那个pageSize*(curpage-1)有一个警告,麻烦问一下如何修改,谢谢
CREATE or replace PROCEDURE PAGINATION_JHGL(sqltext in varchar2,
curpage in number,
pageSize in number) AS
v_sql varchar2(2000);
v_cur sys_refcursor;
v_type youtable%type------这里指定你*所对应的表哈;
begin
v_sql := 'SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (' ||
sqltext || ') row_ WHERE rownum <= ' || pageSize * curpage ||
') WHERE rownum_ >'|| pageSize * (curpage - 1));
open cur for v_sql;
loop
fetch cur
into v_type;
exit when cur%notfound;
dbms_output.put_line('ccccc');
end loop;
close cur;
end;
as
begin
open cur for 'select * from ('||select a.*,rownum rn from ('||sql||')) where rn between '||(Curpage-1)*PageSize||' and '||Curpage*PageSize;
end;
open后面的cur应该是v_Cur吧,要不然使用了未定义的cur
v_cur sys_refcursor;v_cur out sys_refcursor;下面的cur 的改成v_cur