有高手在吗
提个问题,帮忙下
呵呵
关于动态sql的
-- 动态表名
v_tablename := to_char(sysdate,'yyyymmddhhmiss');
yóu tiáo(491419256) 15:19:00
str_sql:='create table '||v_tablename||'(logindate DATE, categorycode varchar2(30),
comcode varchar2(30),
reportcategory varchar2(30),
cnt number(10))';
execute immediate str_sql; --动态执行DDL语句
yóu tiáo(491419256) 15:19:09
INSERT INTO v_tablename
SELECT t.logindate,
T.CATEGORYCODE,
T.COMCODE,
T.REPORTCATEGORY,
T.CNT
FROM PUB_PERFERENCESTAT T
WHERE T.LOGINDATE = V_BEGINTIME
AND T.DATETYPE = 'W';
我先建了动态表
然后插入数据
有些格式不对
跪求指导
提个问题,帮忙下
呵呵
关于动态sql的
-- 动态表名
v_tablename := to_char(sysdate,'yyyymmddhhmiss');
yóu tiáo(491419256) 15:19:00
str_sql:='create table '||v_tablename||'(logindate DATE, categorycode varchar2(30),
comcode varchar2(30),
reportcategory varchar2(30),
cnt number(10))';
execute immediate str_sql; --动态执行DDL语句
yóu tiáo(491419256) 15:19:09
INSERT INTO v_tablename
SELECT t.logindate,
T.CATEGORYCODE,
T.COMCODE,
T.REPORTCATEGORY,
T.CNT
FROM PUB_PERFERENCESTAT T
WHERE T.LOGINDATE = V_BEGINTIME
AND T.DATETYPE = 'W';
我先建了动态表
然后插入数据
有些格式不对
跪求指导
由于表名是动态的,所以insert语句也应该使用动态sql。
--参考:
declare
v_tablename varchar2(20);
str_sql varchar2(1000);
begin
begin
select to_char(sysdate,'yyyymmddhhmiss')
into v_tablename;
from dual;
str_sql:='create table tb_'||v_tablename||'(
logindate DATE,
categorycode varchar2(30),
comcode varchar2(30),
reportcategory varchar2(30),
cnt number(10)
)';
execute immediate str_sql;
commit;
exception
when others then
rollback;
end;
begin
INSERT INTO v_tablename
SELECT T.logindate,
T.CATEGORYCODE,
T.COMCODE,
T.REPORTCATEGORY,
T.CNT
FROM PUB_PERFERENCESTAT T
WHERE T.LOGINDATE = V_BEGINTIME
AND T.DATETYPE = 'W';
commit;
exception
when others then
rollback;
end;
end;