1
SQL: Programming
CPS 116
Introduction to Database Systems
2
Announcements
Homework #1 graded
Pick up graded homework from my office
Verify your grades on Blackboard
Discussion session 4-4:40pm this Friday (Sep. 23)
in D106
Homework #2 due in 5 days
Project milestone #1 due in 7 days
3
Motivation
Pros and cons of SQL
Very high-level, possible to optimize
Not intended for general-purpose computation
Solutions
Augment SQL with constructs from general-purpose
programming languages (SQL/PSM)
Use SQL together with general-purpose programming
languages (JDBC, embedded SQL, etc.)
4
Impedance mismatch and a solution
SQL operates on a set of records at a time
Typical low-level general-purpose programming languages
operates on one record at a time
)Solution: cursor
Open (a table or a result table): position the cursor just before the
first row
Get next: move the cursor to the next row and return th at row;
raise a flag if there is no such row
Close: clean up and release DBMS resources
)Found in virtually every database language/API (with slightly
different syntaxes)
)Some support more cursor positioning and movement options,
modification at the current cursor position (analogous to the view
update problem), etc.
5
Augmenting SQL: SQL/PSM
PSM = Persistent Stored Modules
CREATE PROCEDURE proc_name ( parameter_declarations )
local_declarations
procedure_body;
CREATE FUNCTION func_name ( parameter_declarations )
RETURNS return_type
local_declarations
procedure_body;
CALL proc_name ( parameters );
Inside procedure body:
SET variable = CALL func_name ( parameters );
6
SQL/PSM example
CREATE FUNCTION SetMaxGPA(IN newMaxGPA FLOAT )
RETURNS INT
-- Enforce newMaxGPA; return number of rows modified.
BEGIN
DECLARE rowsUpdated INT DEFAULT 0;
DECLARE thisGPA FLOAT;
-- A cursor to range over all students:
DECLARE studentCursor CUSOR FOR
SELECT GPA FROM Student
FOR UPDATE;
-- Set a flag whenever there is a “not found” exception:
DECLARE noMoreRows INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET noMoreRows = 1;
… (see next slide) …
RETURN rowsUpdated;
END
