Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.
A cursor is a control structure used to traverse and process database records in a result set. Every SQL statement involves the use of a cursor. Since PL/SQL is tightly integrated with SQL, the ability to reference and control cursors is a significant feature of the language. Cursors can be explicit or implicit. An implicit cursor is used when assigning a value to a variable using the SELECT INTO syntax. An implicit cursor is also created when a DML statement (INSERT/UPDATE/DELETE) is executed. An explicit cursor must be declared for SQL statements that return more than one row.
declare v_first_name varchar2(100); v_last_name varchar2(100); begin select first_name, last_name into v_first_name, v_last_name from employees where employee_id = 100; dbms_output.put_line(v_first_name || ' ' || v_last_name); exception when NO_DATA_FOUND then dbms_output.put_line('No data found-is the employee id valid?'); when TOO_MANY_ROWS then dbms_output.put_line('Too many records. ' || 'Are you identifying the employee by id?'); end; /
If you expect to retrieve exactly one row you can use the SELECT INTO syntax shown here. One common case is when obtaining a count of records that satisfy a condition: exactly one value is guaranteed to be returned. Note that INSERT, UPDATE, DELETE and SELECT INTO all use implicit cursors.
If a SELECT INTO clause retrieves multiple records, the following exception will be raised: ORA-01422: exact fetch returns more than requested number of rows. This can be seen by modifying the demo above and removing the WHERE clause.
If a SELECT INTO clause retrieves no records, the following exception will be raised: ORA-01403: no data found. This can be shown by changing the demo so that the where clause specifies WHERE employee_id=1..
These exceptions can be addressed either through exception handling or by using explicit cursors. An explicit cursor can be used to handle a query that returns zero or more rows. It will be discussed later.
A common problem in programming occurs when values do not fit when assigned to a variable. This can result in a runtime error or a loss of data. The use of %TYPE and %ROWTYPE anchored declarations in PL/SQL can eliminate many of these potential problems. %TYPE is used when referencing a single column in a table; %ROWTYPE is used to reference every available column in a given table. The following demonstrations show how the problem can manifest itself and how the use of %TYPE addresses the issue.
declare v_first_name varchar2(5); v_last_name varchar2(100); begin select first_name, last_name into v_first_name, v_last_name from employees where employee_id = 100; dbms_output.put_line(v_first_name || ' ' || v_last_name); end;
The v_first_name variable has been modified in this example so that it is too small to hold the value retrieved from the table. An exception is raised: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
declare v_first_name employees.first_name%type; v_last_name employees.last_name%type; begin select first_name, last_name into v_first_name, v_last_name from employees where employee_id = 100; dbms_output.put_line(v_first_name || ' ' || v_last_name); end;
Variables that will be populated with the contents from table columns should be declared in the manner shown in this example. This eliminates the need to consult the table definition and declare a variable size that matches the column. Since %TYPE uses the datatype of the database column itself, it ensures that the variable in use in a PL/SQL statement will be able to hold the contents of the variable even if the column size changes at some point in the future.
There are a number of cursor attributes that return data about the execution of DML statements (insert, update, delete, etc.). Each is prefixed with a percent sign. The %FOUND attribute returns TRUE if a DML statement or a SELECT INTO statement returned at least one row. The %NOTFOUND attribute returns the opposite of the %FOUND attribute. The %ROWCOUNT attribute returns the number of rows affected by a DML statement or returned by a SELECT INTO statement. In the case of these implicit cursors, the values of the cursor attributes are based upon the most recently executed SELECT INTO or DML statement. In the next section you'll see that there is one additional cursor attribute (%ISOPEN) that is not applicable for implicit cursors.
declare v_first_name employees.first_name%type; v_last_name employees.last_name%type; begin select first_name, last_name into v_first_name, v_last_name from employees where employee_id = 100; dbms_output.put_line('#1 select into %rowcount=' || SQL%rowcount); dbms_output.put_line(v_first_name || ' ' || v_last_name); -- a dml statement and its attributes update employees set salary = salary * 1.1; dbms_output.put_line('Just updated ' || sql%rowcount || ' salaries.'); rollback; -- this is a dml statement that does NOT have -- any implicit cursors associated with it. -- another valid employee id would be 101 -- but let's use an invalid one to throw an error select first_name, last_name into v_first_name, v_last_name from employees where employee_id = 1; -- An error was thrown so we never get here. dbms_output.put_line('#2 select into %rowcount=' || SQL%rowcount); exception when NO_DATA_FOUND then dbms_output.put_line('Oops. Is the employee id valid?'); dbms_output.put_line('No data found. NOTE: %rowcount=' || SQL%rowcount); when TOO_MANY_ROWS then dbms_output.put_line('Oops. Are you using a PK value?'); dbms_output.put_line('Too many rows. NOTE: %rowcount=' || SQL%rowcount); end;
Because each implicit cursor has the same name, SQL, the cursor attributes must be used as soon as possible after the implicit cursor's executing SELECT INTO or DML statement. Notice the value of %ROWCOUNT when no data is found. Now run the code again, changing the equal sign in the second SELECT INTO into a greater than sign. Notice the value of %ROWCOUNT when too many rows are returned.
DECLARE v_name VARCHAR2(46); v_salary employees.SALARY%type; v_date employees.HIRE_DATE%type; -- Add code here for steps 3-5. BEGIN -- Add code here for steps 1-2 and then change it for steps 3-5. dbms_output.put_line(v_name || ' was hired ' || to_char(v_date, 'mm/dd/yyyy') || ' and will be paid ' || to_char(v_salary, '$999,999.00') || '.'); -- Add code here for step 6. END;
DECLARE v_name VARCHAR2(46); v_salary employees.salary%type; v_date employees.hire_date%type; v_min_empid employees.employee_id%type; v_max_empid employees.employee_id%type; BEGIN SELECT min(employee_id), max(employee_id) INTO v_min_empid, v_max_empid FROM employees; -- The only problem with this solution is -- if the employee_id values in the range -- are missing. -- We'll see how to control that issue in -- the next chapter. -- Or we can use a completely different -- solution which we'll see in the next -- section of this chapter. FOR i IN v_min_empid..v_max_empid LOOP SELECT first_name || ' ' || last_name, salary, hire_date INTO v_name, v_salary, v_date FROM employees WHERE EMPLOYEE_ID = i; dbms_output.put_line(v_name || ' was hired ' || to_char(v_date, 'mm/dd/yyyy') || ' and will be paid ' || trim(to_char(v_salary, '$999,999.00')) || '.'); END LOOP; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Unexpected error # ' || sqlcode); END;
An explicit cursor is used to process a result set that contains zero or more rows.
declare CURSOR crs is SELECT * FROM employees; rec employees%rowtype; begin if not crs%isopen then open crs; end if; loop fetch crs into rec; exit when crs%notfound; dbms_output.put_line(rec.first_name || ' ' || rec.last_name); end loop; if crs%isopen then close crs; end if; end; /
An explicit cursor is declared in this example. A corresponding variable that has a %ROWTYPE anchored declaration will hold each record returned. The cursor is opened and a loop is used to fetch each row into the record variable. Note that the same cursor attributes that are available for implicit cursors are also available for explicit cursors. The loop is exited when the cursor attribute %NOTFOUND is true. The cursor is closed when processing is complete. This saves database resources and prevents exceptions that can result from having too many cursors open. Note that an exception is thrown if there is an attempt to close a cursor that is not open, or if there is an attempt to open a cursor that is already open. The %ISOPEN boolean cursor attribute is useful for avoiding these exceptions.
begin FOR c IN (SELECT first_name, last_name FROM employees) LOOP dbms_output.put_line(c.first_name || ' ' || c.last_name); END LOOP; end;
A cursor for loop provides concise syntax for creating an implicitly declared cursor on the fly without the need to explicitly declare it earlier. Like a FOR loop that iterates over a range of numbers, the CURSOR FOR loop iterates over a range of records (all those contained in the returned result set).
declare cursor c1 is SELECT * FROM employees order by first_name, last_name; type t_emps is table of employees%ROWTYPE index by pls_integer; v_emp_list t_emps; begin open c1; fetch c1 bulk collect into v_emp_list; close c1; if v_emp_list.count > 0 then for i in v_emp_list.first .. v_emp_list.last loop DBMS_OUTPUT.PUT_LINE(v_emp_list(i).phone_number || ' ' || v_emp_list(i).email); end loop; else dbms_output.put_line('No Data Found'); end if; end; /
Using the BULK COLLECT clause is a very efficient way to retrieve the result set into a collection in a single operation rather than looping through each row. However, there is often a trade off between memory usage and performance when using this option.
An explicit cursor is declared, followed by a declaration of a collection type. The v_emp_list variable is a specific instance of this type. The cursor is opened, the results are fetched into the list in one operation and the cursor is closed. The results are then iterated through and the phone and email addresses are displayed. The use of collections will be described in greater depth in a later chapter.
DECLARE v_name VARCHAR2(46); v_salary NUMBER; v_date DATE; CURSOR c1 is SELECT first_name || ' ' || last_name, salary, hire_date FROM employees ORDER BY last_name, first_name; BEGIN OPEN c1; LOOP FETCH c1 INTO v_name, v_salary, v_date; EXIT WHEN c1%NOTFOUND; dbms_output.put_line(v_name || ' was hired ' || to_char(v_date, 'mm/dd/yyyy') || ' and will be paid ' || trim(to_char(v_salary, '$999,999.00')) || '.'); END LOOP; close c1; END;
begin FOR c IN (select department_name from departments order by department_name) LOOP dbms_output.put_line(c.department_name); END LOOP; end; /