ORCL建表题

Question

日历ORCL

Answer 1

假设传入的参数为P_Year,VARCHAR2类型
create table T AS
select ID,P_YEAR AS 年,TO_CHAR(A,'MM') AS 月,TO_CHAR(A,'DD') AS 日
from(
    select ROWNUM ID,TO_DATE(P_YEAR||'-01-01','YYYY-MM-DD')+ROWNUM-1 AS A from DUAL
    CONNECT BY TO_CHAR(TO_DATE(P_YEAR||'-01-01','YYYY-MM-DD')+ROWNUM-1,'YYYY')=P_Year
)

Answer 2

create table daysbiao(
       days    varchar2(10),
       Month1  varchar2(10),
       years   varchar2(10),
       MONTHID varchar2(20)
);create or replace procedure Days_insert(in_years  varchar2
                                         ) 
is
begin
  declare
      x number;
      y number;
      c_days number;
      begin
        x:=0;
        
        while x<=11 loop
          x:=x+1;
             SELECT to_char(LAST_DAY(to_date('2000'||'-'||x,'yyyy-mm')),'dd') into c_days  FROM dual;
             y:=0;
             while y<c_days loop
             y:=y+1;              
            insert into daysbiao(dayid,years,month1,days) values (se_years.nextval,in_years,x,y);
            end loop;
        end loop;
      end;
        commit;
exception
  when others
  then  
rollback;
end ;
begin
  days_insert('2000');
end;

Answer 3

create table daysbiao(
       days    varchar2(10),
       Month1  varchar2(10),
       years   varchar2(10),
      dayid varchar2(20)
);create or replace procedure Days_insert(in_years  varchar2
                                         ) 
is
begin
  declare
      x number;
      y number;
      c_days number;
      begin
        x:=0;
        
        while x<=11 loop
          x:=x+1;
             SELECT to_char(LAST_DAY(to_date(in_years||'-'||x,'yyyy-mm')),'dd') into c_days  FROM dual;
             y:=0;
             while y<c_days loop
             y:=y+1;              
            insert into daysbiao(dayid,years,month1,days) values (se_years.nextval,in_years,x,y);
            end loop;
        end loop;
      end;
        commit;
exception
  when others
  then  
rollback;
end ;
begin
  days_insert('2000');
end;