Start Updating pl sql

Updating pl sql

Here are the answers to the PL/SQL Challenge questions in last issue’s “Error Management” article: Answer 1: Choices (a), (c), and (d) all raise ORA-00001, also known as DUP_VAL_ON_INDEX inside PL/SQL code.

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.

If the database administrator changes the maximum length of the last_name column to 200, for instance, this procedure’s status will be changed to INVALID.

When the procedure is recompiled, the compiler will update the definition of the record in this procedure to match the table’s new structure.

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.

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 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 (:=).