declare
cursor cur_prov is select a.prov_cd,a.prov_cd_desc from tb_cde_province a where a.prov_cd between 11 and 65;
v_prov_cd tb_cde_province.prov_cd%type;
v_prov_cd_desc tb_cde_province.prov_cd_desc%type;
v_num number;
v_sql varchar(5000);
begin
open cur_prov;
loop fetch cur_prov into v_prov_cd,v_prov_cd_desc;
exit when cur_prov%notfound;
v_sql := 'select count(*) into '||v_num||' from tb_adr_street_'||v_prov_cd||'';//怎样把查出来的v_prov_cd 作为表名tb_adr_street_的结尾,并把count(*)赋值给变量v_num
execute immediate v_sql;
commit;
dbms_output.put_line(v_prov_cd||' '||v_prov_cd_desc||' 'v_num);
end loop;
end;新手,希望各位多多指教!!!!
cursor cur_prov is select a.prov_cd,a.prov_cd_desc from tb_cde_province a where a.prov_cd between 11 and 65;
v_prov_cd tb_cde_province.prov_cd%type;
v_prov_cd_desc tb_cde_province.prov_cd_desc%type;
v_num number;
v_sql varchar(5000);
begin
open cur_prov;
loop fetch cur_prov into v_prov_cd,v_prov_cd_desc;
exit when cur_prov%notfound;
v_sql := 'select count(*) into '||v_num||' from tb_adr_street_'||v_prov_cd||'';//怎样把查出来的v_prov_cd 作为表名tb_adr_street_的结尾,并把count(*)赋值给变量v_num
execute immediate v_sql;
commit;
dbms_output.put_line(v_prov_cd||' '||v_prov_cd_desc||' 'v_num);
end loop;
end;新手,希望各位多多指教!!!!
改成:
v_sql := 'select count(*) from tb_adr_street_'||v_prov_cd;
execute immediate v_sql into v_num;
v_sql := 'select count(*) into '||v_num||' from tb_adr_street_'||v_prov_cd;
pls-00103:出现符号“V_num”在需要下列之一时:
),*_|=-+</>at
CURSOR cur_prov IS
SELECT a.prov_cd, a.prov_cd_desc
FROM tb_cde_province a
WHERE a.prov_cd BETWEEN 11 AND 65;
v_prov_cd tb_cde_province.prov_cd%TYPE;
v_prov_cd_desc tb_cde_province.prov_cd_desc%TYPE;
v_num NUMBER;
v_sql VARCHAR(5000);
BEGIN
OPEN cur_prov;
LOOP
FETCH cur_prov
INTO v_prov_cd, v_prov_cd_desc;
EXIT WHEN cur_prov%NOTFOUND;
v_sql := 'select count(*) from tb_adr_street_' || v_prov_cd;
EXECUTE IMMEDIATE v_sql
INTO v_num;
COMMIT;
dbms_output.put_line(v_prov_cd || ' ' || v_prov_cd_desc || ' ' ||
v_num); --v_num前面少了连接符||
END LOOP;
END;
/