Converter Procedure SQL Server para Oracle

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
FernandoFonseca
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Ter, 18 Abr 2006 8:42 am
Localização: Brasilia - DF

Preciso converte o codigo abaixo para PL/SQl, ele esta em T-SQL do SQL Server funciona que é uma maravilha, é uma procedure generica para montar estatistica, tipo cubo de decisao.
Se alguém puder ajudar.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDynamicCrossTab]

@RowValue VARCHAR(255), --what is the SQL for the row title
@ColValue VARCHAR(255), --what is the SQL for the column title
@Aggregate VARCHAR(255), --the aggregation value to go in the cells
@FromExpression VARCHAR(8000), --the FROM, ON and WHERE clause
@colOrderValue VARCHAR (255)=NULL, --how the columns are ordered
@Title VARCHAR(80)='_', --the title to put in the first col of first row
@SortBy VARCHAR(255)='row asc', --what you sort the rows by (column heading)
@RowSort VARCHAR(80)=NULL,
@ReturnTheDDL INT=0,--return the SQL code rather than execute it
@Debugging INT=0 --debugging mode
/*
e.g.
Execute spDynamicCrossTab
@RowValue='firstname+'' ''+lastname',
@ColValue='Year(OrderDate)',
@Aggregate= 'count(*)',
@FromExpression='FROM Employees INNER JOIN Orders
ON (Employees.EmployeeID=Orders.EmployeeID)',
@ColOrderValue='Year(OrderDate)',
@Title ='No. Sales per year',
@SortBy ='total desc' --what you sort the rows by (column heading)

Execute spDynamicCrossTab
@RowValue='firstname+'' ''+lastname',
@ColValue='DATENAME(month,orderDate)',
@Aggregate= 'sum(subtotal)',
@FromExpression='FROM Orders
INNER JOIN "Order Subtotals"
ON Orders.OrderID = "Order Subtotals".OrderID
inner join employees on employees.EmployeeID =orders.EmployeeID',
@ColOrderValue='datepart(month,orderDate)',
@Title ='Customers orders per month '

EXECUTE spDynamicCrossTab
@RowValue='country',
@ColValue='datename(quarter,orderdate)
+case datepart(quarter,orderdate)
when 1 then ''st''
when 2 then ''nd''
when 3 then ''rd''
when 4 then ''th'' end',
@Aggregate= 'sum(subtotal)',
@FromExpression='FROM Orders
INNER JOIN "Order Subtotals"
ON Orders.OrderID = "Order Subtotals".OrderID
inner join customers on customers.customerID =orders.customerID',
@ColOrderValue='datepart(quarter,orderDate)',
@sortby='total desc',
@Title ='value of orders per quarter'

*/
AS
SET nocount ON
DECLARE @Command NVARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)
DECLARE @SQL1 VARCHAR(MAX)

--make sure we have sensible defaults for orders
SELECT @ColOrderValue=COALESCE(@ColOrderValue, @ColValue),
@Sortby=COALESCE(@SortBy,@RowValue),
@rowsort=COALESCE(@RowSort,@RowValue)



--first construct tha SQL which is used to calculate the columns in a
--string
SELECT @Command='select @SQL=coalesce(@SQL,''SELECT
['+@Title+']=case when row is null then ''''Sum''''
else convert(Varchar(80),[row]) end ,
'')+
''[''+convert(varchar(100),'
+@ColValue+')+''] =sum( CASE col WHEN ''''''+convert(varchar(100),'
+@ColValue+')+'''''' THEN data else 0 END ),
'' '+@FromExpression+'
GROUP BY '+@ColValue+'
order by max('+@ColorderValue+')'




--Now we execute the string to obtain the SQL that we will use for the
--crosstab query
EXECUTE sp_ExecuteSQL @command,N'@SQL VARCHAR(MAX) OUTPUT',@SQL OUTPUT

IF @@error > 0 --display the string if there is an error
BEGIN
RAISERROR ( 'offending code was ...%s', 0, 1, @command )
RETURN 1
END
IF @debugging <>0 SELECT @Command
--we now add the rest of the SQL into the string



SELECT @SQL=@SQL+' [Total]= sum( data )
from
(select [row]='+@RowValue+',
[col]='+@ColValue+',
[data]='+@Aggregate+',
[sort]=max('+@rowsort+')
'+@FromExpression+'
GROUP BY '+@RowValue+', '+@ColValue+'
)f
group by row with rollup
order by grouping(row),'+@Sortby

--and execute it
IF @ReturnTheDDL<>0 SELECT @SQL ELSE EXECUTE (@SQL)
IF @@error > 0
BEGIN
RAISERROR ( 'offending code was ...%s', 0, 1, @sql )
RETURN 1
END


A parte negritada é o principal problema
FernandoFonseca
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Ter, 18 Abr 2006 8:42 am
Localização: Brasilia - DF

Bom, Feliz 2008 a todos, como ninguém consegui fazer a conversão, fiz por conta própria agora, disponibilizo o Package para que todos possam "estudar" e quem sabe, numa próxima vez ajudar...

Selecionar tudo

CREATE OR REPLACE PACKAGE          "Schema".PKG_ESTATISTICA_GERAL             
AS

TYPE CURSOR_RESULTS IS REF CURSOR;
PROCEDURE PC_Estatistica_Geral  (rowValue  IN 	VARCHAR2 DEFAULT NULL,
ColValue                   IN      	VARCHAR2 DEFAULT NULL,
sAggregate                  IN      	VARCHAR2 DEFAULT NULL,
FromExpression             IN      	VARCHAR2 DEFAULT NULL,
colOrderValue              IN      	VARCHAR2 DEFAULT null,
Title                      IN      	VARCHAR2 DEFAULT '_',
SortBy                     IN      	VARCHAR2 DEFAULT 'arow asc',
RowSort                    IN      	VARCHAR2 DEFAULT null,
    O_Results   OUT CURSOR_RESULTS
);
END PKG_ESTATISTICA_GERAL;


CREATE OR REPLACE PACKAGE BODY          "Schema".PKG_ESTATISTICA_GERAL
AS                  
PROCEDURE PC_Estatistica_Geral (rowValue  IN 	VARCHAR2 DEFAULT NULL,
ColValue                   IN      	VARCHAR2 DEFAULT NULL,
sAggregate                  IN      	VARCHAR2 DEFAULT NULL,
FromExpression             IN      	VARCHAR2 DEFAULT NULL,
colOrderValue              IN      	VARCHAR2 DEFAULT null,
Title                      IN      	VARCHAR2 DEFAULT '_',
SortBy                     IN      	VARCHAR2 DEFAULT 'arow asc',
RowSort                    IN      	VARCHAR2 DEFAULT null,
    O_Results OUT CURSOR_RESULTS
    )  IS
        SQL_ADV                    VARCHAR2(4000);
       Command                    VARCHAR2(4000); 
       cur_handle                 INTEGER;
       texto                      VARCHAR2(80);
       temp                        NUMBER;
       icursor              sys_refcursor;
BEGIN
       open icursor for 'SELECT trim(' || ColValue ||') as pivot '||FromExpression ||' group by '||Colvalue||' order by max('||NVL(colOrderValue,ColValue)||')';
     
     LOOP        
     FETCH icursor INTO texto;        
     EXIT WHEN icursor%NOTFOUND;        
      Command:=Command||'sum(case trim(acol) when '''||texto||''' then adata else 0 end) as "'||texto||'",';   
     END LOOP;    
     CLOSE icursor;
     
     SQL_ADV:=NVL(SQL_ADV,'SELECT case when arow is null then ''Total'' else to_char(arow) end as "'||Title||'" ,'||Command);
     
     SQL_ADV:=SQL_ADV||'  sum( adata ) as Total
                      from (select '||RowValue||' as arow,'  
                      ||ColValue||' as acol,'  
                      ||sAggregate||'as adata,
                      max('||NVL(RowSort, RowValue)||') as asort '||FromExpression||' GROUP BY '||RowValue||', '||ColValue||' )f 
                      group by rollup(arow) order by grouping(arow),'||NVL(SortBy, RowValue); 
                 
        open O_Results for SQL_ADV;
        --close O_Results;
     EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000,  'Unexpected error:' || sqlerrm);
END PC_Estatistica_Geral;
END PKG_ESTATISTICA_GERAL;
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 7 visitantes