PL/SQL and SQL

Working with data that is accessed buy SQL can include the use of

Special Data Types

In the last example we had to specify the data types, Oracle can automatically match the data types for us and there are two data types that allow us to do this %type and %rowtype.

%TYPE

is used to declare a field with the same type as that of a specified table column

DECLARE
  empname emp.ename%TYPE
BEGIN
  select ename into empname from emp where rownum = 1;
  dbms_ouput.put_line( 'Name: ' || empname);
END;

%ROWTYPE

is used to declare a record with the same types as found in the specified table, view or cursor

DECLARE
  emp emp%ROWTYPE
BEGIN
  emp.empno := 10;
  emp.ename := 'vallep';
END;

Cursor Attributes

Cursors have attributes that can be used and are obtained by using cursor.attribute_name

%ISOPEN contains TRUE if the cursor is currently open FALSE if not. Always returns FALSE for any implicit cursor.
%FOUND

can contain several values

NULL - before a fetch on the cursor
TRUE - if a row was fetched successfully
FALSE - if a row was fetched unsuccessfully

%NOTFOUND opposite to %FOUND
%ROWCOUNT contains the number of rows fetched so far by the DML statement or the number of rows returned by a SELECT INTO statement

Implicit Cursor

Every SQL statement in a PL/SQL block is actually an implict cursor. You can use the PL/SQL statement instead of a cursor, Oracle will create a implicit cursor that operates behind the scenes like a explicit cursor.

Using %ROWCOUNT

DECLARE
   n number;
BEGIN
   select 1 into n from dual;
   dbms_output.put_line('Selected' || SQL%ROWCOUNT || ']');
END;
/

Note: SQL stands for any implicit cursor

Single-Row Implicit Cursor

DECLARE
   id        item.item_id%TYPE;
   title     item.item_title%TYPE;
   subtitle  item.item_subtitle%TYPE;
BEGIN
   SELECT item_id, item_title, item_subtitle
   INTO id, title, subtitle                      -- Implicit Cursor, a single row is returned
   FROM item
   WHERE rownum < 2;
   dbms_output.put_line('Selected [' || title || ']');
END;
/

Note: you can also create a record for the data type, saves repeating

Muliple-Row Implicit cursor

-- First Option - DML statement in a PL/SQL block
BEGIN
   update system_user set last_update_date = sysdate;
   if SQL%FOUND
   then
      dbms_output.put_line('Updated [' || SQL%ROWCOUNT || ']');
   else
      dbms_output.put_line('Nothing updated');
   end if;
END;
/

-- Second Option - Write an embedded query in a cursor FOR loop
BEGIN
   for i in (select item_id, item_title from item)
   loop
      dbms_output.put_line('Item #[' || i.item_id || '][' || i.item_title || ']' );
   end loop;
END;
/

Explicit Cursors

Explicit cursors can be either static or dynamic

Static return the same query each time with potentially different results, the results change as the data changes in the tables or views.
Dynamic act like parameterized subroutines, they run different queries each time, depending on the actual parameters provided when they're opened

Explicit cursors require you to open, fetch and close them whether you use while or cursor for loops. Cursor for loops implicitly open, fetch and close cursors for you and thus are simpler to manage.

Explicit cursor

open cursor_name [(parameter1 [, parameter2 [,parameter(n+1]])];

fetch cursor_name into variable1 [, variable2 [, variable(n+1) ]];
fetch cursor_name into record;

close cursor_name;

A cursor acts logically as a pointer to a result set, you can move the pointer through the result set processing each row until the end.

Static explicit cursor

-- Open, Fetch, Close version
DECLARE
   id     item.item_id%TYPE:
   title  item.item_title%TYPE;
   cursor c is select item_id, item_title from item;
BEGIN
   open c;                                                  -- open the cursor
   loop
      fetch c into id, title;                               -- fetch from the cursor
      exit when c%NOTFOUND;
      dbms_output.put_line('Title [' || title || ']');
   end loop;
   close c;                                                 -- close the cursor
END;
/

-- cursor FOR loop version
DECLARE
   cursor c is select item_id as id item_title as title from item;
BEGIN
   for i in c
   loop
      dbms_output.put_line('Title [' || i.title || ']');
   end loop;
END;
/

Note: you can use records as well, you might also want to check that if no records are found

Dynamic explicit cursor

-- Open, Fetch, Close version
DECLARE
   lowend number;
   highend number;
   item_id number := 1000;
   
    type item_record is RECORD
   ( id number,
     title varchar2(60)
   );

   item item_record;

   cursor c (low_id number, high_id number) is
      select item_id, item_title from item where item_id between low_id and high_id;
BEGIN
   lowend  := TO_NUMBER(NVL(&1,1000));
   highend := TO_NUMBER(NVL(&2,1050));
   open c (lowend,highend);
   loop
      fetch c into item;
      exit when c%NOTFOUND;
      dbms_output.put_line('Title [' || item.title || ']');
   end loop;
   close c;
END;
/

-- cursor FOR loop version - substitute the open,fetch, close stuff
for i in c(lowend, highend)
loop
    dbms_output.put_line('Title [' || i.title || ']');
end loop;

For Cursor

You can reduce the amount of code by using the for cursor construct, you had to go through a number of steps to get the data define variables, define the cursor, open the cursor, fetch the data in a loop, exit the loop then close the cursor, with the for construct all you have to do is define the cursor. There are more examples of the for cursor loop in the explicit cursor selection above.

For Cursor example

DECLARE
   cursor c is select item_id AS id item_title AS title from item;   -- Notice the AS in the select statement
BEGIN
   for i in c
   loop
      dbms_output.put_line('Title [' || i.title || ']');
   end loop;
END;
/

REF cursors

A ref cursor allows you to pass a cursor reference from one PL/SQL program unit to another, you can create a variable that will receive a cursor and enable access to it.

REF cursor example

create or replace procedure test_cursor_proc_3(p_cursor OUT sys_refcursor)
IS
BEGIN
  FOR p_cursor IN select fname, lname from employees;
END;

create or replace procedure test_cursor_proc_3(p_cursor OUT sys_refcursor)
IS
  type p_cursor is ref cursor return employees%ROWTYPE;
BEGIN
  FOR p_cursor IN select fname, lname from employees;
END;

Note: the top example is a weakly typed ref cursor, the bottom type is a strongly typed ref cursor.

Collections

See collections for more information