Updating specific row in cursor

02-May-2020 15:24 by 3 Comments

Updating specific row in cursor

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.

So when you use %ROWTYPE to pass arguments to subprograms, make sure to review the subprogram logic after any change to the table.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.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).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.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.Answer 2: To make it possible for the plch_proc procedure to compile without error, change “AND” to “OR” in the WHEN clause of the exception section.It also takes a look at record types, which enable you to work with records that are not necessarily related to a relational table.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.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.