Writing structured programs in Oracle

PL/SQL or Procedural SQL is used to write structured programs in Oracle. Programs may contain a sequence of commands, take input, return output and contain variables that hold intermediate and final values. A developer may use PL/SQL to add structural capacities to sets of SQL commands and create procedures that carry out tasks via conditional constructs, looping constructs that execute a piece of code over and over again and so on. Programs written with PL/SQL programs are either functions or Stored Procedures. Related Stored Procedures may be grouped into a package. PL/SQL was originally modeled after the ADA programming language. It is somewhat similar to Pascal. Let us try out a small PL/SQL block. This block is anonymous - it has no name; Oracle executes it once and forgets about it.

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2   dbms_output.put_line('Hello World');
3  END;
4  /

The output of the PL*SQL program is sent to the console through the 'SET SERVEROUTPUT ON' SQL*Plus command. All the code and commands between the BEGIN and END constitute a block of code. Each line should end with a semicolon (;) character. The put_line function simply prints out a line onto the output.

PL/SQL code may be compiled and executed by running the code on the SQL*Plus command window. If you are executing a stored procedure or a function rather than an anonymous block, the 'SHOW ERRORS' command should be executed if you want a verbose display of compile time errors. Otherwise, a 'warning: procedure created with compilation errors' message alone is displayed and this message is not very useful during debugging. It is best to use an external editor such as the one that comes with the OEM console or the XE Database Home Page to edit and test stored procedures before running them at the SQL command line. The block we just wrote may be executed by simply pressing return after the last line. The '/' symbol denotes the end of a block and has to follow all PL/SQL blocks.

The following syntax creates a simple stored procedure that greets the current user and prints out the current date and time. We declare a variable called this_user and initialize the variable's value to the current user using the variable assignment operator ':='(user is a system function that returns the session user). We use the string concatenate operator '||' to combine literal strings such as 'Hello ' with the values of variables such as this_user, current_time, and current_date. The 'CREATE or REPLACE PROCEDURE greeting command creates a procedure with the name greeting. This is followed by the keyword 'IS', variable initializations and declaration, and finally the code within the 'BEGIN' and 'END' blocks.

CREATE OR REPLACE PROCEDURE greeting
IS
this_user VARCHAR2(8) := user;
BEGIN
dbms_output.put_line('Hello  ' || this_user || '!');
dbms_output.put_line('It is ' || to_char(sysdate, 'HH24:MI:SS') || ' on ' || current_date);
END;
/

Once you create the procedure, execute it as follows. The output is also shown. Do not forget to issue the 'SET SERVEROUTPUT ON' command before executing the procedure.

SQL> exec greeting;
Hello  BOOKSDB!
It is 16:26:39 on 11-DEC-06
 
PL/SQL procedure successfully completed.

A PL/SQL procedure may be a lot more complicated and includes several loops or conditional statements. It may also take input parameters and return output. The following stored procedure takes in the author's lastname and firstname as parameters and returns the total number of books written by the author placed on order.

CREATE OR REPLACE PROCEDURE book_orders
(lastname_param VARCHAR2, firstname_param VARCHAR2)
IS
book_count NUMBER;
BEGIN
SELECT sum(quantity) INTO book_count
FROM book, author, orderdetails
WHERE book.authorid = author.authorid
AND orderdetails.bookid = book.bookid
AND author.lastname = lastname_param
AND author.firstname = firstname_param;
IF book_count > 1 THEN
dbms_output.put_line(book_count  || ' books by '
||lastname_param || ', ' || firstname_param ||' ordered');
ELSIF book_count = 1 THEN
dbms_output.put_line('One book by '
||lastname_param || ', ' || firstname_param ||' ordered');
ELSE
dbms_output.put_line('No books by '
||lastname_param || ', ' || firstname_param ||' ordered');
END IF;
END;
/

The above program shows the number of books by the specified author in the orderdetails table. The variable 'book_count' is returned from the procedure; this variable is set to contain the results of the join query that produces the desired result through the 'SELECT sum(quantity) INTO book_count' clause. Select statements whose results are used to set a variable in a stored procedure also use the 'INTO' clause to store the results by name. Also note that the IF - ELSIF - ELSE - ENDIF loop prints out a custom message based on the number of books found. Run the stored procedure in the following way after creation (remember to issue SET SERVEROUTPUT ON if you have not yet issued this command.):

SQL> exec book_orders('Shakespeare','William');
5 books by Shakespeare, William ordered
 
PL/SQL procedure successfully completed.

The above program is limited - it is hard to specify an exact author name string with first name and last name. How would we make this procedure more general? We could fix it so that a short form of just the last name is taken as input. The procedure should then return a sorted list of all authors whose lastname matches the input string and the books by the author in the orderdetails table. This result, however, involves sending back a set of rows. This may be accomplished by using a Cursor in the following way:

CREATE OR REPLACE PROCEDURE book_orders
(lastname_param VARCHAR2)
IS
author VARCHAR2(40);
book_count NUMBER;
CURSOR books_sum IS
SELECT lastname||', '||firstname AS AUTHOR, sum(quantity)
FROM book, author, orderdetails
WHERE book.authorid = author.authorid
AND orderdetails.bookid = book.bookid
AND author.lastname like initcap(lastname_param||'%')
GROUP BY lastname, firstname
ORDER BY lastname, firstname;
BEGIN
OPEN books_sum;
LOOP
FETCH books_sum INTO author, book_count;
EXIT WHEN books_sum%NOTFOUND;
dbms_output.put_line(book_count  || ' books by ' || author);
END LOOP;
CLOSE books_sum;
END;
/

We declare a cursor called books_sum and variables author and book_count. We set the query we used (with a few modifications - mainly, we use wildcards and the INITCAP function to format the input keyword) within the program last time as the cursor variable books_sum. Within the block, we simply open the cursor, execute a loop for each line of the cursor obtained through the FETCH command (the returned values are stored in variables author and book_count), print a message about the number of books for the author, exit when new rows are not found, and close the cursor. Note that the EXIT statement simply sets the condition for exit from the loop while the END LOOP statement actually exits from the loop. The following are a couple of outputs for the new and improved book_orders function. We can now look for authors merely by typing part of the first name in either upper or lowercase.

SQL< exec book_orders('b');
2 books by Barnum, Phineas T.
1 books by Brown, Dan
 
PL/SQL procedure successfully completed.
 
SQL< exec book_orders('car');
2 books by Carroll, Lewis
2 books by Carson, Rachel
 
PL/SQL procedure successfully completed.

A function is very similar to a stored procedure. However, a function always returns a single value through the 'return' statement. A function may be used as part of a SQL command like any oracle built-in function. The following function computes and returns the discounted price of a book given the unitprice and discount percentage.

create or replace function apply_discount (unitprice number,discount number)
return number is
book_discount number(7,2);
begin
book_discount := unitprice*(100-discount)/100;
return book_discount;
end apply_discount;
/

The following query returns the total price after applying the discount of each order; it uses the apply_discount function to calculate the total price. The NVL oracle function returns the discount percent if available or '0' if the discount column contains null. Partial results are shown underneath.

SQL> select orderid,
sum(quantity*(apply_discount(unitprice,nvl(discount,0))))
AS TOTALPRICE
from orderdetails
group by orderid
order by orderid;
ORDERID TOTALPRICE
---------- ----------
1      62.75
2        140
3      24.75
4     135.98
5     218.88
6     244.23
7        389
8     267.08
9     203.03
10     195.48
11     186.49

Many logically related procedures, functions, and blocks may be bundled into a package to aid modularity. This modular package may then be used as a whole by developers as a handy interface to accomplish related tasks. A package consists of the specification and body. The following is a sample syntax for an invoice creation package.

create package manage_orders as
function apply_discount(orderid number)
return number;
procedure get_details(orderid number);
procedure get_customer_details(orderid number);
procedure make invoice(orderid number);
end manage_orders;
create package body manage_orders as
.
.
(Code for all Functions/stored procedures)
.
.
end manage_orders;

A trigger database object is used to execute sets of commands each time a certain event happens. In other words, a specified event 'triggers' execution of the commands. You may have wondered how the primary key for each table we created gets inserted as it does not have an entry in the corresponding SQL script (authorid for author.sql , boookid book.sql etc.). You may remember that we created sequences for each primary key. Look at the booksdbdd.sql script. You will see several blocks of the following sort:

CREATE OR REPLACE TRIGGER  "BI_AUTHOR"
BEFORE INSERT on "AUTHOR"
FOR EACH ROW
BEGIN
select "AUTHOR_SEQ".nextval into :NEW.AUTHORID from dual;
END;
 
ALTER TRIGGER  "BI_AUTHOR" ENABLE

The above trigger is fired before the insertion of each new row into the AUTHOR table. It performs just one function: getting the next value of the author_seq sequence from the author_seq object and putting this into the NEW.AUTHORID System variable that points to the authorid cell in the to-be-created row. After NEW.AUTHORID is set, Oracle uses this variable to automatically acquire the next authorid for insertion and inserts it along with the rest of the row. Each primary key has an associated trigger. Note that we enabled the trigger with an 'ALTER' statement. Triggers may be enabled or disabled. If you disable the trigger, new insert statements that contain no authorid will no longer work

The above trigger specifies BEFORE INSERT; these keywords may include AFTER followed by DELETE, INSERT, or UPDATE as the situation warrants. The trigger is executed accordingly. The 'FOR EACH ROW' directive executes the trigger for each inserted row. Some triggers do not contain this clause; such triggers are executed just once for each set of insert, update, or delete statements.