Updating a record in sql sexually intimidating women
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.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.
For full explanations of both of these answers, visit plsqlchallenge.com, register or log in, and click the Closed/Taken tab in Play a Quiz.
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.
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.
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.