Sqlserver的存储过程如何变成在Oracle下可以执行的？

Question

CREATE PROCEDURE [dbo].[spTest]  
(  
 @SelectStatement nvarchar(4000),  
 @FromStatement nvarchar(2000),  
 @WhereStatement nvarchar(4000),  
 @OrderByExpression nvarchar(500),   
 @AscOrDesc nvarchar(10),  
 @RecordCount int,  
 @PageSize int,   
 @PageIndex int,   
 @DoCount bit  
)  
  
AS  
  
SET NOCOUNT ON  
  
IF(@DoCount=1)   
 EXEC('SELECT count(*)  FROM '+@FromStatement+' WHERE 1=1 '+@WhereStatement)  
ELSE  
BEGIN  
if isnull(@WhereStatement,'') = '' 
begin
set @WhereStatement = '1=1'
end
else
begin
set @WhereStatement = right(ltrim(@WhereStatement),len(ltrim(@WhereStatement))-3)
end
if isnull(@OrderByExpression,'') = ''
begin
set @OrderByExpression = '1'
end
set @SelectStatement =  'select top 100000000 ' + @SelectStatement
declare @sql1 nvarchar(4000)
declare @sql2 nvarchar(4000)
set @sql1=N'select a.* , identity(int,1,1) as NumberIndex into #tempTablePage1 from (' + @SelectStatement + ' from ' + @FromStatement 
set @sql2 =N' where ' + @WhereStatement + ' order by ' + @OrderByExpression + ' ' + @AscOrDesc +') as a'
+N' declare @nCount int'
+N' declare @nTotalPage int'
+N' declare @PageSize int set @PageSize='+rtrim(@PageSize)
+N' declare @PageIndex int set @PageIndex='+rtrim(@PageIndex)
+N' set @nCount = (select max(NumberIndex) from #tempTablePage1)'
+N' if @nCount % @PageSize > 0 '
+N' set @nTotalPage = @nCount / @PageSize + 1'
+N' else'
+N' set @nTotalPage = @nCount / @PageSize'
+N' if @PageIndex <= 0 '
+N' set @PageIndex = 1'
+N' else if @PageIndex > @nTotalPage'
+N' set @PageIndex = @nTotalPage'
+N' if @PageIndex < @nTotalPage'
+N' select * from #tempTablePage1 where NumberIndex between (@PageIndex - 1)*@PageSize + 1 and @PageIndex*@PageSize'
+N' else if @PageIndex = @nTotalPage'
+N' select * from #tempTablePage1 where NumberIndex between (@PageIndex-1)*@PageSize + 1 and @nCount 'exec(@sql1+@sql2)end
GO

Answer 1

create or replace procedure page_split
(SelectClause varchar2 := null,
 FromClause varchar2,
 WhereClause varchar2 := null,
 OrderByExpression varchar2 := null,
 AscOrDesc varchar2 := null,
 PageSize number,
 PageIndex number,
 OutCount OUT number,
 OutCursor OUT sys_refcursor)
is
 stmt varchar2(8000);
 rowCount number;
 pageCount number;
 pageId number;
 uppers number;
 lowers number;
begin
  stmt := 'select count(*) from ' || FromClause || ' where ' || NVL(WhereClause,'1=1');
  execute immediate stmt into rowCount;  pageCount := CEIL(rowCount/PageSize);
  pageId := (case when PageIndex <= 0 then 1 
            when PageIndex > pageCount then pageCount 
            else PageIndex end);
      
  lowers := (pageId - 1)*PageSize+1;
  uppers := (case when pageId < pageCount then pageId*PageSize else rowCount end);  stmt := 'select * from ('||
          'select t.*,rownum rn '||
          'from ('||
          'select '||NVL(SelectClause,'*')||' from '||FromClause||
          ' where '||NVL(WhereClause,'1=1')||
          ' order by '||NVL(OrderByExpression,'1')||' '||AscOrDesc||') t)'||
          ' where rn between '||TO_CHAR(lowers)||' and '||TO_CHAR(uppers);
  
  OutCount := rowCount;
  --dbms_output.put_line(stmt);
  open OutCursor for stmt;
end;
/