ocl3 oracle 10 g : sql & pl/sql session #7
DESCRIPTION
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 - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/1.jpg)
Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
1
OCL3 Oracle 10g:SQL & PL/SQLSession #7
Matthew P. Johnson
CISDD, CUNY
June, 2005
![Page 2: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/2.jpg)
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
![Page 3: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/3.jpg)
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”
![Page 4: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/4.jpg)
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!
![Page 5: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/5.jpg)
Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
5
Integration with SQLDECLARE
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;
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;
![Page 6: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/6.jpg)
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
![Page 7: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/7.jpg)
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
![Page 8: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/8.jpg)
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
![Page 9: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/9.jpg)
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;
/
BEGIN
-- print out message
DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL');
END;
/
![Page 10: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/10.jpg)
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;
/
SET SERVEROUTPUT ON
BEGIN
-- print out message
DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL');
END;
/
![Page 11: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/11.jpg)
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
![Page 12: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/12.jpg)
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')exec DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL')
![Page 13: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/13.jpg)
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'
SQL> exec dbms_output.put_line('hi '); dbms_output.put_line('there'
![Page 14: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/14.jpg)
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
![Page 15: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/15.jpg)
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;
![Page 16: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/16.jpg)
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 '''''' ''
![Page 17: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/17.jpg)
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
![Page 18: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/18.jpg)
Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
18
Structure of a block
As in Pascal, var declars precede body
header --if named
DECLARE --optional
--var declarations
BEGIN
--executable statements
--queries/updates, etc.
EXCEPTION --optional
--catch exceptions
END;
/ --to execute
header --if named
DECLARE --optional
--var declarations
BEGIN
--executable statements
--queries/updates, etc.
EXCEPTION --optional
--catch exceptions
END;
/ --to execute
![Page 19: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/19.jpg)
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; /
BEGIN
INSERT INTO Likes VALUES(‘Izzy', ‘milk');
DELETE FROM Likes
WHERE drinker = ‘Izzy' AND
beverage = ‘Beaujolais Nouveau ';
COMMIT;
END; /
![Page 20: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/20.jpg)
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 PROCEDURE <my-proc> (<params>) AS
<procedure body as above>;
DROP PROCEDURE <my-proc>;DROP PROCEDURE <my-proc>;
CREATE OR REPLACE PROCEDURE <my-proc>(<params>) AS<procedure body as above>;
CREATE OR REPLACE PROCEDURE <my-proc>(<params>) AS<procedure body as above>;
![Page 21: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/21.jpg)
Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
21
Example procedure Define the procedure:
Now we can call it:
CREATE PROCEDURE testProcedure ASBEGIN
INSERT INTO Student VALUES (5, 'Joe');COMMIT;
END;
CREATE PROCEDURE testProcedure ASBEGIN
INSERT INTO Student VALUES (5, 'Joe');COMMIT;
END;
EXEC testProcedureEXEC testProcedure
![Page 22: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/22.jpg)
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%TYPErelation.attribute%TYPE
![Page 23: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/23.jpg)
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
BEGININSERT INTO SellsVALUES(‘Izzy’’s', b, p);
END;/
CREATE PROCEDURE izzyMenu(b IN char(20),p IN double) AS
BEGININSERT INTO SellsVALUES(‘Izzy’’s', b, p);
END;/
Are these the right types?
![Page 24: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/24.jpg)
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
BEGININSERT INTO SellsVALUES(‘Izzy’’s', b, p);
END;/
CREATE PROCEDURE izzyMenu(b IN Sells.beer%TYPE,p IN Sells.price%TYPE) AS
BEGININSERT INTO SellsVALUES(‘Izzy’’s', b, p);
END;/
![Page 25: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/25.jpg)
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
Beginnew_price := old_price + old_price * percent_hike/100;
End;/
CREATE or replace PROCEDURE hike_prices(old_price number, new_price out number, percent_hike number := 5) is
Beginnew_price := old_price + old_price * percent_hike/100;
End;/
![Page 26: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/26.jpg)
Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
26
Call the procedure
But how to use to modify table data?
Convert to a function
Declarecurrprice number := 20;newprice number;
Beginhike_prices(currprice,newprice,5);dbms_output.put_line(newprice);
End;
Declarecurrprice number := 20;newprice number;
Beginhike_prices(currprice,newprice,5);dbms_output.put_line(newprice);
End;
![Page 27: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/27.jpg)
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;
CREATE FUNCTION <functionName> (<paramList>) RETURN type AS
<localDeclarations>BEGIN<functionBody>END;
DROP FUNCTION <functionName>;DROP FUNCTION <functionName>;
![Page 28: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/28.jpg)
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 ASBEGIN IF a > b THEN RETURN a; ELSE RETURN b; END IF;END maxval;
CREATE OR REPLACE FUNCTION maxval(a IN int, b IN int) RETURN int ASBEGIN IF a > b THEN RETURN a; ELSE RETURN b; END IF;END maxval;
INSERT INTO R VALUES(“abc”, maxval(5,10));INSERT INTO R VALUES(“abc”, maxval(5,10));
![Page 29: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/29.jpg)
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
Beginreturn old_price + old_price * percent_hike/100;
End;/
CREATE or replace FUNCTION hike_pricesf(old_price number, percent_hike number := 5) return number is
Beginreturn old_price + old_price * percent_hike/100;
End;/
![Page 30: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/30.jpg)
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
Seehttp://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/plsql.txt
SQL> @maxval.sqlSQL> @maxval.sql
SQL> exec DBMS_OUTPUT.PUT_LINE (maxval(5,10))SQL> exec DBMS_OUTPUT.PUT_LINE (maxval(5,10))
![Page 31: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/31.jpg)
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.sqlSQL> START maxval.sql
SQL> @c:\somewhere\maxval.sqlSQL> @c:\somewhere\maxval.sql
SQL> SET ECHO ONSQL> SET ECHO ON
![Page 32: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/32.jpg)
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 …CREATE OR REPLACE FUNCTION …
SELECT object_name from user_procedures;SELECT object_name from user_procedures;
SQL> describe wordcountSQL> describe wordcount
![Page 33: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/33.jpg)
Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
33
Word count program
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;
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;
http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/wordcount.sql
![Page 34: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/34.jpg)
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
![Page 35: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/35.jpg)
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.Warning: Function created with compilation errors.
SQL> show errors function wordcountSQL> show errors function wordcount
![Page 36: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/36.jpg)
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;SQL> select(wordcount(‘hi there’) from dual;
![Page 37: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/37.jpg)
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…
![Page 38: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/38.jpg)
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;SELECT object_name from user_procedures;
SQL> describe wordcountSQL> describe wordcount
![Page 39: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/39.jpg)
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;
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;
![Page 40: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/40.jpg)
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;
<<insert_but_ignore_dups>>BEGIN INSERT INTO catalog VALUES (...);EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL;END insert_but_ignore_dups;
![Page 41: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/41.jpg)
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;
<<outerblock>>DECLARE counter INTEGER := 0;BEGIN ... DECLARE counter INTEGER := 1; BEGIN IF counter = outerblock.counter THEN ... END IF; END;END;
![Page 42: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/42.jpg)
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;
BEGIN <<outer_loop>> LOOP LOOP EXIT outer_loop; END LOOP; some_statement ; END LOOP;END;
![Page 43: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/43.jpg)
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;
IF <condition> THEN
<statement(s)>
ELSIF
<statement(s)>
END IF;
![Page 44: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/44.jpg)
Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
44
More ifs
IF <condition>
ELSE
END IF;
IF <condition>
ELSE
END IF;
IF <expression>
ELSEIF <expression>
ELSE
END IF;
IF <expression>
ELSEIF <expression>
ELSE
END IF;
![Page 45: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/45.jpg)
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;
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;
![Page 46: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/46.jpg)
Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
46
Nested ifs As usual, if statements
can be nested:
Can often be replaced with an ANDed condition
IF condition1
THEN
IF condition2
THEN
statements2
ELSE
IF condition3
THEN
statements3
ELSIF condition4
THEN
statements4
END IF;
END IF;
END IF;
IF condition1
THEN
IF condition2
THEN
statements2
ELSE
IF condition3
THEN
statements3
ELSIF condition4
THEN
statements4
END IF;
END IF;
END IF;
![Page 47: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/47.jpg)
Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
47
Loop exampleDECLARE
i NUMBER := 1;BEGIN
LOOP INSERT INTO T1
VALUES(i,i); i := i+1; EXIT WHEN i>100;
END LOOP; END; /
DECLARE i NUMBER := 1;
BEGIN LOOP
INSERT INTO T1VALUES(i,i);
i := i+1; EXIT WHEN i>100;
END LOOP; END; /
![Page 48: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/48.jpg)
Matthew P. Johnson, OCL3, CISDD CUNY, June 2005
48
More loops Infinite loop:
while loop:
LOOP
executable_statements;
END LOOP;
LOOP
executable_statements;
END LOOP;
WHILE condition
LOOP
executable_statements;
END LOOP;
WHILE condition
LOOP
executable_statements;
END LOOP;
![Page 49: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/49.jpg)
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 for_index IN low_value .. high_value
LOOP
executable_statements;
END LOOP;
FOR record_index IN my_cursor
LOOP
executable_statements;
END LOOP;
FOR record_index IN my_cursor
LOOP
executable_statements;
END LOOP;
![Page 50: OCL3 Oracle 10 g : SQL & PL/SQL Session #7](https://reader036.vdocuments.us/reader036/viewer/2022062408/568134af550346895d9bc581/html5/thumbnails/50.jpg)
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> GRANT EXECUTE ON wordcount TO george;
SQL> REVOKE EXECUTE FROM 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;
SQL> GRANT EXECUTE ON wordcount TO dba_role;SQL> GRANT EXECUTE ON wordcount TO public;