OCL1 Oracle 10g: SQL

1
OCL1 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 statments
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 clauses 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 DUMMY'X' THEN 'Dual
is OK' ELSE 'Dual is messed up' END FROM
DUAL
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
• Redundant grant ops are independent

SQLgt GRANT EXECUTE ON wordcount TO scott
SQLgt REVOKE EXECUTE FROM wordcount TO scott
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 tabCount ( tab IN
VARCHAR2) return integer as retval
integer begin execute immediate 'select count()
from ' tab 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 droptbl(procname in
varchar) 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/