ch13.htm

Learn SQL in an easy way...

	is no longer valid.
	<P>
	<LI>Trigger--A cursor created inside a trigger has the same restrictions as one created
	inside a stored procedure.
</UL>

<H2><FONT COLOR="#000077">Creating and Using Stored Procedures</FONT></H2>
<P>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.</P>
<P>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.</P>
<P>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.</P>
<P>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.</P>
<P>Use the following syntax to create a stored procedure using Transact-SQL:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">create procedure procedure_name
     [[(]@parameter_name
         datatype [(length) | (precision [, scale])
         [= default][output]
     [, @parameter_name
         datatype [(length) | (precision [, scale])
         [= default][output]]...[)]]
     [with recompile]
     as SQL_statements
</FONT></PRE>
<P>This <TT>EXECUTE</TT> command executes the procedure:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">execute [@return_status = ]
     procedure_name
     [[@parameter_name =] value |
         [@parameter_name =] @variable [output]...]]
     [with recompile]
</FONT></PRE>
<H4><FONT COLOR="#000077">Example 13.5</FONT></H4>
<P>This example creates a simple procedure using the contents of Example 13.4.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>create procedure Print_Artists_Name</B>
2&gt; <B>as</B>
3&gt; <B>declare @name char(30)</B>
4&gt; <B>declare @homebase char(40)</B>
5&gt; <B>declare @style char(20)</B>
6&gt; <B>declare @artist_id int</B>
7&gt; <B>create Artists_Cursor cursor</B>
8&gt; <B>for select * from ARTISTS</B>
9&gt; <B>open Artists_Cursor</B>
10&gt; <B>fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>
11&gt; <B>while (@@sqlstatus = 0)</B>
12&gt; <B>begin</B>
13&gt;      <B>print @name</B>
14&gt;      <B>fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>
15&gt; <B>end</B>
16&gt; <B>close Artists_Cursor</B>
17&gt;<B> deallocate cursor Artists_Cursor</B>
18&gt; <B>go</B>
</FONT></PRE>
<P>You can now execute the<TT> Print_Artists_Name</TT> procedure using the <TT>EXECUTE</TT>
statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>execute Print_Artists_Name</B>
2&gt; <B>go</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Soul Asylum
Maurice Ravel
Dave Matthews Band
Vince Gill
Oingo Boingo
Crowded House
Mary Chapin-Carpenter
Edward MacDowell
</FONT></PRE>
<P>Example 13.5 was a small stored procedure; however, a stored procedure can contain
many statements, which means you do not have to execute each statement individually.
<H3><FONT COLOR="#000077">Using Stored Procedure Parameters</FONT></H3>
<P>Example 13.5 was an important first step because it showed the use of the simplest
<TT>CREATE PROCEDURE</TT> statement. However, by looking at the syntax given here,
you can see that there is more to the <TT>CREATE PROCEDURE</TT> statement than was
demonstrated in Example 13.5. 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.</P>
<P>Input parameter names must begin with the <TT>@</TT> symbol, and these parameters
must be a valid Transact-SQL data type. Output parameter names must also begin with
the <TT>@</TT> symbol. In addition, the <TT>OUTPUT</TT> keyword must follow the output
parameter names. (You must also give this <TT>OUTPUT</TT> keyword when executing
the stored procedure.)</P>
<P>Example 13.6 demonstrates the use of input parameters to a stored procedure.
<H4><FONT COLOR="#000077">Example 13.6</FONT></H4>
<P>The following stored procedure selects the names of all artists whose media type
is a CD:</P>
<PRE><FONT COLOR="#0066FF">1&gt; <B>create procedure Match_Names_To_Media @description char(30)</B>
2&gt; <B>as</B>
3&gt;   <B> select ARTISTS.name from ARTISTS, MEDIA, RECORDINGS</B>
4&gt;    <B>where MEDIA.description = @description and</B>
5&gt;   <B> MEDIA.media_type = RECORDINGS.media_type and</B>
6&gt;   <B> RECORDINGS.artist_id = ARTISTS.artist_id</B>
7&gt; <B>go</B>
1&gt; <B>execute Match_Names_To_Media &quot;CD&quot;</B>
2&gt; <B>go</B>
</FONT></PRE>
<P>Executing this statement would return the following set of records:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">NAME
Soul Asylum
Maurice Ravel
Vince Gill
Crowded House
Mary Chapin-Carpenter
</FONT></PRE>
<H4><FONT COLOR="#000077">Example 13.7</FONT></H4>
<P>This example demonstrates the use of output parameters. This function takes the
artist's homebase as input and returns the artist's name as output:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>create procedure Match_Homebase_To_Name @homebase char(40), @name char(30) output</B>
2&gt; <B>as</B>
3&gt;      <B>select @name = name from ARTISTS where homebase = @homebase</B>
4&gt;<B> go</B>
1&gt; <B>declare @return_name char(30)</B>
2&gt; <B>execute Match_Homebase_To_Name &quot;Los Angeles&quot;, @return_name = @name output</B>
3&gt; <B>print @name</B>
4&gt; <B>go</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">Oingo Boingo
</FONT></PRE>
<H3><FONT COLOR="#000077">Removing a Stored Procedure</FONT></H3>
<P>By now, you can probably make an educated guess as to how to get rid of a stored
procedure. If you guessed the <TT>DROP</TT> command, you are absolutely correct.
The following statement removes a stored procedure from a database:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">drop procedure procedure_name
</FONT></PRE>
<P>The <TT>DROP</TT> 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 <TT>DROP</TT> command, errors will result.</P>
<P>The following syntax is often used in SQL Server script files before creating
a database object:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">if exists (select * from sysobjects where name = &quot;procedure_name&quot;)
begin
     drop procedure procedure_name
end
go
create procedure procedure_name
as
.
.
.
</FONT></PRE>
<P>These commands check the <TT>SYSOBJECTS</TT> 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.
<H3><FONT COLOR="#000077">Nesting Stored Procedures</FONT></H3>
<P>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:

<UL>
	<LI>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.)
	<P>
	<LI>Nesting stored procedures improves performance. The query optimizer optimizes
	smaller, more concise groups of queries more effectively than one large group of
	statements.
</UL>

<P>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.</P>
<P>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? Example 13.8 should help you understand this process.
<H4><FONT COLOR="#000077">Example 13.8</FONT></H4>
<H5><FONT COLOR="#000000">INPUT:</FONT><FONT COLOR="#000077"></FONT></H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>create procedure Example13_8b</B>
2&gt; <B>as</B>
3&gt;    <B> select * from #temp_table</B>
4&gt; <B>go</B>
1&gt; <B>create procedure Example13_8a</B>
2&gt; <B>as</B>
3&gt;     <B>create #temp_table (</B>
4&gt;    <B> data char(20),</B>
5&gt;    <B> numbers int)</B>
6&gt;    <B> execute Example13_8b</B>
7&gt;    <B> drop table #temp_table</B>
8&gt; <B>go</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>As you can see, procedure <TT>Example13_8b</TT> uses the <TT>#temp_table</TT>.
However, the <TT>#temp_table</TT> is not created until later (in procedure <TT>Example13_8a</TT>).
This results in a procedure creation error. In fact, because <TT>Example13_8b</TT>
was not created (owing to the missing table <TT>#temp_table</TT>), procedure <TT>Example13_8a</TT>
is not created either (because <TT>Example13_8b</TT> was not created).</P>
<P>The following code fixes this problem by creating the <TT>#temp_table</TT> before
the first procedure is created. <TT>#temp_table</TT> is then dropped before the creation
of the second procedure:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>create #temp_table (</B>
2&gt; <B>data char(20),</B>
3&gt; <B>numbers int)</B>
4&gt;<B> go</B>
1&gt; <B>create procedure Example13_8b</B>
2&gt;<B> as</B>
3&gt;    <B> select * from #temp_table</B>
4&gt; <B>go</B>
1&gt; <B>drop table #temp_table</B>
2&gt; <B>go</B>
1&gt; <B>create procedure Example13_8a</B>
2&gt; <B>as</B>
3&gt;    <B> create #temp_table (</B>
4&gt;    <B> data char(20),</B>
5&gt;    <B> numbers int)</B>
6&gt;    <B> execute Example13_8b</B>
7&gt;    <B> drop table #temp_table</B>
8&gt; <B>go</B>
</FONT></PRE>
<H2><FONT COLOR="#000077">Designing and Using Triggers</FONT></H2>
<P>A trigger is essentially a special type of stored procedure that can be executed
in response to one of three conditions:

<UL>
	<LI>An <TT>UPDATE</TT>
	<P>
	<LI>An <TT>INSERT</TT>
	<P>
	<LI>A <TT>DELETE</TT>
</UL>

<P>The Transact-SQL syntax to create a trigger looks like this:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">create trigger trigger_name
   on table_name
   for {insert, update, delete}
   as SQL_Statements
</FONT></PRE>
<P>The Oracle7 SQL syntax used to create a trigger follows.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
  {BEFORE | AFTER}
  {DELETE | INSERT | UPDATE [OF column[, column]...]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}]...
   ON [schema.]table
[[REFERENCING { OLD [AS] old [NEW [AS] new]
       | NEW [AS] new [OLD [AS] old]}]
FOR EACH ROW
[WHEN (condition)] ]
pl/sql statements...
</FONT></PRE>
<P>Triggers are most useful to enforce referential integrity, as mentioned on Day
9, &quot;Creating and Maintaining Tables,&quot; when you learned how to create tables.
Referential integrity enforces rules used to ensure that data remains valid across
multiple tables. Suppose a user entered the following command:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>insert RECORDINGS values (12, &quot;The Cross of Changes&quot;, 3, 1994)</B>
2&gt; <B>go
</B></FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This perfectly valid SQL statement inserts a new record in the <TT>RECORDINGS</TT>
table. However, a quick check of the <TT>ARTISTS</TT> table shows that there is no
<TT>Artist_ID = 12</TT>. A user with <TT>INSERT</TT> privileges in the <TT>RECORDINGS</TT>
table can completely destroy your referential integrity.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Although many database systems can
	enforce referential integrity through the use of constraints in the <TT>CREATE TABLE</TT>
	statement, triggers provide a great deal more flexibility. Constraints return system
	error messages to the user, and (as you probably know by now) these error messages
	are not always helpful. On the other hand, triggers can print error messages, call
	other stored procedures, or try to rectify a problem if necessary. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077">Triggers and Transactions</FONT></H3>
<P>The actions executed within a trigger are implicitly executed as part of a transaction.
Here's the broad sequence of events:

<DL>
	<DD><B>1.</B> A <TT>BEGIN TRANSACTION</TT> statement is implicitly issued (for tables
	with triggers).
	<P><B>2. </B>The insert, update, or delete operation occurs.</P>
	<P><B>3.</B> The trigger is called and its statements are executed.</P>
	<P><B>4.</B> The trigger either rolls back the transaction or the transaction is
	implicitly committed.
</DL>

<H4><FONT COLOR="#000077">Example 13.9</FONT></H4>
<P>This example illustrates the solution to the <TT>RECORDINGS</TT> table update
problem mentioned earlier.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>create trigger check_artists</B>
2&gt; <B>on RECORDINGS</B>
3&gt; <B>for insert, update as</B>
4&gt;      <B>if not exists (select * from ARTISTS, RECORDINGS</B>
5&gt;      <B>where ARTISTS.artist_id = RECORDINGS.artist_id)</B>
6&gt;      <B>begin</B>
7&gt;       <B>  print &quot;Illegal Artist_ID!&quot;</B>
8&gt;       <B>  rollback transaction</B>
9&gt;     <B> end</B>
10&gt; <B>go</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>A similar problem could exist for deletes from the <TT>RECORDINGS</TT> table.