The script will create all the needed objects for this example, I assume you have a user with the proper permisions to execute the example.
Script
/*This anonymous pl/sql block is to drop the table, so anytime we want to run the script it will run without problems*/Script output
BEGIN
--drop the table
EXECUTE IMMEDIATE ('DROP TABLE number_text_pair');
--if there is some problems like it is the first time so there is no table to drop just do nothing
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
/*create the table*/
CREATE TABLE number_text_pair
(
number_value integer,
text_value varchar2(4000)
);
/*create some test data*/
INSERT INTO number_text_pair (number_value, text_value) VALUES (1,'one');
INSERT INTO number_text_pair (number_value, text_value) VALUES (2,'two');
INSERT INTO number_text_pair (number_value, text_value) VALUES (3,'tree');
/*anonymous pl/sql block for iterating number_text_pair's data with a cursor*/
DECLARE
--declare the cursor
CURSOR cur_example is
SELECT *
FROM number_text_pair;
/*create a record to store the data of the table,
note the datatype of the record as row type of cur_example*/
rec_example cur_example%ROWTYPE;
BEGIN
--open the cursor to iterate it
OPEN cur_example;
--loop until there is a condition to stop
LOOP
--fetch a row data from the cursor and store it in rec_example
FETCH cur_example INTO rec_example;
--if there is no more data in the cursor then exit
EXIT WHEN cur_example%NOTFOUND;
/*output the values of the cursor to the console
note the use of . to access the fields of the record*/
DBMS_OUTPUT.put_line('Number value is: ' || rec_example.number_value || ' text value is: ' || rec_example.text_value);
END LOOP;
--close the cursor
CLOSE cur_example;
END;
/
/*the slash is to instruct the program running the script to continue after a pl/sql block,
it is not necessary when you are executing a DML statement*/
After executing the script you should see
Number value is: 1 text value is: oneA simpler and more robust version
Number value is: 2 text value is: two
Number value is: 3 text value is: tree
There is another construction to iterate over cursors, it is the for construction, a simpler more robust pl/sql to iterate the cursor is:
/*anonymous pl/sql block for iterating number_text_pair's data with a cursor*/
DECLARE
--declare the cursor
CURSOR cur_example is
SELECT *
FROM number_text_pair;
BEGIN
/*note the simpler construction, there is no need to open and close the cursor,
also there is no need to declare the record*/
FOR rec_example IN cur_example LOOP
DBMS_OUTPUT.put_line('Number value is: ' || rec_example.number_value || ' text value is: ' || rec_example.text_value);
END LOOP;
END;
/
