Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.
PL/SQL functions as an extension of SQL and operates within the database environment. Oracle has provided a great deal of flexibility in the ways that PL/SQL can reference and operate upon data. Various data retrieval techniques available are discussed and demonstrated in this lesson.
Lesson Goals
A cursor is a name attached to a database result set in memory. You might think of it as a pointer or reference to a virtual table containing zero or more rows of data. Because PL/SQL is a language that is used primarily within a database environment, the use of cursors is unavoidable for all but the simplest of tasks. You have already seen cursors introduced in earlier lessons. To review some of the major points:
Cursors used up to this point were related to simple unchanging SQL queries. Cursor parameters allow you to modify the query results.
declare CURSOR crs (fname VARCHAR2) IS SELECT * FROM employees WHERE first_name = fname ORDER BY first_name; rec employees%rowtype; begin open crs ('David'); loop fetch crs into rec; exit when crs%notfound; dbms_output.put_line(rec.first_name || ' ' || rec.last_name); end loop; close crs; end; /
In this example, a cursor named crs is declared that takes a parameter named fname. When the cursor is opened, a value is supplied to the parameter which limits the results of the query.
A cursor is tied to a specific query. A cursor variable is not tied to a specific query. It is also known as a REF CURSOR. A REF CURSOR is considered strong if it includes a return type. A strong cursor variable type declaration is usually followed by the declaration of the cursor itself.
TYPE employeeCursorType IS REF CURSOR RETURN employees%ROWTYPE; cursor1 employeeCursorType;
It is considered weak if it does not include a return type.
TYPE weakCursorType IS REF CURSOR; cursor2 weakCursorType;
Strong REF CURSOR types are safer (less error prone) because you are limited to associating them with queries that return a specified set of columns. This is somewhat analogous to coding in a strongly typed language where variable types are clearly defined and casting is very controlled. Weak REF CURSOR types are more flexible because this limitation is not enforced. Any query can be referenced - there is no type checking. This flexibility can lead to errors if an unexpected query is referenced by the cursor and the results contain unexpected values.
It is common to see legacy Oracle PL/SQL packages with REF CURSOR type declarations. Because a weak REF CURSOR has no declared structure, recent versions of Oracle include the predefined type SYS_REFCURSOR that can be used wherever a weak ref cursor is needed. You can use this in recent Oracle versions instead of declaring your own REF CURSOR type.
declare rc sys_refcursor; rec employees%rowtype; begin open rc for select * from employees; loop fetch rc into rec; exit when rc%notfound; dbms_output.put_line(rec.first_name || ' ' || rec.last_name); end loop; close rc; open rc for select * from departments; dbms_output.put_line('Using cursor again.'); close rc; end; /
This is a simple example showing the use of %ROWTYPE and SYS_REFCURSOR.
declare rc sys_refcursor; v_string_data varchar2(400); v_switch NUMBER := 2; begin IF v_switch=1 THEN open rc for select first_name from employees; ELSE open rc for select department_name from departments; END IF; loop fetch rc into v_string_data; exit when rc%notfound; dbms_output.put_line(v_string_data); end loop; close rc; end;
This demonstration shows the flexibility available through the use of a weak REF CURSOR. A SYS_REFCURSOR is used to open one of two possible queries with different rows as results. Depending upon the value of v_switch, a particular cursor is opened and the value fetched and displayed.
Bind variables and cursor parameters give a large amount of flexibility with modifying queries using weak cursors. They do not allow you to fundamentally alter the structure of a query though. These limitations can be overcome by constructing SQL in strings and using weak cursors to reference the resulting query, as illustrated in this example.
declare crs SYS_REFCURSOR; v_sql varchar2(200) := 'select <expression> from employees where salary > :amount'; v_string_data varchar2(4000); begin v_sql := replace(v_sql, '<expression>', 'last_name||'' ''||first_name'); /* v_sql := replace(v_sql, '<expression>', 'employee_id||'';''||last_name||'';''||first_name||'';''||salary'); */ open crs for v_sql using 10000; loop fetch crs into v_string_data; exit when crs%notfound; dbms_output.put_line(v_string_data); end loop; close crs; end;
The commented line contains a significantly different query (one that returns a different number of columns separated by semicolons). Run the block as written and them uncomment the commented line to see the difference. This example uses queries against the same table, but the only restriction is that the cursor reference a valid SQL statement and that the value(s) retrieved correspond to a record or variable that can hold each returned result.
declare v_sql varchar2(200) := 'select <expression> from employees where employee_id = :id'; v_string_data varchar2(4000); begin v_sql := replace(v_sql, '<expression>','last_name'); execute immediate v_sql into v_string_data using 101; dbms_output.put_line(v_string_data); end;
In some circumstances, you will need to craft dynamic SQL statements. This example shows some of the features available when using EXECUTE IMMEDIATE to run a SQL query contained in a string. The INTO clause is only relevant with a SELECT statement. This is due to the fact that DML statements cannot return values. The <expression> portion of the string above can be replaced with any valid single column or even an expression (e.g. first_name, to_char(salary,''$999,999.00'')).
This example also demonstrates the use of id as a bind variable. (The use of a bind variable is a frequently cited Oracle "best practice". A bind variable allows the database to recognize common query statements that differ only in values present in bind variables. This benefits application performance). The using clause is used to populate the bind variable (:id).
create or replace procedure employee_report_by_state_prov ( v_state in varchar2) is CURSOR crs (state varchar2) IS select * from emp_details_view where state_province = state order by last_name, first_name; rec emp_details_view%rowtype; begin open crs (v_state); loop fetch crs into rec; exit when crs%notfound; dbms_output.put_line(rec.first_name || ' ' || rec.last_name || ' ' || rec.job_id); end loop; end employee_report_by_state_prov; / -- To test execute the following: BEGIN EMPLOYEE_REPORT_BY_STATE_PROV(V_STATE => 'Texas'); END; /