Syronex

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:

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


Table 2: PL/SQL operators.
Operator Description
+ - / * arithmetic
= equality
!= or <> inequality
|| string concatenation
:= assignment



Table 3: Some Oracle built-in functions. You are referred to Oracles's documentation (see References section) for specific usage examples.
Function Description
String Functions
upper(s), lower(s) convert string s to upper/lower-case
initcap(s) capitalise first letter of each word
ltrim(s), rtrim(s) remove blank char. from left/right
substr(s,start,len) sub-string of length len from position start
length(s) length of s
Date Functions
sysdate current date (on Oracle server)
to_date(date, format) date formatting
Number Functions
round(x) round real number x to integer
mod(n,p) n modulus p
abs(x) absolute value of x
dbms_random.random() generate a random integer
Type Conversion Functions
to_char() convert to string
to_date() convert to date
to_number() convert to number
Miscellaneous Functions
user current Oracle user


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:

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



Footnotes

... command1
Unlike SQL and PL/SQL, SQL*Plus commands are not terminated by a forward slash "/".
...user2
Procedures and functions with no parameters are not decorated with empty brackets, like in SQL.
... Cursors3
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