Suppose I have an employees table in an application that looks like this: CREATE PROCEDURE process_employee ( employee_id_in IN omag_employees.employee_id%TYPE) IS l_employee_id omag_employees.employee_id%TYPE; l_last_name omag_employees.last_name%TYPE; l_salary omag_employees.salary%TYPE; BEGIN SELECT employee_id, last_name, salary INTO l_employee_id, l_last_name, l_salary FROM omag_employees WHERE employee_id = employee_id_in; END; (Note that I use suffixes in my parameters to indicate their mode.Here _in indicates an IN parameter.) That is, however, an awful lot of code to write, read, and maintain.You can also, however, assign values directly to individual fields or even to the record as a whole by using the PL/SQL assignment operator (:=).
As you have seen, PL/SQL makes it very easy to populate a record from a row in a table.
But what if you want to change the contents of a row in a table by using a record?
Suppose I want to write a program to display the last names of all employees.
An elegant and simple way to do this in PL/SQL is to take advantage of the cursor FOR loop (which I discussed in part 2 of this PL/SQL 101 series).
This is especially helpful for fetching either a subset of columns from a table or columns from multiple tables.
Here’s an example: (Note that I usually add a “_cur” suffix to the names of my explicitly declared cursors.) Whenever you are fetching data from a cursor into PL/SQL variables, you should declare a record based on that cursor with %ROWTYPE and fetch into that record.I can even shorten things further and write CREATE PROCEDURE process_employee ( employee_id_in IN omag_employees.employee_id%TYPE) IS l_employee omag_employees%ROWTYPE; BEGIN SELECT * INTO l_employee FROM omag_employees WHERE employee_id = employee_id_in; END; The SELECT * syntax tells Oracle Database to fetch all the columns in the table.I can also use %ROWTYPE to declare a record that has the same structure as a SELECT statement in a cursor.The cursor FOR loop is a variation on the numeric FOR loop, which looks like this: The index is implicitly declared by Oracle Database as an integer and can be referenced only inside the body of this loop.A cursor FOR loop has a similar structure but replaces a numeric range with a query: loop index as well, but in the case of a cursor FOR loop, it declares the index as a record by using %ROWTYPE against the query in the loop header.It also takes a look at record types, which enable you to work with records that are not necessarily related to a relational table.