Why PLSQL

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

2
Id rather use a real language like C!

• While using a host language like C can work,
  more or less, it has some limitations
• The Cs 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.
• 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.
• There are missing datatypes in C, 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
• A C program running outside the database, which
  can be invoked only by humans, comes up lacking
  when we need to protect and secure our data.
• Surely, the DBA has some control over who can
  change some data. So Herman in accounting may
  receive UPDATE privileges on a receivables table.
  We might try to control what operations he can
  perform by programming some business rules in the
  C program that he uses. Well he is supposed to
  use it anyway! If he happens to have, say,
  Microsoft Excel, and he happens to also have
  connector software to let it talk to Oracle,
  boom! Herman can bypass all our carefully
  programmed security checks!

3
PL/SQL advantages

• PL/SQL offers the following advantages
• The PL/SQL variables store data in the same
  binary format with that of the database.
• Hence there is no need for data conversions.
• PL/SQL provides the same data types as the
  database.
• There arent the C problems related to different
  datatype ranges or missing datatypes.
• 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, but also
  for encoding other business rules elegantly and
  in a centralized way.

4
The first PL/SQL program

• 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
• SQLgt SET SERVEROUTPUT ON

5
Anonymous blocks

• There are several forms an anonymous block can
  have. Previously we saw one
• BEGIN
• statements
• END
• Interestingly, a block is just a long PL/SQL
  statement notice that the block itself ends with
  a semicolon. Hence blocks can be nested as we
  wish.
• The second major form of an anonymous block
  includes a declaration section
• DECLARE
• declarations
• BEGIN
• statements
• END

• DECLARE
• msg VARCHAR(30) --a variable
• BEGIN
• msg Hello world
• DBMS_OUTPUT.PUT_LINE(msg)
• END
• The general form of an anonymous block is
• DECLARE
• declarations
• BEGIN
• statements
• EXCEPTION
• exception handlers
• END
• Exception handlers are statements that will run
  when certain error conditions occur.
• An exception section contains the logic that
  describes how your block will respond to errors
  that occur inside the execution section.

6
Variables

• Every variable before being used should be
  declared in DECLARE section.
• PL/SQL data types are a superset of the Oracle
  data types. I.e. what most of the cases we use
  is
• VARCHAR2(n)
• INT
• NUMBER(n,m)
• DATE
• There are also, other types that are only in
  PL/SQL
• PLS_INTEGER (smaller integer)
• BOOLEAN (Can be TRUE, FALSE, NULL)

• The syntax for declaring variables
• variable_name DATATYPE CONSTANT DEFAULT
  initial_value
• If the keyword CONSTANT is present the initial
  value of the variable cannot be changed.
• or DEFAULT are synonyms in this context for
  assigning a initial value to the variable.
• The initial value should be a literal adhering to
  the rules for the datatype.
• E.g.
• name VARCHAR2 'Oracle'
• cur_date DATE SYSDATE

7
Operators

• Assignment
• Addition
• - Subtraction
• / Division
• Multiplication
• Power
• AND, OR, NOT Logical operators
• Equality
• !, ltgt, , Inequality (four variants)
• lt, gt Less than, Greater than
• lt, gt
• IN membership in a set
• BETWEEN Range test
• IS NULL, IS NOT NULL
• LIKE (as in SQL for strings)
• Concatenation of strings

Examples square x2 square_root
x0.5 order_overdue BOOLEAN
ship_date gt ADD_MONTHS(order_date, 3) OR
priority_level(company_id) 'High' full_name
'Chris' 'Smith' IF number_of_pages IS
NULL THEN DBMS_OUTPUT.PUT_LINE('Unknown') END
8
Conditional logic IF statement
IF condition1 THEN statements ELSIF condition2
THEN statements ELSE last_statements END
IF Examples IF hourly_wage lt 10
THEN hourly_wage hourly_wage
1.5 ELSE hourly_wage hourly_wage 1.1 END
IF IF salary BETWEEN 10000 AND 40000 THEN bonus
1500 ELSIF salary gt 40000 AND salary lt
100000 THEN bonus 1000 ELSE bonus 0 END
IF

• Comments
• If none of the conditions are true and there is
  an ELSE clause, then the last_statements are
  executed.
• The end of the IF statement is END IF with a
  space in between.
• The otherwise if is ELSIF not ELSEIF
• You can put parenthesis around boolean expression
  after the IF and ELSIF but you dont have to.
• You dont need to put , or BEGIN, END to
  surround several statements between IF and
  ELSIF/ELSE, or between ELSIF/ELSE and END IF

9
Conditional logic Simple CASE statement

• CASE selector
• WHEN expression1 THEN statements
• WHEN expression2 THEN statements
• ELSE statements
• END CASE
• selector can be an expression of any datatype,
  and it provides the value we are comparing.
• expressionn is the expression to test for
  equality with the selector.
• If no WHEN matches the selector value, then the
  ELSE clause is executed.
• If there is no ELSE clause PL/SLQ will implicitly
  supply ELSE RAISE
  CASE_NOT_FOUND which will terminate the
  program with an error (if we dont catch this
  exception).

E.g. DECLARE grade VARCHAR2(1)
'B' BEGIN CASE grade WHEN 'A' THEN
dbms_output.put_line('Excellent') WHEN 'B'
THEN dbms_output.put_line('Very Good') WHEN
'C' THEN dbms_output.put_line('Good') WHEN
'D' THEN dbms_output.put_line('Fair') WHEN
'F' THEN dbms_output.put_line('Poor') ELSE
dbms_output.put_line('No such grade') END
CASE END /
10
Conditional logic Searched CASE statement

• Consider the following IF statement
• IF salary BETWEEN 10000 AND 40000 THEN
• bonus 1500
• ELSIF salary gt 40000 AND salary lt 100000 THEN
  bonus 1000
• ELSE bonus 0
• END IF
• Can we rewrite this IF statement using the
  previous simple CASE statement?
• The answer is No! We need a more powerful
  statement than the simple CASE we need the
  searched CASE.
• CASE
• WHEN search_condition1 THEN statements1
• WHEN search_condition2 THEN statements2 ...
• ELSE statements
• END CASE

• Again, if there is no ELSE clause PL/SLQ will
  implicitly supply ELSE
  RAISE CASE_NOT_FOUND which will terminate
  the program with an error (if we dont catch
  exception).
• DECLARE
• salary INT 50000
• bonus INT
• BEGIN
• CASE
• WHEN salary BETWEEN 10000 AND 40000
• THEN bonus 1500
• WHEN salary gt 40000 AND salary lt 100000
• THEN bonus 1000
• ELSE bonus 0
• END CASE
• dbms_output.put_line('Bonus is ' bonus)
• END
• /

11
When CASE is an expression instead

• A CASE statement can also serve as an expression
• DECLARE
• grade CHAR(1) 'B'
• appraisal VARCHAR2(20)
• BEGIN
• DBMS_OUTPUT.PUT_LINE(appraisal)
• appraisal
• CASE grade
• WHEN 'A' THEN 'Excellent'
• WHEN 'B' THEN 'Very Good'
• WHEN 'C' THEN 'Good'
• WHEN 'D' THEN 'Fair'
• WHEN 'F' THEN 'Poor'
• ELSE 'No such grade'
• END
• DBMS_OUTPUT.PUT_LINE(appraisal)
• END

• Another example
• DECLARE
• salary INT 50000
• bonus INT
• BEGIN
• bonus CASE
• WHEN salary BETWEEN 10000 AND 40000
• THEN 1500
• WHEN salary gt 40000 AND salary lt 100000
• THEN 1000
• ELSE 0
• END
• dbms_output.put_line('Bonus is ' bonus)
• END
• /
• Observe how bonus takes the value.
• Notice the absence of the terminators .
• Notice the automatic conversion of bonus into
  string when we print on the screen.

12
Iterative Control LOOP and EXIT Statements

• Another example
• E.g. Suppose we wish to categorize salaries
  according to their number of digits.
• DECLARE
• digits INT 1
• salary INT 3005000
• temp INT salary
• BEGIN
• LOOP
• temp temp / 10
• IF temp 0 THEN
• EXIT
• END IF
• --Or we can do EXIT WHEN temp 0
• digits digits 1
• END LOOP
• dbms_output.put_line('Number of digits is '
  digits)

• LOOP statements let you execute a sequence of
  statements multiple times.
• There are three forms of LOOP statements
• LOOP,
• WHILE-LOOP, and
• FOR-LOOP.
• LOOP The simplest form of LOOP statement is the
  basic (or infinite) loop, which encloses a
  sequence of statements between the keywords LOOP
  and END LOOP, as follows
• LOOP
• statements
• END LOOP
• You can use an EXIT statement to complete the
  loop.
• There are two forms of EXIT statements EXIT and
  EXIT-WHEN.

13
WHILE-LOOP
DECLARE digits INT 0 salary INT
3005000 temp INT salary BEGIN WHILE temp
gt 0 LOOP digits digits 1 temp
temp / 10 END LOOP dbms_output.put_line(
'Number of digits is ' digits) END /
WHILE condition LOOP statements END LOOP
14
FOR Loops

• FOR loops iterate over a specified range of
  integers.
• FOR counter IN REVERSE low .. high
• LOOP
• sequence_of_statements
• END LOOP
• 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.
• What happens if you had declared the loop control
  variable before?

15
Background on the example
CREATE TABLE books ( isbn VARCHAR2(13)
PRIMARY KEY, title VARCHAR2(200),
summary VARCHAR2(2000), author
VARCHAR2(200), date_published DATE,
page_count NUMBER ) CREATE TABLE book_copies
( barcode_id VARCHAR2(100) PRIMARY KEY,
isbn VARCHAR2(13) REFERENCES
books(isbn) )

• Lets build a system that will assist in the
  cataloging and searching of library books.
• For now, wed like to address two requirements
• Allow the creation of catalog entries for each
  newly acquired book.
• Provide means of counting how many copies of a
  particular book the library owns.
• A simple E/R schema would be

16
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 / show errors

• If you dont set
• show errors
• and you have compilation, PL/SQL will just tell
  you the fact that there are errors, but wont
  show you them.

17
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 /
18
Adding a book

• Parameter names Its 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 didnt 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)
• What are those column names or are they PL/SQL
  variables? In this particular example it turns
  out that PL/SQL is able to interpret
  isbnpage_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
• The above wont work!

19
Retrieving a book count with a function (contin.)
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 IF isbn_in IS NOT NULL THEN
OPEN bc_cur FETCH bc_cur INTO
number_of_copies CLOSE bc_cur END
IF RETURN number_of_copies END / show
errors

• bc_cur is cursor we will talk later about them.
  For the moment consider it as a vehicle for
  carrying one-tuple-at-a-time from the set result
  of a query.
• We can test this function as
• set serveroutput on
• DECLARE
• how_many INTEGER
• BEGIN
• dbms_output.put_line(
• 'Number of copies of 1-56592-335-9 '
  book_copy_qty('1-56592-335-9'))
• END
• /

20
Dropping, viewing stored programs

• The REPLACE keyword that we were adding saved us
  from dropping a procedure or function each time
  we do a new version of them. However, suppose
  that we dont want anymore a procedure or
  function at all. Then we can drop them
• DROP PROCEDURE add_book
• DROP FUNCTION book_copy_qty

• A natural question about a stored procedure or
  function is Since its 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.

21
Stored procedures

• The 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
• variable1 DATATYPE
• variable2 DATATYPE
• BEGIN
• statements
• EXCEPTION
• WHEN exception_name
• THEN
• statements
• END
• /

• MODE can be IN for read-only parameters, OUT for
  write-only parameters, or IN OUT for both read
  and write parameters.
• The DATATYPE can be any of the types we already
  have mentioned but without the dimensions, e.g.
• VARCHAR2, NUMBER,, but not
• VARCHAR2(20), NUMBER(10,3)

22
Functions

• Before, lets see the 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
• variable1 DATATYPE
• variable2 DATATYPE
• BEGIN
• statements
• RETURN expression
• EXCEPTION
• WHEN exception_name
• THEN
• statements
• END
• /

• If you omit it mode it will implicitly be IN.
• In the header the RETURN DATATYPE is part of the
  function declaration and it is required. It tells
  the compiler what datatype to expect when you
  invoke the function.
• RETURN inside the executable section is also
  required.
• If you miss the the RETURN clause in the
  declaration, the program wont 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.

23
Creating some data for the examples
DECLARE PROCEDURE insert_book_no_complaints
(isbn_in IN VARCHAR2, title_in IN VARCHAR2,
summary_in IN VARCHAR2, author_in IN VARCHAR2,
date_published_in IN DATE, page_count_in IN
NUMBER) IS BEGIN 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) EXCEPTION WHEN
DUP_VAL_ON_INDEX THEN NULL
END PROCEDURE insert_copy_no_complaints
(barcode_id_in IN VARCHAR2, isbn_in IN
VARCHAR2) IS BEGIN INSERT INTO
book_copies (barcode_id, isbn) VALUES
(barcode_id_in, isbn_in) EXCEPTION WHEN
DUP_VAL_ON_INDEX THEN NULL END
24
BEGIN insert_book_no_complaints
('0-596-00180-0', 'Learning Oracle
PL/SQL', 'Beginner''s guide to Oracle''s
PL/SQL Programming Language', 'Bill Pribyl
with Steven Feuerstein', '29-Nov-2001',
401) insert_book_no_complaints
('1-56592-335-9', 'Oracle PL/SQL
Programming', 'Reference for PL/SQL
developers, including examples and best practice
' 'recommendations.',
'Feuestein, Steven, and Bill Pribyl',
'01-SEP-1997', 987) insert_book_no_comp
laints ('1-56592-457-6', 'Oracle PL/SQL
Language Pocket Reference',
'Quick-reference guide for Oracle PL/SQL
developers. Includes Oracle8i '
'coverage.', 'Feuerstein, Steven, Bill
Pribyl, Chip Dawes', '01-APR-1999', 94)
25
insert_book_no_complaints ('0-14071-483-9',
'The tragedy of King Richard the Third',
'Modern publication of popular Shakespeare
historical play in which a '
'treacherous royal attempts to steal the crown
but dies horseless ' 'in battle.',
'Shakespeare, William', '01-AUG-2000',
158) insert_book_no_complaints
('0-14-071415-4', 'The Tempest',
'Duke and daughter on enchanted island encounters
former enemies in this ' 'comic tale
of mystery, love, magic, and (ultimately)
forgiveness.', 'Shakespeare, William',
'01-JAN-1959', 120)
insert_book_no_complaints ('0-672-31798-2',
'Sams Teach Yourself PL/SQL in 21 Days, Second
Edition', 'Tutorial for Oracle''s
procedural language organized around presenting
' 'language features in a three-week
learning schedule.', 'Gennick, Jonathan,
with Tom Luers', '01-DEC-1999',
692)
26
insert_book_no_complaints ('0-07-882438-9',
'Oracle PL/SQL Tips and Techniques',
'Voluminous tome presenting tips, techniques, and
reference material on ' 'Oracle''s
PL/SQL.', 'Trezzo, Joseph C.',
'01-JUL-1999', 942) insert_book_no_comp
laints ('0-13-794314-8', 'Building
Intelligent Databases with Oracle PL/SQL Triggers
and Stored ' 'Procedures-2nd ed.',
'Programmer''s guide to PL/SQL, targeted
toward building reusable '
'components for large Oracle applications.',
'Owens, Kevin T.', '01-JUN-1999',
544) insert_book_no_complaints
('1-56592-674-9', 'Oracle PL/SQL
Developer''s Workbook', 'Beginner,
intermediate, and advanced exercises designed to
test the ' 'reader''s knowledge of
Oracle''s PL/SQL programming language.',
'Feuerstein, Steven, with Andrew Odewahn',
'01-May-1999', 588)
27
insert_copy_no_complaints ('92644384',
'0-596-00180-0') insert_copy_no_complaints
('92644385', '0-596-00180-0')
insert_copy_no_complaints ('92644386',
'0-596-00180-0') insert_copy_no_complaints
('89679588', '1-56592-335-9')
insert_copy_no_complaints ('89679589',
'1-56592-335-9') insert_copy_no_complaints
('29846701', '1-56592-457-6')
insert_copy_no_complaints ('29846702',
'1-56592-457-6') insert_copy_no_complaints
('29846703', '1-56592-457-6')
insert_copy_no_complaints ('12983106',
'0-14-071415-4') END /
28
Talking in HTML

• Oracle has created mod_plsql Apache module for
  transparently, printing HTML from a PL/SQL
  program on the browser.
• HTP.PRN(expression)
• HTP.PRINT(expression) -- prints also a trailing
  newline.
• Lets print a test page with the current time.
• http//athens.cs.uvic.ca8080/my_epg_dad/show_time

CREATE OR REPLACE PROCEDURE show_time AS
the_title VARCHAR2(30) 'What time is it on the
server?' the_time VARCHAR2(20)
TO_CHAR(SYSDATE,'DD-MON-YYYY HH24MI') html
VARCHAR2(200) BEGIN / construct the HTML in
a variable / html 'ltHTMLgt ltHEADgt
ltTITLEgt' the_title 'lt/TITLEgt lt/HEADgt
ltBODYgt It is now ' the_time
'lt/BODYgt lt/HTMLgt' / output to the browser
/ HTP.PRINT(html) END / SHOW ERRORS