Skip to content

Instantly share code, notes, and snippets.

@binjoo

binjoo/oracle.sql

Last active August 29, 2015 14:04
Show Gist options
  • Save binjoo/b14cf7b96428aceabd30 to your computer and use it in GitHub Desktop.
Save binjoo/b14cf7b96428aceabd30 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. binjoo revised this gist Aug 19, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion oracle.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -11,7 +11,7 @@ begin
    insert into sys_calendar(id, date_name, Week_Name, Week_Type, cal_name, cal_type, Am_Begin, am_end, pm_begin, pm_end)
    values(create_table_id('sys_calendar'),
    date1,
    '星期' || decode(week, 1, '一', 2, '二', 3, '三', 4, '四', 5, '五', 6, '六', 7, '七'),
    '星期' || decode(week, 1, '一', 2, '二', 3, '三', 4, '四', 5, '五', 6, '六', 7, '天'),
    week,
    case when week in (6, 7) then '节假日' else '工作日' end,
    case when week in (6, 7) then 1 else 0 end,
  2. binjoo revised this gist Jul 22, 2014. No changes.
  3. binjoo created this gist Jul 22, 2014.
    23 changes: 23 additions & 0 deletions oracle.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,23 @@
    declare
    i int;
    now date;
    date1 date;
    week int;
    begin
    now := trunc(sysdate);
    for i in 0..2000 loop
    date1 := now + i;
    week := to_number(to_char(date1 - 1, 'd'));
    insert into sys_calendar(id, date_name, Week_Name, Week_Type, cal_name, cal_type, Am_Begin, am_end, pm_begin, pm_end)
    values(create_table_id('sys_calendar'),
    date1,
    '星期' || decode(week, 1, '一', 2, '二', 3, '三', 4, '四', 5, '五', 6, '六', 7, '七'),
    week,
    case when week in (6, 7) then '节假日' else '工作日' end,
    case when week in (6, 7) then 1 else 0 end,
    to_date(to_char(date1, 'yyyy-mm-dd') || ' 08:00:00', 'yyyy-mm-dd hh24:mi:ss'),
    to_date(to_char(date1, 'yyyy-mm-dd') || ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss'),
    to_date(to_char(date1, 'yyyy-mm-dd') || ' 14:00:00', 'yyyy-mm-dd hh24:mi:ss'),
    to_date(to_char(date1, 'yyyy-mm-dd') || ' 18:00:00', 'yyyy-mm-dd hh24:mi:ss'));
    end loop;
    end;