14. Writing and Executing SQL Routines and Modules—Triggers and Stored Procedures
Although SQL is not a complete programming language—it lacks I/O statements—the standard does contain statements that perform typical programming language functions such as assignment, selection, and iteration for writing persistent stored modules (PSMs). This chapter looks at creating program routines and modules using those language elements and how they are executed as either triggers or stored procedures.
Note: This chapter does not attempt to teach programming concepts. To get the most out of it you should be familiar with a general-purpose programming or scripting language such as COBOL, C, C++, Java, JavaScript, or Perl.
Triggers are SQL program modules that are executed when a specific data modification activity occurs. For example, a trigger may be configured to execute whenever a row is inserted into a table. Stored procedures are SQL program modules that are invoked by an application program using the SQL CALL command. Both triggers and stored procedures are stored as part of a database.
Note: Support for SQL programming varies considerably from one DBMS to another. This chapter presents what is documented in the SQL standard, but it is highly likely that what is available with your DBMS will be different from what you see here, at least to some degree. You should therefore use what is in this chapter as a starting point for your DBMS's SQL programming support and verify the specifics with your software's documentation.
SQL Programming Elements
The smallest unit of a SQL PSM is a routine. Typically a routine will perform a single action, such as updating a total or inserting a row in a table. Routines are then gathered into modules.
There are three types of routines:
◊ Procedures: Procedures are executed with the SQL CALL statement. They do not return a value.
◊ Functions: Functions return a typed value and are used within other SQL statements (in particular, SELECT).
◊ Methods: Methods are used by SQL's object-relational extensions. They are written using the same programming elements as procedures and functions. Therefore, their structure will be discussed in Chapter 19, but the techniques for creating method bodies can be found in this chapter.
To create a procedure, use the CREATE PROCEDURE statement:
CREATE PROCEDURE
procedure_name (input_parameters)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
procedure_body
END
Function creation must include the type of data being returned and a RETURN statement:
CREATE FUNCTION
function_name (input_parameters)
RETURNS return_data_type
LANGUAGE SQL
CONTAINS SQL
function_body
RETURN return_value
Note: Functions that you write yourself are often called user-defined functions (UDFs) to distinguish them from functions such as SUM (sometimes called BIFs, for Built-In Functions) that are part of the SQL language.
Notice that the two preceding structures include statements that refer to the language and type of statements in the routine:
◊ LANGUAGE language_name: Indicates the programming language used in the routine. In our examples, the language is SQL. If the LANGUAGE clause is not present, then the language defaults to SQL.
◊ Type of SQL statements contained in routine (one of the following):
○ CONTAINS SQL: Indicates that the routine includes SQL statements that do not retrieve or modify data.
○ READS SQL DATA: Indicates that the routine includes SQL statements that read data from a database but that do not modify data.
○ MODIFIES SQL DATA: Indicates that the routine modifies data using SQL commands. This also implies that the routine may be retrieving data.
The routine's contents may include SQL data modification statements (INSERT, UPDATE, and DELETE) along with SQL control structures.
SQL modules are created with the CREATE MODULE statement:
CREATE MODULE module_name
module_contents
END MODULE
Like other SQL structural elements, routines, modules, and the contents are stored in the current schema. To remove a module or routine, you must therefore use
DROP ROUTINE routine_name
or
DROP MODULE module_name
Note: The interactive SELECT, which typically returns multiple rows, is useful only for display of a result table. To manipulate the data in a result table, you will need to use embedded SQL, which retrieves data into a virtual table and then lets you process the rows in that table one at a time. (Embedded SQL is discussed inChapter 15.) Dynamic SQL (Chapter 16) further extends programming with SQL by letting the user enter search values at run time.
Within a module, SQL recognizes compound statements using BEGIN and END:
BEGIN
one_or_more_executable_statements
END
As you might expect, compound statements can be nested as needed.
Variables and Assignment
SQL modules can maintain their own internal variables and perform assignment. Variables must be declared before they are used:
Once a variable has been declared, you assign values to it across the assignment operator:
variable_name = value
For example, if you need to store a sales tax percentage, the routine could contain
DECLARE tax_rate NUMBER (5,2);
tax_rate = 0.075;
Important note: Depending on the DBMS, the assignment operator may be = or : -. Check your documentation to be sure.
Important note: Some DBMSs require a special character at the beginning of a variable name. For example, SQL Server requires @. Once again, the only way to be certain is to consult your DBMS's documentation.
Both functions and procedures can accept input parameters. A parameter list contains the names by which the parameters are to be referenced within the body of the routine and a declaration of their data types:
Passing Parameters
CREATE routine_type routine_name (parameter_1 parameter_1_data_type, parameter_2 parameter_2_data_type, …)
For example, to pass in a tax rate and a selling price to a function that computes sales tax, the function might be written
CREATE FUNCTION compute_tax
(tax_rate NUMBER (6,3), selling_price NUMBER (7,2))
LANGUAGE SQL
RETURNS NUMBER
RETURN selling_price * tax_rate;
Note: Procedures can also use a parameter for output or for both input and output.
Note: Some DBMSs require/allow you to specify whether a parameter is for input, output, or both. If you are working with one of those implementations, each parameter in the parameter list must/ can be labeled with IN, OUT, or INOUT.
Scope of Variables
Variables declared within SQL functions and procedures are local to the routine. Values passed in through a parameter list are declared in the parameter list and therefore become local variables. However, there are circumstances in which you may want to use variables declared outside the function or procedure (host language variables). In that case, there are two things you need to do:
◊ Redeclare the host language variables using a SQL declare section.
BEGIN SQL DECLARE SECTION;
redeclaration of host language
variables;
END SQL DECLARE SECTION;
◊ Place a colon in front of the name of each host language variable whenever it is used in the body of the SQL routine.
You will see examples of these techniques in use with embedded SQL in Chapter 15.
Selection
The SQL standard provides two selection structures: IF and CASE. Both function essentially like the analogous elements in general-purpose programming languages.
IF
In its simplest form, the SQL IF construct has the following structure:
IF boolean_expression THEN
body_of_IF
END IF
Assume, for example, that the owner of the rare book store wants to give a discount on a total purchase to customers who order more than $100 on a single order. The code to do so could be written
IF sale_total_amt >= 100 THEN
sale_total_amt = sale_total_amt * .9;
END IF;
As you would expect, the IF statement can be extended with ELSEIF and ELSE clauses:
IF boolean_expression THEN
body_of_IF
ELSEIF boolean_expression THEN
body_of_ELSEIF
:
ELSE
body_of_ELSE
END OF
The ELSEIF clause is shorthand for the following:
IF boolean_expression THEN
body_of_IF
ELSE
IF boolean_expression THEN
body_of_nested_IF
END IF
END IF
A purchase from the rare book store that must be shipped is assessed shipping charges based on the number of volumes in the purchase. Assuming the number of volumes in the purchase is stored in how_many, an IF construct to assign those shipping charges might be written as
IF how_many <= 5 THEN
shipping_charges = how_many * 2;
ELSEIF how_many <= 10 THEN
shipping_charges = how_many * 1.5;
ELSE
shipping_charges = how_many;
END IF
Note: Obtaining the count of the number of volumes in a single purchase requires embedded SQL, which is discussed inChapter 15.
CASE
The SQL CASE expression comes in two forms, one with a single condition and one with multiple conditions. (The syntaxes are essentially the same as the CASE statement that can be used in a SELECT clause.) In its simplest form, it has the general format:
CASE logical_expression
WHEN value1 THEN executable_statement(s)
WHEN value2 THEN executable_statement(s)
WHEN value3 THEN executable_statement(s)
:
ELSE default
END CASE
For example, suppose T-shirt sizes are stored as integer codes and you want to translate those sizes to words. The code could be written
DECLARE text_size CHAR (10)
CASE size
WHEN 1 THEN text_size = ‘Small’
WHEN 2 THEN text_size = ‘Medium’
WHEN 3 THEN text_size = ‘Large’
WHEN 4 THEN text_size = ‘Extra Large’
END CASE
The multiple condition version is a bit more flexible:
CASE
WHEN logical_expression1 THEN executable_ statement(s)
WHEN logical_expression1 THEN executable_ statement(s)
WHEN logical_expression1 THEN executable_ statement(s)
:
ELSE default
END CASE;
Someone could use this second version to compute a book discount based on selling price:
CASE
WHEN asking_price < 50 THEN selling_price = asking_price * .9
WHEN asking_price < 100 THEN selling_price = asking_price * .85
WHEN asking_price < 150 THEN selling_price = asking_price * .75
ELSE selling_price = asking_price * .5
END CASE;
Iteration
SQL has four statements that perform iteration—LOOP, WHILE, REPEAT, and FOR—that work somewhat differently from similar statements in general-purpose programming languages.
Note: The SQL FOR statement is not a general-purpose looping construct. Instead, it is designed to work with embedded SQL code that processes each row in a virtual table that has been created as the result of a SELECT. We will therefore defer a discussion of FOR untilChapter 15.
LOOP
The LOOP statement is a simple construct that sets up an infinite loop:
loop_name: LOOP
body_of_loop
END LOOP
The condition that terminates the loop and a command to exit the loop must therefore be contained in the body of the loop. A typical structure therefore would be
loop_name: LOOP
body_of_loop
IF termination_condition
LEAVE loop_name
END LOOP
Assume (for some unknown reason) that we want to total the numbers from 1 through 100. We could do it with a LOOP statement as follows:
DECLARE sum INT;
DECLARE count INT;
sum = 0;
count = 1;
sum_loop: LOOP
sum = sum + count;
count = count + 1;
IF count > 100
LEAVE sum_loop;
END LOOP;
Note: LEAVE can be used with any named looping construct. However, it is essential only for a LOOP structure because there is no other way to stop the iteration.
WHILE
The SQL WHILE is very similar to what you will find as part of a general-purpose programming language:
loop_name: while boolean_expression DO
body_of_loop
END WHILE
The loop name is optional.
As an example, assume that you wanted to continue to purchase items until all of your funds were exhausted, but that each time you purchased an item the price went up ten percent. Each purchase is stored as a row in a table. Code to handle that could be written as in Figure 14-1.
Note: Whenever a host language (in this case SQL) variable is used in a SQL statement, it must be preceded by a colon, as in :price.
 |
| Figure 14-1 Using a WHILE loop to make repeated purchases |
REPEAT
The SQL REPEAT statement is similar to the WHILE DO statement in high-level languages where the test for termination/continuation of the loop is at the bottom of the loop. It has the general format:
We could rewrite the example from the preceding section using a REPEAT in the following way:
DECLARE funds NUMBER (7,2);
funds = 1000.00;
DECLARE price NUMBER (5,2);
price = 29.95;
REPEAT
INSERT INTO items_purchased
VALUES (6, CURRENT_DATE, :price);
funds = funds - price;
price = price * 1.1;
UNTIL price > funds
END REPEAT;
Example #1: Interactive Retrievals
One of the things you might decide to do with a stored procedure is simplify issuing a query or series of queries. For example, suppose that the owner of the rare book store wants to see the sales that were made each day along with the total sales. A single interactive SQL command won't produce both a listing of individual sales and a total. However, the two queries in Figure 14-2 will do the trick. The user only needs to run the procedure to see the needed data.
Note: Without embedded-SQL, we can only display data retrieved by a SELECT. We can't process the individual rows.
 |
| Figure 14-2 A SQL procedure that contains multiple SELECT statements for display |
Example #2: Nested Modules
Procedures and functions can call other procedures and functions. For this example, let's assume that a column for the sales tax has been added to the sale table and prepare a procedure that populates the sale table and updates the volume table when a purchase is made (Figure 14-3). The sell_it procedures uses the compute_tax function you saw earlier in this chapter.
 |
| Figure 14-3 A SQL procedure that calls a user-defined function |
Executing Modules as Triggers
A trigger is a module that is attached to a single table and executed in response to one of the following events:
◊ INSERT (either before or after an insert occurs)
◊ UPDATE (either before or after a modify occurs)
◊ DELETE (either before or after a delete occurs)
“Before” triggers are run prior to checking constraints on the table and prior to running INSERT, UPDATE, or DELETE command. “After” triggers work on the table after the INSERT, UPDATE, or DELETE has been performed, using the table as it has been changed by the command. A trigger can be configured to run once for every row in the table or just once for the entire table.
Note: It is possible to attach multiple triggers for the same event to the same table. The order in which they execute is implementation dependent. Some DBMSs execute them in alphabetical order by name; others execute them chronologically, with the first created being the first executed.
Before creating a trigger, you must create the function or procedure that is to be run. Once that is in place, you use the CREATE TRIGGER statement to attach the trigger to its table and specify when it should be run:
The when_to_execute value is either BEFORE or AFTER, the type of event is INSERT, MODIFY, or DELETE, and the row_or_table_specifier is either FOR EACH ROW or FOR EACH STATEMENT.
For example, to trigger the procedure that updates the sale_total_amt in the sale table whenever a volume is sold, someone at the rare book store could use
CREATE TRIGGER t_update_total AFTER UPDATE
ON volume FOR EACH STATEMENT
EXECUTE PROCEDURE p_update_total;
The trigger will then execute automatically whenever an update is performed on the volume table.
You remove a trigger with the DROP TRIGGER statement:
DROP TRIGGER trigger_name
However, it can be a bit tedious to continually drop a trigger when what you want to do is replace an existing trigger with a new version. To simply replace an existing trigger, use
CREATE OR MODIFY TRIGGER trigger_name …
instead of simply CREATE TRIGGER.
Executing Modules as Stored Procedures
Stored procedures are invoked with either the EXECUTE or CALL statement:
EXECUTE procedure_name (parameter_list)
or
CALL procedure_name (parameter_list)
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.