Oracle中的重叠功能 [英] Overlap function in Oracle

问题描述

我正在编写一个软件包来学习Oracle.我想创建一个OVERLAP函数，该函数检查两个日期范围是否相互重叠.

I'm writing a package to learn Oracle. I want to create an OVERLAP function that checks if two date ranges overlap each other.

  FUNCTION OVERLAP(p_START_DATE_1 DATE, p_END_DATE_1 DATE,
   p_START_DATE_2 DATE, p_END_DATE_2 DATE) RETURN VARCHAR2 AS

   lv_RESULT VARCHAR2(1);
  BEGIN 

   lv_RESULT := SELECT 'T' AS overlap FROM dual
       WHERE (p_START_DATE_1, p_END_DATE_1) overlaps (p_START_DATE_2, p_END_DATE_2);

   IF (lv_RESULT = 'T')
     RETURN 'T';

   RETURN 'N';

  END OVERLAP;

我试图执行我的功能，但出现错误ORA-04063: package body 'XYZ' contains errors ...

I tried to execute my function, but getting an error ORA-04063: package body 'XYZ' contains errors...

SELECT KP_XYZ_PACKAGE_SQL.OVERLAP(
       TO_DATE('01/01/2014', 'DD/MM/YYYY'),
       TO_DATE('01/12/2014', 'DD/MM/YYYY'),
       TO_DATE('01/02/2014', 'DD/MM/YYYY'),
       TO_DATE('01/05/2014', 'DD/MM/YYYY'))
FROM DUAL;

我认为SELECT可以正常工作.但是这里出现错误(我想):lv_RESULT := SELECT....为什么?

I think SELECT works fine. But the error occurs (I suppose) here: lv_RESULT := SELECT.... Why?

推荐答案

尝试一下:

CREATE OR REPLACE FUNCTION OVERLAP(p_START_DATE_1 DATE, p_END_DATE_1 DATE,
   p_START_DATE_2 DATE, p_END_DATE_2 DATE) RETURN VARCHAR2 AS

   lv_RESULT VARCHAR2(1);
  BEGIN 
       lv_RESULT := 'N';      

       SELECT 'T' into lv_RESULT FROM dual
           WHERE (p_START_DATE_1, p_END_DATE_1) overlaps (p_START_DATE_2, p_END_DATE_2);

   IF (lv_RESULT = 'T') THEN
     RETURN 'T';
   END IF;

   RETURN 'N';

  END OVERLAP;

IF语句也不完整-THEN和END IF丢失了，我现在已经添加了.

The IF statement was incomplete too - THEN and END IF were missing, which I have now added.

SELECT INTO语句的文档为 这里 .页面末尾有示例的链接.

The documentation for SELECT INTO statement is here. There are links to examples at the end of the page.

这篇关于Oracle中的重叠功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！