1 / 79

C20.0046: Database Management Systems Lecture #20

C20.0046: Database Management Systems Lecture #20. M.P. Johnson Stern School of Business, NYU Spring, 2008. Agenda. Stored procedures? Triggers Transactions RAID? Implementation?. Programming: step back. Recall basic problem: need SQL plus stronger programming lang

heidis
Download Presentation

C20.0046: Database Management Systems Lecture #20

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. C20.0046: Database Management SystemsLecture #20 M.P. Johnson Stern School of Business, NYU Spring, 2008 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  2. Agenda • Stored procedures? • Triggers • Transactions • RAID? • Implementation? M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  3. Programming: step back • Recall basic problem: need SQL plus stronger programming lang •  need to connect the two langs • In all these cases (and in the web app case), idea is: put SQL in (traditional-lang) programs • Another way: put programs in SQL • i.e., store programs on the DBMS • “stored procedures” M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  4. Next topic: SPs • “Persistent, Stored Modules” / “Stored Procedures / “PL/SQL programs” (in Oracle) • Another way to connect application programming language and SQL • Supports usual things: • Declare, set vars to vals of expressions • Print output • Define (optional) procedures, functions • Cursors • PL/SQL can compute n! M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  5. Integration with SQL DECLARE l_book_count INTEGER; BEGIN SELECT COUNT(*) INTO l_book_count FROM books WHERE author LIKE '%FEUERSTEIN, STEVEN%'; DBMS_OUTPUT.PUT_LINE ( 'Steven has written (or co-written) ' || l_book_count || ' books.'); -- Oh, and I changed my name, so... UPDATE books SET author = REPLACE (author, 'STEVEN', 'STEPHEN') WHERE author LIKE '%FEUERSTEIN, STEVEN%'; END; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  6. PL/SQL • “Procedural Language/SQL” • Oracle’s language for stored procedures • MySQL’s is similar • Simple, interpreted, procedural language • But Pascal-like: • BEGIN END, not { } • AND OR, not && || • vars defined at top of procedure • how return works M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  7. PL/SQL • Generally speaking can be used wherever SQL can be • Sql command-line • embeded SQL • JDBC/PHP/etc • Can store programs in files (.sql), run later • CALL myprog.sql runs code in myprog.sql M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  8. PL/SQL: Hello, World • http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/hello.sql BEGIN -- print out message DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL'); END; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  9. Hello, World • Try again… SET SERVEROUTPUT ON BEGIN -- print out message DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL'); END; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  10. How to run code • The code before ended with a forward slash • Not SQL and not PL/SQL – just for SQL*Plus to tell it to run the code entered • Must go on its own line • O.w., will be ignored and then interpreted as part of code, causing an error • To call a procedure in SQL*Plus, can also use execute/exec: exec DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL') M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  11. How to run code • EXEC is just short-hand: SQL> exec dbms_output.put_line('hi '); dbms_output.put_line('there' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  12. SP operators/symbols • ; end statement • % attribute indicator (cursor attributes like %ISOPEN and indirect declaration attributes like %ROWTYPE • : host variable indicator • <> and != not-equal-to • = equal-to • := assignment op • ** exponentiation operator • -- , /* and */, rem comments M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  13. Var names • identifiers limited to 30 alpha-num chars • Must start with letter, $, _, or # • E.g.: abc, $a$, $$$ • PL/SQL is case INsensitive • abc, ABC, AbC all the same • Unless you use double-quotes… • Also supports constants: • Varname datatype CONSTANT := val; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  14. Literals • Numbers: 123, 12.3, 3.05E19, 12e-5, null • String: 'abc', 'AbC', null • String comparison is case-SENSitive • Boolean: true, false, null • true != ‘true’ • No date literals, as in regular SQL • To_date('31-JAN-94') • Escape single-quotes in strings with two single-quotes • 'it''s'  it's • ''''''  '' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  15. Blocks • SP languages are block-structured • Block = seq. of instructions, with scope • Can have anonymous blocks • And named blocks • Procedures • Functions M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  16. Structure of a block header --if named DECLARE --optional --var declarations BEGIN --executable statements --queries/updates, etc. EXCEPTION --optional --catch exceptions END; / --to execute • As in Pascal, var declars precede body M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  17. PL/SQL code examples • One example: • Likes(drinker, beverage) • Another example: • http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/age.sql BEGIN INSERT INTO Likes VALUES(‘Izzy', ‘milk'); DELETE FROM Likes WHERE drinker = ‘Izzy' AND beverage = ‘Beaujolais Nouveau '; COMMIT; END; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  18. Procedures • Stored database objects that use a PL/SQL statement(s) in their body • Create/drop similar to other SQL objects: CREATE PROCEDURE <my-proc> (<params>) AS <procedure body as above>; CREATE OR REPLACE PROCEDURE <my-proc>(<params>) AS <procedure body as above>; DROP PROCEDURE <my-proc>; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  19. Example procedure • Define the procedure: • Now we can call it: CREATE PROCEDURE testProcedure AS BEGIN INSERT INTO Student VALUES (5, 'Joe'); END; EXEC testProcedure M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  20. More details on procedures • Parameter list has name-mode-type triples: • Modes: IN, OUT, or IN OUT • Fulfills role similar to pass-by-value v. pass-by-reference • Default is IN • Types must match, so can get exact field type: relation.attribute%TYPE M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  21. Procedure I/O example • A procedure to take a beer and price and add it to Joe's menu: Sells(bar, beer, price) CREATE PROCEDURE izzyMenu( b IN char(20), p IN double) AS BEGIN INSERT INTO Sells VALUES('Izzy''s', b, p); END; / • Are these the right types? M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  22. Procedure I/O example • A procedure to take a beer and price and add it to Joe's menu: Sells(bar, beer, price) CREATE PROCEDURE izzyMenu( b IN Sells.beer%TYPE, p IN Sells.price%TYPE) AS BEGIN INSERT INTO Sells VALUES('Izzy''s', b, p); END; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  23. Larger procedure e.g. CREATE or replace PROCEDURE hike_prices(old_price number, new_price out number, percent_hike number := 5) is Begin new_price := old_price + old_price * percent_hike/100; End; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  24. Call the procedure • But how to use to modify table data? • Convert to a function Declare currprice number := 20; newprice number; Begin hike_prices(currprice,newprice,5); dbms_output.put_line(newprice); End; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  25. Functions • Like procedures but with return values • Big strength: can be called from SQL itself CREATE FUNCTION <functionName> (<paramList>) RETURN type AS <localDeclarations> BEGIN <functionBody> END; DROP FUNCTION <functionName>; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  26. Function example • http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/maxval.sql CREATE OR REPLACE FUNCTION maxval(a IN int, b IN int) RETURN int AS BEGIN IF a > b THEN RETURN a; ELSE RETURN b; END IF; END maxval; INSERT INTO R VALUES('abc', maxval(5,10)); M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  27. Hike function • Now can use directly in update statements • NB: had to use different name for ftn • Same namespace for ftns & procs, although different CREATE or replace FUNCTION hike_pricesf(old_price number, percent_hike number := 5) return number is Begin return old_price + old_price * percent_hike/100; End; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  28. How to run scripts • Usually don’t type ftns into sqlplus by hand • Define them in a .sql file • In sqlplus, execute .sql file • Runs commands in file • Here, defines function • Now, we can call functions (they persist) • See http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/plsql.txt SQL> CALL maxval.sql SQL> exec DBMS_OUTPUT.PUT_LINE (maxval(5,10)) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  29. ED command • Though define procedures at cmd-prompt • Forgot AS! • Type ED… SQL> create or replace procedure hello2 begin dbms_output.put_line('hi'); end; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  30. Calling functions and procedures • Procedures can executed, ftns can’t • How to just call a ftn? • Can use dbms_output, as seen • Can also select the ftn value from dual SQL> select(wordcount('hi there') from mytable; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  31. Stored ftns & procs persist • Once a function or procedure is created, it persists until it’s dropped • Stored procs are stored in the DB itself • In user_procedures in Oracle • Also, can describe ftns and procs: CREATE OR REPLACE FUNCTION … SELECT object_name from user_procedures; SQL> describe wordcount M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  32. Look up procedures, functions • In Oracle, functions & procedures in user_procedures: • Also, can describe ftns and procs: SELECT object_name from user_procedures; SQL> describe wordcount M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  33. Getting errors • Simply says: • To get actual errors, say SHOW ERR(ORS) • Can also get errors per object: • Warning: must get object type right! • Can also look at user_errors tbl directly Warning: Function created with compilation errors. SQL> show errors function wordcount M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  34. Branching • IF–THEN statements use THEN • Must end with END IF • Use ELSIF in place of ELSE IF • Example: • http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/maxval.sql IF <condition> THEN <statement(s)> ELSIF <statement(s)> END IF; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  35. 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 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  36. 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; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  37. 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; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  38. Loop example DECLARE     i NUMBER := 1; BEGIN     LOOP         INSERT INTO T1VALUES(i,i); i := i+1;         EXIT WHEN i>100;     END LOOP; END; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  39. More loops • Numerical for loop: • Cursor for loop: FOR for_index IN low_value .. high_value LOOP executable_statements; END LOOP; FOR record_index IN my_cursor LOOP executable_statements; END LOOP; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  40. http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/wordcount.sqlhttp://pages.stern.nyu.edu/~mjohnson/oracle/plsql/wordcount.sql CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2) RETURN PLS_INTEGER AS /* words PLS_INTEGER := 0; ***Commented out for intentional error*** */ len PLS_INTEGER := NVL(LENGTH(str),0); inside_a_word BOOLEAN; BEGIN FOR i IN 1..len + 1 LOOP IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len THEN IF inside_a_word THEN words := words + 1; inside_a_word := FALSE; END IF; ELSE inside_a_word := TRUE; END IF; END LOOP; RETURN words; END; Word count program M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  41. 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; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  42. 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; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  43. Using query results: 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; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  44. 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; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  45. 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… SQL> GRANT EXECUTE ON wordcount TO george; SQL> REVOKE EXECUTE FROM wordcount TO george; SQL> GRANT EXECUTE ON wordcount TO dba_role; SQL> GRANT EXECUTE ON wordcount TO public; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  46. 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 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  47. 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>; M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  48. 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; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  49. 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; / M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  50. 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/ M.P. Johnson, DBMS, Stern/NYU, Spring 2008

More Related