You can, alternatively, explicitly declare a cursor and then perform the open, fetch, and close operations yourself.
Suppose I need to write a block that fetches employees in ascending salary order and gives them a bonus from a total pool of funds by calling the assign_bonus procedure, whose header is Each time assign_bonus is called, the procedure subtracts the bonus given from the total and returns that reduced total.
Part 12 in a series of articles on understanding and using PL/SQL The central purpose of the Oracle PL/SQL language is to make it as easy and efficient as possible to query and change the contents of tables in a database.
Code Listing 1: Block and description of explicit cursor implementation 1 DECLARE 2 l_total INTEGER := 10000; 3 4 CURSOR employee_id_cur 5 IS 6 SELECT employee_id 7 FROM plch_employees 8 ORDER BY salary ASC; 9 10 l_employee_id employee_id_cur%ROWTYPE; 11 BEGIN 12 OPEN employee_id_cur; 13 14 LOOP 15 FETCH employee_id_cur INTO l_employee_id; 16 EXIT WHEN employee_id_cur%NOTFOUND; 17 18 assign_bonus (l_employee_id, l_total); 19 EXIT WHEN l_total Declare a record based on the row of data returned by the query.
In this case, there is just a single column value, so you could just as easily have declared l_employee_id as employees.employee_id%TYPE.
You must, of course, use the SQL language to access tables, and each time you do so, you use a to get the job done.
A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or data manipulation language (DML) statement (INSERT, UPDATE, DELETE, or MERGE).
First, we'll take a look at how to execute a function using a test block.