Introduction to PL/SQL: Oracle's Procedural Language Extension
A
N
I
N
T
R
O
D
U
C
T
I
O
N
T
O
P
L
/
S
Q
L
Mehdi Azarmi
1
Introduction
•
PL/SQL is Oracle's
procedural
language extension to
SQL, the non-procedural relational database language.
•
Combines power and flexibility of SQL (4GL) with
procedural constructs of a 3GL
•
Extends SQL by adding
•
Variables and types
•
Control Structures (conditional, loops)
•
Procedures and functions
•
Exception handling
•
…
2
Block Definition
•
Basic unit of PL/SQL is a block
•
Three possible sections of a block
•
Declarative section
•
Executable section
•
Exception handling
•
A block performs a logical unit of work in the program
•
Blocks can be nested
3
Block Structure
DECLARE
/* Declarative section: variables, types, and local
subprograms. */
BEGIN
/* Executable section: procedural and SQL statements go
here. */
/* This is the only section of the block that is
required. */
EXCEPTION
/* Exception handling section: error handling statements
go here. */
END;
4
Executable Section
•
The only required section
•
Contains constructs such as assignments, branches, loops,
procedure calls, and
triggers
•
SELECT, INSERT, UPDATE, DELETE
are supported
•
the SELECT statement has a special form in which a single tuple is
placed in variables
•
Data definition statements like
CREATE
,
DROP
, or ALTER are
not allowed.
•
PL/SQL is not case sensitive. C style comments (
/* ... */
) may
be used.
5
Variables and Types
•
Declared in the declaration section
•
Variables have a specific type associated with them
•
Types
•
One of the types used by SQL for database columns
•
A generic type used in PL/SQL
•
Most useful is
NUMBER
(can hold either an integer or a real number)
•
BOOLEAN (but not supported as a type for database columns)
•
Declared to be the same as the type of some database column
•
It is essential that the variable have the same type as the
relation column.
•
use the %TYPE operator
DECLARE
myBeer Beers.name%TYPE;
•
A variable may also have a type that is a record with several fields
beerTuple Beers%ROWTYPE; /* (name, manufacture)*/
6
Variables - Example
DECLARE
a NUMBER := 3;
BEGIN
a := a + 1;
END;
.
run;
•
The initial value of any variable, regardless of its type, is NULL.
•
This program has no effect when run, because there are no
changes to the database.
To execute the program
7
Example
CREATE TABLE T1(
e INTEGER,
f INTEGER
);
DELETE FROM T1;
INSERT INTO T1 VALUES(1, 3);
INSERT INTO T1 VALUES(2, 4);
/* Above is plain SQL; below is the
PL/SQL program. */
DECLARE
a NUMBER;
b NUMBER;
BEGIN
SELECT e,f INTO a,b FROM T1 WHERE
e>1;
INSERT INTO T1 VALUES(b,a);
END;
.
run;
•
There is only one tuple of T1 that has
first component greater than 1, (2,4).
Therefore, INSERT statement inserts
(4,2) into T1.
•
The SELECT statement in PL/SQL
only works if the result of the query
contains a
single tuple
•
If the query returns more than one
tuple, you need to use a
cursor
8
CONTROL FLOW IN
PL/SQL
9
I
F
S
t
a
t
e
m
e
n
t
•
An IF statement looks like:
IF <condition>
THEN <statement_list>
ELSE <statement_list>
END IF;
•
The ELSE part is optional
•
If you want a multiway branch, use:
IF <condition_1> THEN …
ELSIF <condition_2> THEN …
...
ELSIF <condition_n> THEN …
ELSE …
END IF;
10
10
I
F
-
E
x
a
m
p
l
e
DECLARE
a NUMBER;
b NUMBER;
BEGIN
SELECT e,f INTO a,b FROM T1 WHERE e>1;
IF b=1 THEN
INSERT INTO T1 VALUES(b,a);
ELSE
INSERT INTO T1 VALUES(b+10,a+10);
END IF;
END;
.
run;
11
11
I
F
-
E
x
a
m
p
l
e
2
DECLARE
TotalStudents NUMBER;
BEGIN
SELECT COUNT(*)
INTO TotalStudents
FROM students;
…
IF TotalStudents = 0 THEN
INSERT INTO temp_table (char_col)
VALUES ('There are no studentsregistered');
ELSIF TotalStudents < 5 THEN
INSERT INTO temp_table (char_col)
VALUES ('There are only a fewstudents registered');
ELSIF TotalStudents < 10 THEN
INSERT INTO temp_table (char_col)
VALUES ('There are a little morestudents registered');
ELSE
INSERT INTO temp_table (char_col)
VALUES ('There are many studentsregistered');
END IF;
END;
/
12
12
I
F
a
n
d
U
P
D
A
T
E
-
E
x
a
m
p
l
e
DECLARE
NewMajor VARCHAR2(10) := ’CS';
FirstName VARCHAR2(10) := ’Mehdi';
LastName VARCHAR2(10) := ’Azarmi';
BEGIN
UPDATE
students
SET major = NewMajor
WHERE first_name = FirstName
AND last_name = LastName;
IF
SQL%NOTFOUND THEN
INSERT INTO students (ID, first_name, last_name, major)
VALUES (student_sequence.NEXTVAL, FirstName, LastName,
NewMajor);
END IF;
END;
/
13
13
Loops
•
A loop allows execution of a set of statements repeatedly
•
Types of loops
•
Simple loop
•
Numeric For loop
•
While loop
•
Loops are created with the following:
LOOP
<loop_body> /* A list of statements. */
END LOOP;
•
At least one of the statements in <loop_body> should be
an EXIT statement of the form
•
EXIT WHEN <condition>;
14
14
L
O
O
P
-
E
x
a
m
p
l
e
DECLARE
i NUMBER := 1;
BEGIN
LOOP
INSERT INTO T1 VALUES(i,i);
i := i+1;
EXIT WHEN i>100;
END LOOP;
END;
.
run;
15
15
F
O
R
a
n
d
W
H
I
L
E
L
o
o
p
s
•
A
W
H
I
L
E
l
o
o
p
c
a
n
b
e
f
o
r
m
e
d
w
i
t
h
WHILE <condition> LOOP
<loop_body>
END LOOP;
•
A
s
i
m
p
l
e
F
O
R
l
o
o
p
c
a
n
b
e
f
o
r
m
e
d
w
i
t
h
:
FOR <var> IN <start>..<finish> LOOP
<loop_body>
END LOOP;
•
Here, <var> can be any variable; it is local to the for-loop
and need not be declared. Also, <start> and <finish> are
constants.
16
16
F
O
R
-
E
x
a
m
p
l
e
BEGIN
FOR LoopCounter IN 1..50 LOOP
INSERT INTO temp_table (num_col)
VALUES (LoopCounter);
END LOOP;
END;
/
17
17
Cursors
•
the SELECT statement in PL/SQL only works if the result
of the query contains a
single tuple
•
If the query returns more than one tuple, or you want to
manipulate a relation with more than one row, you need to
use a cursor
•
A
cursor
creates a named context area as a result of
executing an associated SQL statement
•
Permits the program to step through the multiple rows
displayed by an SQL statement
18
18
1) DECLARE
/* Output variables to hold the result of the query: */
2) a T1.e%TYPE;
3) b T1.f%TYPE;
/* Cursor declaration: */
4) CURSOR T1Cursor IS
5) SELECT e, f
6) FROM T1
7) WHERE e < f
8) FOR UPDATE;
9) BEGIN
10) OPEN T1Cursor;
…
Next page
19
19
C
U
R
S
O
R
–
E
x
a
m
p
l
e
p
a
r
t
1
C
U
R
S
O
R
–
E
x
a
m
p
l
e
p
a
r
t
2
20
20
11) LOOP
/* Retrieve each row of the result of the above query
into PL/SQL variables: */
12) FETCH T1Cursor INTO a, b;
/* If there are no more rows to fetch, exit the loop: */
13) EXIT WHEN T1Cursor%NOTFOUND;
/* Delete the current tuple: */
14) DELETE FROM T1 WHERE CURRENT OF T1Cursor;
/* Insert the reverse tuple: */
15) INSERT INTO T1 VALUES(b, a);
16) END LOOP;
/* Free cursor used by the query. */
17) CLOSE T1Cursor;
18) END;
19) .
20) run;
Procedure
•
PROCEDURE and FUNCTIONS
•
Parameters
•
Mode
of operation:
•
IN (read-only)
•
OUT (write-only)
•
INOUT (read and write)
•
Type
•
the type specifier in a parameter declaration must be
unconstrained
.
•
Example: CHAR(10) and VARCHAR(20) are illegal
•
CHAR or VARCHAR should be used instead.
21
21
P
R
O
C
E
D
U
R
E
-
T
e
m
p
l
a
t
e
CREATE
OR REPLACE
PROCEDURE
PROCNAME
(
PARAMETERS
) AS
<local_var_declarations>
BEGIN
<procedure_body>
END;
.
run;
•
The run at the end runs the statement that creates the procedure; it
does not execute the procedure.
•
To execute the procedure, use another PL/SQL statement, in which
the procedure is invoked as an executable statement.
•
For example:
BEGIN addtuple1(99); END;
.
Run;
22
22
P
R
O
C
E
D
U
R
E
–
E
x
a
m
p
l
e
1
CREATE TABLE T2 (
a INTEGER,
b CHAR(10)
);
CREATE PROCEDURE addtuple2(
x IN T2.a%TYPE,
y IN T2.b%TYPE)
AS
BEGIN
INSERT INTO T2(a, b)
VALUES(x, y);
END addtuple2;
.
run;
•
Now, to add a tuple (10, 'abc') to T2:
BEGIN
addtuple2(10, 'abc');
END;
.
run;
23
23
P
R
O
C
E
D
U
R
E
–
E
x
a
m
p
l
e
2
CREATE TABLE T3 (
a INTEGER,
b INTEGER
);
CREATE PROCEDURE addtuple3(a NUMBER, b OUT NUMBER)
AS
BEGIN
b := 4;
INSERT INTO T3 VALUES(a, b);
END;
.
Run;
DECLARE
v NUMBER;
BEGIN
addtuple3(10, v); /* second parameter should be an
lvalue
*/
END;
.
run;
24
24
P
R
O
C
E
D
U
R
E
–
F
i
n
a
l
N
o
t
e
s
•
We can also write functions instead of procedures. In a function
declaration, we follow the parameter list by RETURN and the
type of the return value:
•
CREATE FUNCTION <func_name>(<param_list>) RETURN
<return_type> AS ...
•
In the body of the function definition, "RETURN <expression>;" exits
from the function and returns the value of <expression>.
•
To find out what procedures and functions you have created,
use the following SQL query:
select object_type, object_name
from user_objects
where object_type = 'PROCEDURE’ or object_type =
'FUNCTION’;
•
To drop a stored procedure/function:
drop procedure <procedure_name>;
drop function <function_name>;
25
25
Printing
•
Always use the following line (setting output buffer) at the
beginning of your SQL file:
set serveroutput on size 32000
•
Printing a line:
dbms_output.put_line(VAR1|| '. ' || VAR2);
•
You may declare and use a
bind variable
to print a
local variable
VARIABLE x NUMBER
BEGIN
:x := 1;
END;
.
run;
PRINT :x;
26
26
Debugging
•
PL/SQL does not always tell you about compilation errors.
Instead, it gives you a cryptic message such as:
"procedure created with compilation errors"
.
•
If you don't see what is wrong immediately, try issuing the
command
show errors procedure <procedure_name>;
•
Alternatively, you can type, SHO ERR (short for SHOW
ERRORS) to see the most recent compilation error.
•
Note that the location of the error given as part of the error
message is not always accurate!
27
27
Performance of PL/SQL
•
SQL results in many network trips, one for each SQL
statement
•
PL/SQL permits several SQL statements to be bundled
into a single block
•
Results in fewer calls to database
•
Less network traffic
•
faster response time
28
28
References
•
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-
plsql.html
•
Oracle PL/SQL Programming: Covers Versions Through
Oracle Database 11g Release 2, by Steven Feuerstein
and Bill Pribyl (Oct 1, 2009)
29
29
PL/SQL is Oracle's extension to SQL, combining the power of SQL with the procedural constructs of a 3GL. This introduction covers the basics of PL/SQL blocks, block structure, variables and types, executable sections, and example variable declarations. It also highlights the features such as error handling, control structures, and types declaration.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
1 AN INTRODUCTION TO PL/SQL Mehdi Azarmi
2 Introduction PL/SQL is Oracle's procedural language extension to SQL, the non-procedural relational database language. Combines power and flexibility of SQL (4GL) with procedural constructs of a 3GL Extends SQL by adding Variables and types Control Structures (conditional, loops) Procedures and functions Exception handling
3 Block Definition Basic unit of PL/SQL is a block Three possible sections of a block Declarative section Executable section Exception handling A block performs a logical unit of work in the program Blocks can be nested
4 Block Structure DECLARE /* Declarative section: variables, types, and local subprograms. */ BEGIN here. */ /* This is the only section of the block that is required. */ /* Executable section: procedural and SQL statements go EXCEPTION /* Exception handling section: error handling statements go here. */ END;
5 Executable Section The only required section Contains constructs such as assignments, branches, loops, procedure calls, and triggers SELECT, INSERT, UPDATE, DELETE are supported the SELECT statement has a special form in which a single tuple is placed in variables Data definition statements like CREATE, DROP, or ALTER are not allowed. PL/SQL is not case sensitive. C style comments (/* ... */) may be used.
6 Variables and Types Declared in the declaration section Variables have a specific type associated with them Types One of the types used by SQL for database columns A generic type used in PL/SQL Most useful is NUMBER (can hold either an integer or a real number) BOOLEAN (but not supported as a type for database columns) Declared to be the same as the type of some database column It is essential that the variable have the same type as the relation column. use the %TYPE operator DECLARE myBeer Beers.name%TYPE; A variable may also have a type that is a record with several fields beerTuple Beers%ROWTYPE; /* (name, manufacture)*/
7 Variables - Example DECLARE a NUMBER := 3; BEGIN a := a + 1; END; . run; To execute the program The initial value of any variable, regardless of its type, is NULL. This program has no effect when run, because there are no changes to the database.
8 Example There is only one tuple of T1 that has first component greater than 1, (2,4). Therefore, INSERT statement inserts (4,2) into T1. CREATE TABLE T1( e INTEGER, f INTEGER ); DELETE FROM T1; INSERT INTO T1 VALUES(1, 3); INSERT INTO T1 VALUES(2, 4); The SELECT statement in PL/SQL only works if the result of the query contains a single tuple /* Above is plain SQL; below is the PL/SQL program. */ DECLARE a NUMBER; b NUMBER; BEGIN SELECT e,f INTO a,b FROM T1 WHERE e>1; INSERT INTO T1 VALUES(b,a); END; . run; If the query returns more than one tuple, you need to use a cursor
9 CONTROL FLOW IN PL/SQL
10 IF Statement An IF statement looks like: IF <condition> THEN <statement_list> ELSE <statement_list> END IF; The ELSE part is optional If you want a multiway branch, use: IF <condition_1> THEN ELSIF <condition_2> THEN ... ELSIF <condition_n> THEN ELSE END IF;
11 IF - Example DECLARE a NUMBER; b NUMBER; BEGIN SELECT e,f INTO a,b FROM T1 WHERE e>1; IF b=1 THEN INSERT INTO T1 VALUES(b,a); ELSE INSERT INTO T1 VALUES(b+10,a+10); END IF; END; . run;
12 IF - Example 2 IF TotalStudents = 0 THEN INSERT INTO temp_table (char_col) VALUES ('There are no students registered'); ELSIF TotalStudents < 5 THEN INSERT INTO temp_table (char_col) VALUES ('There are only a few students registered'); ELSIF TotalStudents < 10 THEN INSERT INTO temp_table (char_col) VALUES ('There are a little more students registered'); ELSE INSERT INTO temp_table (char_col) VALUES ('There are many students registered'); END IF; END; / DECLARE TotalStudents NUMBER; BEGIN SELECT COUNT(*) INTO TotalStudents FROM students;
13 IF and UPDATE - Example DECLARE NewMajor VARCHAR2(10) := CS'; FirstName VARCHAR2(10) := Mehdi'; LastName VARCHAR2(10) := Azarmi'; BEGIN UPDATE students SET major = NewMajor WHERE first_name = FirstName AND last_name = LastName; IF SQL%NOTFOUND THEN INSERT INTO students (ID, first_name, last_name, major) VALUES (student_sequence.NEXTVAL, FirstName, LastName, NewMajor); END IF; END; /
14 Loops A loop allows execution of a set of statements repeatedly Types of loops Simple loop Numeric For loop While loop Loops are created with the following: LOOP <loop_body> /* A list of statements. */ END LOOP; At least one of the statements in <loop_body> should be an EXIT statement of the form EXIT WHEN <condition>;
15 LOOP - Example DECLARE i NUMBER := 1; BEGIN LOOP INSERT INTO T1 VALUES(i,i); i := i+1; EXIT WHEN i>100; END LOOP; END; . run;
16 FOR and WHILE Loops A WHILE loop can be formed with WHILE <condition> LOOP <loop_body> END LOOP; A simple FOR loop can be formed with: FOR <var> IN <start>..<finish> LOOP <loop_body> END LOOP; Here, <var> can be any variable; it is local to the for-loop and need not be declared. Also, <start> and <finish> are constants.
17 FOR - Example BEGIN FOR LoopCounter IN 1..50 LOOP INSERT INTO temp_table (num_col) VALUES (LoopCounter); END LOOP; END; /
18 Cursors the SELECT statement in PL/SQL only works if the result of the query contains a single tuple If the query returns more than one tuple, or you want to manipulate a relation with more than one row, you need to use a cursor A cursor creates a named context area as a result of executing an associated SQL statement Permits the program to step through the multiple rows displayed by an SQL statement
19 CURSOR Example part1 1) DECLARE /* Output variables to hold the result of the query: */ 2) a T1.e%TYPE; 3) b T1.f%TYPE; /* Cursor declaration: */ 4) CURSOR T1Cursor IS 5) SELECT e, f 6) FROM T1 7) WHERE e < f 8) FOR UPDATE; 9) BEGIN 10) OPEN T1Cursor; Next page
20 CURSOR Example part2 11) LOOP /* Retrieve each row of the result of the above query into PL/SQL variables: */ 12) FETCH T1Cursor INTO a, b; /* If there are no more rows to fetch, exit the loop: */ 13) EXIT WHEN T1Cursor%NOTFOUND; /* Delete the current tuple: */ 14) DELETE FROM T1 WHERE CURRENT OF T1Cursor; /* Insert the reverse tuple: */ 15) INSERT INTO T1 VALUES(b, a); 16) END LOOP; /* Free cursor used by the query. */ 17) CLOSE T1Cursor; 18) END; 19) . 20) run;
21 Procedure PROCEDURE and FUNCTIONS Parameters Mode of operation: IN (read-only) OUT (write-only) INOUT (read and write) Type the type specifier in a parameter declaration must be unconstrained. Example: CHAR(10) and VARCHAR(20) are illegal CHAR or VARCHAR should be used instead.
22 PROCEDURE - Template CREATE OR REPLACE PROCEDURE PROCNAME(PARAMETERS) AS <local_var_declarations> BEGIN <procedure_body> END; . run; The run at the end runs the statement that creates the procedure; it does not execute the procedure. To execute the procedure, use another PL/SQL statement, in which the procedure is invoked as an executable statement. For example: BEGIN addtuple1(99); END; . Run;
23 PROCEDURE Example 1 CREATE TABLE T2 ( a INTEGER, b CHAR(10) ); CREATE PROCEDURE addtuple2( x IN T2.a%TYPE, y IN T2.b%TYPE) AS BEGIN INSERT INTO T2(a, b) VALUES(x, y); END addtuple2; . run; Now, to add a tuple (10, 'abc') to T2: BEGIN addtuple2(10, 'abc'); END; . run;
24 PROCEDURE Example 2 CREATE TABLE T3 ( a INTEGER, b INTEGER ); CREATE PROCEDURE addtuple3(a NUMBER, b OUT NUMBER) AS BEGIN b := 4; INSERT INTO T3 VALUES(a, b); END; . Run; DECLARE v NUMBER; BEGIN addtuple3(10, v); /* second parameter should be an lvalue*/ END; . run;
25 PROCEDURE Final Notes We can also write functions instead of procedures. In a function declaration, we follow the parameter list by RETURN and the type of the return value: CREATE FUNCTION <func_name>(<param_list>) RETURN <return_type> AS ... In the body of the function definition, "RETURN <expression>;" exits from the function and returns the value of <expression>. To find out what procedures and functions you have created, use the following SQL query: select object_type, object_name from user_objects where object_type = 'PROCEDURE or object_type = 'FUNCTION ; To drop a stored procedure/function: drop procedure <procedure_name>; drop function <function_name>;
26 Printing Always use the following line (setting output buffer) at the beginning of your SQL file: set serveroutput on size 32000 Printing a line: dbms_output.put_line(VAR1|| '. ' || VAR2); You may declare and use a bind variable to print a local variable VARIABLE x NUMBER BEGIN :x := 1; END; . run; PRINT :x;
27 Debugging PL/SQL does not always tell you about compilation errors. Instead, it gives you a cryptic message such as: "procedure created with compilation errors". If you don't see what is wrong immediately, try issuing the command show errors procedure <procedure_name>; Alternatively, you can type, SHO ERR (short for SHOW ERRORS) to see the most recent compilation error. Note that the location of the error given as part of the error message is not always accurate!
28 Performance of PL/SQL SQL results in many network trips, one for each SQL statement PL/SQL permits several SQL statements to be bundled into a single block Results in fewer calls to database Less network traffic faster response time
29 References http://infolab.stanford.edu/~ullman/fcdb/oracle/or- plsql.html Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2, by Steven Feuerstein and Bill Pribyl (Oct 1, 2009)
