OCL 3 Oracle 10 g SQL PLSQL Session

OCL 3 Oracle 10 g: SQL & PL/SQL Session #7 Matthew P. Johnson CISDD, CUNY June, 2005 Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 1

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

Step back n Recall basic problem: need SQL plus stronger programming lang q need to connect the two langs n In all these cases (and in the web app case), idea is: put SQL in (traditional-lang) programs n Another way: put programs in SQL q q i. e. , store programs on the DBMS “stored procedures” Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 3

Next topic: SPs n “Persistent, Stored Modules” / “Stored Procedures / “PL/SQL programs” (in Oracle) n Another way to connect application programming language and SQL n Supports usual things: q q n Declare, set vars to vals of expressions Print output Define (optional) procedures, functions Cursors PL/SQL can compute n! Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 4

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, OCL 3, CISDD CUNY, June 2005 5

PL/SQL n “Procedural Language/SQL” q Oracle’s language for stored procedures n Simple, interpreted, procedural language n But Pascal-like: q q BEGIN END, not { } AND OR, not && || vars defined at top of procedure how return works Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 6

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

Scripting languages n n n Big problems v. small problems Big solutions v. small solutions Programming languages: q C/C++, Java, etc. Scripting languages: q PL/SQL, Perl, PHP, Unix shell, DOS batch files, Python, Excel macros, VBA, Java. Script Usual properties of scripting languages: q Interpreted n q Don’t require functions/procedures n q Though now compiled to bytecode or (optionally) to native Though now supported Weakly typed n Lots of auto-conversion Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 8

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

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

Use start-up script n n Go to <Orahome>sqlplusadminglogin. 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, OCL 3, CISDD CUNY, June 2005 11

How to run code n n n 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 q n 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, OCL 3, CISDD CUNY, June 2005 12

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

PL/SQL operators/symbols n n n n ; 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, OCL 3, CISDD CUNY, June 2005 14

Var names n identifiers limited to 30 alpha-num chars q Must start with letter, $, _, or # n E. g. : abc, $a$, $$$ n PL/SQL is case Insensitive q q n abc, ABC, Ab. C all the same Unless you use double-quotes… Also supports constants: q Varname datatype CONSTANT : = val; Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 15

Literals n n Numbers: 123, 12. 3, 3. 05 E 19, 12 e-5, null String: ‘abc’, ‘Ab. C’, null q n Boolean: true, false, null q n n n true != ‘true’ No date literals, as in regular SQL q n String comparison is case-SENSitive To_date('31 -JAN-94') Escape single-quotes in strings with two singlequotes ‘it’’s’ it’s '''''' '' Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 16

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

Structure of a block header --if named DECLARE --optional --var declarations BEGIN --executable statements --queries/updates, etc. EXCEPTION --optional --catch exceptions END; / --to execute n As in Pascal, var declars precede body Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 18

PL/SQL code examples n One example: q Likes(drinker, beverage) BEGIN INSERT INTO Likes VALUES(‘Izzy', ‘milk'); DELETE FROM Likes WHERE drinker = ‘Izzy' AND beverage = ‘Beaujolais Nouveau '; COMMIT; END; / n Another example: q http: //pages. stern. nyu. edu/~mjohnson/dbms/eg/lec 19/age. sql Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 19

Procedures n n Stored database objects that use a PL/SQL statement(s) in their body Create/drop similar to other SQL objects: q ALTER PROCEDURE… in My. SQL 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, OCL 3, CISDD CUNY, June 2005 20

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

More details on procedures n n Parameter list has name-mode-type triples: Modes: IN, OUT, or IN OUT q q n Fulfills role similar to pass-by-value v. pass-byreference Default is IN Types must match, so can get exact field type: relation. attribute%TYPE Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 22

Procedure I/O example n A procedure to take a beer and price and add it to Joe's menu: Sells(bar, beer, price) CREATE PROCEDURE izzy. Menu( n. Are these b IN char(20), the right p IN double) AS types? BEGIN INSERT INTO Sells VALUES(‘Izzy’’s', b, p); END; / Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 23

Procedure I/O example n A procedure to take a beer and price and add it to Joe's menu: Sells(bar, beer, price) CREATE PROCEDURE izzy. Menu( b IN Sells. beer%TYPE, p IN Sells. price%TYPE) AS BEGIN INSERT INTO Sells VALUES(‘Izzy’’s', b, p); END; / Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 24

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, OCL 3, CISDD CUNY, June 2005 25

Call the procedure Declare currprice number : = 20; newprice number; Begin hike_prices(currprice, newprice, 5); dbms_output. put_line(newprice); End; n But how to use to modify table data? n Convert to a function Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 26

Functions n Like procedures but with return values CREATE FUNCTION <function. Name> (<param. List>) RETURN type AS <local. Declarations> BEGIN <function. Body> END; DROP FUNCTION <function. Name>; n Big strength: can be called from SQL Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 27

Function example 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)); n http: //pages. stern. nyu. edu/~mjohnson/dbms/eg/lec 19/maxval. sql Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 28

Hike function 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; / n Now can use directly in update statements n NB: had to use different name for ftn q Same namespace for ftns & procs, although different Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 29

How to run scripts n n n Don’t want to type ftns into sqlplus by hand Define them in a. sql file In sqlplus, execute. sql file SQL> @maxval. sql q q n n Runs commands in file Here, defines function Now, we can call functions See SQL> exec DBMS_OUTPUT. PUT_LINE (maxval(5, 10)) http: //pages. stern. nyu. edu/~mjohnson/dbms/eg/lec 19/plsql. txt Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 30

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

Stored ftns & procs persist n Once a function or procedure is created, it persists until it’s dropped CREATE OR REPLACE FUNCTION … n Stored procs are stored in the DB itself q In user_procedures in Oracle SELECT object_name from user_procedures; n Also, can describe ftns and procs: SQL> describe wordcount Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 32

http: //pages. stern. nyu. edu/~mjohnson/dbms/plsql/wordcount. sql CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR 2) 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; Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 END; Word count program 33

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

Getting errors n Simply says: Warning: Function created with compilation errors. n n To get actual errors, say SHOW ERR(ORS) Can also get errors per object: SQL> show errors function wordcount n n Warning: must get object type right! Can also look at user_errors tbl directly Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 35

Calling functions and procedures n n 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, OCL 3, CISDD CUNY, June 2005 36

Agenda n n n 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, OCL 3, CISDD CUNY, June 2005 37

Look up procedures, functions n In Oracle, functions & procedures in user_procedures: SELECT object_name from user_procedures; n Also, can describe ftns and procs: SQL> describe wordcount Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 38

Subblocks n 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; Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 39

More on scope n 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, OCL 3, CISDD CUNY, June 2005 40

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

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

Branching n n IF–THEN statements use THEN Must end with END IF Use ELSIF in place of ELSE IF Example: q IF <condition> THEN <statement(s)> ELSIF <statement(s)> END IF; http: //pages. stern. nyu. edu/~mjohnson/dbms/eg/lec 19/maxval. sql Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 43

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

Multiple elsifs n 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, OCL 3, CISDD CUNY, June 2005 45

Nested ifs n As usual, if statements can be nested: n Can often be replaced with an ANDed condition IF condition 1 THEN IF condition 2 THEN statements 2 ELSE IF condition 3 THEN statements 3 ELSIF condition 4 THEN statements 4 END IF; Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 46

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

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

More loops n Numerical for loop: FOR for_index IN low_value. . high_value LOOP executable_statements; END LOOP; n Cursor for loop: FOR record_index IN my_cursor LOOP executable_statements; END LOOP; Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 49

Programs and rights By default, only the creator of a program may run it (apart from the admin) n If others should run, must GRANT them permission: SQL> GRANT EXECUTE ON wordcount TO george; n Permissions can be revoked: SQL> REVOKE EXECUTE FROM wordcount TO george; n Can also grant to particular roles or everyone: SQL> GRANT EXECUTE ON wordcount TO dba_role; SQL> GRANT EXECUTE ON wordcount TO public; n n Wider/narrower grant ops are independent… Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 50