學習筆記-----一時間函式
一時間函式[@more@]create or replace procedure test
(
date_beg in varchar2,
date_end in varchar2,
duration_type in varchar2 )
as
beg_date date;
end_date date;
beg_month date;
end_month date;
begin
if to_date(date_beg,'yyyy-mm-dd')>to_date(date_end,'yyyy-mm-dd') then
dbms_output.put_line('beginning date must be earlier than end date');
else
beg_date := to_date(date_beg,'yyyy-mm-dd');
end_date := to_date(date_end,'yyyy-mm-dd');
if duration_type='week' then
while beg_date+6<=end_date loop
if to_char(beg_date,'D')='2' then
while beg_date+6<=end_date loop
insert into test2 values(to_char(beg_date,'yyyy-mm-dd'),to_char(beg_date+6,'yyyy-mm-dd'));
beg_date := beg_date+7;
end loop;
end if;
beg_date := beg_date+1;
end loop;
else
if to_char(beg_date,'dd')='01' then
beg_month := beg_date;
else
beg_month := to_date(to_char(add_months(beg_date,1),'yyyy-mm')||'-01','yyyy-mm-dd');
end if;
if to_char(end_date,'yyyy-mm-dd')=to_char(last_day(end_date),'yyyy-mm-dd') then
end_month := end_date;
else
end_month := to_date(to_char(last_day(add_months(end_date,-1)),'yyyy-mm-dd'),'yyyy-mm-dd');
end if;
if duration_type='month' then
while to_char(beg_month,'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop
insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(beg_month),'yyyy-mm-dd'));
beg_month := add_months(beg_month,1);
end loop;
elsif duration_type='quarter' then
while to_char(add_months(beg_month,2),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop
if to_char(beg_month,'mmdd') in ('0101','0401','0701','1001') then
while to_char(add_months(beg_month,2),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop
insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(add_months(beg_month,2)),'yyyy-mm-dd'));
beg_month := add_months(beg_month,3);
end loop;
end if;
beg_month := add_months(beg_month,1);
end loop;
elsif duration_type='semiyear' then
while to_char(add_months(beg_month,5),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop
if to_char(beg_month,'mmdd') in ('0101','0701') then
while to_char(add_months(beg_month,5),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop
insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(add_months(beg_month,5)),'yyyy-mm-dd'));
beg_month := add_months(beg_month,6);
end loop;
end if;
beg_month := add_months(beg_month,1);
end loop;
end if;
end if;
end if;
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
SQL> exec test('2007-06-01','2009-11-30','semiyear');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TEST2;
BEG_DATE END_DATE
---------- ----------
2007-07-01 2007-12-31
2008-01-01 2008-06-30
2008-07-01 2008-12-31
2009-01-01 2009-06-30
(
date_beg in varchar2,
date_end in varchar2,
duration_type in varchar2 )
as
beg_date date;
end_date date;
beg_month date;
end_month date;
begin
if to_date(date_beg,'yyyy-mm-dd')>to_date(date_end,'yyyy-mm-dd') then
dbms_output.put_line('beginning date must be earlier than end date');
else
beg_date := to_date(date_beg,'yyyy-mm-dd');
end_date := to_date(date_end,'yyyy-mm-dd');
if duration_type='week' then
while beg_date+6<=end_date loop
if to_char(beg_date,'D')='2' then
while beg_date+6<=end_date loop
insert into test2 values(to_char(beg_date,'yyyy-mm-dd'),to_char(beg_date+6,'yyyy-mm-dd'));
beg_date := beg_date+7;
end loop;
end if;
beg_date := beg_date+1;
end loop;
else
if to_char(beg_date,'dd')='01' then
beg_month := beg_date;
else
beg_month := to_date(to_char(add_months(beg_date,1),'yyyy-mm')||'-01','yyyy-mm-dd');
end if;
if to_char(end_date,'yyyy-mm-dd')=to_char(last_day(end_date),'yyyy-mm-dd') then
end_month := end_date;
else
end_month := to_date(to_char(last_day(add_months(end_date,-1)),'yyyy-mm-dd'),'yyyy-mm-dd');
end if;
if duration_type='month' then
while to_char(beg_month,'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop
insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(beg_month),'yyyy-mm-dd'));
beg_month := add_months(beg_month,1);
end loop;
elsif duration_type='quarter' then
while to_char(add_months(beg_month,2),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop
if to_char(beg_month,'mmdd') in ('0101','0401','0701','1001') then
while to_char(add_months(beg_month,2),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop
insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(add_months(beg_month,2)),'yyyy-mm-dd'));
beg_month := add_months(beg_month,3);
end loop;
end if;
beg_month := add_months(beg_month,1);
end loop;
elsif duration_type='semiyear' then
while to_char(add_months(beg_month,5),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop
if to_char(beg_month,'mmdd') in ('0101','0701') then
while to_char(add_months(beg_month,5),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop
insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(add_months(beg_month,5)),'yyyy-mm-dd'));
beg_month := add_months(beg_month,6);
end loop;
end if;
beg_month := add_months(beg_month,1);
end loop;
end if;
end if;
end if;
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
SQL> exec test('2007-06-01','2009-11-30','semiyear');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TEST2;
BEG_DATE END_DATE
---------- ----------
2007-07-01 2007-12-31
2008-01-01 2008-06-30
2008-07-01 2008-12-31
2009-01-01 2009-06-30
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/101162/viewspace-962627/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- js純函式學習筆記（一）JS函式筆記
- async函式學習筆記。函式筆記
- Go 函式 學習筆記Go函式筆記
- 分析函式（學習筆記）函式筆記
- 生成函式 學習筆記函式筆記
- TS學習筆記（四）：函式筆記函式
- JavaScript學習筆記 - 原生函式JavaScript筆記函式
- Golang學習筆記-1.6 函式Golang筆記函式
- Oracle學習筆記（6）——函式Oracle筆記函式
- LoadRunner函式學習筆記函式筆記
- MYSQL學習筆記14: 函式MySql筆記函式
- mysql日期和時間函式學習MySql函式
- 深度學習——loss函式的學習筆記深度學習函式筆記
- python學習筆記（六）——函式Python筆記函式
- OpenCV學習筆記（4）——mixChannels函式OpenCV筆記函式
- OpenCV學習筆記（5）——normalize函式OpenCV筆記ORM函式
- Flutter學習筆記（4）--Dart函式Flutter筆記Dart函式
- C++學習筆記（二）——函式C++筆記函式
- c語言學習筆記===函式C語言筆記函式
- 【C#學習筆記】函式呼叫C#筆記函式
- oracle學習筆記８: 分析函式Oracle筆記函式
- MYSQL學習筆記7: 聚合函式MySql筆記函式
- Shell學習筆記_時間計算筆記
- javascript學習筆記--函式的返回值可以是一個函式JavaScript筆記函式
- PHP 第八週函式學習筆記PHP函式筆記
- 學習筆記：javascript中的Generator函式筆記JavaScript函式
- JavaScript學習筆記（七）—— 再說函式JavaScript筆記函式
- pandas之常用基本函式學習筆記函式筆記
- 【C#學習筆記】函式過載C#筆記函式
- Erlang學習筆記(四)模組與函式筆記函式
- c++學習筆記 -- 建構函式C++筆記函式
- MATLAB學習筆記—多型函式Matlab筆記多型函式
- MYSQL學習筆記15: 數值函式MySql筆記函式
- 工作學習筆記（三）to_char函式筆記函式
- Manim 學習筆記（一）--常用的幾個函式和操作筆記函式
- Objective－C學習筆記－－－建構函式和解構函式Object筆記函式
- Python學習筆記_函式_匯入模組Python筆記函式
- ES6學習筆記(三)【函式，物件】筆記函式物件