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

Lesson: Use of SQL in PL/SQL

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.

Lesson Goals

  • Learn how to use inline SQL statements
  • Learn what implicit cursors are
  • Learn how to use explicit cursors
  • Learn about cursor variables and cursor expressions.

Implicit Cursors

Code Sample:

Use-of-SQL-in-PLSQL/Demos/select_into.sql
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;
/

Code Explanation

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.

%TYPE and %ROWTYPE Anchored Declaration

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.

Code Sample:

Use-of-SQL-in-PLSQL/Demos/select_into_value_error.sql
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;

Code Explanation

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

Code Sample:

Use-of-SQL-in-PLSQL/Demos/select_into_value_error_resolved.sql
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;

Code Explanation

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.

Cursor Attributes

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.

Code Sample:

Use-of-SQL-in-PLSQL/Demos/select_into_and_dml_with_rowcount.sql
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;

Code Explanation

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.

Implicit Cursor

Duration: 5 to 10 minutes.
  1. Modify the exercise script below so that the name (first name and last name separated by a space), salary, and hire date are retrieved from the employee table (use employee_id = 100 for the initial test in step 3).
  2. Output the results in a sentence like the following: <first name last name> was hired <hire date> and will be paid <salary amount>.
  3. Execute your code.
  4. On a new line after the BEGIN statement, add the code to retrieve the lowest and highest employee ids.
  5. Modify your code so that you loop through each person in the employees table, retrieving and displaying each employee's information. Use the values you just retrieved in step 4 as your start and end values for the loop.
  6. Add an exception handler that will display any unexpected error codes.
  7. Execute your code.

Code Sample:

Use-of-SQL-in-PLSQL/Exercises/name_salary_date_query.sql
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;

Code Explanation

Solution:

Use-of-SQL-in-PLSQL/Solutions/name_salary_date_query_solution.sql
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;

Code Explanation

Explicit Cursors

An explicit cursor is used to process a result set that contains zero or more rows.

Code Sample:

Use-of-SQL-in-PLSQL/Demos/employees_cursor.sql
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;
/

Code Explanation

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.

Code Sample:

Use-of-SQL-in-PLSQL/Demos/employees_cursor_for_loop.sql
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;

Code Explanation

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

Code Sample:

Use-of-SQL-in-PLSQL/Demos/bulk_collect.sql
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;
/

Code Explanation

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.

Explicit Cursor

Duration: 10 to 15 minutes.
  1. Modify the script you created using an implicit cursor for the last exercise so that employee name (first name and last name separated by a space), salary and hire date for all employees are retrieved from the employee table (sort by last name and first name).

Solution:

Use-of-SQL-in-PLSQL/Solutions/name_salary_date_cursor_solution.sql
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;

Code Explanation

Cursor FOR loop

Duration: 5 to 10 minutes.
  1. Create a script that lists department names from the department table using a cursor FOR loop.

Solution:

Use-of-SQL-in-PLSQL/Solutions/departments_cursor_for_loop_solution.sql
begin

 FOR c IN (select department_name
           from departments 
           order by department_name)
 LOOP
        dbms_output.put_line(c.department_name);
 END LOOP;

end;
/

Code Explanation