1 / 26

OCL3 Oracle 10 g : SQL & PL/SQL Session #8

OCL3 Oracle 10 g : SQL & PL/SQL Session #8. Matthew P. Johnson CISDD, CUNY June, 2004. Agenda. More PL/SQL: CASE statements SELECT … INTO Exception-handling Packages Execution rights DDL in PL/SQL with dynamic PL/SQL Triggers. Case-statements. Saw if and if-else statements last time

spencer
Download Presentation

OCL3 Oracle 10 g : SQL & PL/SQL Session #8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. OCL3 Oracle 10g:SQL & PL/SQLSession #8 Matthew P. Johnson CISDD, CUNY June, 2004 Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  2. Agenda • More PL/SQL: • CASE statements • SELECT … INTO • Exception-handling • Packages • Execution rights • DDL in PL/SQL with dynamic PL/SQL • Triggers Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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 Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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 Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  6. Simple case’s 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'); … Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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 Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  8. Searched case e.g. CASE WHEN salary >= 10000 AND salary <=20000 THEN give_bonus(employee_id, 1500); WHEN salary > 20000 AND salary <= 40000 THEN give_bonus(employee_id, 1000); WHEN salary > 40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END CASE; • Q: Can this be implemented as a simple case? Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  9. Searched case e.g. CASE TRUE WHEN salary >= 10000 AND salary <=20000 THEN give_bonus(employee_id, 1500); WHEN salary > 20000 AND salary <= 40000 give_bonus(employee_id, 1000); WHEN salary > 40000 give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END CASE; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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_cur%ROWTYPE; BEGIN OPEN occupancy_cur; LOOP FETCH occupancy_cur INTO occupancy_rec; EXIT WHEN occupancy_cur%NOTFOUND; update_bill (occupancy_rec.pet_id, occupancy_rec.room_number); END LOOP; CLOSE occupancy_cur; END; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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; / Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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; / Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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(sqlcode); dbms_output.put_line(sqlerrm); end; end; / Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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 pkg’s programs Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  21. Package e.g. • Run example: • http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/numsys.sql Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  22. Dynamic PL/SQL • Saw “dynamic SQL” in the cases of Pro*C 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 <string>; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  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; / Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  24. Dynamic PL/SQL for DDL • Ordinarily can’t do DDL in PL/SQL • But you can in dynamic PL/SQL • Here’s an e.g.: CREATE OR REPLACE procedure dropproc(procname in varchar2) as begin execute immediate 'drop procedure ' || procname; end; / Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  25. More on PL/SQL • O’Reilly’s 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/introduction.html • Oracle FAQ's Script and Code Exchange: • http://www.orafaq.com/scripts/ Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  26. Live examples • Factorial function: • http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/fact.sql • Converting between bases: • http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/numsys.sql • Directory of examples: • http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/ Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

More Related