如何将一个小查询从SQL Server转换为Oracle，我不知道如何将其等效物应用于保留的词语中？

Question

我一直与SQL Server合作，现在我想学习Oracle，如何将此小查询从SQL Server转换为Oracle？

我在此查询中有错误：

with cte as, OFFSET @start ROWS FETCH ,
and exists(select 1 from cte where cte.id_city = h.id_city),
(dateadd(second, h.days, h.datehome) >= @from and dateadd(second, h.days, h.datehome) <= @to)

我不知道Oracle等效物是什么。

这是我的完整查询

CREATE OR REPLACE 
PACKAGE PKG_HOME AS 

CREATE PROCEDURE SP_HOME_DATA (
from            in timestamp,
to              in timestamp,
start           number,                
page            number    
)                          


AS
BEGIN   
 
    with cte as (
            select   isnull(h.id_city,'') as id_city
                     from main_bdd.home h  
                     left join second_bdd.owner.clients cl on cl.idHome = h.idHome                  
                     where ((dateadd(second, h.days, h.datehome) >= @from and dateadd(second, h.days, h.datehome) <= @to))
                     order by h.datehome asc                 
                     OFFSET @start ROWS FETCH NEXT @page ROWS ONLY
        )

        select 
         
                     isnull(h.id_city,'') as id_city
                     isnull(h.last_ubication,'') as last_ubication            
                     from main_bdd.home h  
                     left join second_bdd.owner.clients cl on cl.idHome = h.idHome 
                     where ((dateadd(second, h.days, h.datehome) >= @from and dateadd(second, h.days, h.datehome) <= @to))
                     AND exists(select 1 from cte where cte.id_city = h.id_city)
                     order by h.datehome asc 
END; 
END PKG_HOME;

Answer 1

您想要类似的东西（尽管second_bdd.owner.clients看起来不错，因为它应该为schema_name.table_name schema_name.table_name.something_else_else_else ）：

CREATE PACKAGE PKG_HOME AS
PROCEDURE SP_HOME_DATA (
  i_from   IN  timestamp,
  i_to     IN  timestamp,
  i_start  IN  number,                
  i_page   IN  number,
  o_cursor OUT SYS_REFCURSOR
);
END PKG_HOME;
/

CREATE PACKAGE BODY PKG_HOME AS
PROCEDURE SP_HOME_DATA (
  i_from   IN  timestamp,
  i_to     IN  timestamp,
  i_start  IN  number,                
  i_page   IN  number,
  o_cursor OUT SYS_REFCURSOR
)                          
AS
BEGIN
  OPEN o_cursor FOR
  with cte as (
    SELECT h.id_city
    FROM   main_bdd.home h  
           left join second_bdd.owner.clients cl
           on cl.idHome = h.idHome                  
    WHERE  h.day + INTERVAL '1' SECOND * h.datehome
             BETWEEN i_from AND i_to
    ORDER BY h.datehome asc                 
    OFFSET i_start ROWS FETCH NEXT i_page ROWS ONLY
  )
  SELECT h.id_city,
         h.last_ubication
  FROM   main_bdd.home h  
         LEFT JOIN second_bdd.owner.clients cl
         on cl.idHome = h.idHome 
  WHERE  h.day + INTERVAL '1' SECOND * h.datehome
             BETWEEN i_from AND i_to
  AND    EXISTS(
           select 1 from cte where cte.id_city = h.id_city
         )
  order by h.datehome asc;
END; 
END PKG_HOME;
/

注意：在Oracle '''和null是相同的代码> ISNULL 函数）与value没有任何包装功能。

db＆lt;＆gt; fiddle