facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Data Retrieval Techniques

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

  • Learn about cursor handling techniques
  • Learn how to use cursor variables
  • Learn how to use REF cursors
  • Learn possibilities available through dynamic SQL

Cursor Review

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 are explicit if they are specifically declared as such in PL/SQL code. They are implicit when simply used in the background as part of a SELECT INTO or DML statement.
  • The basic pattern for dealing with explicit cursors is to declare the cursor, open it, loop through the rows referenced by the cursor (if necessary), fetch the results into variables and close the cursor.
  • An alternative to this is to use a cursor for loop where a cursor is created on the fly without the need to declare, open or close it.
  • Variables declared with %TYPE anchored declarations are based upon corresponding database fields. Variables declared with %ROWTYPE anchored declarations are based upon entire rows in a table.
  • Cursor attributes (e.g. %NOTFOUND, %FOUND, %ISOPEN, %ROWCOUNT) provide information about the cursor in use.

Cursors Declared with Parameters

Cursors used up to this point were related to simple unchanging SQL queries. Cursor parameters allow you to modify the query results.

Code Sample:

Data-Retrieval-Techniques/Demos/employees_cursor_parameter.sql
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;
/

Code Explanation

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.

Cursors as Parameters and Variables

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.

Code Sample:

Data-Retrieval-Techniques/Demos/employees_sys_refcursor_and_rowtype.sql
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;
/

Code Explanation

This is a simple example showing the use of %ROWTYPE and SYS_REFCURSOR.

Code Sample:

Data-Retrieval-Techniques/Demos/cursor_variable_pointing_to_different_cursors.sql
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;

Code Explanation

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.

Dynamic SQL

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.

Code Sample:

Data-Retrieval-Techniques/Demos/dynamic_sql_cursor.sql
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;

Code Explanation

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.

EXECUTE IMMEDIATE statement

Code Sample:

Data-Retrieval-Techniques/Demos/execute_immediate.sql
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;

Code Explanation

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).

Employee Report by State/Province

Duration: 15 to 20 minutes.
  1. Create a stored procedure named employee_report_by_state_prov.
  2. Modify the procedure so that it takes an input parameter named v_state of type varchar2.
  3. Create a cursor that takes a cursor variable called state of type varchar2. The query should select all columns from the EMP_DETAILS_VIEW and return rows that have a STATE_PROVINCE column that matches the state parameter. Sort the results by last name followed by first name.
  4. In the body of the procedure, open the cursor using the parameter passed in through v_state.
  5. Loop through the results and output the first name, last name and job id.
  6. Test the procedure using Texas, Washington and Bavaria.

Solution:

Data-Retrieval-Techniques/Solutions/solution.sql
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;
/

Code Explanation