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);
 colList varchar2(2000);
 rowCount number;
 pageCount number;
 pageId number;
begin
  stmt := 'select count(*) from ' || FromClause || ' where ' || NVL(WhereClause,'1=1');
  execute immediate stmt into rowCount;  OutCount := rowCount;
  
  colList := (case when SelectClause is null or SelectClause = '*' then 't.*'
              else regexp_replace(SelectClause,'([0-9a-zA-Z_#\$]{1,30})','t.\1') end);  stmt := 'select ' || colList || ', row_number() over (' || 'order by ' || NVL(OrderByExpression,'1') || 
          ' ' || AscOrDesc || ') pid from ' || FromClause || ' t where ' || NVL(WhereClause,'1=1');  pageCount := ceil(rowCount/PageSize);
  pageId := (case when PageIndex <= 0 then 1 when PageIndex > pageCount then pageCount else PageIndex end);  stmt := 'select ' || colList || ' from (' || stmt || ') t where pid between ' || to_char((pageId - 1)*PageSize+1) || 
          ' and '|| (case when pageId < pageCount then to_char(pageId*PageSize) else to_char(rowCount) end);--  dbms_output.put_line(stmt);
  open OutCursor for stmt;
end;
/select * from user_errors;