15 October 2007
Most ColdFusion applications center around a database. The key to creating efficient and secure ColdFusion applications is in the database integration. Stored procedures help developers accomplish both of these goals and also provide greater abstraction between the application code and database details. In this article, we’ll explore stored procedures and review a few examples using Oracle, Microsoft SQL Server (MSSQL), and MySQL.
A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form. Procedures can be shared between a number of programs, and provide improved performance as well as increased security.
Stored procedures can contain not only simple select, insert, update, and delete statements, but also can encapsulate procedural code. Since you compile and encapsulate stored procedures, they provide more efficient and secure data access.
Whenever you run a query statement against a database, the database performs two functions. First, it compiles the statement and, Second, it executes it. During compilation the database engine breaks the query apart, identifies the tables and relationships, determines which indexes to use and when to combine and filter records. For example, see the execution plan in Figure 1.
This execution plan shows a simple query that combines data from five tables using outer joins. With a standard query from a ColdFusion
cfquery tag, the database engine recompiles the statement each time it executes the query. With a stored procedure, however, the database compiles the query plan once and reuses the compiled process. This pre-compilation can yield a significant performance advantage.
Stored procedures also help developers encapsulate database code. By placing all database access and manipulation code inside stored procedures, you create lots of little black boxes. Instead of a team of ColdFusion developers having to be familiar with the database schema, indexes, and columns, they simply need to know the names of stored procedures, which an experienced DBA can write and optimize. For example, a ColdFusion page might have a poorly written cross-tab query, as shown in Listing 1.
Listing 1. A query that performs a cross-tab style query against an Oracle database through ColdFusion.
<CFQUERY NAME="getCrossTabData" DATASOURCE="ORATEST"> SELECT DV1.State_ID, DV1.Data_Year, DV1.Data_Value AS Val1, DV2.Data_Value AS Val2 FROM Data_Values DV1, Data_Values DV2 WHERE DV1.State_ID = DV2.State_ID AND DV1.Data_Year = DV2.Data_Year AND DV1.Data_Year BETWEEN #yearStart# AND #yearEnd# AND DV1.Field_ID = 1000 AND DV2.Field_ID = 1001 ORDER BY DV1.State_ID, DV1.Data_Year </CFQUERY>
If you instead use a stored procedure to perform the same select, it simplifies the ColdFusion code, as shown in Listing 2.
Listing 2. Using a
cfstoredproc tag to perform the same cross-tab style query shown in Listing 1.
<CFSTOREDPROC PROCEDURE="pkg_Simple.Get_Values" DATASOURCE="oratst"> <CFPROCPARAM VALUE="#yearStart#" CFSQLTYPE="cf_sql_numeric"> <CFPROCPARAM VALUE="#yearEnd#" CFSQLTYPE="cf_sql_numeric"> <CFPROCRESULT NAME="getCrossTabData"> </CFSTOREDPROC>
Encapsulating the database interaction provides a number of benefits:
DECODE, to generate the cross-tab data.
The section below reviews several sample stored procedures in Oracle, MSSQL, and MySQL. To follow along, download the table creation scripts from the Requirements section at the beginning of this article. These scripts create the tables and columns, and insert data.
Refer to the documentation for your database to learn how to execute the scripts. Typically, Oracle, use SQL Plus. In SQL Server, use the SQL Management Studio, and in MySQL use MySQL Command Line Client. Note that MySQL version 5 is required for stored procedure support.
At this time, also create your data sources in ColdFusion administrator. Name your Oracle data source oratst, SQL Server data source sqltst, and MySQL data source mytst.
In the first example, you'll learn to create a stored procedure that inserts new records into the Books table shown in Figure 2.
The table has four columns; three information fields, and a BookID (its primary key). The stored procedure will accept three input parameters and return one output parameter, the newly inserted BookID. See the stored procedures for both Oracle and MSSQL below in listings 3 and 4. Typically, you execute your stored procedures in SQL Plus in Oracle and Query Analyzer in SQL Server. For more information on executing queries and stored procedures, refer to the documentation for your specific database.
Listing 3. Stored procedure to insert a record into the Books table in Oracle.
CREATE OR REPLACE PROCEDURE Insert_Book ( arg_Title Books.Title%type, arg_Price Books.Price%type, arg_PublishDate Books.PublishDate%type, arg_BookID OUT Books.BookID%type) AS num_BookID NUMBER; BEGIN SELECT seq_Books.NEXTVAL INTO num_BookID FROM DUAL; INSERT INTO Books ( BookID, Title, Price, PublishDate ) VALUES ( num_BookID, arg_Title, arg_Price, arg_PublishDate ); arg_BookID := num_BookID; END; /
Listing 4. Stored procedure to insert a record into the Books table in MSSQL.
CREATE PROCEDURE Insert_Book ( @arg_Title VARCHAR(255), @arg_Price SMALLMONEY, @arg_PublishDate DATETIME, @arg_BookID INT OUT) AS BEGIN INSERT INTO Books ( Title, Price, PublishDate ) VALUES ( @arg_Title, @arg_Price, @arg_PublishDate ); SELECT @arg_BookID = @@IDENTITY; END;
Listing 5. Stored procedure to insert a record into the Books table in MySQL.
DELIMITER // CREATE PROCEDURE Insert_Book ( arg_Title VARCHAR(255), arg_Price DECIMAL(6,2), arg_PublishDate DATETIME, OUT arg_BookID INT) BEGIN INSERT INTO Books ( Title, Price, PublishDate ) SELECT arg_Title, arg_Price, arg_PublishDate; SET arg_BookID = LAST_INSERT_ID(); END; // DELIMITER ;
All three stored procedures perform the same function but require slightly different syntax. Oracle has a beneficial feature that allows you to specify data types for procedures so they match a field type using the following syntax:
[Table Name].[Field Name]%type. MSSQL and MySQL use a simpler mechanism for inserting incremental values, which means the same procedure requires half the code for this simple routine.
While the stored procedure syntax is different, one big benefit of stored procedures is that the ColdFusion code that calls a stored procedure is exactly the same regardless of DBMS. Listing 5 shows a ColdFusion page that inserts books into the database using the stored procedure and confirm that the insert succeeded.
Listing 6. ColdFusion code inserts a book into a database and confirms the insert with the user. You can copy and paste this code into your own CFML page to see the results. Comment out the data source you are not using.
<cfset ds = "sqltst"> <!--- <cfset ds = "oratst"> ---> <!--- <cfset ds = "mytst"> ---> <!--- If submitting a new book, insert the record and display confirmation ---> <cfif isDefined("form.title")> <cfstoredproc procedure="Insert_Book" datasource="#ds#"> <cfprocparam cfsqltype="cf_sql_varchar" value="#form.title#"> <cfprocparam cfsqltype="cf_sql_numeric" value="#form.price#"> <cfprocparam cfsqltype="cf_sql_date" value="#form.price#"> <cfprocparam cfsqltype="cf_sql_numeric" type="out" variable="bookId"> </cfstoredproc> <cfoutput> <h3>'#form.title#' inserted into database. The ID is #bookId#.</h3> </cfoutput> </cfif> <cfform action="#CGI.SCRIPT_NAME#" method="post"> <h3>Insert a new book</h3> Title: <cfinput type="text" size="20" required="yes" name="title"/> <br/> Price: <cfinput type="text" size="20" required="yes" name="price" validate="float" /> <br/> Publish Date: <cfinput type="text" size="5" required="yes" name="publishDate" validate="date" /> <br/> <input type="submit" value="Insert Book"/> </cfform>
To call a stored procedure, you use two ColdFusion tags:
cfstoredproc tag starts the call to the database and wraps the related tags. At a minimum, the tag requires the
datasource attributes, which specify the stored procedure you wish to call and the data source to use for the call.
cfprocparam tag to both pass parameters to a stored procedure and to specify variable names for output parameters. The
cfsqltype attribute is always required and specifies the data type for the parameter. To input parameters, include the
value attribute with the passed value. Alternatively, if you wanted to pass a null value instead, you could include the
For the output parameter, you again use the
cfprocparam tag, specifying the
type attribute value as out, and provide a ColdFusion variable name in the
variable attribute. While in this page, you only display the Book ID to the user, but you could use the ID to also add authors and publishers to the book record with additional programming.
In listings 3, 4, and 5, you created stored procedures that accepted arguments and returned simple values. Stored procedures can also return database records. The procedures in listings 7, 8, and 9 return book details based on a title search.
Listing 7. Stored procedure that searches for books in an Oracle data source.
CREATE OR REPLACE PACKAGE pkg_Search AS TYPE CUSTOM_REF_CURSOR IS REF CURSOR; PROCEDURE By_Title ( arg_Title Books.Title%type, arg_Cursor IN OUT CUSTOM_REF_CURSOR); END; / CREATE OR REPLACE PACKAGE BODY pkg_Search AS PROCEDURE By_Title ( arg_Title Books.Title%type, arg_Cursor IN OUT CUSTOM_REF_CURSOR) IS BEGIN OPEN arg_Cursor FOR SELECT BookID, Title, Price, PublishDate FROM Books WHERE Title LIKE '%' || arg_Title || '%'; END; END; /
Listing 8. Stored procedure that searches for books in a MSSQL data source.
CREATE PROCEDURE Search_By_Title ( @arg_Title VARCHAR(255)) as BEGIN SELECT BookID, Title, Price, PublishDate FROM Books WHERE Title LIKE '%' + @arg_Title + '%'; END;
Listing 9. Stored procedure that searches for books in a MySQL data source.
DELIMITER // CREATE PROCEDURE Search_By_Title ( arg_Title VARCHAR(255)) BEGIN SELECT BookID, Title, Price, PublishDate FROM Books WHERE Title LIKE CONCAT('%', arg_Title, '%'); END; // DELIMITER ;
Notice first in this example that the Oracle example is long for the amount of work it does. Oracle returns records to the calling application through a reference cursor. However, you cannot directly use a reference cursor (
REF CURSOR) in a stored procedure; instead you must declare a custom type within a package—which is a collection of stored procedures and functions, similar to a ColdFusion component (CFC).
All Oracle packages have two parts, the package header and the package body. The header includes type declarations and stored procedure headers. The stored procedure details are then placed in the package body using the syntax shown in listing 7, above.
Again, you can use similar code to call both stored procedures, as shown in listing 10. The only difference in the call for Oracle and the other two database engines is the stored procedure name, since the Oracle procedure is a part of a package and neither MSSQL nor MySQL require packages for this task.
Listing 10. ColdFusion code to search for books using the stored procedures in listings 7, 8, and 9.
<cfset ds = "sqltst"> <cfset sp = "Search_By_Title"> <!--- <cfset ds = "mytst"> <cfset ds = "oratst"> <cfset sp = "pkg_Search.By_Title"> ---> <cfoutput> <form action="#cgi.SCRIPT_NAME#" method="post"> <input type="text" name="criteria" size="20" /> <input type="submit" value="Search"/> </form> </cfoutput> <cfif isDefined("form.criteria") AND form.criteria NEQ ""> <cfstoredproc procedure="#sp#" datasource="#ds#"> <cfprocparam cfsqltype="cf_sql_varchar" value="#form.criteria#"> <cfprocresult name="searchResults"> </cfstoredproc> <cfoutput> <h3>Search results for '#form.criteria#'</h3> </cfoutput> <cfdump var="#searchResults#" label="Search Results"> </cfif>
Listing 8 provides a small form for users to enter search criteria; the form then passes the search criteria to the database. The listing uses
cfdump to print the results to the screen.
The stored procedure call uses one new tag,
cfprocresult, to provide a ColdFusion variable name for the recordset returned from the stored procedure. Since a stored procedure may return multiple recordsets, ColdFusion allows multiple
cfprocresult tags within a single
Stored procedures provide a number of advantages over storing queries in ColdFusion pages. Pre-compiling SQL helps your application execute pages faster, and encapsulating SQL within a database allows you to specialize database development and increase security. Using stored procedures is simple in ColdFusion and you should consider using them for all database-centric applications.