1 / 50

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

OCL3 Oracle 10 g : SQL & PL/SQL Session #7. Matthew P. Johnson CISDD, CUNY June, 2005. Agenda. Last time: Programming for SQL Pro*C, JDBC This time: SPs in PL/SQL Next time: More PL/SQL Triggers. Step back. Recall basic problem: need SQL plus stronger programming lang

silas-miranda
Download Presentation

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

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

  2. Agenda • Last time: • Programming for SQL • Pro*C, JDBC • This time: • SPs in PL/SQL • Next time: • More PL/SQL • Triggers Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

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

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

  6. 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 procedure • how return works Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  7. PL/SQL • Generally speaking can be used wherever SQL can be • sqlplus • embeded SQL • JDBC • Can store programs in files (.sql), run later • @myprog.sql runs code in myprog.sql Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  8. 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 • Though now compiled to bytecode or (optionally) to native • Don’t require functions/procedures • Though now supported • Weakly typed • Lots of auto-conversion Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  9. PL/SQL: Hello, World • http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/hello.sql BEGIN -- print out message DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL'); END; / Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  10. Hello, World • Try again… SET SERVEROUTPUT ON BEGIN -- print out message DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL'); END; / Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  11. Use start-up script • Go to <Orahome>\sqlplus\admin\glogin.sql • Start-up script run upon login to SQL*Plus • Add “SET SERVEROUTPUT ON” to it • If running non-i version of SQL*Plus, also looks in current dir for login.sql script Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

  13. How to run code • EXEC is just short-hand: SQL> exec dbms_output.put_line('hi '); dbms_output.put_line('there' Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

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

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

  17. Blocks • PL/SQL is a block-structured language • Block = seq. of instructions, with scope • Can have anonymous blocks • And named blocks • Procedures • Functions Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

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

  20. 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>) AS <procedure body as above>; CREATE OR REPLACE PROCEDURE <my-proc>(<params>) AS <procedure body as above>; DROP PROCEDURE <my-proc>; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  21. Example procedure • Define the procedure: • Now we can call it: CREATE PROCEDURE testProcedure AS BEGIN INSERT INTO Student VALUES (5, 'Joe'); COMMIT; END; EXEC testProcedure Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

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

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

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

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

  27. Functions • Like procedures but with return values • Big strength: can be called from SQL CREATE FUNCTION <functionName> (<paramList>) RETURN type AS <localDeclarations> BEGIN <functionBody> END; DROP FUNCTION <functionName>; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  28. Function example • 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)); Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

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

  31. How to run scripts • Can also use the start command: • If no file extension is given, .sql is assumed • Can use full paths: • Scripts can call other scripts • Use @ for current dir, @@ for dir of current script • Scripts are not (by default) echoed. Can use: SQL> START maxval.sql SQL> @c:\somewhere\maxval.sql SQL> SET ECHO ON Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

  33. http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/wordcount.sqlhttp://pages.stern.nyu.edu/~mjohnson/dbms/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 Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  34. Intermission • Fill out evals • Scottish Parliament/Outer join exercises • Work on exercises 1-3 of lab 7 Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

  36. Calling functions and procedures • Procedures can simple 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 dual; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  37. Agenda • A little more PL/SQL lecture • Go through some SQL lab exercises? • Evals • More PL/SQL lecture/lab… • Later: go through some PL/SQL exercises… Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

  39. Subblocks • Blocks may contain blocks, for narrower scope: CREATE OR REPLACE PROCEDURE calc_totals IS year_total NUMBER; BEGIN year_total := 0; /* Nested anonymous block */ DECLARE month_total NUMBER; BEGIN month_total := year_total / 12; END; END; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  40. More on scope • Can name blocks and loops with labels <<insert_but_ignore_dups>> BEGIN INSERT INTO catalog VALUES (...); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END insert_but_ignore_dups; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  41. Scope and nested, labeled loops <<outerblock>> DECLARE counter INTEGER := 0; BEGIN ... DECLARE counter INTEGER := 1; BEGIN IF counter = outerblock.counter THEN ... END IF; END; END; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  42. Scope and nested, labeled loops BEGIN <<outer_loop>> LOOP LOOP EXIT outer_loop; END LOOP; some_statement ; END LOOP; END; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

  44. More ifs IF <condition> ELSE END IF; IF <expression> ELSEIF <expression> ELSE END IF; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  45. Multiple elsifs • An if statement can have multiple elseifs: IF salary >= 10000 AND salary <= 20000 THEN give_bonus(employee_id, 1500); ELSIF salary > 20000 AND salary <= 40000 THEN give_bonus(employee_id, 1000); ELSIF salary > 40000 THEN give_bonus(employee_id, 400); END IF; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  46. Nested ifs IF condition1 THEN IF condition2 THEN statements2 ELSE IF condition3 THEN statements3 ELSIF condition4 THEN statements4 END IF; END IF; END IF; • As usual, if statements can be nested: • Can often be replaced with an ANDed condition Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  47. Loop example DECLARE     i NUMBER := 1; BEGIN     LOOP         INSERT INTO T1 VALUES(i,i);         i := i+1;         EXIT WHEN i>100;     END LOOP; END; / Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

  48. More loops LOOP executable_statements; END LOOP; • Infinite loop: • while loop: WHILE condition LOOP executable_statements; END LOOP; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005

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

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

More Related