declare
cursor cur_prov_cd is select a.prov_cd from tb_cde_province a where a.prov_cd between 11 and 65 order by a.prov_cd;
v_prov_cd tb_cde_province.prov_cd%type;
v_sql varchar2(5000);
begin
open cur_prov_cd;
loop
fetch cur_prov_cd into v_prov_cd;
exit when cur_prov_cd%notfound;
v_sql:='select * //一个v_prov_cd对应查出tb_org_organization 表中n(n>1)条数据,怎样把这些查出来的数据显示出来,tb_org_organization 表中有四十多列。或者应该用什么实现此功能?
from tb_org_organization a where a.addr_id not in(
select distinct dorplt_id from
(select c.dorplt_id
from tb_adr_dist a,tb_adr_street_'||v_prov_cd||' b,tb_adr_bldg_'||v_prov_cd||' c,tb_adr_unit_'||v_prov_cd||' d
where a.dist_cd=b.dist_cd
and b.strt_id=c.strt_id
and c.dorplt_id=d.dorplt_id
and a.stat_cd=''1''
and b.stat_cd=''1''
and c.stat_cd=''1''
and d.stat_cd=''1''
union all
select c.dorplt_id
from tb_adr_dist a,tb_adr_street_'||v_prov_cd||' b,tb_adr_bldg_'||v_prov_cd||' c
where a.dist_cd=b.dist_cd
and b.strt_id=c.strt_id
and a.stat_cd=''1''
and b.stat_cd=''1''
and c.stat_cd=''1''))';
execute immediate v_sql;
end loop;
end;
cursor cur_prov_cd is select a.prov_cd from tb_cde_province a where a.prov_cd between 11 and 65 order by a.prov_cd;
v_prov_cd tb_cde_province.prov_cd%type;
v_sql varchar2(5000);
begin
open cur_prov_cd;
loop
fetch cur_prov_cd into v_prov_cd;
exit when cur_prov_cd%notfound;
v_sql:='select * //一个v_prov_cd对应查出tb_org_organization 表中n(n>1)条数据,怎样把这些查出来的数据显示出来,tb_org_organization 表中有四十多列。或者应该用什么实现此功能?
from tb_org_organization a where a.addr_id not in(
select distinct dorplt_id from
(select c.dorplt_id
from tb_adr_dist a,tb_adr_street_'||v_prov_cd||' b,tb_adr_bldg_'||v_prov_cd||' c,tb_adr_unit_'||v_prov_cd||' d
where a.dist_cd=b.dist_cd
and b.strt_id=c.strt_id
and c.dorplt_id=d.dorplt_id
and a.stat_cd=''1''
and b.stat_cd=''1''
and c.stat_cd=''1''
and d.stat_cd=''1''
union all
select c.dorplt_id
from tb_adr_dist a,tb_adr_street_'||v_prov_cd||' b,tb_adr_bldg_'||v_prov_cd||' c
where a.dist_cd=b.dist_cd
and b.strt_id=c.strt_id
and a.stat_cd=''1''
and b.stat_cd=''1''
and c.stat_cd=''1''))';
execute immediate v_sql;
end loop;
end;
我基础比较差而且有点愚钝,希望说的仔细点,谢谢。
定义一个游标 result1 sys_refcursor;
打开执行 open result1 for v_sql;
返回游标 return result1;
cursor cur_prov_cd is select a.prov_cd from tb_cde_province a where a.prov_cd between 11 and 65 order by a.prov_cd;
v_prov_cd tb_cde_province.prov_cd%type;
v_sql varchar2(5000);
begin
open cur_prov_cd;
loop
fetch cur_prov_cd into v_prov_cd;
exit when cur_prov_cd%notfound;
v_sql:='select * from tb_org_organization//我想这个动态查出来的数据插入到一个新表tb_org_organization_1中,表结构和tb_org_organization完全一样,应该怎样实现啊?我用select s.* into tb_org_organization_1 from tb_org_organization为什么不行? a where a.addr_id not in(
select distinct dorplt_id from
(select c.dorplt_id
from tb_adr_dist a,tb_adr_street_'||v_prov_cd||' b,tb_adr_bldg_'||v_prov_cd||' c,tb_adr_unit_'||v_prov_cd||' d
where a.dist_cd=b.dist_cd
and b.strt_id=c.strt_id
and c.dorplt_id=d.dorplt_id
and a.stat_cd=''1''
and b.stat_cd=''1''
and c.stat_cd=''1''
and d.stat_cd=''1''
union all
select c.dorplt_id
from tb_adr_dist a,tb_adr_street_'||v_prov_cd||' b,tb_adr_bldg_'||v_prov_cd||' c
where a.dist_cd=b.dist_cd
and b.strt_id=c.strt_id
and a.stat_cd=''1''
and b.stat_cd=''1''
and c.stat_cd=''1''))';
execute immediate v_sql;
end loop;
end;
--declare中需增加以下定义
type v_org_table is table of tb_org_organization%rowtype index by pls_integer;
t_org_table v_org_table;
i int;--execute immediate v_sql;这边修改成以下:
execute immediate v_sql bulk collect into t_org_table;
forall i in t_org_table.first..t_org_table.last
insert into tb_org_organization_1 values t_org_table(i);