About Stored Procedure

A stored procedure is a group of SQL statements compiled into an execution plan and stored under a unique name in the database. It is then executed as a unit. Stored procedures in SQL Server and Oracle are similar to queries in Access. However, stored procedures are much more flexible and powerful than queries, as you'll discover in later chapters when you start implementing more complex stored procedures.

Each database vendor provides tools that enable you to work with the objects within their databases and to view and create stored procedures. For example, Microsoft provides the Query Analyzer for SQL Server, whereas Oracle provides SQL Plus and SQLPlus Worksheet for Oracle.

However, Visual Studio .NET also provides tools for viewing and creating stored procedures in SQL Server and Oracle. Although these tools are not as robust as the database vendors' tools, they do the job and enable you to develop database applications without having to leave the development environment.

Before you begin using Visual Studio .NET to develop stored procedures, you'll need to take a look at the basic syntax for creating stored procedures in SQL Server and Oracle. The following example is a basic stored procedure that will insert group data into the Groups table in SQL Server:

CREATE PROCEDURE usp_InsertGroup
(
@GroupID           UNIQUEIDENTIFIER,
@GroupName         VARCHAR(50),
@GroupDescription  TEXT
)
AS

INSERT INTO Groups
(GroupID, GroupName, GroupDescription, LastUpdateDate)
VALUES(@GroupID, @GroupName, @GroupDescription, GETDATE())

The INSERT statement for this stored procedure is similar to the INSERT statement that you created for your usp_InsertGroup query in Chapter 7. The only difference is instead of using the built-in Access function to insert a date and time into the LastUpdateDate column, you are using the built-in SQL Server function GETDATE().

When you built your Access query, you didn't need to specify the input parameters to the query or their data types. That is inferred by Access, and Access will prompt you for them when running the query within Access. When you ran your query from your VB.NET program, you knew what parameters to specify and the order in which to specify them based on their use in your INSERT statement. You also knew their data types as specified in the column definition when you created the tables. This is not the case with stored procedures.

You create a stored procedure with a CREATE PROCEDURE statement and then specify the input and/or output parameters to the stored procedure. You also specify the parameter data types as you want them submitted to your stored procedure. A case in point is the example stored procedure listed previously.

Another important point regarding input parameters and variables in general when dealing with SQL Server stored procedures is that the parameters and variables must begin with an at (@) sign as shown in the previous example. This identifies the parameters and variables as variables local to the stored procedure, unlike SQL Server's global variables, which begin with two at signs and are global to all stored procedures.

Let's examine the syntax of the previous example that creates a stored procedure for SQL Server. You must specify the statement CREATE PROCEDURE to create a new stored procedure in SQL Server. This is followed by the stored procedure name, which can be up to 128 characters in length. You then specify the parameters for the stored procedure and their data types. Enclosing the parameters in a set of parentheses is totally optional in SQL Server but will be done here to be consistent with Oracle, which does require them.

Each parameter begins with an at sign (@) followed by the name of the parameter. Each parameter has the data type of the parameter specified, and if the data type allows a varying number of characters or digits, that must also be specified. For example, the @GroupName parameter in the previous example specifies a data type of VARCHAR. Because this data type allows a varying number of characters, you must also specify the maximum number of characters that will be allowed in the parameter.

After you have specified the CREATE PROCEDURE statement, the stored procedure name, and the parameters, you specify the AS keyword followed by the body of the stored procedure. The body of the stored procedure will be made up of one or more sets of SQL statements, such as the INSERT statement. The VALUES clause of the INSERT statement shown in the example contains the parameters as the source of input for the INSERT statement.

Now that you've had a look at how to create stored procedures in SQL Server, turn your attention to how a stored procedure is created in Oracle. An example of the usp_InsertGroup stored procedure for Oracle is shown here for reference:

CREATE OR REPLACE PROCEDURE usp_InsertGroup
(
inGroupID            CHAR,
inGroupName          VARCHAR2,
inGroupDescription   CLOB
)
AS
BEGIN
INSERT INTO Groups
(GroupID, GroupName, GroupDescription, LastUpdateDate)
VALUES(inGroupID, inGroupName, inGroupDescription, SYSDATE);
END;

In Oracle, you specify the CREATE OR REPLACE PROCEDURE statement to create a stored procedure, followed by the stored procedure name. You should note that a stored procedure name in Oracle is limited to 30 characters.

Next, you must specify the stored procedure parameters inside a set of parentheses. Each parameter is given a name, followed by the data type of the parameter. Note that an at sign (@) is an illegal character in Oracle so the parameters in the example above have been prefixed with the word in to indicate that these are input parameters. Also note that you do not specify the size of the data type for data types that allow a varying number of characters.

After you have specified the parameters for the stored procedure, you specify the AS keyword and then specify the BEGIN clause. This is followed by the body of the stored procedure, and the BEGIN clause is terminated with a matching END clause and a semicolon.

The body of the stored procedure in this example contains the INSERT statement, which you have become very familiar with. The VALUES clause for this INSERT statement contains the parameters for the stored procedure as the source of input for the INSERT statement. The complete INSERT statement is terminated with a semicolon. It is important to note that every SQL statement in Oracle must be terminated with a semicolon character.

As you can see from the two previous examples, the stored procedure in SQL Server is not all that different from the stored procedure in Oracle. The INSERT statement is the same and uses the parameters for the stored procedure as the source of input for the VALUES clause. There are very minor differences in the syntax for creating a stored procedure in SQL Server and Oracle, as you saw in the previous examples.

Oracle packages
The Oracle stored procedure that you examined previously works well as a stored procedure that inserts data into a table. In fact, stored procedures that insert, update, and delete data in Oracle work in the same manner as they do for SQL Server. You call the stored procedure from your code and pass it the required parameters and the data is inserted, updated, or deleted.

However, the major difference between SQL Server and Oracle becomes apparent when you are using a stored procedure to return data. This is a stored procedure that executes a SQL SELECT statement and returns the results of the SELECT statement back to the caller. Stored procedures that execute SELECT statements in SQL Server behave just like the queries you wrote in Access that execute SELECT statements. However, to have a stored procedure in Oracle return data from a SELECT statement, you must encapsulate the stored procedure in a package.

A package in Oracle can be thought of in the same terms as a class in VB.NET. A package, like a class in VB.NET, can contain variables, functions, and procedures. In fact, your package can even contain overloaded stored procedures. That is, multiple stored procedures are defined with the same name but each stored procedure accepts a different number of parameters.

However, for our discussion here, you want to examine a simple package that contains a single variable declaration and a single stored procedure. The package itself contains the definition for the stored procedure along with its parameters. However, the stored procedure is created in what is known as the package body. Every package contains the definition of the stored procedures within it and the package body contains the actual stored procedures.

Take a look at an example of a package that will perform the same function as your usp_SelectGroups query in Access. The code that follows creates a package that defines a variable named CURSOR_TYPE as a REF CURSOR and a stored procedure definition named usp_SelectGroups. Then the package body is created, and within the package body, the actual stored procedure is created.

A REF CURSOR is a cursor variable in Oracle that can be used by a stored procedure to return data. The stored procedure opens the cursor and populates the cursor with data from the SELECT statement. Then the application that calls the stored procedure fetches the data from the cursor.

CREATE OR REPLACE PACKAGE GroupsPackage
AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY GroupsPackage
AS
PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE)
AS
BEGIN
 OPEN results_cursor FOR
    SELECT GroupID, GroupName, GroupDescription, LastUpdateDate
    FROM Groups
    ORDER BY GroupName;
END;
END;
/

Creating a package is a two-step process. First you create the package itself using the CREATE OR REPLACE PACKAGE statement followed by the name of the package, which in this example is GroupsPackage. This is followed by the AS keyword and the definition of the package.

The definition of this package contains a variable declaration with a variable named CURSOR_TYPE and is defined as a REF CURSOR. Then the stored procedure definition is specified with the PROCEDURE clause and the name of the stored procedure, along with its parameters, direction, and type. You can distinguish input and output parameters by the inclusion of the OUT keyword. Input parameters can contain the IN keyword but this is the default, and if not specified, the parameter is assumed to be an input parameter. You end the package with the END keyword and a semicolon.

Before you can create the package body, the package itself must be created. You need to signal to Oracle to execute the batch of statements to create the package before creating the package body. You accomplish this by using a forward slash (/). This signals Oracle to execute the preceding batch of statements and to commit them before continuing to the next set of statements, which create the package body.

The package body is created by using the CREATE OR REPLACE PACKAGE BODY statement followed by the package name, which is the package that this package body will belong to. This is followed by the AS keyword and then the statements to create the stored procedures for the package, which in this case is only one stored procedure.

Because the stored procedure is part of a package body, you specify only the PROCEDURE statement to create the stored procedure, followed by the stored procedure name and the parameters for the stored procedure contained in parentheses. Then, the AS keyword is specified, followed by the BEGIN clause.

Next comes the body of the stored procedure. In this example, you must open the cursor using the OPEN statement followed by the cursor name, which is the output parameter to this stored procedure. This is followed by the FOR keyword and then the SELECT statement. The SELECT statement will select all data from the Groups table and place the results of that SELECT statement in the cursor.

The stored procedure is terminated by an END clause and the package body is also terminated by the END clause. Another forward slash has been included so that Oracle will immediately create the package body and commit those statements before it proceeds to the next statement. (In this case, there are none.) This is just a good coding habit to get into when executing multiple statements in Oracle to prevent unnecessary errors.

Enough about packages and stored procedures; now its time to get your feet wet with hands-on exercises.

Try It Out: Creating Stored Procedures and Packages
In this exercise, you'll be creating stored procedures in SQL Server and stored procedures and packages in Oracle that mirror the functionality of the queries that currently exist in your ProjectTimeTracker Access database.

The steps in this exercise and the exercises to come will tell you when something is specific to SQL Server and when something is specific to Oracle. This will enable a single set of instructions to be used by all readers, regardless of the database that you are using.

To create your stored procedures:

1. Start Visual Studio .NET if it is not started. You do not need a project open to complete this exercise, so you can close any existing project that you may have open.

2. View the Server Explorer window and click the Auto Hide icon on the window to keep it visible.

3. View the Stored Procedures node for your database.

SQL Server
Expand the Servers node and expand the computer node on which SQL Server is running. Then expand the SQL Servers node and expand the SQL Server instance node on which your database is running. Expand the ProjectTimeTracker database node next and click the Stored Procedures node. There should be no stored procedures as you have not created any yet.

Oracle
Expand the Data Connections node and expand the Oracle connection that was created by the Oracle Data Wizard project from Chapter 3. You will be prompted with the Microsoft OLE DB for Oracle Connect dialog box. Enter your password and click OK to establish a connection to Oracle. Then click the Stored Procedures node and expand it. Stored procedures may exist for other database tables if the samples were installed with this instance of Oracle.

4. There are 10 stored procedures in Access that you'll need to create in your SQL Server or Oracle database. Either right-click the Stored Procedures node and choose New Stored Procedure or click the Database menu and choose New Stored Procedure. This causes a stored procedure template to be added to Visual Studio .NET. Enter the following code in this template:

SQL Server

CREATE PROCEDURE usp_DeleteGroup
(
@GroupID   UNIQUEIDENTIFIER
)
AS
DELETE FROM Groups
WHERE GroupID = @GroupID

Oracle

CREATE OR REPLACE PROCEDURE usp_DeleteGroup
(
inGroupID   CHAR
)
AS
BEGIN
DELETE FROM Groups
WHERE GroupID = inGroupID;
END;

Click the Save icon on the toolbar to have the stored procedure created in your database. You'll see that the usp_DeleteGroup stored procedure has been added to the Stored Procedures node in Server Explorer.

5. To create the usp_DeleteProject stored procedure, right-click the Stored Procedures node and choose New Stored Procedure from the context menu or click the Database menu and choose New Stored Procedure and enter the following code:

SQL Server

CREATE PROCEDURE usp_DeleteProject
(
@ProjectID   UNIQUEIDENTIFIER
)
AS
DELETE FROM Projects
WHERE ProjectID = @ProjectID

Oracle

CREATE OR REPLACE PROCEDURE usp_DeleteProject
(
inProjectID   CHAR
)
AS
BEGIN
DELETE FROM Projects
WHERE ProjectID = inProjectID;
END;

Click the Save icon on the toolbar to create the stored procedure in your database.

6. To create the usp_InsertGroup stored procedure, right-click the Stored Procedures node and choose New Stored Procedure or click the Database menu and choose New Stored Procedure. Enter the following code:

SQL Server

CREATE PROCEDURE usp_InsertGroup
(
@GroupID            UNIQUEIDENTIFIER,
@GroupName          VARCHAR(50),
@GroupDescription   TEXT
)
AS
INSERT INTO Groups
(GroupID, GroupName, GroupDescription, LastUpdateDate)
VALUES(@GroupID, @GroupName, @GroupDescription, GETDATE())

Oracle

CREATE OR REPLACE PROCEDURE usp_InsertGroup
(
inGroupID            CHAR,
inGroupName          VARCHAR2,
inGroupDescription   CLOB
)
AS
BEGIN
INSERT INTO Groups
(GroupID, GroupName, GroupDescription, LastUpdateDate)
VALUES(inGroupID, inGroupName, inGroupDescription, SYSDATE);
END;

Click the Save icon on the toolbar to create the stored procedure in your database.