Lesson: Data Retrieval Techniques

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:

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

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:

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

Code Sample:

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

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:

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

EXECUTE IMMEDIATE statement

Code Sample:

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

Employee Report by State/Province

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:

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;
/