从SQL Server到Oracle-更改过程并查看语法

2024-04-27 • 问答

我在sql server中有此过程，该过程执行此计算日期的逻辑并返回该信息以供应用程序使用，但是不幸的是，我将不得不切换到oracle数据库，因此希望您的帮助知道我必须更改的语法

P.S：从未使用过oracle数据库

CREATE PROCEDURE [dbo].[SP_ObterDadosApontamentosOperacional]
@dataInicio as dateTime = null,@dataFim as datetime = null,@embarcacaoId as varchar(50) = null

AS
BEGIN

if(@dataInicio is null)
begin
    set @dataInicio = DATEADD(day,DATEDIFF(day,GETDATE()),0)
end

if(@dataFim is null)
begin
    set @dataFim = (DATEADD(ms,-3,DATEADD(dd,DATEDIFF(dd,GetDate()),0)) +1)
end


select 
    codigoOperacao.Id           as 'Id',codigoOperacao.Descricao    as 'Descricao',codigoOperacao.Codigo       as 'CodigoOperacao',count(codigoOperacao.Codigo) as 'TotalUnitario'
into #parcial
from INFO_APONTAMENTO as apontamento
inner join CODIGOS_OPERACAO as codigoOperacao on codigoOperacao.Id = apontamento.CodigosDeOperacao_Id
where apontamento.DataCadastro between @dataInicio and @dataFim
and ((apontamento.Barco_Id = @embarcacaoId) or @embarcacaoId is null)

group by
    codigoOperacao.Codigo,codigoOperacao.Descricao,codigoOperacao.Id   


/*--------------------------------------------*/

   select 
        Id,Descricao,CodigoOperacao,DataInicio,DataTermino,DATEDIFF(MINUTE,DataTermino)
    into #tempApontamentosPorHora
    from #tempApontamentos
    where DataInicio is not null
    and DataTermino is not null

declare @somaTotal as int = (select     sum(totalUnitario) from #parcial)
select *,@somaTotal as TotalAgregado from #parcial

结束

如果您查看Oracle SQL Developer，它具有内置的翻译器。

这是它试图与您的T-SQL一起使用的

/*Global Temporary Tables:2 *//* Translation Extracted DDL For Required Objects*/
CREATE GLOBAL TEMPORARY TABLE tt_parcial
AS (
   SELECT codigoOperacao.Id Id,codigoOperacao.Descricao Descricao,codigoOperacao.Codigo CodigoOperacao,0  TotalUnitario  
     FROM INFO_APONTAMENTO apontamento
     JOIN CODIGOS_OPERACAO codigoOperacao   ON codigoOperacao.Id = apontamento.CodigosDeOperacao_Id
   WHERE 1=2
);
/
CREATE GLOBAL TEMPORARY TABLE tt_tempApontamentosPorHora
AS (
   SELECT Id,Descricao,CodigoOperacao,DataInicio,DataTermino,utils.datediff('MINUTE',DataTermino) 
     FROM tt_tempApontamentos
   WHERE 1=2
);
/



CREATE OR REPLACE PROCEDURE SP_ObterDadosApontamentosOperacional
(
  iv_dataInicio IN DATE DEFAULT NULL,iv_dataFim IN DATE DEFAULT NULL,v_embarcacaoId IN VARCHAR2 DEFAULT NULL 
)
AS
   v_dataInicio DATE := iv_dataInicio;
   v_dataFim DATE := iv_dataFim;
   v_somaTotal NUMBER(10,0) := ( SELECT SUM(totalUnitario)  
     FROM tt_parcial  );
   v_cursor SYS_REFCURSOR;

BEGIN
   IF ( v_dataInicio IS NULL ) THEN

   BEGIN
      v_dataInicio := utils.dateadd('DAY',utils.datediff('DAY',SYSDATE),0) ;

   END;
   END IF;
   IF ( v_dataFim IS NULL ) THEN

   BEGIN
      v_dataFim := (utils.dateadd('MS',-3,utils.dateadd('DD',utils.datediff('DD',0)) + 1) ;

   END;
   END IF;
   DELETE FROM tt_parcial;
   UTILS.IDENTITY_RESET('tt_parcial');

   INSERT INTO tt_parcial ( 
    SELECT codigoOperacao.Id Id,COUNT(codigoOperacao.Codigo)  TotalUnitario  
      FROM INFO_APONTAMENTO apontamento
             JOIN CODIGOS_OPERACAO codigoOperacao   ON codigoOperacao.Id = apontamento.CodigosDeOperacao_Id
     WHERE  apontamento.DataCadastro BETWEEN v_dataInicio AND v_dataFim
              AND ( ( apontamento.Barco_Id = v_embarcacaoId )
              OR v_embarcacaoId IS NULL )
      GROUP BY codigoOperacao.Codigo,codigoOperacao.Descricao,codigoOperacao.Id );
   /*--------------------------------------------*/
   DELETE FROM tt_tempApontamentosPorHora;
   UTILS.IDENTITY_RESET('tt_tempApontamentosPorHora');

   INSERT INTO tt_tempApontamentosPorHora ( 
    SELECT Id,DataTermino) 
      FROM tt_tempApontamentos 
     WHERE  DataInicio IS NOT NULL
              AND DataTermino IS NOT NULL );
   OPEN  v_cursor FOR
      SELECT *,v_somaTotal TotalAgregado  
        FROM tt_parcial  ;
      DBMS_SQL.RETURN_RESULT(v_cursor);

EXCEPTION WHEN OTHERS THEN utils.handleerror(SQLCODE,SQLERRM);
END;

我并不是说这是您的理想答案，甚至不是答案，但是该功能可以帮助您快速入门。

从SQL Server到Oracle的一件大事-我看到的许多SQL Server存储过程只是要运行的查询。

Oracle存储过程并非仅设置为“哑” SQL语句。它旨在处理数据。因此，如果您不希望更改行为，则必须做一件事，您的应用程序将需要获取GET过程的结果，没有运行代码，使用PL / SQL时要返回查询结果。

在12c and higher versions of Oracle where we have added support for implicit results中，这要容易一些，但是您必须添加对DBMS_SQL.RETURN_RESULT的调用才能获取它们。

从SQL Server到Oracle-更改过程并查看语法

xiaoxia20008 回答：从SQL Server到Oracle-更改过程并查看语法

大家都在问