英文:
Inserting date with procedure in oracle PL/SQL
问题
-- 创建存储过程，向MYTABLE表插入日期数据
CREATE OR REPLACE PROCEDURE FILL_MYTABLE AS
BEGIN
FOR YEAR_IN_NUMBER IN 2000..2100 LOOP
FOR MONTH_IN_NUMBER IN 1..12 LOOP
FOR QUARTER_IN_NUMBER IN 1..4 LOOP
FOR DAY_IN_NUMBER IN 1..31 LOOP
-- 计算日期
DECLARE
CURRENT_DATE1 DATE;
CURRENT_QUARTER VARCHAR2(4);
CURRENT_MONTH NUMBER;
CURRENT_WEEKDAY VARCHAR2(2);
BEGIN
CURRENT_DATE1 := TO_DATE(YEAR_IN_NUMBER || '-' || LPAD(MONTH_IN_NUMBER, 2, '0') || '-' || LPAD(DAY_IN_NUMBER, 2, '0'), 'YYYY-MM-DD');
CURRENT_QUARTER := 'Q' || QUARTER_IN_NUMBER;
CURRENT_MONTH := MONTH_IN_NUMBER;
-- 计算星期几
CURRENT_WEEKDAY := CASE WHEN TO_CHAR(CURRENT_DATE1, 'D') BETWEEN 2 AND 6 THEN 'YES' ELSE 'NO' END;
-- 插入数据到MYTABLE表
INSERT INTO MYTABLE (ID, DATE1, QUARTAL, MONTH, WEEKDAY) VALUES (TO_CHAR(CURRENT_DATE1, 'YYYY'), CURRENT_DATE1, CURRENT_QUARTER, CURRENT_MONTH, CURRENT_WEEKDAY);
END;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END FILL_MYTABLE;
/
英文:
I have to create procedure that will fill in the table MYTABLE with dates from 2000-01-01 to 2100-01-01
CREATE TABLE MYTABLE (
ID VARCHAR2(4),
DATE1 DATE,
QUARTAL VARCHAR2(4),
MONTH NUMBER,
WEEKDAY VARCHAR2(2)
);
ID must be in format yyyy, DATE1 will be dates from 2000-01-01 to 2100-01-01, QUARTAL will either be Q1, Q2, Q3 or Q4, MONTH will be from 1-12, WEEKDAY will show either YES or NO.
答案1
得分: 2
这是你需要的行生成器。以下是示例。
日期格式（只是了解是什么）：
SQL> alter session set nls_date_Format = 'yyyy-mm-dd';
Session altered.
CTE在指定的日期之间创建日历：
SQL> with temp (datum) as
2 (select date '2000-01-01' + level - 1
3 from dual
4 connect by level <= date '2100-01-01' - date '2000-01-01' + 1
5 )
6 select min(datum), max(datum) from temp;
MIN(DATUM) MAX(DATUM)
---------- ----------
2000-01-01 2100-01-01
代码返回值，正如你所解释的。我只提取了一个小的子集，只是为了说明查询返回的值：
SQL> with temp (datum) as
2 (select date '2000-01-01' + level - 1
3 from dual
4 connect by level <= date '2100-01-01' - date '2000-01-01' + 1
5 )
6 select extract(year from datum) as id,
7 datum as date1,
8 to_char(datum, 'q') as quartal,
9 extract(month from datum) as month,
10 case when to_char(datum, 'dy', 'nls_date_language = english') in ('sat', 'sun') then 'N'
11 else 'Y'
12 end as weekday
13 from temp
14 where datum between date '2000-03-25' and date '2000-04-05'
15 order by datum;
ID DATE1 Q MONTH W
---------- ---------- - ---------- -
2000 2000-03-25 1 3 N
2000 2000-03-26 1 3 N
2000 2000-03-27 1 3 Y
2000 2000-03-28 1 3 Y
2000 2000-03-29 1 3 Y
2000 2000-03-30 1 3 Y
2000 2000-03-31 1 3 Y
2000 2000-04-01 2 4 N
2000 2000-04-02 2 4 N
2000 2000-04-03 2 4 Y
2000 2000-04-04 2 4 Y
2000 2000-04-05 2 4 Y
12 rows selected.
SQL>
插入到目标表的部分就留给你了。
英文:
It is row generator you need. Here's example.
Date format (just to know what is what):
SQL> alter session set nls_date_Format = 'yyyy-mm-dd';
Session altered.
CTE creates calendar between dates you specified:
SQL> with temp (datum) as
2 (select date '2000-01-01' + level - 1
3 from dual
4 connect by level <= date '2100-01-01' - date '2000-01-01' + 1
5 )
6 select min(datum), max(datum) from temp;
MIN(DATUM) MAX(DATUM)
---------- ----------
2000-01-01 2100-01-01
Code that returns values, as you explained. I'm extracting only a small subset, just to illustrate values query returns:
SQL> with temp (datum) as
2 (select date '2000-01-01' + level - 1
3 from dual
4 connect by level <= date '2100-01-01' - date '2000-01-01' + 1
5 )
6 select extract(year from datum) as id,
7 datum as date1,
8 to_char(datum, 'q') as quartal,
9 extract(month from datum) as month,
10 case when to_char(datum, 'dy', 'nls_date_language = english') in ('sat', 'sun') then 'N'
11 else 'Y'
12 end as weekday
13 from temp
14 where datum between date '2000-03-25' and date '2000-04-05'
15 order by datum;
ID DATE1 Q MONTH W
---------- ---------- - ---------- -
2000 2000-03-25 1 3 N
2000 2000-03-26 1 3 N
2000 2000-03-27 1 3 Y
2000 2000-03-28 1 3 Y
2000 2000-03-29 1 3 Y
2000 2000-03-30 1 3 Y
2000 2000-03-31 1 3 Y
2000 2000-04-01 2 4 N
2000 2000-04-02 2 4 N
2000 2000-04-03 2 4 Y
2000 2000-04-04 2 4 Y
2000 2000-04-05 2 4 Y
12 rows selected.
SQL>
I'll leave inserting into target table to you.
答案2
得分: 0
以下代码将创建一个完整的日历表，从你想要的任何日期开始，并生成你想要的天数。有两个占位符应该正确放置。在代码中，它们已经设置为从2000年01月01日生成日历到2099年12月31日。
--
-- 创建日历表
--
-- 如果已经存在，删除表
--
--DROP TABLE CALENDAR_TABLE;
--
CREATE TABLE CALENDAR_TABLE AS
WITH
base_calendar AS
(
SELECT CurrDate AS Day_ID,
1 AS Day_Time_Span,
CurrDate AS Day_End_Date,
TO_CHAR(CurrDate,'Day') AS Week_Day_Full,
TO_CHAR(CurrDate,'DY') AS Week_Day_Short,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,
UPPER(TO_CHAR(CurrDate,'Mon') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Month_ID,
TO_CHAR(CurrDate,'Mon') || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,
RTRIM(TO_CHAR(CurrDate,'Month')) || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,
TO_CHAR(CurrDate,'Mon') AS Month_Short,
TO_CHAR(CurrDate,'Month') AS Month_Long,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,
'Q' || UPPER(TO_CHAR(CurrDate,'Q') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Quarter_ID,
TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,
CASE
WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
THEN 1
ELSE 2
END AS half_num_of_year,
CASE
WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
THEN 'H'
|| 1
|| '-'
|| TO_CHAR(CurrDate,'YYYY')
ELSE 'H'
|| 2
|| '-'
|| TO_CHAR(CurrDate,'YYYY')
END AS half_of_year_id,
TO_CHAR(CurrDate,'YYYY') AS Year_ID
FROM
(
SELECT level n,
-- 日历从下一行中日期的后一天开始
TO_DATE('31/12/1999','DD/MM/YYYY') + NUMTODSINTERVAL(level,'DAY') CurrDate
FROM dual
-- 要添加到表中的天数在下一行中
CONNECT BY level <= 36525
)
)
SELECT day_id,
day_time_span,
day_end_date,
week_day_full,
week_day_short,
day_num_of_week,
day_num_of_month,
day_num_of_year,
month_id,
COUNT(*) OVER (PARTITION BY month_id) AS Month_Time_Span,
MAX(day_id) OVER (PARTITION BY month_id) AS Month_End_Date,
month_short_desc,
month_long_desc,
month_short,
month_long,
month_num_of_year,
quarter_id,
COUNT(*) OVER (PARTITION BY quarter_id) AS Quarter_Time_Span,
MAX(day_id) OVER (PARTITION BY quarter_id) AS Quarter_End_Date,
quarter_num_of_year,
half_num_of_year,
half_of_year_id,
COUNT(*) OVER (PARTITION BY half_of_year_id) AS Half_Year_Time_Span,
MAX(day_id) OVER (PARTITION BY half_of_year_id) AS Half_Year_End_Date,
year_id,
COUNT(*) OVER (PARTITION BY year_id) AS Year_Time_Span,
MAX(day_id) OVER (PARTITION BY year_id) AS Year_End_Date
FROM base_calendar
ORDER BY day_id
在创建表之后进行测试
Select Min(DAY_ID) "START_DATE", Max(DAY_ID) "END_DATE", Count(DAY_ID) "NUM_OF_DATES"
From Calendar_Table
START_DATE END_DATE NUM_OF_DATES
---------- --------- ------------
01-JAN-00 31-DEC-99 36525
英文:
The code below will create full calendar table starting any day you want and generating as many days you want. There are 2 placeholders that should be put correctly. In the code they are allready set to generate calendar from 01.01.2000 until 31.12.2099
--
-- Create calendar table
--
-- Drop table if it alreadyy exist
--
--DROP TABLE CALENDAR_TABLE;
--
CREATE TABLE CALENDAR_TABLE AS
WITH
base_calendar AS
(
SELECT CurrDate AS Day_ID,
1 AS Day_Time_Span,
CurrDate AS Day_End_Date,
TO_CHAR(CurrDate,'Day') AS Week_Day_Full,
TO_CHAR(CurrDate,'DY') AS Week_Day_Short,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,
UPPER(TO_CHAR(CurrDate,'Mon') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Month_ID,
TO_CHAR(CurrDate,'Mon') || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,
RTRIM(TO_CHAR(CurrDate,'Month')) || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,
TO_CHAR(CurrDate,'Mon') AS Month_Short,
TO_CHAR(CurrDate,'Month') AS Month_Long,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,
'Q' || UPPER(TO_CHAR(CurrDate,'Q') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Quarter_ID,
TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,
CASE
WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
THEN 1
ELSE 2
END AS half_num_of_year,
CASE
WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
THEN 'H'
|| 1
|| '-'
|| TO_CHAR(CurrDate,'YYYY')
ELSE 'H'
|| 2
|| '-'
|| TO_CHAR(CurrDate,'YYYY')
END AS half_of_year_id,
TO_CHAR(CurrDate,'YYYY') AS Year_ID
FROM
(
SELECT level n,
-- Calendar starts at the day after the date in next line
TO_DATE('31/12/1999','DD/MM/YYYY') + NUMTODSINTERVAL(level,'DAY') CurrDate
FROM dual
-- Tthe number of days to be added to the table in next line
CONNECT BY level <= 36525
)
)
SELECT day_id,
day_time_span,
day_end_date,
week_day_full,
week_day_short,
day_num_of_week,
day_num_of_month,
day_num_of_year,
month_id,
COUNT(*) OVER (PARTITION BY month_id) AS Month_Time_Span,
MAX(day_id) OVER (PARTITION BY month_id) AS Month_End_Date,
month_short_desc,
month_long_desc,
month_short,
month_long,
month_num_of_year,
quarter_id,
COUNT(*) OVER (PARTITION BY quarter_id) AS Quarter_Time_Span,
MAX(day_id) OVER (PARTITION BY quarter_id) AS Quarter_End_Date,
quarter_num_of_year,
half_num_of_year,
half_of_year_id,
COUNT(*) OVER (PARTITION BY half_of_year_id) AS Half_Year_Time_Span,
MAX(day_id) OVER (PARTITION BY half_of_year_id) AS Half_Year_End_Date,
year_id,
COUNT(*) OVER (PARTITION BY year_id) AS Year_Time_Span,
MAX(day_id) OVER (PARTITION BY year_id) AS Year_End_Date
FROM base_calendar
ORDER BY day_id
Test after the table is created
Select Min(DAY_ID) "START_DATE", Max(DAY_ID) "END_DATE", Count(DAY_ID) "NUM_OF_DATES"
From Calendar_Table
START_DATE END_DATE NUM_OF_DATES
---------- --------- ------------
01-JAN-00 31-DEC-99 36525
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论