OCL3 Oracle 10g: SQL

1
OCL3 Oracle 10gSQL PL/SQLSession 8

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

2
Agenda

• More PL/SQL
• CASE statements
• SELECT INTO
• 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 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
11
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
12
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
13
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
14
SELECT INTO
create or replace function getprod(manuf varchar)
return varchar as pn varchar(255) begin select
prodname into pn from products where mfg
manuf return pn end /
15
SELECT INTO and exceptions
create or replace function getprod(manuf varchar)
return varchar as pn varchar(255) begin select
prodname into pn from products where mfg
manuf return pn Exception When TOO_MANY_ROWS
then dbms_output.put_line('got too many') end /
16
SELECT INTO and exceptions
Exception When TOO_MANY_ROWS then declare
err_num number sqlcode err_msg
varchar2(255) sqlerrm begin dbms_output.put_
line('got too many') dbms_output.put_line(sqlcod
e) dbms_output.put_line(sqlerrm) end end /
17
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
18
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
19
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
20
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

21
Package e.g.

• Run example
• http//pages.stern.nyu.edu/mjohnson/oracle/plsql/
  numsys.sql

22
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
23
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 /
24
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 /
25
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/

26
Live examples

• Factorial function
• http//pages.stern.nyu.edu/mjohnson/dbms/plsql/fa
  ct.sql
• Converting between bases
• http//pages.stern.nyu.edu/mjohnson/dbms/plsql/nu
  msys.sql
• Directory of examples
• http//pages.stern.nyu.edu/mjohnson/dbms/plsql/