Procedures Stored in the Schema

PL/SQL
 Why PL/SQL?
• Database vendors usually provide a procedural language as an
extension to SQL.
– PL/SQL (ORACLE)
– Microsoft SQL Server, Sybase provide their languages – similar to
PL/SQL

• Such a language is needed in order to code various business rules,

such as:
– “When you sell a widget, total the monthly sale figures, and
decrease the widget inventory.”
– “Only a manager can discount blue widgets by 10%.”

• Such rules are impossible to be enforced by just using SQL.

• Before PL/SQL, the only way was to bundle SQL statements in complex
procedural programs running outside the DB server, in order to enforce
business rules.
“I’d rather use a ‘real’ language like C!”
While using a “host language” like C or JAVA can work, more or less, it has
some limitations:
1. C and JAVA variables store data in different binary format from that of
the database.
Hence, each time a value is retrieved from the database into a C
variable a conversion is being done, which when multiplied by
thousands or millions of occurrences, it can turn to be a big impact.
2. Also the range of the data types is different.
E.g. where would you store a database INT, which can be 38 digits
long? If we are not careful and assign it to a C int or even a long, the
value can be truncated.
3. There are missing datatypes in C and JAVA, which are present in
databases e.g. DATE.
In such cases we use conversions to strings, which are expensive.
Even more expensive is then the string manipulation…
 PL/SQL advantages
PL/SQL offers the following advantages:
1. PL/SQL variables store data in the same binary format with that of
the database. Hence there is no need for data conversions.
2. PL/SQL provides the same data types as the database.
There aren’t the C or JAVA problems related to different
datatype ranges or missing datatypes.
3. A PL/SQL program can run without the human invocation (e.g.
triggers).
Such programs can be used to encode business rules
protecting and securing our data (in an elegant and
centralized way).
 The first PL/SQL program – Anonymous blocks
• You can execute this from the SQLPLUS command prompt:
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/
• This is called an anonymous block – that is a block without a name.
– A block is surrounded by BEGIN, END keywords.
– The built-in procedure PUT_LINE, part of the DBMS_OUTPUT
package, is being called. This procedure takes a string as input and
displays that string on screen.
– The / indicates that we are finished.
• However, in order to really see the “Hello world” message on the screen,
you should set the SQLPLUS environment variable:
SQL> SET SERVEROUTPUT ON
 Procedures
CREATE OR REPLACE Procedure HelloWorld IS
msg VARCHAR(50); --a local variable
BEGIN
msg := 'Hello world from a Procedure!';
DBMS_OUTPUT.PUT_LINE(msg);
END; := is used for variable
assignment
/
(not = like in C and JAVA)

To call it we need to use an anonymous block.

BEGIN
HelloWorld;
END;
/
 Variables
• Syntax for declaring variables: • Variables before being used should
be declared in the declaration section
variable_name DATATYPE of a block (not inside the block).
[CONSTANT] • PL/SQL data types are a superset of
the Oracle data types, i.e. what most
[:= | DEFAULT initial_value]
of the cases we use is:
• VARCHAR2(n)
• If keyword CONSTANT is present, • INT
the initial value of the variable can’t
be changed. • NUMBER[(n,m)]

• := or DEFAULT are synonyms in • DATE

for assigning an initial value to the • There are also, other types that are
variable. only in PL/SQL:
• E.g. • PLS_INTEGER (smaller int)
name VARCHAR2 := 'Oracle'; • BOOLEAN (TRUE, FALSE, or
name VARCHAR2 DEFAULT 'Oracle'; NULL)

cur_date DATE := SYSDATE;

• := Assignment
Operators
• + Addition
• - Subtraction
(Superset of those for SQL)
• / Division Examples
• * Multiplication
• ** Power square := x**2;

• AND, OR, NOT Logical operators

square_root := x**0.5;
• = Equality
• !=, <>, ~=, ^= Inequality (four
order_overdue BOOLEAN :=
variants) ship_date > '28-Feb-2008' OR
priority_level(company_id) = 'High';
• <, > Less than, Greater than
• <=, >=
full_name = 'Chris' || 'Smith';
• IN membership in a set
• BETWEEN Range test
IF number_of_pages IS NULL
• IS NULL, IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Unknown');
• LIKE (as in SQL for strings)
END;
• || Concatenation of strings
 Stored procedures
Syntax to create a stored procedure is as follows:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 MODE DATATYPE [DEFAULT expression],
parameter2 MODE DATATYPE [DEFAULT expression],
…)]
AS • MODE can be IN for read-only parameters, OUT for
write-only parameters, or IN OUT for both read and
[variable1 DATATYPE; write parameters.
variable2 DATATYPE; • The DATATYPE can be any of the types we already
…] have mentioned but without the dimensions, e.g.

BEGIN • VARCHAR2, NUMBER,…, but not

statements • VARCHAR2(20), NUMBER(10,3)…

END;
/
Variable Declaration
Section (local variables).
 Stored Functions
Syntax to create a stored function:
CREATE [OR REPLACE] FUNCTION function_name
[(parameter1 MODE DATATYPE [DEFAULT expression],
parameter2 MODE DATATYPE [DEFAULT expression],
…)] RETURN DATATYPE
AS
• If you omit it mode it will implicitly be IN.
[variable1 DATATYPE;
• In the header, the RETURN DATATYPE is part of the
variable2 DATATYPE; …] function declaration and it is required. It tells the
BEGIN compiler what datatype to expect when you invoke the
function.
statements
• RETURN inside the executable section is also required.
RETURN expression;
• If you miss the the RETURN clause in the declaration,
END; the program won’t compile.
/ • On the other hand, if you miss the RETURN inside the
body of the function, the program will execute but at
runtime Oracle will give the error ORA-06503: PL/SQL:
Function returned without a value.
 Dropping, viewing stored programs
DROP PROCEDURE add_book;

• A natural question about a stored procedure or function is: Since it’s

stored, can we view it?
• The answer is yes:

SELECT text
FROM User_source
WHERE name = 'ADD_BOOK'
ORDER BY line;
• Observe that the name of the procedure or function is stored in upper
case.
 Conditional logic –IF statement
IF condition1 THEN
statements
[ELSIF condition2 THEN] Comments
statements • The end of the IF statement is
[ELSE “END IF;” with a space in
last_statements] between.
END IF;
• The “otherwise if” is ELSIF not
ELSEIF
Examples
IF hourly_wage < 10 THEN
• You can put parenthesis around
boolean expression after the IF
hourly_wage := hourly_wage * 1.5;
and ELSIF but you don’t have
ELSE to.
hourly_wage := hourly_wage * 1.1;
• You don’t need to put {, } or
END IF;
BEGIN, END to surround
several statements between IF
IF salary BETWEEN 10000 AND 40000 THEN
and ELSIF/ELSE, or between
bonus := 1500; ELSIF/ELSE and END IF;
ELSIF salary > 40000 AND salary <= 100000
THEN bonus := 1000;
ELSE bonus := 0;
END IF;
Conditional logic –Simple CASE statement
CASE selector CREATE OR REPLACE PROCEDURE
WHEN expression_1 THEN statements PrintQualification(grade IN CHAR) AS
[WHEN expression_2 THEN statements] BEGIN
[ELSE statements] CASE grade
WHEN 'A' THEN
END CASE; dbms_output.put_line('Excellent');
• selector can be an expression of WHEN 'B' THEN
any datatype, and it provides the dbms_output.put_line('Very Good');
WHEN 'C' THEN
value we are comparing.
dbms_output.put_line('Good');
• expression_n is the expression to WHEN 'D' THEN
test for equality with the selector. dbms_output.put_line('Fair');
WHEN 'F' THEN
• If no WHEN matches the selector dbms_output.put_line('Poor');
value, then the ELSE clause is ELSE dbms_output.put_line('No such
executed. grade');
END CASE;
• If there is no ELSE clause PL/SLQ END;
will implicitly supply: /
ELSE RAISE
BEGIN
CASE_NOT_FOUND; which will PrintQualification('B');
terminate the program with an error END;
(if the program ends up in the ELSE /
clause).
 Iterative Control: LOOP and EXIT Statements
• Three forms of LOOP statements: E.g. We wish to categorize salaries
according to their number of digits.
• LOOP, CREATE OR REPLACE FUNCTION
• WHILE-LOOP, and SalDigits(salary INT) RETURN INT
AS
• FOR-LOOP. digits INT := 1;
temp INT := salary;
• LOOP: The simplest form of LOOP BEGIN
statement is the basic (or infinite) LOOP
loop: temp := temp / 10;
LOOP
IF temp = 0 THEN
statements EXIT;
END IF;
END LOOP; --Or we can do: EXIT WHEN temp = 0
• You can use an EXIT statement to digits := digits + 1;
complete the loop. END LOOP;
RETURN digits;
• Two forms of EXIT statements: END;
/
• EXIT and
• EXIT WHEN. BEGIN
dbms_output.put_line(SalDigits(150000));
END;
/
 WHILE-LOOP
E.g. We wish to categorize salaries
WHILE condition according to their number of digits.
LOOP CREATE OR REPLACE FUNCTION
statements SalDigits(salary INT) RETURN INT
END LOOP; AS
digits INT := 1;
temp INT := salary;
BEGIN

WHILE temp > 0

LOOP
digits := digits + 1;
temp := temp / 10;
END LOOP;

RETURN digits;
END;
/

BEGIN
dbms_output.put_line(SalDigits(150000));
END;
/
 FOR Loops
• FOR loops iterate over a specified range of integers.

FOR counter IN [REVERSE] low .. high for i in 1..1000 loop

LOOP INSERT INTO a
sequence_of_statements VALUES(i,i*2);
end loop;
END LOOP;

Comments
The range is evaluated when the FOR loop is first
entered and is never re-evaluated.
By default, iteration proceeds upward from the lower
bound to the higher bound. However, if you use the
keyword REVERSE, iteration proceeds downward from
the higher bound to the lower bound.
Nevertheless, you write the range bounds in ascending
(not descending) order.
Inside a FOR loop, the loop counter can be referenced
like a constant but cannot be assigned values.
 An Example
• Let’s build a system that will assist
CREATE TABLE books (
in the cataloging and searching of isbn VARCHAR2(13) PRIMARY KEY,
library books. title VARCHAR2(200),
• For now, we’d like to address two summary VARCHAR2(2000),
author VARCHAR2(200),
requirements:
date_published DATE,
1. Allow the creation of catalog page_count NUMBER
entries for each newly );
acquired book.
2. Provide means of counting CREATE TABLE book_copies (
how many copies of a barcode_id VARCHAR2(100) PRIMARY
KEY,
particular book the library
isbn VARCHAR2(13) REFERENCES
owns. books(isbn)
• A simple E/R schema would be: );

Books Owns Book copies

Implementing a stored procedure to add a book
CREATE OR REPLACE PROCEDURE add_book (
isbn_in IN VARCHAR2,
barcode_id_in IN VARCHAR2,
title_in IN VARCHAR2,
author_in IN VARCHAR2,
page_count_in IN NUMBER,
summary_in IN VARCHAR2 DEFAULT NULL,
date_published_in IN DATE DEFAULT NULL
) AS
BEGIN
/*Check for reasonable inputs*/
IF isbn_in IS NULL THEN
RAISE VALUE_ERROR;
END IF;

INSERT INTO books (isbn, title, summary, author, date_published, page_count)

VALUES (isbn_in, title_in, summary_in, author_in, date_published_in, page_count_in);

/*if barcode is supplied, put a record in the book_copies table*/

if NOT(barcode_id_in IS NULL) then
INSERT INTO book_copies (isbn, barcode_id)
VALUES(isbn_in, barcode_id_in);
end if;
END add_book;
/
 Using the procedure to add a book
BEGIN
add_book(
'1-56592-335-9',
'100000002',
'Oracle PL/SQL Programming',
'Feuerstein, Steven, with Bill Pribyl',
987,
'Reference for PL/SQL developers, ' ||
'including examples and best practice recommendations.',
TO_DATE('01-Sep-1997', 'DD-MON-YYYY')
);
END;
/
 Adding a book…
• Parameter names: It’s good if you follow the convention to end the IN
parameters with a suffix _in. (similarly _out for the OUT parameters, or _inout for
the INOUT parameters).
• Such naming is not compulsory but helps in avoiding conflicts with the columns
names. E.g.
• If we didn’t put the _in suffix, then it is hard to read code like this:
INSERT INTO books (isbn, title, summary, author, date_published, page_count)
VALUES (isbn, title, summary, author, date_published, page_count);
• Are they column names or are they PL/SQL variables?
• In this particular example it turns out that PL/SQL is able to interpret isbn…
page_count of the first line as table columns, while in second as PL/SQL
variables.
• But, what about this:
UPDATE Books
SET summary = summary
WHERE isbn = isbn;
• This won’t work!
 Cursors
Fundamental challenge: SQL is a set oriented language while procedure oriented
languages like PL/SQL are record (or tuple oriented).
Solution: use cursors. Example: • When we open a cursor, behind
CREATE OR REPLACE PROCEDURE favorite_play AS the scene, Oracle reads
favorite_play_title VARCHAR(200); (parses) the statement, and
publication_date DATE; associates with it those rows in
CURSOR bcur IS the table that satisfy the query.
SELECT title, date_published • After we open a cursor we fetch
FROM books row by row. We need to make
WHERE UPPER(author) sure we list as many variables
LIKE 'SHAKESPEARE%'; as there are columns in the
BEGIN tuples returned from the SQL
OPEN bcur; statement.
LOOP
FETCH bcur INTO favorite_play_title, • The FETCH in the example
publication_date; retrieves one row.
EXIT WHEN bcur%NOTFOUND; • A second FETCH would
overwrite the values written
/*Do something useful with by the first FETCH.
favorite_play_title and publication_date */
END LOOP; • You cannot re-fetch a tuple
CLOSE bcur; already fetched. You
END; should close and open
/ again the cursor.
 Cursor attributes
• General form is CREATE OR REPLACE PROCEDURE
• cursor_name%ATTRIBUTE_NAME favorite_play AS

• This is similar to the accessing some favorite_play_title VARCHAR(200);

attribute of some record with the dot publication_date DATE;
notation. Here instead of a dot we CURSOR bcur IS
use the % sign. SELECT title, date_published
FROM books
WHERE UPPER(author) LIKE
'SHAKESPEARE%';
• cursor_name%FOUND is BOOLEAN BEGIN
and it’s TRUE if the most recent fetch OPEN bcur;
found a row to return; otherwise FALSE. LOOP
FETCH bcur INTO favorite_play_title,
publication_date;
EXIT WHEN bcur%NOTFOUND;
• cursor_name%NOTFOUND is
BOOLEAN and it’s the logical inverse of /*Do something useful with
%FOUND. favorite_play_title and
publication_date */
END LOOP;
• cursor_name%ROWCOUNT is CLOSE bcur;
NUMBER and it contains the number of END;
/
rows fetched so far.
 Shortcut – Anchored declarations
variable_name table_name.column_name CREATE OR REPLACE PROCEDURE
%TYPE; favorite_play AS

favorite_play_title books.title%TYPE;
publication_date DATE;
CURSOR bcur IS
SELECT title, date_published
FROM books
WHERE UPPER(author) LIKE
'SHAKESPEARE%';
BEGIN
OPEN bcur;
LOOP
FETCH bcur INTO favorite_play_title,
publication_date;
EXIT WHEN bcur%NOTFOUND;

/*Do something useful with

favorite_play_title and
publication_date */
END LOOP;
CLOSE bcur;
END;
/
 Retrieving a book count with a function
CREATE OR REPLACE FUNCTION book_copy_qty (isbn_in VARCHAR2) return NUMBER
AS
number_of_copies NUMBER := 0;
CURSOR bc_cur IS
SELECT count(*) FROM book_copies WHERE isbn = isbn_in;

BEGIN
We can test this function as:
IF isbn_in IS NOT NULL THEN
OPEN bc_cur;
set serveroutput on
FETCH bc_cur INTO number_of_copies;
DECLARE
how_many INT;
CLOSE bc_cur;
END IF;
BEGIN
dbms_output.put_line(
RETURN number_of_copies;
'Number of copies of 1-56592-335-9: ' ||
END;
book_copy_qty('1-56592-335-9'));
/
END;
/