Examples of different types of cursors. Each has its own pros and cons. Example 1 is the most straightforward and easiest to understand. The variants in example 3 perform better. I've put them here for reference.
-- 1) implicit cursor
DECLARE l_old_tbs VARCHAR(100) := 'USERS';
BEGIN
FOR item IN (
SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs )
LOOP
dbms_output.put_line(item.TABLE_NAME);
END LOOP;
END;
-- 2) explicit cursor
DECLARE l_old_tbs VARCHAR(100);
CURSOR c1 IS (SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs);
BEGIN
l_old_tbs := 'USERS'; -- set variable here, otherwise it is NULL!
FOR item IN c1 LOOP
dbms_output.put_line(item.TABLE_NAME);
END LOOP;
END;
-- 3a) local collection variable with multiple fields using BULK COLLECT and FORALL
CREATE TABLE TEST1 (OWNER VARCHAR(30), TABLE_NAME VARCHAR(30));
DECLARE l_old_tbs VARCHAR(100);
TYPE r_tmp IS RECORD (OWNER ALL_TABLES.OWNER%TYPE, TABLE_NAME ALL_TABLES.TABLE_NAME%TYPE);
TYPE t_tmp IS TABLE OF r_tmp;
tmp t_tmp;
BEGIN l_old_tbs:= 'USERS';
-- fill local collection "tmp" with one bulk operation
SELECT OWNER, TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- no "loop" keyword, because following statement is performed as one batch. Check dbms_output, this is only one line.
-- always use "commit" statement
FORALL i IN tmp.FIRST..tmp.LAST
INSERT INTO TEST1 VALUES tmp(i);
dbms_output.put_line('first: 'tmp.FIRST', last: 'tmp.LAST);
COMMIT;
END;
-- 3b) local collection variable with single field using BULK COLLECT and FORALL
DECLARE l_old_tbs VARCHAR(100);
TYPE t_tmp IS TABLE OF ALL_TABLES.TABLE_NAME%TYPE;
tmp t_tmp;
BEGIN
l_old_tbs:= 'USERS';
-- fill "tmp" with one bulk operation
SELECT TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- no "loop" keyword, because following statement is performed as one batch.
-- always use "commit" statement
FORALL i IN tmp.FIRST..tmp.LAST
INSERT INTO TEST1 (TABLE_NAME) SELECT tmp(i) FROM DUAL;
COMMIT;
END;
-- 3c) local collection variable with multiple fields using BULK COLLECT and FOR
SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
l_old_tbs VARCHAR2(100);
TYPE r_tmp IS RECORD (OWNER ALL_TABLES.OWNER%TYPE, TABLE_NAME ALL_TABLES.TABLE_NAME%TYPE);
TYPE t_tmp IS TABLE OF r_tmp;
tmp t_tmp;
BEGIN
l_old_tbs:= 'USERS';
-- fill local collection "tmp" with one bulk operation
SELECT OWNER, TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- with "loop" statement. Check dbms_output, there are as much lines as tmp.LAST.
-- always use "commit" statement
FOR i IN 1..tmp.COUNT
LOOP
--INSERT INTO TEST1 VALUES tmp(i);
dbms_output.put_line('tablename: '||tmp(i).TABLE_NAME||', last: '||tmp.LAST);
COMMIT;
END LOOP;
END;
-- 3d) local collection variable with single field using BULK COLLECT and FOR
SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
i INT := 0;
l_tabname VARCHAR2(100);
TYPE r_tmp IS RECORD (TABLE_NAME USER_TABLES.TABLE_NAME%TYPE);
TYPE t_tmp IS TABLE OF r_tmp;
tmp t_tmp;
BEGIN
-- fill local collection "tmp" with one bulk operation
SELECT TABLE_NAME BULK COLLECT INTO tmp FROM USER_TABLES WHERE UPPER(TABLE_NAME) LIKE 'TTMP_%';
-- update/insert/delete with one bulk operation; select not permitted.
-- with "loop" statement. Check dbms_output, there are as much lines as tmp.LAST.
-- always use "commit" statement
FOR i IN 1..tmp.COUNT
LOOP
dbms_output.put_line(i);
l_tabname := 'DROP TABLE ' || tmp(i).TABLE_NAME;
dbms_output.put_line(l_tabname);
EXECUTE IMMEDIATE l_tabname;
COMMIT;
END LOOP;
END;
-- 4) use a temp table
CREATE GLOBAL TEMPORARY TABLE tmp (OWNER VARCHAR(30), TABLE_NAME VARCHAR(30);