create or replace procedure ph(zzjgdm in varchar2 default null,
cjry in varchar2 default null,
startCjrq varchar2 default null,
endCjrq varchar2 default null --,
-- cur_out out pypgisPackage.cjphResultSet
) as v_psql varchar2(3000);
cursor cur_tableNames is
select distinct (ename) from GIS410900000000.Table_Cj;
cursor cur_allCjry is
select * from GIS410900000000.Table_Cjry;
type countSet_cur is ref cursor;
my_cur countSet_cur;
v_gathstate varchar2(30);
v_count number;
begin
for cjry_record in cur_allCjry loop
dbms_output.put_line('采集人' || cjry_record.userid || '-' ||
cjry_record.username);
for table_meta_record in cur_tableNames loop
v_psql := 'select count(*) tcount,gathstate from ' ||
table_meta_record.ename || ' where cjry=''' ||
cjry_record.userid || '''' || 'group by gathstate';
dbms_output.put_line(v_psql);
open my_cur for v_psql;
loop
fetch my_cur into v_gathstate,v_count;
exit when my_cur%notfound;
dbms_output.put_line(table_meta_record.ename || '-----' || v_gathstate ||v_count);
end loop;
end loop;
end loop;
end ph;
/
参数暂时还没有用到,
select distinct (ename) from GIS410900000000.Table_Cj; 这个是存放采集数据的所有表名;
select * from GIS410900000000.Table_Cjry;这张表存的是所有的采集人员;我要查出来采集人员对每张采集表的采集数量, 根据采集状态分组,然后计算每个采集人员对所有采集表的采集量的综合;
现在就是输出就出现执行的时候好像plsql总是挂掉。
cjry in varchar2 default null,
startCjrq varchar2 default null,
endCjrq varchar2 default null --,
-- cur_out out pypgisPackage.cjphResultSet
) as v_psql varchar2(3000);
cursor cur_tableNames is
select distinct (ename) from GIS410900000000.Table_Cj;
cursor cur_allCjry is
select * from GIS410900000000.Table_Cjry;
type countSet_cur is ref cursor;
my_cur countSet_cur;
v_gathstate varchar2(30);
v_count number;
begin
for cjry_record in cur_allCjry loop
dbms_output.put_line('采集人' || cjry_record.userid || '-' ||
cjry_record.username);
for table_meta_record in cur_tableNames loop
v_psql := 'select count(*) tcount,gathstate from ' ||
table_meta_record.ename || ' where cjry=''' ||
cjry_record.userid || '''' || 'group by gathstate';
dbms_output.put_line(v_psql);
open my_cur for v_psql;
loop
fetch my_cur into v_gathstate,v_count;
exit when my_cur%notfound;
dbms_output.put_line(table_meta_record.ename || '-----' || v_gathstate ||v_count);
end loop;
end loop;
end loop;
end ph;
/
参数暂时还没有用到,
select distinct (ename) from GIS410900000000.Table_Cj; 这个是存放采集数据的所有表名;
select * from GIS410900000000.Table_Cjry;这张表存的是所有的采集人员;我要查出来采集人员对每张采集表的采集数量, 根据采集状态分组,然后计算每个采集人员对所有采集表的采集量的综合;
现在就是输出就出现执行的时候好像plsql总是挂掉。
v_psql := 'select count(*) tcount,gathstate from ' ||
table_meta_record.ename || ' a inner join
GIS410900000000.Table_Cjry b on a.cjry = b.userid
group by gathstate';
end loop;