Oracle cursor for updating in oracle

END process_employee; The implicit SELECT INTO offers the most-efficient means of returning that single row of information to your PL/SQL program.

In addition, the use of SELECT INTO states very clearly that you expect at most one row, and the statement will raise exceptions (NO_DATA_FOUND or TOO_MANY_ROWS) if your expectations are not met.

BULK COLLECT helps retrieve multiple rows of data quickly.

Rather than retrieve one row of data at a time into a record or a set of individual variables, BULK COLLECT lets us retrieve hundreds, thousands, even tens of thousands of rows with a single context switch to the SQL engine and deposit all that data into a collection.

oracle cursor for updating in oracle-28oracle cursor for updating in oracle-54

Use an implicit SELECT INTO for single-row fetches.

Having said that, I can identify two circumstances in which using a cursor FOR loop would do little harm.

First, because Oracle Database automatically optimizes cursor FOR loops to execute similarly to BULK COLLECT, as long as the body of your loop does not push changes back to the database, your “read-only” cursor FOR loop will almost always suffice.

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.

Leave a Reply