有个学生提问
G3 2015-01-15 13:23:27
for list_init in (select PROV_CODE,PROV_NAME from province)
loop
insert into T_BUSI_MONTH_REPORT (PROV_CODE, PROV_NAME, BUSI_MONTH, BUSI_DAY, BUSI_TYPE, INSERTTIME)
values (list_init.PROV_CODE, list_init.PROV_NAME, busiMonth, busiDay, 'RING', sysdate);
end loop;
commit;
from province如果是个变量,如何写呢?
这种需求比较简单,使用动态sql就可以了
DECLARE
v_str VARCHAR2(1000);
cur_dept SYS_REFCURSOR;
v_dname VARCHAR2(100);
BEGIN
v_str := 'select dname from ' || 'dept';
OPEN cur_dept FOR v_str;
LOOP
FETCH cur_dept
INTO v_dname;
EXIT WHEN cur_dept%NOTFOUND;
dbms_output.put_line(v_dname);
END LOOP;
END;
执行效果如下:
SQL> SET serveroutput ON
SQL> DECLARE
2 v_str VARCHAR2(1000);
3 cur_dept SYS_REFCURSOR;
4 v_dname VARCHAR2(100);
5 BEGIN
6 v_str := 'select dname from ' || 'dept';
7 OPEN cur_dept FOR v_str;
8 LOOP
9 FETCH cur_dept
10 INTO v_dname;
11 EXIT WHEN cur_dept%NOTFOUND;
12 dbms_output.put_line(v_dname);
13 END LOOP;
14 END;
15 /
ACCOUNTING
RESEARCH
SALES
OPERATIONS
PL/SQL procedure successfully completed
SQL>