Oracle cursor for updating in oracle
This upper limit is specified when the varray type is declared; it can also be modified afterward, if you are using Oracle Database 10 Release 2 or higher.Listing 2 shows how to define a varray type and declare a variable based on this type.I can use a varray to ensure that the absolute limit of 12 is respected, as shown in Listing 3.If, for some reason, more than 12 rows are found in the table, Oracle Database will raise an exception.Even when the query itself is automatically optimized to return 100 rows with each fetch, the INSERT or UPDATE will happen on a row-by-row basis.This is one of the worst performance “anti-patterns” in database programming.END process_employee; The implicit SELECT INTO offers the most-efficient means of returning that single row of information to your PL/SQL program.
A varray is a collection that has an upper limit on the number of elements that can be defined in the collection.
It looks like you expect to retrieve multiple rows, yet you get just one. You should not write code that appears to do one thing while it, in fact, does another.
If you need to retrieve a single row and you know that at most one row should be retrieved, you should use a SELECT INTO statement, as in the following: PROCEDURE process_employee ( id_in IN employees.employee_id%TYPE) IS l_last_name employees.last_name%TYPE; BEGIN SELECT e.last_name INTO l_last_name FROM employees e WHERE e.employee_id = process_employee.id_in; ...
I further recommend that you encapsulate your SELECT INTO statements into their own functions whose sole purpose is to retrieve this one row of information, as shown in Listing 1.
Code Listing 1: Encapsulating SELECT INTO in a function PACKAGE employees_qp IS FUNCTION last_name (id_in IN employees.employee_id%TYPE) RETURN employees.last_name%TYPE; END employees_qp; PROCEDURE process_employee (id_in IN employees.employee_id%TYPE) IS l_last_name employees.last_name%TYPE; BEGIN l_last_name := employees_qp.last_name (id_in); END process_employee; With this approach, you are much more likely to reuse that SELECT INTO rather than write it repeatedly in your code.
The resulting performance improvement can be an order of magnitude or greater.