500 likes | 635 Views
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
E N D
OCL3 Oracle 10g:SQL & PL/SQLSession #7 Matthew P. Johnson CISDD, CUNY June, 2005 Matthew P. Johnson, OCL3, 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 Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
More ifs IF <condition> ELSE END IF; IF <expression> ELSEIF <expression> ELSE END IF; Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
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
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
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
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
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
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