--写个循环,动态拼接你的创建语句就可以了嘛declare sql_str varchar2(32767); begin sql_str:='create table tab_long ('; for i in 0..471 loop sql_str:=sql_str||' '; sql_str:=sql_str||'v'||lpad(i*5,4,0)||' varchar2(10)'||',' ; end loop; sql_str:=rtrim(sql_str,','); sql_str:=sql_str||')'; execute immediate sql_str; end;
5min会写入数据在里面 只要一个值 就作为一个字段横向装了
啊 你好厉害 不过我得先研究一下 咋看不懂呢 lpad(i*5,4,0) 这个是什么意思哦 高手
lpad(i*5,4,0)表示i*5不足四位的左边补0,如0012,这样就可以保证都是四位
declare sql_str varchar2(32767); begin sql_str:='create table tab_long ('; for i in 0..23 loop for j in 0..11 loop sql_str:=sql_str||'v'||lpad(i,2,0)||lpad(5*j,2,0)||' varchar2(10)'||',' ; end loop; end loop; sql_str:=rtrim(sql_str,','); sql_str:=sql_str||')'; execute immediate sql_str; end;
select to_char(trunc(sysdate)+1*5/24/60,'hh24:mi') from dual; select to_char(trunc(sysdate)+2*5/24/60,'hh24:mi') from dual; .... select to_char(trunc(sysdate)+N*5/24/60,'hh24:mi') from dual;
-- 不需要循环,将这个语句执行的结果再次执行! with a as(select trunc(sysdate) as cdate from dual) select 'crate table TB1(' as c_tb from dual union all select 'v'||to_char(cdate+level*5/1440,'hh24mi')|| (case when to_char(cdate+level*5/1440,'hh24mi')='2355' then ' varchar2(5) ' else ' varchar2(5), ' end) as c_tb from a connect by level<1440/5 union all select ')' from dual;
-- 不需要循环,将这个语句执行的结果再次执行! with a as(select trunc(sysdate) as cdate from dual) select 'create table TB1(' as c_tb from dual union all select 'v'||to_char(cdate+level*5/1440,'hh24mi')|| (case when to_char(cdate+level*5/1440,'hh24mi')='2355' then ' varchar2(5) ' else ' varchar2(5), ' end) as c_tb from a connect by level<1440/5 union all select ');' from dual;
--写个循环,动态拼接你的创建语句就可以了嘛declare
sql_str varchar2(32767);
begin
sql_str:='create table tab_long (';
for i in 0..471 loop
sql_str:=sql_str||' ';
sql_str:=sql_str||'v'||lpad(i*5,4,0)||' varchar2(10)'||',' ;
end loop;
sql_str:=rtrim(sql_str,',');
sql_str:=sql_str||')';
execute immediate sql_str;
end;
啊 你好厉害 不过我得先研究一下 咋看不懂呢 lpad(i*5,4,0) 这个是什么意思哦 高手
lpad(i*5,4,0)表示i*5不足四位的左边补0,如0012,这样就可以保证都是四位
declare
sql_str varchar2(32767);
begin
sql_str:='create table tab_long (';
for i in 0..23 loop
for j in 0..11 loop
sql_str:=sql_str||'v'||lpad(i,2,0)||lpad(5*j,2,0)||' varchar2(10)'||',' ;
end loop;
end loop;
sql_str:=rtrim(sql_str,',');
sql_str:=sql_str||')';
execute immediate sql_str;
end;
select to_char(trunc(sysdate)+2*5/24/60,'hh24:mi') from dual;
....
select to_char(trunc(sysdate)+N*5/24/60,'hh24:mi') from dual;
with a as(select trunc(sysdate) as cdate from dual) select 'crate table TB1(' as c_tb from dual
union all
select
'v'||to_char(cdate+level*5/1440,'hh24mi')||
(case when to_char(cdate+level*5/1440,'hh24mi')='2355' then ' varchar2(5) ' else ' varchar2(5), ' end) as
c_tb from a connect by level<1440/5
union all
select ')' from dual;
with a as(select trunc(sysdate) as cdate from dual) select 'create table TB1(' as c_tb from dual
union all
select
'v'||to_char(cdate+level*5/1440,'hh24mi')||
(case when to_char(cdate+level*5/1440,'hh24mi')='2355' then ' varchar2(5) ' else ' varchar2(5), ' end) as
c_tb from a connect by level<1440/5
union all
select ');' from dual;
名称 是否为空? 类型
----------------------------------------------------------------------------------------- -------- -----------------------------
V0005 VARCHAR2(5)
V0010 VARCHAR2(5)
V0015 VARCHAR2(5)
V0020 VARCHAR2(5)
V0025 VARCHAR2(5)
V0030 VARCHAR2(5)
V0035 VARCHAR2(5)
V0040 VARCHAR2(5)
V0045 VARCHAR2(5)
V0050 VARCHAR2(5)
V0055 VARCHAR2(5)
V0100 VARCHAR2(5)
V0105 VARCHAR2(5)
V0110 VARCHAR2(5)
V0115 VARCHAR2(5)
V0120 VARCHAR2(5)
V0125 VARCHAR2(5)
V0130 VARCHAR2(5)
V0135 VARCHAR2(5)
V0140 VARCHAR2(5)
V0145 VARCHAR2(5)
V0150 VARCHAR2(5)
V0155 VARCHAR2(5)
V0200 VARCHAR2(5)
V0205 VARCHAR2(5)
V0210 VARCHAR2(5)
V0215 VARCHAR2(5)
V0220 VARCHAR2(5)
V0225 VARCHAR2(5)
V0230 VARCHAR2(5)
V0235 VARCHAR2(5)
V0240 VARCHAR2(5)
V0245 VARCHAR2(5)
V0250 VARCHAR2(5)
V0255 VARCHAR2(5)
V0300 VARCHAR2(5)
V0305 VARCHAR2(5)
V0310 VARCHAR2(5)
V0315 VARCHAR2(5)
V0320 VARCHAR2(5)
V0325 VARCHAR2(5)
V0330 VARCHAR2(5)
V0335 VARCHAR2(5)
V0340 VARCHAR2(5)
......
V2145 VARCHAR2(5)
V2150 VARCHAR2(5)
V2155 VARCHAR2(5)
V2200 VARCHAR2(5)
V2205 VARCHAR2(5)
V2210 VARCHAR2(5)
V2215 VARCHAR2(5)
V2220 VARCHAR2(5)
V2225 VARCHAR2(5)
V2230 VARCHAR2(5)
V2235 VARCHAR2(5)
V2240 VARCHAR2(5)
V2245 VARCHAR2(5)
V2250 VARCHAR2(5)
V2255 VARCHAR2(5)
V2300 VARCHAR2(5)
V2305 VARCHAR2(5)
V2310 VARCHAR2(5)
V2315 VARCHAR2(5)
V2320 VARCHAR2(5)
V2325 VARCHAR2(5)
V2330 VARCHAR2(5)
V2335 VARCHAR2(5)
V2340 VARCHAR2(5)
V2345 VARCHAR2(5)
V2350 VARCHAR2(5)
V2355 VARCHAR2(5)eygle@SZTYORA>