CREATE OR REPLACE PROCEDURE prc_test IS
strSQL VARCHAR2(50);
BEGIN
dbms_output.put_line('test');
FOR i in 1..5 LOOP
strSQL := 'insert into mytest(allnumber,vTest)values(:ab,:bc)';
EXECUTE IMMEDIATE strSQL USING 1,2;
END LOOP;
END prc_test;
strSQL VARCHAR2(50);
BEGIN
dbms_output.put_line('test');
FOR i in 1..5 LOOP
strSQL := 'insert into mytest(allnumber,vTest)values(:ab,:bc)';
EXECUTE IMMEDIATE strSQL USING 1,2;
END LOOP;
END prc_test;
书上说可以在FORALL循环内使用EXECUTE IMMEDIATE语句
但是我就是编译通不过,我和书上的写法是一样的
不过书名叫《ORACLE9i PL/SQL》
我用的是8.1.7,是不是这个时候还不支持这种用法
语法:
FORALL index IN lower_bound..upper_bound
sql_statement;
备注:
The index can be referenced only within the FORALL statement and only as a collection subscript.
The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:FORALL index IN lower_bound..upper_bound
sql_statement;
The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range.oracle是这样解释,它只持insert,update,delete语句
1. FORALL不能和execute immediate 子句一起使用.
2. 书上说的你就肯定是对的??? 要看书最好是看oracle的官方文档.
CREATE OR REPLACE TYPE name_array_type IS
VARRAY(10000) of VARCHAR2(50)ename_col NAME_ARRAY_TYPE;vstr := 'INSERT INTO A_TABLE0(FIELD1,FIELD2) VALUES (:tab(i),''ckd'');';EXECUTE IMMEDIATE
'BEGIN
FORALL i IN :first .. :last
' || vstr || '
END;'
USING ename_col.first, ename_col.last, ename_col;