A much better approach is to fetch that row of data into a record, and the best way to declare that record is as follows: CREATE PROCEDURE process_employee ( employee_id_in IN omag_employees.employee_id%TYPE) IS l_employee omag_employees%ROWTYPE; BEGIN SELECT employee_id, last_name, salary INTO l_employee FROM omag_employees WHERE employee_id = employee_id_in; END; When this procedure is compiled, PL/SQL looks up the structure of the omag_employees table and defines a record that has a field for each column in the table, with the same name and datatype.By using %ROWTYPE to declare the record, I also tell Oracle Database that this procedure the omag_employees table.
Inserting and Updating with Records 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?
It also takes a look at record types, which enable you to work with records that are not necessarily related to a relational table.
Declare a Record with %ROWTYPE PL/SQL makes it very easy to declare records that have the same structure as a table, a view, or the result set of a cursor by offering the %ROWTYPE attribute.
PL/SQL offers special syntax in both the INSERT and UPDATE statements so that you can easily use records to perform those data manipulation language (DML) operations as well.
The most common form of an INSERT statement is where column_list is the list of columns that will be populated on insert and expression_list is the list of expressions that will be assigned to their respective columns.
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’s rare, in fact, that the data with which you are working is just a single value, so records and other composite datatypes are likely to figure prominently in your PL/SQL programs.