Oracle中使用fetch bulk collect into批量读取游标数据
DECLARE
l_return_status VARCHAR2(1);
l_msg_data VARCHAR2(2000);
TYPE contacts_type IS TABLE OF sr_contacts%ROWTYPE;
v_contacts contacts_type;
CURSOR all_contacts_cur IS
SELECT * FROM sr_contacts WHERE rownum <= 1;
--cursor的字段数要与要循环插入的表中的字段数一致且一一对应
BEGIN
OPEN all_contacts_cur;
LOOP
FETCH all_contacts_cur BULK COLLECT
INTO v_contacts LIMIT 256;
FOR i IN 1 .. v_contacts.count LOOP
INSERT INTO sr_contacts VALUES v_contacts (i); --循环执行插入程序
END LOOP;
EXIT WHEN all_contacts_cur%NOTFOUND;
END LOOP;
CLOSE all_contacts_cur;
EXCEPTION
WHEN OTHERS THEN
l_return_status := fnd_api.g_ret_sts_error;
l_msg_data := SQLERRM;
END;
注意事项