plsql programming introduction
TRANSCRIPT
-
8/8/2019 Plsql Programming Introduction
1/42
Programming in Oracle
with PL/SQL
Procedural Language Extension to SQL
-
8/8/2019 Plsql Programming Introduction
2/42
PL/SQL
Allows using general programming toolswith SQL, for example: loops,conditions, functions, etc.
This allows a lot more freedom thangeneral SQL, and is lighter-weight thanJDBC.
We write PL/SQL code in a regular file,for example PL.sql, and load it with @PLin the sqlplus console.
-
8/8/2019 Plsql Programming Introduction
3/42
PL/SQL Blocks PL/SQL code is built of Blocks, with a unique
structure.
There are two types of blocks in PL/SQL:
1. Anonymous Blocks: have no name (like scripts) can be written and executed immediately in SQLPLUS
can be used in a trigger
2. Named Blocks:
Procedures
Functions
-
8/8/2019 Plsql Programming Introduction
4/42
Anonymous Block Structure:
DECLARE (optional)/* Here you declare the variables you will use in this
block */BEGIN (mandatory)
/* Here you define the executable statements (whatthe block DOES!)*/
EXCEPTION (optional)/* Here you define the actions that take place if an
exception is thrown during the run of this block */END; (mandatory)/
Always put a new line with only a
/ at the end of a block! (This
tells Oracle to run the block)
A correct completion of a blockwill generate the followingmessage:
PL/SQL procedure successfullycompleted
-
8/8/2019 Plsql Programming Introduction
5/42
-
8/8/2019 Plsql Programming Introduction
6/42
DECLARESyntax
Examples
identifier[CONSTANT] datatype [NOT NULL][:= | DEFAULT expr];
Declarebirthday DATE;
age NUMBER(2) NOT NULL := 27;name VARCHAR2(13) := 'Levi';magic CONSTANT NUMBER := 77;valid BOOLEAN NOT NULL := TRUE;
Notice that PL/SQLincludes all SQL types,
and more
-
8/8/2019 Plsql Programming Introduction
7/42
Declaring Variables with the
%TYPE AttributeExamples
DECLAREsname Sailors.sname%TYPE;fav_boat VARCHAR2(30);my_fav_boat fav_boat%TYPE := 'Pinta';...
Accessing column snamein table Sailors
Accessinganother variable
-
8/8/2019 Plsql Programming Introduction
8/42
Declaring Variables with the
%ROWTYPE Attribute
Declare a variable with the type of a
ROW of a table.
And how do we access the fields inreserves_record?
reserves_record Reserves%ROWTYPE;
reserves_record.sid:=9;
Reserves_record.bid:=877;
Accessing
tableReserves
-
8/8/2019 Plsql Programming Introduction
9/42
Creating a PL/SQL Record
A record is a type of variable which we candefine (like struct in C or object in Java)
DECLARETYPE sailor_record_type IS RECORD
(sname VARCHAR2(10),sid VARCHAR2(9),
age NUMBER(3),rating NUMBER(3));
sailor_record sailor_record_type;
...BEGIN
Sailor_record.sname:=peter;Sailor_record.age:=45;
-
8/8/2019 Plsql Programming Introduction
10/42
Creating a Cursor
We create a Cursor when we want to go over aresult of a query (like ResultSet in JDBC) Syntax Example:
DECLAREcursor c is select * from sailors;sailorData sailors%ROWTYPE;
BEGINopen c;fetch c into sailorData;
sailorData is a
variable thatcan hold aROW fromthe sailorstable
Here thefirst row ofsailors isinserted intosailorData
-
8/8/2019 Plsql Programming Introduction
11/42
Example
DECLARE
Pi constant NUMBER(8,7) := 3.1415926;area NUMBER(14,2);cursor rad_cursor is select * from RAD_VALS;rad_value rad_cursor%ROWTYPE;
BEGINopen rad_cursor;fetch rad_cursor into rad_val;area:=pi*power(rad_val.radius,2);insert into AREAS values (rad_val.radius, area);
close rad_cursor;END;/
radius
36
8
Rad_cursor
f
etch
Rad_val
Radius AreaAREAS
3 28.27
RAD_VALS
-
8/8/2019 Plsql Programming Introduction
12/42
DECLAREcursor rad_cursor is select * from
RAD_VALS;rad_value rad_cursor%ROWTYPE;
BEGINopen rad_cursor;fetch rad_cursor into rad_val;area:=pi*power(rad_val.radius,2);insert into AREAS values (rad_val.radius,area);
DECLARE
cursor rad_cursor is select * fromRAD_VALS;rad_value RAD_VALS%ROWTYPE;
BEGINopen rad_cursor;fetch rad_cursor into rad_val;area:=pi*power(rad_val.radius,2);insert into AREAS values (rad_val.radius,
area);
DECLARE
cursor rad_cursor is select radius from
RAD_VALS;rad_value RAD_VALS.radius%TYPE;
BEGINopen rad_cursor;fetch rad_cursor into rad_val;area:=pi*power(rad_val,2);insert into AREAS values (rad_val, area);
DECLAREcursor rad_cursor is select *from
RAD_VALS;rad_value RAD_VALS.radius%TYPE;
BEGINopen rad_cursor;fetch rad_cursor into rad_val;area:=pi*power(rad_val,2);insert into AREAS values (rad_val, area);
-
8/8/2019 Plsql Programming Introduction
13/42
Explicit Cursor Attributes
Obtain status information about acursor.
Attribute Type Description
%ISOPEN Boolean Evaluates to TRUE if the cursor
is open.
%NOTFOUND Boolean Evaluates to TRUE if the most
recent fetch does not return a row.
%FOUND Boolean Evaluates to TRUE if the most
recent fetch returns a row;
complement of %NOTFOUND
%ROWCOUNT Number Evaluates to the total number of
rows returned so far.
-
8/8/2019 Plsql Programming Introduction
14/42
SELECT Statements
INTO clause is required. Query must return exactly one row.
Otherwise, a NO_DATA_FOUND orTOO_MANY_ROWS exception is thrown
DECLAREv_sname VARCHAR2(10);v_rating NUMBER(3);
BEGINSELECT sname, rating
INTO v_sname, v_ratingFROM Sailors
WHERE sid = '112';END;/
-
8/8/2019 Plsql Programming Introduction
15/42
Conditional logic
If
then
elsif then
else
end if;
If
then
if then
end if;else
end if;
Condition: Nested conditions:
-
8/8/2019 Plsql Programming Introduction
16/42
IF-THEN-ELSIF Statements
. . .IF rating > 7 THENv_message := 'You are great';
ELSIF rating >= 5 THENv_message := 'Not bad';
ELSEv_message := 'Pretty bad';
END IF;. . .
-
8/8/2019 Plsql Programming Introduction
17/42
Suppose we have the following
table:
Want to keep track of howmany times someone
logged on to the DB When running, if user is
already in table, incrementlogon_num. Otherwise,
insert user into table
create table mylog(
who varchar2(30),
logon_num number
);
logon_numwho
3Peter4John
2Moshe
mylog
-
8/8/2019 Plsql Programming Introduction
18/42
SolutionDECLARE
cnt NUMBER;BEGINselect count(*)into cntfrom mylog
where who = user;if cnt > 0 thenupdate mylogset logon_num = logon_num + 1
where who = user;
elseinsert into mylog values(user, 1);
end if;commit;
end;
/
-
8/8/2019 Plsql Programming Introduction
19/42
SQL%ROWCOUNT Number of rows affected by themost recent SQL statement (aninteger value).
SQL%FOUND Boolean attribute that evaluates toTRUE if the most recent SQLstatement affects one or more rows.
SQL%NOTFOUND Boolean attribute that evaluates toTRUE if the most recent SQLstatement does not affect any rows.
SQL%ISOPEN Always evaluates to FALSE becausePL/SQL closes implicit cursorsimmediately after they are executed.
SQL Cursor
SQL cursor is automatically created aftereach SQL query. It has 4 useful attributes:
-
8/8/2019 Plsql Programming Introduction
20/42
Solution (2)
BEGINupdate mylogset logon_num = logon_num + 1
where who = user;
if SQL%ROWCOUNT = 0 theninsert into mylog values(user, 1);
end if;commit;END;/
-
8/8/2019 Plsql Programming Introduction
21/42
Loops: Simple Loop
DECLARE
i number_table.num%TYPE := 1;BEGINLOOPINSERT INTO number_tableVALUES(i);i := i + 1;EXIT WHEN i > 10;
END LOOP;END;
create table number_table(
num NUMBER(10));
-
8/8/2019 Plsql Programming Introduction
22/42
Loops: Simple Cursor Loop
DECLARE
cursor c is select * from number_table;cVal c%ROWTYPE;BEGINopen c;LOOP
fetch c into cVal;EXIT WHEN c%NOTFOUND;insert into doubles values(cVal.num*2);
END LOOP;END;
create table number_table(
num NUMBER(10));
-
8/8/2019 Plsql Programming Introduction
23/42
Loops: FOR Loop
DECLAREi number_table.num%TYPE;
BEGIN
FOR i IN 1..10 LOOPINSERT INTO number_table VALUES(i);
END LOOP;END;
Notice that i is incrementedautomatically
-
8/8/2019 Plsql Programming Introduction
24/42
Loops: For Cursor Loops
DECLAREcursor c is select * from number_table;
BEGIN
for num_row in c loopinsert into doubles_tablevalues(num_row.num*2);
end loop;END;
/
Notice that a lot is being done implicitly:declaration of num_row, open cursor,
fetch cursor, the exit condition
-
8/8/2019 Plsql Programming Introduction
25/42
Loops: WHILE Loop
DECLARETEN number:=10;i number_table.num%TYPE:=1;BEGINWHILE i
-
8/8/2019 Plsql Programming Introduction
26/42
Printing Output
You need to use a function in theDBMS_OUTPUT package in order to print to
the output
If you want to see the output on the screen, youmust type the following (before starting):
set serveroutput on format wrapped size 1000000
Then print using dbms_output. put_line(your_string);
dbms_output.put(your_string);
-
8/8/2019 Plsql Programming Introduction
27/42
Input and output example
set serveroutput on format wrap size 1000000ACCEPT high PROMPT 'Enter a number: '
DECLAREi number_table.num%TYPE:=1;
BEGI
Ndbms_output.put_line('Look Ma, I can print from PL/SQL!!!');WHILE i
-
8/8/2019 Plsql Programming Introduction
28/42
Reminder- structure of a block
DECLARE (optional)/* Here you declare the variables you will use in this
block */BEGIN (mandatory)
/* Here you define the executable statements (whatthe block DOES!)*/
EXCEPTION (optional)/* Here you define the actions that take place if an
exception is thrown during the run of this block */END; (mandatory)/
-
8/8/2019 Plsql Programming Introduction
29/42
Trapping Exceptions
Here we define the actions that shouldhappen when an exception is thrown.
Example Exceptions:
NO_DATA_FOUND TOO_MANY_ROWS
ZERO_DIVIDE
When handling an exception, considerperforming a rollback
-
8/8/2019 Plsql Programming Introduction
30/42
DECLAREnum_row number_table%ROWTYPE;
BEGIN
select *into num_rowfrom number_table;dbms_output.put_line(1/num_row.num);
EXCEPTIONWHENNO_DATA_FOUND THEN
dbms_output.put_line('No data!');
WHEN TOO_MANY_ROWS THENdbms_output.put_line('Too many!');WHEN OTHERS THEN
dbms_output.put_line(Error);end;
-
8/8/2019 Plsql Programming Introduction
31/42
User-Defined ExceptionDECLARE
e_number1 EXCEPTION;cnt NUMBER;
BEGINselect count(*)into cnt
from number_table;
IF cnt = 1 THEN RAISE e_number1;ELSE dbms_output.put_line(cnt);END IF;
EXCEPTIONWHEN e_number1 THEN
dbms_output.put_line('Count = 1');
end;
-
8/8/2019 Plsql Programming Introduction
32/42
Functions and Procedures
Up until now, our code was in ananonymous block
It was run immediately It is useful to put code in a function or
procedure so it can be called severaltimes
Once we create a procedure or functionin a Database, it will remain untildeleted (like a table).
-
8/8/2019 Plsql Programming Introduction
33/42
CREATE [OR REPLACE] PROCEDUREprocedure_name[(parameter1 [mode1] datatype1,parameter2[mode2] datatype2,. . .)]
IS|ASPL/SQL Block;
Creating Procedures
Modes: IN: procedure must be called with a value for the parameter.
Value cannot be changed OUT : procedure must be called with a variable for theparameter. Changes to the parameter are seen by the user(i.e., call by reference)
IN OUT: value can be sent, and changes to the parameterare seen by the user
Default Mode is: IN
-
8/8/2019 Plsql Programming Introduction
34/42
create or replace procedurenum_logged(person IN mylog.who%TYPE,num OUT mylog.logon_num%TYPE)
ISBEGIN
select logon_numinto num
from mylogwhere who = person;END;/
Example- what does this do?
logon_
numwho
3Pete
4John
2Joe
Table mylog
-
8/8/2019 Plsql Programming Introduction
35/42
declare
howmany mylog.logon_num%TYPE;begin
num_logged(John',howmany);
dbms_output.put_line(howmany);end;/
Calling the Procedure
-
8/8/2019 Plsql Programming Introduction
36/42
Errors in a Procedure
When creating the procedure, if there areerrors in its definition, they will not be shown
To see the errors of a procedure called
myProcedure, typeSHOW ERRORS PROCEDURE myProcedure
in the SQLPLUS prompt
For functions, typeSHOW ERRORS FUNCTIONmyFunction
-
8/8/2019 Plsql Programming Introduction
37/42
Creating a Function
Almost exactly like creating aprocedure, but you supply a return type
CREATE [OR REPLACE] FUNCTIONfunction_name
[(parameter1 [mode1] datatype1,parameter2[mode2] datatype2,
. . .)]RETURN datatypeIS|ASPL/SQL Block;
-
8/8/2019 Plsql Programming Introduction
38/42
create or replace functionrating_message(rating IN NUMBER)return VARCHAR2
ASBEGINIF rating > 7 THENreturn 'You are great';
ELSIF rating >= 5 THENreturn 'Not bad';
ELSEreturn 'Pretty bad';
END IF;END;/
A Function
NOTE THAT YOU
DON'T SPECIFY THE
SIZE
-
8/8/2019 Plsql Programming Introduction
39/42
declarepaulRate:=9;
Begindbms_output.put_line(ratingMessage(paulRate));
end;/
Calling the function
-
8/8/2019 Plsql Programming Introduction
40/42
create or replace function squareFunc(num in number)
return number
is
BEGIN
return num*num;
End;
/
BEGINdbms_output.put_line(squareFunc(3.5));
END;
/
Creating a function:
Using the function:
-
8/8/2019 Plsql Programming Introduction
41/42
Packages
Functions, Procedures, Variables can be puttogether in a package
In a package, you can allow some of the
members to be "public" and some to be"private"
There are also many predefined Oraclepackages
Won't discuss packages in this course
-
8/8/2019 Plsql Programming Introduction
42/42
Triggers
Triggers are special procedures whichwe want activated when someone hasperformed some action on the DB.
For example, we might define a triggerthat is executed when someoneattempts to insert a row into a table,and the trigger checks that theinserted data is valid.
To be continued