C20.0046: Database Management Systems Lecture #19

1. C20.0046: Database Management SystemsLecture #19 Matthew P. Johnson Stern School of Business, NYU Spring, 2004 M.P. Johnson, DBMS, Stern/NYU, Sp2004

2. Agenda • Previously: JDBC • Next: • Project part 3 due now • Project part 4 due next week • Scripting for SQL • SPs • Scripting for SQL on the web • CGI/Perl • PHP • Security M.P. Johnson, DBMS, Stern/NYU, Sp2004

3. Recap: JDBC prog.pc Host language + Embedded SQL Oracle’s Pro*C Preprocessor Preprocessor Prog.java Host Language + function calls Host language compiler Host language compiler javac + jar Proj.class Executable M.P. Johnson, DBMS, Stern/NYU, Sp2004

4. Java & parameter-based SQL • Like SQL/CLI in C, Java also supports parameterized queries (why?) • Prepare structure of query • Then can set values PreparedStatement ps = conn.prepareStatement( "SELECT * FROM table WHERE f1 = ? and f2 = ?"); ps.setString(1 “abc"); ps.setString(2, “def"); ResultSet rs = ps.executeQuery(); ... M.P. Johnson, DBMS, Stern/NYU, Sp2004

5. Also: ODBC • Used by Microsoft platforms/tools, others • Access: • Start | Control Panel | Administrative Tools | Data Sources (ODBC) • Similar to JDBC • Won’t cover M.P. Johnson, DBMS, Stern/NYU, Sp2004

6. Other combinations • So far: C/Pro*C, Java/JDBC • Q: Only choices? • A: No • “Call-level interface” for C: SQL/CLI • ODBC • Embedded Java: SQL/J • CLI for Perl, PHP, etc. • Stored Procedures (next) • {langs} x {dyn/not} x {SPs/not} x {DBMSs} M.P. Johnson, DBMS, Stern/NYU, Sp2004

7. Step back • Recall basic problem: need SQL plus stronger programming lang •  need to connect the two langs • In all these case (and web apps next time): put SQL in (traditional lang) programs • Another way: let programs in SQL • i.e., put programs in the DBMS • “stored procedures” M.P. Johnson, DBMS, Stern/NYU, Sp2004

8. Next topic: SPs (8.2) • “Persistent, Stored Modules” / “Stored Procedures / “PL/SQL programs” (in Oracle) • Added to MySQL in 5.0 • 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, Sp2004

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

10. PL/SQL • Generally speaking can be used wherever SQL can be • sqlplus • embeded SQL • Can store programs in files (.sql), run later • @myprog.sql runs code in myprog.sql M.P. Johnson, DBMS, Stern/NYU, Sp2004

11. Scripting languages • Big problems v. small problems • Big solutions v. small solutions • Programming languages: • C/C++, Java, etc. • Scripting languages: • PL/SQL, Perl, PHP, Unix shell, DOS batch files, Python, Excel macros, VBA, JavaScript • Usual properties of scripting languages: • Interpreted • Don’t require functions/procedures • Weakly typed M.P. Johnson, DBMS, Stern/NYU, Sp2004

12. PL/SQL • Structure of procedure body: • As in Pascal, var declars precede body DECLARE --Optional --var declarations BEGIN --executable statements --queries/updates, etc. END; / --to execute M.P. Johnson, DBMS, Stern/NYU, Sp2004

13. PL/SQL: Hello, World • http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/hello.sql SET SERVEROUTPUT ON; BEGIN -- print out message DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL'); END; / M.P. Johnson, DBMS, Stern/NYU, Sp2004

14. PL/SQL code examples • One example: • Likes(drinker, beverage) • Another example: • http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/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, Sp2004

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

16. Example procedure • Define the procedure: • Now we can call it: CREATE PROCEDURE testProcedure BEGIN INSERT INTO Student VALUES (5, ‘Joe’); COMMIT; END; EXEC testProcedure; M.P. Johnson, DBMS, Stern/NYU, Sp2004

17. 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, Sp2004

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

19. 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, Sp2004

20. Loop example DECLARE     i NUMBER := 1; BEGIN     LOOP         INSERT INTO T1 VALUES(i,i);         i := i+1;         EXIT WHEN i>100;     END LOOP; END; / M.P. Johnson, DBMS, Stern/NYU, Sp2004

21. 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/dbms/eg/lec19/for.sql FOR my-rec IN my-cursor LOOP … END LOOP; M.P. Johnson, DBMS, Stern/NYU, Sp2004

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

23. Function example • Like procedures but with return values • drop in same way • Big strength: can be called from SQL • http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/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, Sp2004

24. How to run scripts • Don’t want to 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 • See http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/plsql.txt SQL> @maxval.sql SQL> exec DBMS_OUTPUT.PUT_LINE (maxval(5,10)) M.P. Johnson, DBMS, Stern/NYU, Sp2004

25. Triggers in Oracle • Oracle triggers are written in PL/SQL • Trigger body is like regular procedure body, but following trigger syntax: CREATE OR REPLACE TRIGGER MYTRIG1 BEFORE DELETE ON mytable BEGIN --code END; M.P. Johnson, DBMS, Stern/NYU, Sp2004

26. Look up procedures, functions • In Oracle, functions & procedures in user_procedures: SELECT object_name from user_procedures; M.P. Johnson, DBMS, Stern/NYU, Sp2004

27. More on PL/SQL • O’Reilly’s Oracle PL/SQL Programming: • http://www.unix.org.ua/orelly/oracle/prog2/ • 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/ M.P. Johnson, DBMS, Stern/NYU, Sp2004