Oracle PL/SQL/Data Type/INTERVAL

Материал из SQL эксперт
Перейти к: навигация, поиск

An assignment to var2

   <source lang="sql">
  

SQL> SQL> DECLARE

 2    var2 INTERVAL YEAR(3) TO MONTH;
 3  BEGIN
 4      -- Shorthand for a 101 year and 3 month interval.
 5      var2 := "101-3";
 6  END;
 7  /

PL/SQL procedure successfully completed.


 </source>
   
  


INTERVAL DAY TO SECOND

   <source lang="sql">
 

SQL> SQL> declare

 2    t1   timestamp := current_timestamp + 1 ;
 3    i1  INTERVAL DAY TO SECOND;
 4  begin
 5    i1 := t1 - current_timestamp;
 6    dbms_output.put_line(i1);
 7  end;
 8  /

+00 23:59:59.640000 PL/SQL procedure successfully completed. SQL> SQL> SQL>


 </source>
   
  


Shorthand for a 101 year and 3 month interval(INTERVAL "101-3" YEAR TO MONTH)

   <source lang="sql">
 

SQL> SQL> DECLARE

 2    var2 INTERVAL YEAR(3) TO MONTH;
 3  BEGIN
 4      
 5      var2 := INTERVAL "101-3" YEAR TO MONTH;
 6  END;
 7  /

PL/SQL procedure successfully completed.


 </source>
   
  


Shorthand for a 101 year and 3 month interval(INTERVAL "101" YEAR)

   <source lang="sql">
 

SQL> SQL> DECLARE

 2    var2 INTERVAL YEAR(3) TO MONTH;
 3  BEGIN
 4      
 5      var2 := INTERVAL "101" YEAR;
 6  END;
 7  /

PL/SQL procedure successfully completed.


 </source>
   
  


Shorthand for a 101 year and 3 month interval(INTERVAL "3" MONTH)

   <source lang="sql">
 

SQL> SQL> DECLARE

 2    var2 INTERVAL YEAR(3) TO MONTH;
 3  BEGIN
 4      -- Shorthand for a 101 year and 3 month interval.
 5      var2 := INTERVAL "3" MONTH;
 6  END;
 7  /

PL/SQL procedure successfully completed.


 </source>
   
  


This script demonstrates the use of the INTERVAL types

   <source lang="sql">
 

SQL> SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE

 2     v_college_deadline TIMESTAMP;
 3  BEGIN
 4     v_college_deadline := TO_TIMESTAMP("06/06/2004", "DD/MM/YYYY") + INTERVAL "12-3" YEAR TO MONTH;
 5
 6     DBMS_OUTPUT.PUT_LINE("My daughter leaves for college in " ||v_college_deadline);
 7  END;
 8  /

My daughter leaves for college in 06-SEP-16 12.00.00.000000 AM PL/SQL procedure successfully completed.


 </source>