SQL is packaged with basic programming language control structures.
Procedures written in this language are stored by the DBMS, can be called by an application program (or DBMS command line shell: mysql or sqlplus)
|
DBMS |
SQL-programming language name |
|
Oracle |
PL/SQL |
|
SQL Server 2005 |
TRANSACT-SQL |
|
MYSQL |
SQL:2003 syntax for stored routines |
|
IBM DB2 |
SQL:2003 syntax for stored routines |
A stored procedure is a series of sql statements that can be called -- like calling a method in java.
Rationale: stored procedures act as an interface between the database and an application program that uses the database.
Stored procedures are blocks of code stored in the database. You must ‘create’ them, much like you do tables.
Much like a method header, a stored procedure header names the procedure and declares a list of typed paraemeters.
A series of statements between keywords ‘begin’ and ‘end’
Create procedure that takes a Student ID and set of 4 courses. Any courses having a value of 0 should not be added.
drop procedure if exists insertSchedule;
delimiter //
CREATE PROCEDURE insertSchedule(ID INT, C1 INT, C2 INT, C3 INT, C4 INT)
-- insert up to 4 courses for the Student ID given
-- non-zero Course IDs are inserted into the Schedule table
BEGIN
-- assume ID is good for now
-- not pretty, but it works
IF C1 != 0 THEN INSERT INTO Schedule VALUES (ID, C1); END IF;
IF C2 != 0 THEN INSERT INTO Schedule VALUES (ID, C2); END IF;
IF C3 != 0 THEN INSERT INTO Schedule VALUES (ID, C3); END IF;
IF C4 != 0 THEN INSERT INTO Schedule VALUES (ID, C4); END IF;
END
//
The delimiter statement tells the mysql command line it should consider all the input between the delimiter statement and the closing // as one logical unit.
Stored procedures can be called with a CALL statement. The procedure above would be called by executing:
CALL insertSchedule(1,5,10,12,0);
OUT – can pass the name of a variable to a stored procedure and if the procedure assigns a value to this variable, can read the value the procedure wrote from the call site – like you can with a function’s return value
INOUT – can pass data both into and out of the procedure
IN (default) – what we are used to. Any changes made inside the procedure are not visible at the call site after the procedure finishes.
Two methodologies:
Inside a stored procedure, you can return data using keyword ‘select’. This is a good methodology for returning a result set.
Script to create tables for Student, Course, Schedule.
-- delete Student 1 (and cascaded Course records)
delete from Student where id = 1;
-- insert Student record for test case
insert Student values (1, 'jimbob');
A small modification to the stored procedure above:
drop procedure if exists insertSchedule;
delimiter //
CREATE PROCEDURE insertSchedule(ID INT, C1 INT, C2 INT, C3 INT, C4 INT)
-- insert up to 4 courses for the Student ID given
-- non-zero Course IDs are inserted into the Schedule table
BEGIN
-- assume ID is good for now
-- not pretty, but it works
IF C1 != 0 THEN INSERT INTO Schedule VALUES (ID, C1); END IF;
IF C2 != 0 THEN INSERT INTO Schedule VALUES (ID, C2); END IF;
IF C3 != 0 THEN INSERT INTO Schedule VALUES (ID, C3); END IF;
IF C4 != 0 THEN INSERT INTO Schedule VALUES (ID, C4); END IF;
-- 'return' set of courses for which Student is registered
select name
from Course
where crn in (
select crn
from Schedule
where id=ID);
END
//
CALL insertSchedule(1,1,0,3,2);
+-------+
| name |
+-------+
| cs257 |
| cs360 |
| cs310 |
+-------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
select <value> into <parameterName> …
Parameters have types, which, as far as I know, cannot be a result set.
We can use these to test values returned from stored procedures.
Session variables last for an entire mysql session (one execution of a SQL file in mysql workbench or from start to exit of the mysql on the command line.
Declared and assigned using:
select <value> into @<variable name>;
or
set @<variable name> = <value>;
mysql> set @r = 'here it is';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r;
+------------+
| @r |
+------------+
| here it is |
+------------+
1 row in set (0.00 sec)
Again a slight modification to return the number of courses for which ID is registered.
-- delete Student 1 (and cascaded Course records)
delete from Student where id = 1;
-- insert Student record for test case
insert Student values (1, 'jimbob');
drop procedure if exists insertSchedule;
delimiter //
CREATE PROCEDURE insertSchedule(ID INT,
C1 INT, C2 INT, C3 INT, C4 INT,
OUT numCourses int)
-- insert up to 4 courses for the Student ID given
-- non-zero Course IDs are inserted into the Schedule table
BEGIN
-- assume ID is good for now
-- not pretty, but it works
IF C1 != 0 THEN INSERT INTO Schedule VALUES (ID, C1); END IF;
IF C2 != 0 THEN INSERT INTO Schedule VALUES (ID, C2); END IF;
IF C3 != 0 THEN INSERT INTO Schedule VALUES (ID, C3); END IF;
IF C4 != 0 THEN INSERT INTO Schedule VALUES (ID, C4); END IF;
-- 'return' set of courses for which Student is registered
select count(*) into numCourses
from Schedule
where id=ID;
END
//
delimiter ;
-- declare session variable to hold result
set @r = 0;
CALL insertSchedule(1,1,0,3,2, @r);
select @r;