| procedure [message #108955] |
Sun, 20 February 2005 13:53  |
zach
Messages: 7
Registered: January 2005
|
Junior Member |
|
|
i need to create a procedure that calculates the number of days between two dates that the user enters.
create or replace procedure Day_Between (p_day1 in date, p_day2 in date, p_daybetween out number) is
v_day1 date;
v_day2 date;
v_daybetween number;
begin
select to_date(lpad(to_char(sv_day_1),2,'0')||lpad(to_char(sv_month_1),2,'0')||sv_year_1, 'DD-MM-YYYY')
into v_day1 from dual;
select to_date(lpad(to_char(sv_day_2),2,'0')||lpad(to_char(sv_month_2),2,'0')||sv_year_2, 'DD-MM-YYYY')
into v_day2 from dual;
select (v_day2-v_day1) into v_daybetween from dual
where v_day1 = p_day1
and v_day2 = p_day2
and v_daybetween = p_daybetween;
dbms_output.put_line('The number of days between '||p_daybetween);
end;
and if i have to create an unnamed block in order to test the procedure, where should I declare it?
user will enter two dates: day, month, year respectively.
|
|
|
|
| Re: procedure [message #108958 is a reply to message #108955] |
Sun, 20 February 2005 17:05  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
This question should have been posted in the SQL and PL/SQL newbies forum, not in the Java and XML forum. You are making it more complicated than it needs to be. I have demonstrated below how to do this in SQL, in an anonymous PL/SQL block, in a PL/SQL stored procedure, and using a function. In the procedure, I have used both an out parameter and dbms_output to display the results, although only one or the other is necessary.
-- sql:
scott@ORA92> select to_date ('&day2', 'dd-mm-yyyy') - to_date ('&day1', 'dd-mm-yyyy') as days_between
2 from dual
3 /
Enter value for day2: 20-02-2005
Enter value for day1: 14-02-2005
DAYS_BETWEEN
------------
6
-- anonymous pl/sql block:
scott@ORA92> set serveroutput on
scott@ORA92> declare
2 v_daybetween number;
3 begin
4 v_daybetween := to_date ('&day2', 'dd-mm-yyyy') - to_date ('&day1', 'dd-mm-yyyy');
5 dbms_output.put_line ('The number of days between is: ' || v_daybetween);
6 end;
7 /
Enter value for day2: 20-02-2005
Enter value for day1: 14-02-2005
The number of days between is: 6
PL/SQL procedure successfully completed.
-- pl/sql stored procedure:
scott@ORA92> create or replace procedure Day_Between
2 (p_day1 in varchar2,
3 p_day2 in varchar2,
4 p_daybetween out number)
5 is
6 begin
7 p_daybetween := to_date (p_day2, 'dd-mm-yyyy') - to_date (p_day1, 'dd-mm-yyyy');
8 dbms_output.put_line ('The number of days between ' || p_daybetween);
9 end Day_Between;
10 /
Procedure created.
scott@ORA92> show errors
No errors.
scott@ORA92> set serveroutput on
scott@ORA92> variable g_days_between number
scott@ORA92> execute Day_Between ('&day1', '&day2', :g_days_between)
Enter value for day1: 14-02-2005
Enter value for day2: 20-02-2005
The number of days between 6
PL/SQL procedure successfully completed.
scott@ORA92> print g_days_between
G_DAYS_BETWEEN
--------------
6
-- or a function:
scott@ORA92> create or replace function days_between
2 (p_day1 in varchar2,
3 p_day2 in varchar2)
4 return number
5 as
6 begin
7 return to_date (p_day2, 'dd-mm-yyyy') - to_date (p_day1, 'dd-mm-yyyy');
8 end days_between;
9 /
Function created.
scott@ORA92> show errors
No errors.
scott@ORA92> select days_between ('&day1', '&day2') as days_between from dual
2 /
Enter value for day1: 14-02-2005
Enter value for day2: 20-02-2005
DAYS_BETWEEN
------------
6
scott@ORA92>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
