70.907
Stored Procedures in MySQL

---

Objectives

Upon completion of this lesson, you will be able to:

• define a stored procedure in MySQL
• invoke a stored procedure from within an R program
• remove a stored procedure

---

Introduction

Stored procedures are in important database application development mechanism in which complex data computations are defined and executed within the database – and on the database server rather than the client. This allows large and complex data manipulations to be done by a more powerful server and thus enables the use of less powerful (and cheaper) clients. The drawback is, of course, that the database may become a bottleneck. Nevertheless, server-based computations within stored procedures are an important architectural design mechanism and result in two-tier client/server applications.

Aside from leveraging a shared computational resource, stored procedure have other benefits. For one, they centralize business logic that can be executed from clients written in different languages. Updates to the business logic can be made once in the centrally located code within the database. An additional benefit is that large amounts of data do not have to be transmitted over a network – which can be slow and also a potential security risk.Furthermore, data is not stored on potentially unsecured clients where it could be saved or otherwise compromised. So, stored procedures are an important aspect in building secure applications. Stored procedures are also very fast as the database often caches results and data is local within the database server. From a development perspective, the source code for the stored procedure is stored as an object within the database and can be easily inspected (of course, inspection can also be prohibited through security settings via the REVOKE SQL statement).

This lesson focuses on the syntax for defining stored procedures for MySQL. While many databases support stored procedures, each has their own programming language syntax and restrictions. The use of stored procedures thus results in applications that are vendor-dependent and not easily portable, although many vendors do conform to the SQL:203 standard which standardizes some aspects of stored procedures and triggers.

To follow along, go to Files & Resources and download the R Notebook and copy (or better yet, type) the R and SQL code chunks into an R Notebook of your own – and experiment along the way.

Sample Database

To demonstrate the process for creating, editing, invoking, and deleting stored procedures, we will define the following database on an existing MySQL database hosted on db4free.net. To run this code for yourself, create and connect to your own database and change the setting accordingly.

Connect to MySQL

Of course, be sure to disconnect from the database once you are done.

Sample Tables

The code below creates two sample tables: Faculty and Committee.

CREATE TABLE IF NOT EXISTS Faculty (
  fid INTEGER NOT NULL PRIMARY KEY,
  fname TEXT NOT NULL,
  lname TEXT NOT NULL,
  facrank TEXT
);

We will now create the second table and link that table with a foreign key to the Faculty table.

CREATE TABLE IF NOT EXISTS Committee (
  name VARCHAR(32) NOT NULL PRIMARY KEY,
  level TEXT,
  chair INTEGER NOT NULL,
  FOREIGN KEY (chair) REFERENCES Faculty(fid)
)

Sample Data

Next, we will add some sample data to the database.

INSERT INTO Faculty (fid,fname,lname,facrank) VALUES 
  (100,"Jose","Annunziato","Assistant"),
  (200,"Dan","Feinberg","Adjunct"),
  (300,"Martin","Schedlbauer","Full"),
  (400,"Kathleen","Durant","Associate");

INSERT INTO Committee VALUES 
  ("Hiring","College",300),
  ("Merit","College", 300),
  ("Teaching","Department",400)

Defining Stored Procedures

Stored procedures must be created (defined) before they can be called. They are a function written in a vendor-specific programming language. Stored procedures should be defined as part of the database configuration code which is often in a separate configuration program or a script, alongside the SQL code to create tables, indexes, and triggers. Ad-hoc stored procedures can also be created using interactive means, such as via the MySQL Workbench.

To create a stored procedure from within an R Notebook, define either a {sql} chunk and place the code for the stored procedure within that chunk (and, remember, one procedure definition per chunk) or call the function dbSendStatement(). Of course, the code in a {sql} chunk is eventually translated to a call to dbSendStatement() during knitting, so it makes no difference which is used. If you write an R program (script), then you must use dbSendStatement().

If a stored procedure already exists, you will get an error if you try to define it again. So, drop the procedure first if it exists.

Before being able to create a stored procedure we must select a database if it wasn’t selected as part of the connection. To see the list of databases available on the server use SHOW DATABASES from either MySQL Workbench, the MySQL interactive console, or from within a {sql} chunk.

SHOW DATABASES;

To connect to a specific database, use the USE command.

USE cs5200db1;

Define with {sql} Chunk

In R, the simplest and quickest way to define a stored is not use a {sql} chunk. Be sure to only create the stored procedure if it does not already exist or drop it first before redefining it.

CREATE PROCEDURE getDepartmentCommittees ()
BEGIN
  SELECT * 
    FROM Committee
   WHERE level = 'Department';
END

CALL getDepartmentCommittees ();

Table 1: 1 records

name	level	chair
Teaching	Department	400

[1] TRUE

Define with R Function

Alternatively to defining a stored procedure within a {sql} chunk, we can also define it using the R functions dbExecute() or dbSendStatement(). Of course, defining a function in a {sql} chunk will be simply be translated to an R function call anyway during the knitting of a markdown document.

Naturally, R functions are required when building R programs (scripts) where code chunks are not possible as they only work within markdown documents (such as R Notebooks).

Note the use of the function paste0() to build the SQL across multiple lines and with indentation.

sql <- paste0("CREATE PROCEDURE getDepartmentCommittees ()",
              " BEGIN",
              "   SELECT * ",
              "     FROM Committee",
              "    WHERE level = 'Department';",
              " END")

rs <- dbExecute(dbcon, sql)

Security Restrictions

CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. They might also require the SUPER privilege, depending on the DEFINER value. By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges system variable.

DELIMITER

When defining multiple stored procedures or when a stored procedure definition is included within a SQL script, then the default SQL statement delimiter of semi-colon (;) has to be changed to some other character so that the delimiter ; can be used within stored procedures. However, within an R {sql} one can only define a single SQL statement, so no statement terminator is needed and the delimiter does not have to be changed. Also, including DELIMITER within a {sql} chunk will not work as a {sql} chunk can only contain a single SQL statement.

So, changing the delimiter with DELIMITER is only needed if you use SQL scripts to configure the database; they are not used within host programming languages such as R or Java.

Executing a Stored Procedure

Unlike a trigger, a stored procedure must be explicitly invoked by a client program. We have already seen that a stored procedure can be called from within a {sql} chunk. We will now look at how to call a procedure from an R function and how to overcome the problem of error message when calling another stored procedure before disconnecting from the database.

Calling a procedure actually returns two results set of which only the first is useful but the second can be ignored. Here is a solution to fetching all result sets and only using the first one:

sql = "CALL getDepartmentCommittees ()"

rs = dbSendQuery(dbcon, sql)
data = fetch(rs,n=-1)
# data now has ALL the rows from the first result set

# loop through any additional result sets and discard
while(dbMoreResults(dbcon) == TRUE) {
  dbNextResult(dbcon)
}

# the actual data is in the object 'data'
print(data)

##       name      level chair
## 1 Teaching Department   400

The above code should be used as the template for calling stored procedures from within R.

The call to dbMoreResults(dbcon) returns TRUE while there are more result set. Since we only need the first of the result sets, we can ignore the others. By fetching any additional results sets from the database connection, we will avoid any warnings from R that additional result sets are pending.

Return Values

The return value of a call to a stored procedure is always a table, so it is always a data frame in R.

Parameters

A stored procedure can take parameters as input arguments. The example below shows an argument and its use within the stored procedure.

CREATE PROCEDURE getCommittees (IN kind TEXT)
BEGIN
  SELECT * 
    FROM Committee
   WHERE level = kind;
END

An IN parameter is an argument that is passed by value and is not modified within the procedure. It is a call-by-value.

MySQL also recognizes INOUT and OUT parameters. An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns. It is similar to a call-by-reference in programming. An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns. It is another way to pass a value back from the procedure and is also an example of a call-by-value. Its programming value is somewhat dubious.

Debugging Stored Procedures

Stored procedure code runs on the database server and thus you cannot step through procedure code with a local debugger. They are best developed in small steps and through MySQL Workbench.

Removing a Stored Procedure

To remove a stored procedure use DROP PROCEDURE. You can optionally add the IF EXISTS or IF NOT EXISTS conditions. Dropping a procedure that does not exist results in an error.

Alternatively to dropping a procedure to rebuild it, one can also use an ALTER PROCEDURE statement to modify a procedure.

Developing Stored Procedures

This section takes a closer look at the programming language constructs available to build stored procedures in MySQL. It is a procedural programming language supporting most common programming constructs such as local variables, if and case statement, and loops.

Definition Syntax

The complete syntax for defining stored procedures is below:

CREATE [DEFINER = { user | CURRENT_USER }]          
PROCEDURE sp_name ([proc_parameter[,...]])          
[characteristic ...] routine_body    
proc_parameter: [ IN | OUT | INOUT ] param_name type    
type:          
Any valid MySQL data type    
characteristic:          
COMMENT 'string'     
| LANGUAGE SQL      
| [NOT] DETERMINISTIC      
| { CONTAINS SQL | NO SQL | READS SQL DATA 
| MODIFIES SQL DATA }      
| SQL SECURITY { DEFINER | INVOKER }    
routine_body:      
Valid SQL routine statement

Programming Constructs

See MySQL Stored Procedures.

Conclusion

Stored procedures are an important database application mechanism. They allow shared business logic to be stored and executed in the database which can result in faster, safer, and more maintainable applications. However, they come with the drawback of making the database server a potential bottleneck.

---

Files & Resources

All Files for Lesson 70.907

---

See Also

TBD

References

• MySQL Stored Procedures
• MySQL Stored Procedures

Errata

Let us know.