PL/SQL Tutorial, Part 2

Need for PL/SQL — declarative vs. procedural — anonymous blocks — debugging — a first program — code compilation — code execution — procedures & functions — PL/SQL in SQL — SQL in PL/SQL — cursors & loops — operators & built-in functions reference tables.

Embedding SQL in PL/SQL

PL/SQL alone does not allow us to query a database, and use the resulting data in our program. However, any SQL (i.e. DML) may be embedded in PL/SQL code. In particular, there exists a form of the ``SELECT'' statement for assigning the result of a query to a variable. Note the following code requires the books and book_reviews tables that you should have created during the first Oracle tutorial.

1  CREATE OR REPLACE PROCEDURE count_reviews 
2    (author_param VARCHAR2)
3  IS
4    review_count NUMBER;
5  BEGIN
6    SELECT COUNT(*) INTO review_count
7    FROM book_reviews r, books b
8    WHERE b.isbn = r.isbn  AND author = author_param;
9    
10   IF review_count > 1 THEN 
11    dbms_output.put_line('There are ' 
12      || review_count || ' reviews.');
12   ELSIF review_count = 1 THEN  
14     dbms_output.put_line('There is 1 review.');
15   ELSE
16     dbms_output.put_line('There is no review.');
17   END IF;
18 END;
19 /

Note in the code above how:

• the procedure takes one parameter author_param of type VARCHAR2
• a value from an SQL query is assigned to a PL/SQL variable (i.e. review_count) using SELECT...INTO... (line 6)
• a value from a PL/SQL variable is used in an SQL statement (line 8)

Try the programs with different authors:

EXEC count_reviews('Oscar Wilde')
EXEC count_reviews('Charles Dickens')

Working with Cursors

The last program we are going to write will display the number of reviews relevant to each author. Notice that the query may now return multiple rows. However, a SELECT...INTO... statement can only retrieve data from (at most) one  tuple into individual variables.
Cursors³ provide a means to retrieve multiple rows into a buffer (when you OPEN the cursor) that can then be traversed sequentially (FETCH) to retrieve individual rows—until there is no more data (cur_revs%NOTFOUND becomes true).

CREATE OR REPLACE PROCEDURE count_by_author 
IS
  auth VARCHAR2(30);
  cnt NUMBER;
  CURSOR cur_revs IS
    SELECT author, COUNT(author) AS revs_cnt 
    FROM books b, book_reviews r
    WHERE b.isbn = r.isbn GROUP BY author;
BEGIN
  OPEN cur_revs;
  LOOP
    FETCH cur_revs INTO auth, cnt;
  EXIT WHEN cur_revs%NOTFOUND;

  IF cnt = 1 THEN dbms_output.put_line('1 review for '
    || auth);
  ELSE
    dbms_output.put_line(cnt || ' reviews for ' || auth); 
  END IF; 

  END LOOP;
CLOSE CUR_REVS;   
END;
/

Execute count_by_author, adding more data to the tables if necessary.

References

You can copy & paste the following URI (note that you will need a username/password to access Oracle's web site. You can use database@example.com/database):

PL/SQL User's Guide and Reference:

//download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89856/toc.htm

---

Footnotes

... command¹

Unlike SQL and PL/SQL, SQL*Plus commands are not terminated by a forward slash "/".

...user²

Procedures and functions with no parameters are not decorated with empty brackets, like in SQL.

... Cursors³

Cursors are conceptually similar to iterators in Java.

Contents
SQL in PL/SQL
Cursors
References
See Also
Oracle SQL
SQL Built-In Functions
Oracle Data Dictionary
Oracle & Java
Advanced Oracle & Java
Tell Your Friends

Your name

Your friend's email

Add to del.icio.us