看到网上的一个下例子,我机器上执行时报错了
CREATE TYPE books_nt IS TABLE OF book%ROWTYPE;
CREATE OR REPLACE PROCEDURE add_books (
books_in IN books_nt)
IS
BEGIN
FORALL book_index
IN books_in.FIRST .. books_in.LAST
INSERT INTO book
VALUES books_in(book_index);
end;
pls-00302:必须说明'first'组件
望高手帮忙解释一下
FORALL book_index
IN books_in.FIRST .. books_in.LAST
什么意思
CREATE TYPE books_nt IS TABLE OF book%ROWTYPE;
CREATE OR REPLACE PROCEDURE add_books (
books_in IN books_nt)
IS
BEGIN
FORALL book_index
IN books_in.FIRST .. books_in.LAST
INSERT INTO book
VALUES books_in(book_index);
end;
pls-00302:必须说明'first'组件
望高手帮忙解释一下
FORALL book_index
IN books_in.FIRST .. books_in.LAST
什么意思
为了提高运行效率,我们就可以使用forall来进行数据批量绑定,具体用法:
/*FORALL book_index
IN books_in.FIRST .. books_in.LAST
什么意思*/
--first:book_index是个变量
forall book_index in 下边界..上边界
--then:
执行插入语句。--可以按理解for循环来理解 forall
这个能执行成功?
给你个例子,希望能帮你理解!CREATE OR REPLACE PROCEDURE add_books
IS
BEGIN
FORALL i IN 1..100
INSERT INTO book VALUES books_in(i);
end;
create table book(
id varchar2(20),
name varchar2(10)
)create or replace type books_nt is object
( id varchar2(20),
name varchar2(10)
)
类型创建成功
CREATE OR REPLACE PROCEDURE add_books (
books_in IN books_nt)
IS
BEGIN
FORALL book_index
IN books_in.FIRST .. books_in.LAST
INSERT INTO book
VALUES books_in(book_index);
end;
pls-00302:必须说明'first'组件
CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15));
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- 10-char job title
INSERT INTO emp2 VALUES(30, 'Analyst');
INSERT INTO emp2 VALUES(30, 'Analyst');
Comit;
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
UPDATE emp2 SET job = job || ' (temp)'
WHERE deptno = depts(j);
-- raises a "value too large" exception
EXCEPTION
WHEN OTHERS THEN
COMMIT;
END;
/
PL/SQL procedure successfully completed
SQL> select * from emp2;
CREATE TYPE books_nt IS TABLE OF book%ROWTYPE;
books_in books_nt;
CREATE OR REPLACE PROCEDURE add_books (books_in IN books_nt)
IS
BEGIN
FORALL book_index IN books_in.count
INSERT INTO book VALUES books_in(book_index);
end;
create or replace type books_nt is object
( id varchar2(20),
name varchar2(10)
)
类型创建成功
CREATE OR REPLACE PROCEDURE add_books (
books_in IN books_nt)
IS
BEGIN
FORALL book_index
IN books_in.FIRST .. books_in.LAST
INSERT INTO book
VALUES books_in(book_index);
end;
pls-00302:必须说明'first'组件
first是指一个集合的第一个元素,如果是表的话 就是第一行记录而你的books_in并不是一个集合,只是一行数据 所以
create table myuser as select * from all_users;DECLARE
TYPE AllUsersTab IS TABLE OF all_users%ROWTYPE;
all_users_recs AllUsersTab;
CURSOR c1 IS
SELECT username, user_id, created
FROM all_users WHERE user_id <= 20;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO all_users_recs;
FORALL I IN all_users_recs.FIRST .. all_users_recs.LAST
INSERT INTO myuser VALUES all_users_recs(i);
END;
/