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