790 likes | 1k Views
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
E N D
C20.0046: Database Management SystemsLecture #20 M.P. Johnson Stern School of Business, NYU Spring, 2008 M.P. Johnson, DBMS, Stern/NYU, Spring 2008
Agenda • Stored procedures? • Triggers • Transactions • RAID? • Implementation? M.P. Johnson, DBMS, Stern/NYU, Spring 2008
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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