OCL2 Oracle 10g: SQL

1
OCL2 Oracle 10gSQL PL/SQLSession 8

• Matthew P. Johnson
• CISDD, CUNY
• Fall, 2004

2
Agenda

• More PL/SQL
• CASE statements
• Exception-handling
• Packages
• Execution rights
• DDL in PL/SQL with dynamic PL/SQL
• Triggers

3
Case-statements

• Saw if and if-else statements last time
• Oracle 8i added support for case stmts
• Two kinds
• Simple cast stmt
• searched case stmt
• Also case expressions

4
Simple case statements
CASE expression WHEN result1 THEN
statements1 WHEN result2 THEN
statements2 ... ELSE statements_else END CASE

• General form
• ELSE is optional
• expression and results are scalars
• numbers, chars, strings, etc. not tables
• Literals or vars

5
Simple case e.g.
CASE employee_type WHEN 'S' THEN
award_salary_bonus(employee_id) WHEN 'H' THEN
award_hourly_bonus(employee_id) WHEN 'C' THEN
award_commissioned_bonus(employee_id) ELSE
RAISE invalid_employee_type END CASE
6
Simple cases ELSE clause

• This ELSE is optional, but if omitted, you get an
  implicit else clause
• Run example
• Can use a NULL statement in the ELSE clause

ELSE RAISE CASE_NOT_FOUND
declare x number 1 begin case x when 2
then dbms_output.put_line('2')
7
Searched case statement
CASE WHEN expression1 THEN statements1 WHEN
expression2 THEN statements2 ... ELSE
statements_else END CASE

• General form
• Like C/Java if switch ? case and case ?
  when
• Only the first matching WHEN clause is executed

8
Searched case e.g.
CASE WHEN salary gt 10000 AND salary lt20000
THEN give_bonus(employee_id, 1500) WHEN
salary gt 20000 AND salary lt 40000 THEN
give_bonus(employee_id, 1000) WHEN salary gt
40000 THEN give_bonus(employee_id, 500) ELSE
give_bonus(employee_id, 0) END CASE

• Q Can this be implemented as a simple case?

9
Searched case e.g.
CASE TRUE WHEN salary gt 10000 AND salary
lt20000 THEN give_bonus(employee_id,
1500) WHEN salary gt 20000 AND salary lt 40000
give_bonus(employee_id, 1000) WHEN salary gt
40000 give_bonus(employee_id, 500) ELSE
give_bonus(employee_id, 0) END CASE
10
Case expressions

• Above were case statements
• One statement/set of statements executed,
  depending on value of test expression
• Also have case expressions
• Evaluates to some expression, depending on value
  of test expression
• Case expressions, too, come in both varieties

11
Simple case expression
Simple_Case_Expression CASE expression
WHEN result1 THEN result_expression1
WHEN result2 THEN result_expression2
... ELSE result_expression_else END
Searched_Case_Expression CASE WHEN
expression1 THEN result_expression1 WHEN
expression2 THEN result_expression2 ...
ELSE result_expression_else END
12
CASE statements in SQL

• By the way CASE statements are now supported in
  Oracle SQL itself

SELECT CASE WHEN comm is null THEN 0
ELSE comm END FROM emp
13
Explicit cursors v. for loop cursors
DECLARE CURSOR occupancy_cur IS SELECT
pet_id, room_number FROM occupancy WHERE
occupied_dt TRUNC (SYSDATE) occupancy_rec
occupancy_curROWTYPE BEGIN OPEN
occupancy_cur LOOP FETCH occupancy_cur
INTO occupancy_rec EXIT WHEN
occupancy_curNOTFOUND update_bill
(occupancy_rec.pet_id, occupancy_rec.room_number
) END LOOP CLOSE occupancy_cur END
14
Explicit cursors v. for loop cursors
DECLARE CURSOR occupancy_cur IS SELECT
pet_id, room_number FROM occupancy WHERE
occupied_dt TRUNC (SYSDATE) BEGIN FOR
occupancy_rec IN occupancy_cur LOOP
update_bill (occupancy_rec.pet_id,
occupancy_rec.room_number) END LOOP END
15
Another loop e.g.
CREATE OR REPLACE PROCEDURE pay_out_balance (
account_id_in IN accounts.idTYPE) -- the type of
the var is the same as the field type! IS --IS or
AS can be used in place of DECLARE
l_balance_remaining NUMBER BEGIN LOOP
l_balance_remaining account_balance
(account_id_in) IF l_balance_remaining lt
1000 THEN EXIT -- exit from the
LOOP ELSE apply_balance
(account_id_in, l_balance_remaining) END
IF END LOOP END pay_out_balance
16
Exception handlers

• Each WHEN-THEN names a possible exception, like a
  case in a switch stmt

EXCEPTION WHEN NO_DATA_FOUND THEN
executable_statements1 WHEN DUP_VAL_ON_INDEX
THEN executable_statements1 ... WHEN
OTHERS THEN otherwise_code END
17
Function e.g.
FUNCTION company_name (company_id_in IN
company.company_idTYPE) RETURN VARCHAR2 IS
cname company.company_idTYPE BEGIN SELECT
name INTO cname FROM company WHERE company_id
company_id_in RETURN cname EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL END
18
Longer exception e.g.
CREATE OR REPLACE PROCEDURE check_account (
account_id_in IN accounts.idTYPE) IS
l_balance_remaining NUMBER
l_balance_below_minimum EXCEPTION
l_account_name accounts.nameTYPE BEGI
N SELECT name INTO l_account_name
FROM accounts WHERE id account_id_in
l_balance_remaining account_balance
(account_id_in) DBMS_OUTPUT.put_line
('Balance for ' l_account_name ' '
l_balance_remaining)
19
Longer exception e.g.
IF l_balance_remaining lt 1000 THEN
RAISE l_balance_below_minimum END
IF EXCEPTION WHEN NO_DATA_FOUND THEN
-- No account found for this ID log_error
(...) WHEN l_balance_below_minimum THEN
log_error (...) RAISE END
20
WHEN OTHERS and NULL

• Can have generic exception catcher with WHEN
  OTHERS
• To swallow all other exception types, use a null
  statement

EXCEPTION WHEN exception_name1 THEN
--do one thing WHEN exception_name2 THEN
--do another thing WHEN OTHERS THEN
null END
21
Raising exceptions

• You can raise an exception with RAISE

DECLARE exception_name EXCEPTION BEGIN
IF condition THEN RAISE exception_name
END IF EXCEPTION WHEN exception_name THEN
statement END
22
More on scope

• Can name blocks and loops with labels

ltltinsert_but_ignore_dupsgtgt BEGIN INSERT INTO
catalog VALUES (...) EXCEPTION WHEN
DUP_VAL_ON_INDEX THEN NULL END
insert_but_ignore_dups
23
Scope and nested, labeled loops
ltltouterblockgtgt DECLARE counter INTEGER
0 BEGIN ... DECLARE counter INTEGER
1 BEGIN IF counter
outerblock.counter THEN ...
END IF END END
24
Scope and nested, labeled loops
BEGIN ltltouter_loopgtgt LOOP LOOP
EXIT outer_loop END LOOP
some_statement END LOOP END
25
Packages

• Functions and procedures (and vars) can be
  grouped in packages
• Like Java packages, C namespaces, etc.
• A pkg has a specification and a body
• Somewhat like C class definitions
• Specification declares public functions
• public means can be run by a user with EXECUTE
  authority on this pkg
• Body defines all functions
• Vars defined here are visible to the pkgs
  programs

26
Package e.g.
Create or replace PACKAGE rg_select as
list_name VARCHAR2(60) PROCEDURE init_list
(item_name_in IN VARCHAR2,
fill_action_in IN VARCHAR2 'IMMEDIATE')
PROCEDURE delete_list PROCEDURE
clear_list END rg_select
27
Package body

• Package body is defined separately, containing
  actual ftn/proc implementations
• Do not preface ftns and procs with create or
  replace

Create or replace PACKAGE body rg_select
as PROCEDURE init_list (item_name_in IN
VARCHAR2, fill_action_in IN VARCHAR2
'IMMEDIATE') as End END rg_select
28
Cursors in PL/SQL

• As expected, PL/SQL has syntax to do the usual
  things
• Declare cursors
• Open and close
• Fetch and eventually leave
• Each can be done manually
• Also has elegant for/cursor loop
• Declare, open, close, fetch all automatic
• Example
• http//pages.stern.nyu.edu/mjohnson/oracle/plsql/
  for.sql

FOR my-rec IN my-cursor LOOP END LOOP
29
Record-based DML
CREATE OR REPLACE PROCEDURE set_book_info (
book_in IN booksROWTYPE) IS BEGIN INSERT INTO
books VALUES book_in EXCEPTION WHEN
DUP_VAL_ON_INDEX THEN UPDATE books SET
ROW book_in WHERE isbn
book_in.isbn END
30
Programs and rights

• By default, only the creator of a program may run
  it (apart from the admin)
• If others should run, must GRANT them permission
• Permissions can be revoked
• Can also grant to particular roles or everyone
• Wider/narrower grant ops are independent

SQLgt GRANT EXECUTE ON wordcount TO george
SQLgt REVOKE EXECUTE FROM wordcount TO george
SQLgt GRANT EXECUTE ON wordcount TO everyone
31
PL/SQL v. SQL

• There are some things SQL cant do (e.g.,
  factorial), but some problems can be solved in
  both

DECLARE CURSOR checked_out_cur IS
SELECT pet_id, name, checkout_date FROM
occupancy WHERE checkout_date IS NOT
NULL BEGIN FOR checked_out_rec IN
checked_out_cur LOOP INSERT INTO
occupancy_history (pet_id, name, checkout_date)
VALUES (checked_out_rec.pet_id,
checked_out_rec.name,
checked_out_rec.checkout_date) DELETE FROM
occupancy WHERE pet_id checked_out_rec.pet_id
END LOOP END
32
PL/SQL v. SQL

• The same thing can be done w/o a cursor

BEGIN INSERT INTO occupancy_history (pet_id,
NAME, checkout_date) SELECT pet_id, NAME,
checkout_date FROM occupancy WHERE
checkout_date IS NOT NULL DELETE FROM
occupancy WHERE checkout_date IS NOT NULL END
33
Dynamic PL/SQL

• Saw dynamic SQL in the cases of ProC and JDBC
• Ability to run ad-hoc (non-hard-coded) SQL in
  programs/scripts
• Can also do this in PL/SQL
• The string can be passed in, created from
  concatenation, etc.

EXECUTE IMMEDIATE ltstringgt
34
Dynamic PL/SQL

• E.g. write function to return number rows in an
  arbitrary table

CREATE OR REPLACE FUNCTION rowCount ( tabname IN
VARCHAR2) return integer as retval
integer begin execute immediate 'select count()
from ' tabname into retval return
retval end /
35
Dynamic PL/SQL for DDL

• Ordinarily cant do DDL in PL/SQL
• But you can in dynamic PL/SQL
• Heres an e.g.

CREATE OR REPLACE procedure dropproc(procname in
varchar2) as begin execute immediate 'drop
procedure ' procname end /
36
More on PL/SQL

• OReillys Oracle PL/SQL Programming
• http//www.unix.org.ua/orelly/oracle/prog2/
• This lecture somewhat follows 3rd edition of this
  book
• PL/SQL Reference Tutorial
• http//www.ilook.fsnet.co.uk/ora_sql/sqlmain2.htm
• Introduction to PL/SQL
• http//www.geocities.com/cliktoprogram/plsql/intro
  duction.html
• Oracle FAQ's Script and Code Exchange
• http//www.orafaq.com/scripts/