Oracle PL/SQL Tutorial/PL SQL Data Types/INTERVAL
Содержание
- 1 Add 1 year, 2 months, 3 days, 4 hours, 5 minutes, 6.7 seconds
- 2 Add date interval to timestamp
- 3 Adding and subtracting intervals
- 4 Add some days, hours, minutes, and seconds
- 5 Add some years and months
- 6 DAY TO SECOND examples
- 7 Double the interval
- 8 INTERVAL includes two datatypes that allow you to set appropriate durations more precisely
- 9 Interval multiplication
- 10 Interval value expressions
- 11 length of service for INTERVAL YEAR TO MONTH
- 12 Show some interval division
- 13 Storing elapsed time with INTERVAL DAY TO SECOND
- 14 Use INTERVAL types
- 15 workarounds for defining intervals
Add 1 year, 2 months, 3 days, 4 hours, 5 minutes, 6.7 seconds
<source lang="sql">
SQL> SQL> DECLARE
2 hire_date TIMESTAMP WITH TIME ZONE; 3 4 a INTERVAL YEAR TO MONTH; 5 b INTERVAL DAY TO SECOND; 6 BEGIN 7 hire_date := TIMESTAMP "2000-09-01 00:00:00 -5:00"; 8 DBMS_OUTPUT.PUT_LINE(hire_date); 9 10 a := INTERVAL "1-2" YEAR TO MONTH; 11 b := INTERVAL "3 4:5:6.7" DAY TO SECOND; 12 13 14 hire_date := hire_date + a + b + 36/24; 15 DBMS_OUTPUT.PUT_LINE(hire_date); 16 17 18 END; 19 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL></source>
Add date interval to timestamp
<source lang="sql">
SQL> SQL> DECLARE
2 v_college_deadline TIMESTAMP;
3 BEGIN
4 v_college_deadline := TO_TIMESTAMP("06/06/2004", "DD/MM/YYYY")
5 + INTERVAL "12-3" YEAR TO MONTH
6 + INTERVAL "19 9:0:0.0" DAY TO SECOND;
7
8 DBMS_OUTPUT.PUT_LINE("My daughter leaves for college in "
9 ||v_college_deadline);
10 END;
11 /
My daughter leaves for college in 25-SEP-16 09.00.00.000000 AM PL/SQL procedure successfully completed. SQL> SQL></source>
Adding and subtracting intervals
<source lang="sql">
SQL> DECLARE
2 a1 INTERVAL DAY TO SECOND := "2 3:4:5.6"; 3 b1 INTERVAL DAY TO SECOND := "1 1:1:1.1"; 4 5 a2 INTERVAL YEAR TO MONTH := "2-10"; 6 b2 INTERVAL YEAR TO MONTH := "1-1"; 7 8 a3 NUMBER := 3; 9 b3 NUMBER := 1; 10 BEGIN 11 DBMS_OUTPUT.PUT_LINE(a1 - b1); 12 DBMS_OUTPUT.PUT_LINE(a2 - b2); 13 DBMS_OUTPUT.PUT_LINE(a3 - b3); 14 END; 15 /
PL/SQL procedure successfully completed. SQL> SQL></source>
Add some days, hours, minutes, and seconds
<source lang="sql">
SQL> DECLARE
2 hire_date TIMESTAMP WITH TIME ZONE; 3 4 a INTERVAL YEAR TO MONTH; 5 b INTERVAL DAY TO SECOND; 6 BEGIN 7 hire_date := TIMESTAMP "2000-09-01 00:00:00 -5:00"; 8 DBMS_OUTPUT.PUT_LINE(hire_date); 9 10 a := INTERVAL "1-2" YEAR TO MONTH; 11 b := INTERVAL "3 4:5:6.7" DAY TO SECOND; 12 13 hire_date := hire_date + b; 14 DBMS_OUTPUT.PUT_LINE(hire_date); 15 END; 16 /
PL/SQL procedure successfully completed. SQL></source>
Add some years and months
<source lang="sql">
SQL> SQL> DECLARE
2 hire_date TIMESTAMP WITH TIME ZONE; 3 4 a INTERVAL YEAR TO MONTH; 5 b INTERVAL DAY TO SECOND; 6 BEGIN 7 hire_date := TIMESTAMP "2000-09-01 00:00:00 -5:00"; 8 DBMS_OUTPUT.PUT_LINE(hire_date); 9 10 a := INTERVAL "1-2" YEAR TO MONTH; 11 b := INTERVAL "3 4:5:6.7" DAY TO SECOND; 12 13 hire_date := hire_date + a; 14 DBMS_OUTPUT.PUT_LINE(hire_date); 15 16 END; 17 /
PL/SQL procedure successfully completed.</source>
DAY TO SECOND examples
<source lang="sql">
SQL> SQL> DECLARE
2 A INTERVAL YEAR TO MONTH; 3 B INTERVAL YEAR TO MONTH; 4 C INTERVAL DAY TO SECOND; 5 D INTERVAL DAY TO SECOND; 6 BEGIN 7 C := INTERVAL "10 1:02:10.123" DAY TO SECOND; 8 9 END; 10 /
PL/SQL procedure successfully completed. SQL></source>
Double the interval
<source lang="sql">
SQL> SQL> DECLARE
2 B INTERVAL DAY(9) TO SECOND(9); 3 4 FUNCTION double_my_interval (A IN INTERVAL DAY TO SECOND) RETURN INTERVAL DAY TO SECOND 5 IS 6 BEGIN 7 RETURN A * 2; 8 END; 9 BEGIN 10 B := "1 0:0:0.123456789"; 11 DBMS_OUTPUT.PUT_LINE(B); 12 DBMS_OUTPUT.PUT_LINE(double_my_interval(B)); 13 END; 14 /
PL/SQL procedure successfully completed. SQL></source>
INTERVAL includes two datatypes that allow you to set appropriate durations more precisely
<source lang="sql">
declare
variable1_int INTERVAL YEAR[(precision)] TO MONTH;
begin
NULL;
end;</source>
Interval multiplication
<source lang="sql">
SQL> SQL> DECLARE
2 a1 INTERVAL DAY TO SECOND := "2 3:4:5.6"; 3 a2 INTERVAL YEAR TO MONTH := "2-10"; 4 a3 NUMBER := 3; 5 BEGIN 6 7 DBMS_OUTPUT.PUT_LINE(a1 * 2); 8 DBMS_OUTPUT.PUT_LINE(a2 * 2); 9 DBMS_OUTPUT.PUT_LINE(a3 * 2); 10 11 END; 12 /
PL/SQL procedure successfully completed. SQL> SQL></source>
Interval value expressions
<source lang="sql">
SQL> SQL> DECLARE
2 A INTERVAL YEAR TO MONTH; 3 B INTERVAL YEAR TO MONTH; 4 C INTERVAL DAY TO SECOND; 5 D INTERVAL DAY TO SECOND; 6 BEGIN 7 A := INTERVAL "40-3" YEAR TO MONTH; 8 B := INTERVAL "40" YEAR; 9 10 END; 11 /
PL/SQL procedure successfully completed. SQL></source>
length of service for INTERVAL YEAR TO MONTH
<source lang="sql">
SQL> SQL> DECLARE
2 start_date DATE;
3 end_date DATE;
4 service_interval INTERVAL YEAR TO MONTH;
5 years_of_service NUMBER;
6 months_of_service NUMBER;
7 BEGIN
8 start_date := TO_DATE("29-DEC-1988","dd-mon-yyyy");
9 end_date := TO_DATE ("26-DEC-1995","dd-mon-yyyy");
10
11 END;
12 /
PL/SQL procedure successfully completed. SQL></source>
Show some interval division
<source lang="sql">
SQL> SQL> DECLARE
2 a1 INTERVAL DAY TO SECOND := "2 3:4:5.6"; 3 a2 INTERVAL YEAR TO MONTH := "2-10"; 4 a3 NUMBER := 3; 5 BEGIN 6 7 DBMS_OUTPUT.PUT_LINE(a1 / 2); 8 DBMS_OUTPUT.PUT_LINE(a2 / 2); 9 DBMS_OUTPUT.PUT_LINE(a3 / 2); 10 END; 11 /
PL/SQL procedure successfully completed. SQL></source>
Storing elapsed time with INTERVAL DAY TO SECOND
<source lang="sql">
SQL> SQL> declare
2 v_start_ts TIMESTAMP:= to_timestamp("14:00:00","HH24:MI:SS");
3 v_end_ts TIMESTAMP:= to_timestamp("15:12:24","HH24:MI:SS");
4 v_delta_int INTERVAL DAY TO SECOND;
5 begin
6 v_delta_int:=v_end_ts-v_start_ts;
7 DBMS_OUTPUT.put_line(v_delta_int);
8 end;
9 /
+00 01:12:24.000000 PL/SQL procedure successfully completed. SQL></source>
Use INTERVAL types
<source lang="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")
5 + INTERVAL "12-3" YEAR TO MONTH;
6
7 DBMS_OUTPUT.PUT_LINE("My daughter leaves for college in "
8 ||v_college_deadline);
9 END;
10 /
My daughter leaves for college in 06-SEP-16 12.00.00.000000 AM PL/SQL procedure successfully completed. SQL> SQL></source>
workarounds for defining intervals
<source lang="sql">
SQL> SQL> DECLARE
2 A INTERVAL YEAR TO MONTH; 3 B INTERVAL YEAR TO MONTH; 4 C INTERVAL DAY TO SECOND; 5 D INTERVAL DAY TO SECOND; 6 BEGIN 7 SELECT INTERVAL "1:02" HOUR TO MINUTE 8 INTO D 9 FROM dual; 10 11 D := INTERVAL "1" HOUR + INTERVAL "02" MINUTE; 12 13 END; 14 /
PL/SQL procedure successfully completed. SQL></source>
