T表
datetime area 增值税 营业税 所得税
2008-10-1 八达岭 124 235 345
2008-11-1 西城 3124 2355 2345
....... S表
datetime area subjectname yearvalue
2008-10-1 八达岭 增值税 124
2008-10-1 八达岭 营业税 235
2008-10-1 八达岭 所得税 345
2008-11-1 西城 增值税 3124
2008-11-1 西城 营业税 2355
2008-11-1 西城 所得税 2345
想利用游标从T表转成S表,下面为存储过程,想利用T表的列名当作查询语句的字段名,对表S进行操作
create or replace procedure sxy.P_S_ZX_NEW_IN_XZ(D_Name date) is
begin
--将t_zx_new_in_xz的数据导入S_NEW_ZX_IN_XZ,使用游标的形式进行导入
declare v_name nvarchar2(10);
cursor INSERT_S_NEW_ZX_IN_XZ is
select column_name
from all_tab_columns
where table_name='T_ZX_NEW_IN_XZ' and
column_name not like 'F%' and
column_name <>'AREA' and
column_name <>'DATETIME';
begin
open INSERT_S_NEW_ZX_IN_XZ;
loop
--将游标所指的数据取到各变量中
fetch INSERT_S_NEW_ZX_IN_XZ into v_name;
--当游标所指的数据没有时退出循环(到close INSERT_S_NEW_ZX_IN_XZ)
exit when INSERT_S_NEW_ZX_IN_XZ%notfound;
--插入数据到S_NEW_ZX_IN_XZ表中
insert into s_zx_new_in_xz
(datetime, area, subjectname,yearvalue)
select D_Name,t.Area,d.subjectname,v_name
from t_zx_new_in_xz t,d_subject_zx_new_in_xz d
where Area is not null and datetime=D_Name and d.subjectname=v_name;
end loop;
close INSERT_S_NEW_ZX_IN_XZ;
commit;
end;
end P_S_ZX_NEW_IN_XZ;
datetime area 增值税 营业税 所得税
2008-10-1 八达岭 124 235 345
2008-11-1 西城 3124 2355 2345
....... S表
datetime area subjectname yearvalue
2008-10-1 八达岭 增值税 124
2008-10-1 八达岭 营业税 235
2008-10-1 八达岭 所得税 345
2008-11-1 西城 增值税 3124
2008-11-1 西城 营业税 2355
2008-11-1 西城 所得税 2345
想利用游标从T表转成S表,下面为存储过程,想利用T表的列名当作查询语句的字段名,对表S进行操作
create or replace procedure sxy.P_S_ZX_NEW_IN_XZ(D_Name date) is
begin
--将t_zx_new_in_xz的数据导入S_NEW_ZX_IN_XZ,使用游标的形式进行导入
declare v_name nvarchar2(10);
cursor INSERT_S_NEW_ZX_IN_XZ is
select column_name
from all_tab_columns
where table_name='T_ZX_NEW_IN_XZ' and
column_name not like 'F%' and
column_name <>'AREA' and
column_name <>'DATETIME';
begin
open INSERT_S_NEW_ZX_IN_XZ;
loop
--将游标所指的数据取到各变量中
fetch INSERT_S_NEW_ZX_IN_XZ into v_name;
--当游标所指的数据没有时退出循环(到close INSERT_S_NEW_ZX_IN_XZ)
exit when INSERT_S_NEW_ZX_IN_XZ%notfound;
--插入数据到S_NEW_ZX_IN_XZ表中
insert into s_zx_new_in_xz
(datetime, area, subjectname,yearvalue)
select D_Name,t.Area,d.subjectname,v_name
from t_zx_new_in_xz t,d_subject_zx_new_in_xz d
where Area is not null and datetime=D_Name and d.subjectname=v_name;
end loop;
close INSERT_S_NEW_ZX_IN_XZ;
commit;
end;
end P_S_ZX_NEW_IN_XZ;
(
datetime date,
area varchar(20),
zzs number(10,2),
yys number(10,2),
sds number(10,2)
)
insert into t values (to_date('2008-10-01','yyyy-MM-dd'),'八达岭',124,235,345);
insert into t values (to_date('2008-11-01','yyyy-MM-dd'),'西城',3124,2355,2345)create table s(
datetime date,
area varchar2(40),
subjectname varchar2(50),
yearvalue varchar(50)
)
游标:
declare
v_name varchar2(10);
srow s%rowtype;
v_insertsql varchar2(1000);
cursor c1 is
select column_name
from all_tab_columns
where table_name=upper('t') and
column_name not like 'F%' and
column_name <>'AREA' and
column_name <>'DATETIME';
cursor c2(v_name varchar2) is
select datetime,area,
decode(v_name,null,null,''||v_name||'') as s_name,
v_name,
from t
group by datetime,area,v_name;
begin
open c1;
loop fetch c1 into v_name;
if c1%found then
open c2(v_name);
loop
fetch c2 into srow;
if c2%found then
DBMS_output.put_line('area:'||srow.area);
v_insertsql:='insert into s values('''||srow.datetime||''','''||srow.area||''','''||srow.subjectname||''','||srow.yearvalue||')';
DBMS_output.put_line('v_insertsql:'||v_insertsql);
-- EXECUTE IMMEDIATE (v_insertsql); --EXECUTE IMMEDIATE ('COMMIT');
else
exit;
end if;
end loop;
close c2;
else
exit;
end if;
end loop;
close c1;
end;
declare
v_name varchar2(10);
srow s%rowtype;
v_insertsql varchar2(1000);
cursor c1 is
select column_name
from all_tab_columns
where table_name=upper('t') and
column_name not like 'F%' and
column_name <>'AREA' and
column_name <>'DATETIME';
c2 sys_refcursor;
begin
open c1;
loop
fetch c1 into v_name;
if c1%found then
open c2 for ' select datetime,area,
decode('||v_name||',null,null,'''||v_name||''') as s_name,
'||v_name||'
from t
group by datetime,area,'||v_name||'';
loop
fetch c2 into srow;
if c2%found then
DBMS_output.put_line('area:'||srow.area);
v_insertsql:='insert into s values('''||srow.datetime||''','''||srow.area||''','''||srow.subjectname||''','||srow.yearvalue||')';
DBMS_output.put_line('v_insertsql:'||v_insertsql);
-- EXECUTE IMMEDIATE (v_insertsql); --EXECUTE IMMEDIATE ('COMMIT');
else
exit;
end if;
end loop;
close c2;
else
exit;
end if;
end loop;
close c1;
end;