Real SQL Programming
Persistent Stored Modules
(PSM)
PL/SQL
Embedded SQL
1
SQL in Real Programs
We have seen only how SQL is
used at the generic query interface
--- an environment where we sit at
a terminal and ask queries of a
database.
Reality is almost always different:
conventional programs interacting
with SQL.
2
Options
1. Code in a specialized language is
stored in the database itself (e.g.,
PSM, PL/SQL).
2. SQL statements are embedded in
a host language (e.g., C).
3. Connection tools are used to allow
a conventional language to access
a database (e.g., CLI, JDBC,
PHP/DB). 3
Stored Procedures
PSM, or “persistent stored
modules,” allows us to store
procedures as database schema
elements.
PSM = a mixture of conventional
statements (if, while, etc.) and SQL.
Lets us do things we cannot do in
SQL alone.
4
Basic PSM Form
CREATE PROCEDURE <name> (
<parameter list> )
<optional local declarations>
<body>;
Function alternative:
CREATE FUNCTION <name> (
<parameter list> ) RETURNS
<type>
5
Parameters in PSM
Unlike the usual name-type pairs in
languages like C, PSM uses mode-
name-type triples, where the mode
can be:
IN = procedure uses value, does not
change value.
OUT = procedure changes, does not
use.
INOUT = both.
6
Example: Stored
Procedure
Let’s write a procedure that takes
two arguments b and p, and adds
a tuple to Sells(bar, beer, price)
that has bar = ’Joe’’s Bar’, beer =
b, and price = p.
Used by Joe to add to his menu more
easily.
7
The Procedure
CREATE PROCEDURE JoeMenu (
IN b CHAR(20), Parameters are both
read-only, not changed
IN p REAL
)
INSERT INTO Sells The body ---
VALUES(’Joe’’s Bar’, b, p); a single insertion
8
Invoking Procedures
Use SQL/PSM statement CALL, with the
name of the desired procedure and
arguments.
Example:
CALL JoeMenu(’Moosedrool’, 5.00);
Functions used in SQL expressions
wherever a value of their return type is
appropriate.
9
Kinds of PSM statements – (1)
RETURN <expression> sets the return
value of a function.
Unlike C, etc., RETURN does not
terminate function execution.
DECLARE <name> <type> used to
declare local variables.
BEGIN . . . END for groups of
statements.
Separate statements by semicolons. 10
Kinds of PSM Statements – (2)
Assignment statements:
SET <variable> =
<expression>;
Example: SET b = ’Bud’;
Statement labels: give a statement
a label by prefixing a name and a
colon.
11
IF Statements
Simplest form:
IF <condition> THEN
<statements(s)>
END IF;
Add ELSE <statement(s)> if desired, as
IF . . . THEN . . . ELSE . . . END IF;
Add additional cases by ELSEIF
<statements(s)>: IF … THEN … ELSEIF …
THEN … ELSEIF … THEN … ELSE … END
IF;
12
Example: IF
Let’s rate bars by how many
customers they have, based on
Frequents(drinker,bar).
<100 customers: ‘unpopular’.
100-199 customers: ‘average’.
>= 200 customers: ‘popular’.
Function Rate(b) rates bar b.
13
Example: IF (continued)
CREATE FUNCTION Rate (IN b CHAR(20) )
Number of
RETURNS CHAR(10)
customers of
DECLARE cust INTEGER; bar b
BEGIN
SET cust = (SELECT COUNT(*) FROM
Frequents
WHERE bar = b);
IF cust < 100 THEN RETURN ’unpopular’
ELSEIF cust < 200 THEN RETURN ’average’
ELSE RETURN ’popular’
END IF; Nested
Return occurs here, not at IF statement
END; 14
one of the RETURN statements
Loops
Basic form:
<loop name>: LOOP
<statements>
END LOOP;
Exit from a loop by:
LEAVE <loop name>
15
Example: Exiting a Loop
loop1: LOOP
...
LEAVE loop1; If this statement is executed . . .
...
END LOOP;
Control winds up here
16
Other Loop Forms
WHILE <condition>
DO <statements>
END WHILE;
REPEAT <statements>
UNTIL <condition>
END REPEAT;
17
Queries
General SELECT-FROM-WHERE
queries are not permitted in PSM.
There are three ways to get the
effect of a query:
1. Queries producing one value can be
the expression in an assignment.
2. Single-row SELECT . . . INTO.
3. Cursors.
18
Example:
Assignment/Query
Using local variable p and Sells(bar,
beer, price), we can get the price Joe
charges for Bud by:
SET p = (SELECT price FROM Sells
WHERE bar = ’Joe’’s Bar’ AND
beer = ’Bud’);
19
SELECT . . . INTO
Another way to get the value of a query
that returns one tuple is by placing INTO
<variable> after the SELECT clause.
Example:
SELECT price INTO p FROM Sells
WHERE bar = ’Joe’’s Bar’ AND
beer = ’Bud’;
20
Cursors
A cursor is essentially a tuple-
variable that ranges over all tuples
in the result of some query.
Declare a cursor c by:
DECLARE c CURSOR FOR <query>;
21
Opening and Closing
Cursors
To use cursor c, we must issue the
command:
OPEN c;
The query of c is evaluated, and c is set
to point to the first tuple of the result.
When finished with c, issue
command:
CLOSE c;
22
Fetching Tuples From a
Cursor
To get the next tuple from cursor c,
issue command:
FETCH FROM c INTO x1, x2,
…,xn ;
The x ’s are a list of variables, one for
each component of the tuples
referred to by c.
c is moved automatically to the next
tuple. 23
Breaking Cursor Loops –
(1)
The usual way to use a cursor is to
create a loop with a FETCH
statement, and do something with
each tuple fetched.
A tricky point is how we get out of
the loop when the cursor has no
more tuples to deliver.
24
Breaking Cursor Loops –
(2)
Each SQL operation returns a status,
which is a 5-digit character string.
For example, 00000 = “Everything OK,”
and 02000 = “Failed to find a tuple.”
In PSM, we can get the value of the
status in a variable called SQLSTATE.
25
Breaking Cursor Loops –
(3)
We may declare a condition, which
is a boolean variable that is true if
and only if SQLSTATE has a
particular value.
Example: We can declare condition
NotFound to represent 02000 by:
DECLARE NotFound CONDITION FOR
SQLSTATE ’02000’;
26
Breaking Cursor Loops –
(4)
The structure of a cursor loop is thus:
cursorLoop: LOOP
…
FETCH c INTO … ;
IF NotFound THEN LEAVE cursorLoop;
END IF;
…
END LOOP;
27
Example: Cursor
Let’s write a procedure that
examines Sells(bar, beer, price),
and raises by $1 the price of all
beers at Joe’s Bar that are under
$3.
Yes, we could write this as a simple
UPDATE, but the details are
instructive anyway.
28
The Needed Declarations
CREATE PROCEDURE JoeGouge( ) Used to hold
DECLARE theBeer CHAR(20); beer-price pairs
when fetching
DECLARE thePrice REAL; through cursor c
DECLARE NotFound CONDITION FOR
SQLSTATE ’02000’;
Returns Joe’s menu
DECLARE c CURSOR FOR
(SELECT beer, price FROM Sells
WHERE bar = ’Joe’’s Bar’);
29
The Procedure Body
BEGIN
OPEN c; Check if the recent
FETCH failed to
menuLoop: LOOP
get a tuple
FETCH c INTO theBeer, thePrice;
IF NotFound THEN LEAVE menuLoop END IF;
IF thePrice < 3.00 THEN
UPDATE Sells SET price = thePrice + 1.00
WHERE bar = ’Joe’’s Bar’ AND beer = theBeer;
END IF;
END LOOP;
CLOSE c; If Joe charges less than $3 for
END; the beer, raise its price at
Joe’s Bar by $1.
30
PL/SQL
Oracle uses a variant of SQL/PSM
which it calls PL/SQL.
PL/SQL not only allows you to create
and store procedures or functions,
but it can be run from the generic
query interface (sqlplus), like any
SQL statement.
Triggers are a part of PL/SQL.
31
Trigger Differences
Compared with SQL standard triggers,
Oracle has the following differences:
1. Action is a PL/SQL statement.
2. New/old tuples referenced automatically.
3. Strong constraints on trigger actions
designed to make certain you can’t fire off
an infinite sequence of triggers.
See on-line [Link] document.
32
SQLPlus
In addition to stored procedures, one
can write a PL/SQL statement that
looks like the body of a procedure,
but is executed once, like any SQL
statement typed to the generic
interface.
Oracle calls the generic interface
“sqlplus.”
PL/SQL is really the “plus.”
33
Form of PL/SQL
Statements
DECLARE
<declarations>
BEGIN
<statements>
END;
.
run
The DECLARE section is optional.
34
Form of PL/SQL Procedure
CREATE OR REPLACE PROCEDURE
<name> (<arguments>) AS Notice AS
needed here
<optional declarations>
BEGIN
<PL/SQL statements>
END;
. Needed to store
procedure in database;
run does not really run it.
35
PL/SQL Declarations and
Assignments
The word DECLARE does not appear
in front of each local declaration.
Just use the variable name and its type.
There is no word SET in assignments,
and := is used in place of =.
Example: x := y;
36
PL/SQL Procedure
Parameters
There are several differences in the
forms of PL/SQL argument or local-
variable declarations, compared
with the SQL/PSM standard:
1. Order is name-mode-type, not mode-
name-type.
2. INOUT is replaced by IN OUT in
PL/SQL.
3. Several new types.
37
PL/SQL Types
In addition to the SQL types,
NUMBER can be used to mean INT or
REAL, as appropriate.
You can refer to the type of attribute
x of relation R by R.x%TYPE.
Useful to avoid type mismatches.
Also, R%ROWTYPE is a tuple whose
components have the types of R’s
attributes. 38
Example:JoeMenu
Recall the procedure JoeMenu(b,p)
that adds beer b at price p to the
beers sold by Joe (in relation Sells).
Here is the PL/SQL version.
39
Procedure JoeMenu in
PL/SQL
CREATE OR REPLACE PROCEDURE JoeMenu (
b IN [Link]%TYPE,
p IN [Link]%TYPE
Notice these types
) AS will be suitable
BEGIN for the intended
uses of b and p.
INSERT INTO Sells
VALUES (’Joe’’s Bar’, b, p);
END;
.
run 40
PL/SQL Branching
Statements
Like IF … in SQL/PSM, but:
Use ELSIF in place of ELSEIF.
Viz.: IF … THEN … ELSIF … THEN …
ELSIF … THEN … ELSE … END IF;
41
PL/SQL Loops
LOOP … END LOOP as in SQL/PSM.
Instead of LEAVE … , PL/SQL uses
EXIT WHEN <condition>
And when the condition is that
cursor c has found no tuple, we
can write c%NOTFOUND as the
condition.
42
PL/SQL Cursors
The form of a PL/SQL cursor
declaration is:
CURSOR <name> IS <query>;
To fetch from cursor c, say:
FETCH c INTO <variable(s)>;
43
Example: JoeGouge() in PL/SQL
Recall JoeGouge() sends a cursor
through the Joe’s-Bar portion of
Sells, and raises by $1 the price of
each beer Joe’s Bar sells, if that
price was initially under $3.
44
Example: JoeGouge()
Declarations
CREATE OR REPLACE PROCEDURE
JoeGouge() AS
theBeer [Link]%TYPE;
thePrice [Link]%TYPE;
CURSOR c IS
SELECT beer, price FROM Sells
WHERE bar = ’Joe’’s Bar’;
45
Example: JoeGouge() Body
BEGIN
OPEN c;
LOOP How PL/SQL
FETCH c INTO theBeer, thePrice; breaks a cursor
EXIT WHEN c%NOTFOUND; loop
IF thePrice < 3.00 THEN
UPDATE Sells SET price = thePrice + 1.00;
WHERE bar = ’Joe’’s Bar’ AND beer = theBeer;
END IF;
END LOOP;
CLOSE c; Note this is a SET clause
in an UPDATE, not an assignment.
END;
PL/SQL uses := for assignments.
46
Tuple-Valued Variables
PL/SQL allows a variable x to have a
tuple type.
x R%ROWTYPE gives x the type of
R’s tuples.
R could be either a relation or a
cursor.
x.a gives the value of the component
for attribute a in the tuple x.
47
Example: Tuple Type
Repeat of JoeGouge() declarations
with variable bp of type beer-price
pairs.
CREATE OR REPLACE PROCEDURE
JoeGouge() AS
CURSOR c IS
SELECT beer, price FROM Sells
WHERE bar = ’Joe’’s Bar’;
bp c%ROWTYPE;
48
JoeGouge() Body Using bp
BEGIN
OPEN c;
LOOP
FETCH c INTO bp;
EXIT WHEN c%NOTFOUND;
IF [Link] < 3.00 THEN
UPDATE Sells SET price = [Link] + 1.00
WHERE bar = ’Joe’’s Bar’ AND beer =[Link];
END IF;
END LOOP;
CLOSE c; Components of bp are
END; obtained with a dot and
the attribute name
49
Embedded SQL
Key idea: A preprocessor turns SQL
statements into procedure calls
that fit with the surrounding host-
language code.
All embedded SQL statements
begin with EXEC SQL, so the
preprocessor can find them easily.
50
Shared Variables
To connect SQL and the host-
language program, the two parts
must share some variables.
Declarations of shared variables are
bracketed by:
EXEC SQL BEGIN DECLARE SECTION;
Always <host-language declarations>
needed
EXEC SQL END DECLARE SECTION;
51
Use of Shared Variables
In SQL, the shared variables must be
preceded by a colon.
They may be used as constants provided
by the host-language program.
They may get values from SQL
statements and pass those values to the
host-language program.
In the host language, shared variables
behave like any other variable.
52
Example: Looking Up
Prices
We’ll use C with embedded SQL to
sketch the important parts of a
function that obtains a beer and a
bar, and looks up the price of that
beer at that bar.
Assumes database has our usual
Sells(bar, beer, price) relation.
53
Example: C Plus SQL
EXEC SQL BEGIN DECLARE SECTION;
Note 21-char
char theBar[21], theBeer[21]; arrays needed
float thePrice; for 20 chars +
endmarker
EXEC SQL END DECLARE SECTION;
/* obtain values for theBar and theBeer */
EXEC SQL SELECT price INTO :thePrice
FROM Sells
WHERE bar = :theBar AND beer
= :theBeer;
/* do something with thePrice */ SELECT-INTO
as in PSM 54
Embedded Queries
Embedded SQL has the same
limitations as PSM regarding
queries:
SELECT-INTO for a query guaranteed
to produce a single tuple.
Otherwise, you have to use a cursor.
• Small syntactic differences, but the key
ideas are the same.
55
Cursor Statements
Declare a cursor c with:
EXEC SQL DECLARE c CURSOR FOR
<query>;
Open and close cursor c with:
EXEC SQL OPEN CURSOR c;
EXEC SQL CLOSE CURSOR c;
Fetch from c by:
EXEC SQL FETCH c INTO <variable(s)>;
Macro NOT FOUND is true if and only if the
FETCH fails to find a tuple. 56
Example: Print Joe’s Menu
Let’s write C + SQL to print Joe’s
menu – the list of beer-price pairs
that we find in Sells(bar, beer,
price) with bar = Joe’s Bar.
A cursor will visit each Sells tuple
that has bar = Joe’s Bar.
57
Example: Declarations
EXEC SQL BEGIN DECLARE SECTION;
char theBeer[21]; float thePrice;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE c CURSOR FOR
SELECT beer, price FROM Sells
WHERE bar = ’Joe’’s Bar’;
The cursor declaration goes
outside the declare-section
58
Example: Executable Part
EXEC SQL OPEN CURSOR c;
The C style
while(1) { of breaking
loops
EXEC SQL FETCH c
INTO :theBeer, :thePrice;
if (NOT FOUND) break;
/* format and print theBeer and thePrice
*/
}
EXEC SQL CLOSE CURSOR c; 59
Need for Dynamic SQL
Most applications use specific queries
and modification statements to
interact with the database.
The DBMS compiles EXEC SQL …
statements into specific procedure calls
and produces an ordinary host-language
program that uses a library.
What about sqlplus, which doesn’t
know what it needs to do until it
runs? 60
Dynamic SQL
Preparing a query:
EXEC SQL PREPARE <query-name>
FROM <text of the query>;
Executing a query:
EXEC SQL EXECUTE <query-name>;
“Prepare” = optimize query.
Prepare once, execute many times.
61
Example: A Generic
Interface
EXEC SQL BEGIN DECLARE SECTION;
char query[MAX_LENGTH];
EXEC SQL END DECLARE SECTION;
while(1) {
/* issue SQL> prompt */
/* read user’s query into array query */
EXEC SQL PREPARE q FROM :query;
EXEC SQL EXECUTE q; q is an SQL variable
representing the optimized
} form of whatever statement
is typed into :query 62
Execute-Immediate
If we are only going to execute the
query once, we can combine the
PREPARE and EXECUTE steps into
one.
Use:
EXEC SQL EXECUTE IMMEDIATE
<text>;
63
Example: Generic Interface
Again
EXEC SQL BEGIN DECLARE SECTION;
char query[MAX_LENGTH];
EXEC SQL END DECLARE SECTION;
while(1) {
/* issue SQL> prompt */
/* read user’s query into array query */
EXEC SQL EXECUTE IMMEDIATE :query;
}
64