NASHIM_ORACLE: CREATING CALENDAR USING PL/SQL

Calendar
========

This one makes a little calendar:

create or replace
procedure calendar (year in number)
is
--
-- Calendar is a PL/SQL stored procedure that displays a 12-month calendar
-- for the input Gregorian year upon SQL*Plus execution. (Remember to SET
-- SERVEROUTPUT ON, with adequate SIZE, before executing. SET SERVEROUTPUT
-- ON SIZE 1000000 is the max, and will more than accomodate the DBMS_OUTPUT
-- displays).
-- "calproc.sql" Planet Source Code submission.
--
--
-- The Gregorian calendar begins with October 15, 1582.
--
--
-- input: year number(4) optional YYYY or null (sysdate year)
-- range: 1583 through 9999
--
-- output: DBMS_OUTPUT display of 12-month calendar for input year, or
-- DBMS_OUTPUT display of "* input year error *"
--
--
--
-- .
-- .
-- .SUN MON TUE WED THU FRI SAT
-- +----+----+----+----+----+----+----+
-- | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
-- +----+----+----+----+----+----+----+
-- | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
-- +----+----+----+----+----+----+----+
-- | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
-- +----+----+----+----+----+----+----+
-- | 22 | 23 | 24 | 25 | 26 | 27 | 28 |
-- +----+----+----+----+----+----+----+
-- | 29 | 30 | 31 |
-- +----+----+----+
--
--
--
h_year number (4);
ldom number (2);
mname varchar2(9);
dweek number (1);
sgmnt varchar2(5);
sgmnt_cnt number (1) := 0;
begin
h_year := nvl (year, to_char (sysdate, 'YYYY'));
if h_year between 1583 and 9999 then
for month in 1 .. 12 loop
--
-- --------------------------------
-- get number for last day of month
-- --------------------------------
--
ldom := substr (last_day (to_date (h_year || month, 'YYYYMM')), 1, 2);
--
-- --------------
-- get month name
-- --------------
--
mname := to_char (to_date (month, 'MM'), 'Month');
--
-- ---------------------------------------------
-- get day of week number for first day of month
-- ---------------------------------------------
--
dweek := to_char (to_date (h_year || month, 'YYYYMM'), 'D');
--
dbms_output.put_line ('.');
dbms_output.put_line ('.');
dbms_output.put_line ('. ' || rtrim (mname) || ' - ' || h_year);
dbms_output.put_line ('.');
dbms_output.put_line ('.SUN MON TUE WED THU FRI SAT');
dbms_output.put_line ('+----+----+----+----+----+----+----+');
--
for x in 1 .. dweek - 1 loop
dbms_output.put ( '| ');
end loop;
--
for dd in 1 .. ldom loop
if (dd < 10) then
sgmnt := '| ' || dd || ' ';
else
sgmnt := '| ' || dd || ' ';
end if;
sgmnt_cnt := sgmnt_cnt + 1;
if mod (dd + (dweek - 1), 7) = 0 then
dbms_output.put (sgmnt || '|');
dbms_output.new_line;
dbms_output.put_line ('+----+----+----+----+----+----+----+');
sgmnt_cnt := 0;
else
dbms_output.put (sgmnt);
end if;
end loop;
--
if sgmnt_cnt between 1 and 6 then
dbms_output.put ('|');
end if;
--
dbms_output.new_line;
--
if sgmnt_cnt = 1 then
dbms_output.put_line ('+----+');
elsif
sgmnt_cnt = 2 then
dbms_output.put_line ('+----+----+');
elsif
sgmnt_cnt = 3 then
dbms_output.put_line ('+----+----+----+');
elsif
sgmnt_cnt = 4 then
dbms_output.put_line ('+----+----+----+----+');
elsif
sgmnt_cnt = 5 then
dbms_output.put_line ('+----+----+----+----+----+');
elsif
sgmnt_cnt = 6 then
dbms_output.put_line ('+----+----+----+----+----+----+');
end if;
end loop;
else
raise value_error;
end if;
exception
when others then
dbms_output.put_line ('* input year error *');
end calendar;

----------------------------------

This one makes a big calendar:

create or replace
procedure calendar (year in number)
is
--
-- Calendar is a PL/SQL stored procedure that displays a 12-month calendar
-- for the input Gregorian year upon SQL*Plus execution. (Remember to SET
-- SERVEROUTPUT ON, with adequate SIZE, before executing. SET SERVEROUTPUT
-- ON SIZE 1000000 is the max, and will more than accomodate the DBMS_OUTPUT
-- displays). Calendar is a modified version of Sergio Rueda's 7/25/2002
-- "calproc.sql" Planet Source Code submission.
--
--
-- The Gregorian calendar begins with October 15, 1582.
--
--
-- input: year number(4) optional YYYY or null (sysdate year)
-- range: 1583 through 9999
--
-- output: DBMS_OUTPUT display of 12-month calendar for input year, or
-- DBMS_OUTPUT display of "* input year error *"
--
--
--
-- . J A N U A R Y - 2 0 0 6
-- .
-- .SUN MON TUE WED THU FRI SAT
-- +----------+----------+----------+----------+----------+----------+----------+
-- | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
-- | | | | | | | |
-- | | | | | | | |
-- | | | | | | | |
-- +----------+----------+----------+----------+----------+----------+----------+
-- | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
-- | | | | | | | |
-- | | | | | | | |
-- | | | | | | | |
-- +----------+----------+----------+----------+----------+----------+----------+
-- | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
-- | | | | | | | |
-- | | | | | | | |
-- | | | | | | | |
-- +----------+----------+----------+----------+----------+----------+----------+
-- | 22 | 23 | 24 | 25 | 26 | 27 | 28 |
-- | | | | | | | |
-- | | | | | | | |
-- | | | | | | | |
-- +----------+----------+----------+----------+----------+----------+----------+
-- | 29 | 30 | 31 |
-- | | | |
-- | | | |
-- | | | |
-- +----------+----------+----------+
--
--
--
h_year number (4);
d_year varchar2(7);
ldom number (2);
sgmnt varchar2(11);
mname varchar2(9);
lmname number (1);
dweek number (1);
sgmnt_cnt number (1) := 0;
begin
h_year := nvl (year, to_char (sysdate, 'YYYY'));
if h_year between 1583 and 9999 then
d_year := substr (to_char (h_year), 1, 1) || ' ' ||
substr (to_char (h_year), 2, 1) || ' ' ||
substr (to_char (h_year), 3, 1) || ' ' ||
substr (to_char (h_year), 4, 1);
for month in 1 .. 12 loop
--
-- --------------------------------
-- get number for last day of month
-- --------------------------------
--
ldom := substr (last_day (to_date (h_year || month, 'YYYYMM')), 1, 2);
--
-- --------------
-- get month name
-- --------------
--
mname := to_char (to_date (month, 'MM'), 'MONTH');
--
-- ---------------------------------------------
-- get day of week number for first day of month
-- ---------------------------------------------
--
dweek := to_char (to_date (h_year || month, 'YYYYMM'), 'D');
--
lmname := length (rtrim (mname));
dbms_output.put_line ('.');
dbms_output.put_line ('.');
dbms_output.put ('. ');
for x in 1 .. lmname loop
dbms_output.put (substr (mname, x, 1) || ' ');
end loop;
dbms_output.put (' - ' || d_year);
dbms_output.new_line;
dbms_output.put_line ('.');
dbms_output.put_line ('.SUN MON TUE WED THU FRI SAT');
dbms_output.put_line ('+----------+----------+----------+----------+----------+----------+----------+');
--
for x in 1 .. dweek - 1 loop
dbms_output.put ( '| ');
end loop;
--
for dd in 1 .. ldom loop
if (dd < 10) then
sgmnt := '| ' || dd || ' ';
else
sgmnt := '| ' || dd || ' ';
end if;
sgmnt_cnt := sgmnt_cnt + 1;
if mod (dd + (dweek - 1), 7) = 0 then
dbms_output.put (sgmnt || '|');
dbms_output.new_line;
dbms_output.put_line ('| | | | | | | |');
dbms_output.put_line ('| | | | | | | |');
dbms_output.put_line ('| | | | | | | |');
dbms_output.put_line ('+----------+----------+----------+----------+----------+----------+----------+');
sgmnt_cnt := 0;
else
dbms_output.put (sgmnt);
end if;
end loop;
--
if sgmnt_cnt between 1 and 6 then
dbms_output.put ('|');
end if;
--
dbms_output.new_line;
--
if sgmnt_cnt = 1 then
dbms_output.put_line ('| |');
dbms_output.put_line ('| |');
dbms_output.put_line ('| |');
dbms_output.put_line ('+----------+');
elsif
sgmnt_cnt = 2 then
dbms_output.put_line ('| | |');
dbms_output.put_line ('| | |');
dbms_output.put_line ('| | |');
dbms_output.put_line ('+----------+----------+');
elsif
sgmnt_cnt = 3 then
dbms_output.put_line ('| | | |');
dbms_output.put_line ('| | | |');
dbms_output.put_line ('| | | |');
dbms_output.put_line ('+----------+----------+----------+');
elsif
sgmnt_cnt = 4 then
dbms_output.put_line ('| | | | |');
dbms_output.put_line ('| | | | |');
dbms_output.put_line ('| | | | |');
dbms_output.put_line ('+----------+----------+----------+----------+');
elsif
sgmnt_cnt = 5 then
dbms_output.put_line ('| | | | | |');
dbms_output.put_line ('| | | | | |');
dbms_output.put_line ('| | | | | |');
dbms_output.put_line ('+----------+----------+----------+----------+----------+');
elsif
sgmnt_cnt = 6 then
dbms_output.put_line ('| | | | | | |');
dbms_output.put_line ('| | | | | | |');
dbms_output.put_line ('| | | | | | |');
dbms_output.put_line ('+----------+----------+----------+----------+----------+----------+');
end if;
end loop;
else
raise value_error;
end if;
exception
when others then
dbms_output.put_line ('* input year error *');
end calendar;
/

EXEC CALENDAR(2001);

NASHIM_ORACLE

Monday, 29 April 2013

CREATING CALENDAR USING PL/SQL

No comments:

Post a Comment

Pages