New Document
Computer Science
Computer Catlog
Oracle Catlog

History of Oracle
Introduction to Terms
Oracle Configurations
Roles of Database Administrator
Oracle Architecture
A Brief History of SQL
Dr. Codd's 12 Rules
An Overview of SQL
The SELECT Statement
Expressions, Conditions, and Operators
Clauses in SQL
Joining Tables
Sub Query
Manipulating Data
Building a Database
Views and Indexes
Controlling Transactions
Database Security
Advanced SQL Topics
Stored Procedures
Embedded SQL
SQL Tuning
Using Views in Data Dictionary
Using SQL to Generate SQL Statements

Creating and Using Stored Procedures

    The concept of stored procedures is an important one for the professional database programmer to master. Stored procedures are functions that contain potentially large groupings of SQL statements. These functions are called and executed just as C, FORTRAN, or Visual Basic functions would be called. A stored procedure should encapsulate a logical set of commands that are often executed (such as a complex set of queries, updates, or inserts). Stored procedures enable the programmer to simply call the stored procedure as a function instead of repeatedly executing the statements inside the stored procedure. However, stored procedures have additional advantages.

    Sybase, Inc. pioneered stored procedures with its SQL Server product in the late 1980s. These procedures are created and then stored as part of a database, just as tables and indexes are stored inside a database. Transact SQL permits both input and output parameters to stored procedure calls. This mechanism enables you to create the stored procedures in a generic fashion so that variables can be passed to them.

    One of the biggest advantages to stored procedures lies in the design of their execution. When executing a large batch of SQL statements to a database server over a network, your application is in constant communication with the server, which can create an extremely heavy load on the network very quickly. As multiple users become engaged in this communication, the performance of the network and the database server becomes increasingly slower. The use of stored procedures enables the programmer to greatly reduce this communication load.

    After the stored procedure is executed, the SQL statements run sequentially on the database server. Some message or data is returned to the user's computer only when the procedure is finished. This approach improves performance and offers other benefits as well. Stored procedures are actually compiled by database engines the first time they are used. The compiled map is stored on the server with the procedure. Therefore, you do not have to optimize SQL statements each time you execute them, which also improves performance.

    Use the following syntax to create a stored procedure using Transact-SQL:


Create procedure procedure-name [[(]@parameter-name datatype
[(length) | (precision [, scale]) [= default][output] [, @parameter-name datatype
[(length) | (precision [, scale]) [= default][output]]...[)]] [with recompile] as

    This EXECUTE command executes the procedure:


Execute [@return-status = ] procedure-name [[@parameter-name =]
value | [@parameter-name =] @variable [output]...]] [with recompile]

    This example creates a simple procedure using the contents.


create procedure Print-Artists-Name as
declare @name char(30)
declare @homebase char(40)
declare @style char(20)
declare @artist-id int
create Artists-Cursor cursor for select * from ARTISTS
open Artists-Cursor
fetch Artists-Cursor into @name, @homebase, @style, @artist-id
while (@@sqlstatus = 0)
print @name
fetch Artists-Cursor into @name, @homebase, @style, @artist-id
close Artists-Cursor
deallocate cursor Artists-Cursor go

    You can now execute the Print-Artists-Name procedure using the EXECUTE statement:


Execute Print-Artists-Name go

Using Stored Procedure Parameters

    The above example was an important first step because it showed the use of the simplest CREATE PROCEDURE statement. However, by looking at the syntax given here, you can see that there is more to the CREATE PROCEDURE statement than was demonstrated in Example. Stored procedures also accept parameters as input to their SQL statements. In addition, data can be returned from a stored procedure through the use of output parameters.

    Input parameter names must begin with the @ symbol, and these parameters must be a valid Transact-SQL data type. Output parameter names must also begin with the @ symbol. In addition, the OUTPUT keyword must follow the output parameter names. (You must also give this OUTPUT keyword when executing the stored procedure.)

The below example demonstrates the use of input parameters to a stored procedure.

The following stored procedure selects the names of all artists whose media type is a CD:

create procedure Match-Names-To-Media @description char(30) as
where MEDIA.description = @description
and =
and RECORDINGS.artist-id = ARTISTS.artist-id go

execute Match-Names-To-Media "CD" go

    This example demonstrates the use of output parameters. This function takes the artist's home base as input and returns the artist's name as output:


create procedure Match-Homebase-To-Name @homebase char(40), @name char(30)
output as select @name = name from ARTISTS where homebase = @homebase go

declare @return-name char(30) execute Match-Homebase-To-Name
"Los Angeles", @return-name = @name output print @name go

Removing a Stored Procedure

    By now, you can probably make an educated guess as to how to get rid of a stored procedure. If you guessed the DROP command, you are absolutely correct. The following statement removes a stored procedure from a database:


drop procedure procedure-name

    The DROP command is used frequently: Before a stored procedure can be re-created, the old procedure with its name must be dropped. From personal experience, there are few instances in which a procedure is created and then never modified. Many times, in fact, errors occur somewhere within the statements that make up the procedure. We recommend that you create your stored procedures using an SQL script file containing all your statements. You can run this script file through your database server to execute your desired statements and rebuild your procedures. This technique enables you to use common text editors such as vi or Windows Notepad to create and save your SQL scripts. When running these scripts, however, you need to remember to always drop the procedure, table, and so forth from the database before creating a new one. If you forget the DROP command, errors will result.

    The following syntax is often used in SQL Server script files before creating a database object:


if exists (select * from sysobjects where name = "procedure-name") begin
drop procedure procedure-name end go create procedure procedure-name as . . .

    These commands check the SYSOBJECTS table (where database object information is stored in SQL Server) to see whether the object exists. If it does, it is dropped before the new one is created. Creating script files and following the preceding steps saves you a large amount of time (and many potential errors) in the long run.

Nesting Stored Procedures

    Stored procedure calls can also be nested for increased programming modularity. A stored procedure can call another stored procedure, which can then call another stored procedure, and so on. Nesting stored procedures is an excellent idea for several reasons:

Nesting stored procedures reduces your most complex queries to a functional level. (Instead of executing 12 queries in a row, you could perhaps reduce these 12 queries to three stored procedure calls, depending on the situation.)

Nesting stored procedures improves performance. The query optimizer optimizes smaller, more concise groups of queries more effectively than one large group of statements.

    When nesting stored procedures, any variables or database objects created in one stored procedure are visible to all the stored procedures it calls. Any local variables or temporary objects (such as temporary tables) are deleted at the end of the stored procedure that created these elements.

    When preparing large SQL script files, you might run into table or database object referencing problems. You must create the nested stored procedures before you can call them. However, the calling procedure may create temporary tables or cursors that are then used in the called stored procedures. These called stored procedures are unaware of these temporary tables or cursors, which are created later in the script file. The easiest way around this problem is to create the temporary objects before all the stored procedures are created; then drop the temporary items (in the script file) before they are created again in the stored procedure. Are you confused yet?


create procedure Example13-8b as select * from #temp-table go

create procedure Example13-8a as create #temp-table (data char(20), numbers int)
execute Example13-8b
drop table #temp-table go


    As you can see, procedure Example13-8b uses the #temp-table. However, the #temp-table is not created until later (in procedure Example13-8a). This results in a procedure creation error. In fact, because Example13-8b was not created (owing to the missing table #temp-table), procedure Example13-8a is not created either (because Example13-8b was not created).

    The following code fixes this problem by creating the #temp-table before the first procedure is created. #temp-table is then dropped before the creation of the second procedure:


create #temp-table (data char(20), numbers int) go

create procedure Example13-8b as select * from #temp-table go

drop table #temp-table go

create procedure Example13-8a as
create #temp-table ( data char(20), numbers int)
execute Example13-8b
drop table #temp-table go