SQL Bible [Electronic resources] نسخه متنی

Stored procedures

As we mentioned before, stored procedures are linear or sequential programs. The syntax varies from implementation to implementation, but some common features can be emphasized. Stored procedures can accept parameters and allow local variable declarations; they are structured and allow the use of submodules; also, they allow repeated and conditional statement execution.

CREATE PROCEDURE syntax

The CREATE PROCEDURE syntax is different among RDBMS implementations. The simplified syntaxes for SQL99 and all our three major RDBMS vendors are provided below:

SQL99

CREATE PROCEDURE <procedure_name>
<procedure_definition>

PL/SQL

CREATE [OR REPLACE] PROCEDURE [qualifier.]<procedure_name>
([<argument_name> IN | OUT | IN OUT <datatype>
[DEFAULT <default>],...])
{IS | AS}
[<variable_name <datatype> [DEFAULT <default>];],...
BEGIN
<procedure_body>
[EXCEPTION
<exception_statements>]
END;

DB2 procedural language

CREATE PROCEDURE [qualifier.]<procedure_name>
([IN | OUT | INOUT <argument_name> <datatype>,...])
{MODIFIES SQL DATA |
NO SQL |
CONTAINS SQL |
READS SQL DATA}
[[NOT] DETERMINISTIC]
LANGUAGE SQL
BEGIN [ATOMIC]
<procedure_body>
END

Transact-SQL

CREATE PROC[EDURE] <procedure_name>    [@<parameter_name> <datatype> [ = <default>] 
[OUTPUT] ] ,...
AS
<procedure_body>

Creating a simple stored procedure

Our stored procedure implements the following business logic. New products are often entered into the PRODUCT table of the ACME sample database. The problem is the clerks sometimes enter duplicate product names using the different combinations of uppercase and lowercase letters. For example, SPRUCE LUMBER 30X40X50, spruce lumber 30x40x50 and Spruce Lumber 30X40X50 are supposed to be a single entry, but could be entered as three separate entities with different primary keys referred by foreign keys from other tables causing data integrity problems.

The procedure SP_PRODUCTADD adds a row to the PRODUCT table if the product with the given product description does not exist or updates the existing record with new values. It accepts ten parameters, one for each column in the PRODUCT table, then checks if a product with such a description (in uppercase or lowercase letters) already exists, and then performs the appropriate action (INSERT or UPDATE) based on the result.

Oracle 9i

A typical Oracle-stored procedure consists of header, declaration part, body, and exception handling component. The header (between keywords CREATE and IS) includes parameter names and data types. The local variables can be declared in the declaration section. The body stores the procedure's logic, and the optional exception section is for handling exceptions — errors that could happen during the execution of the procedure. Here is the syntax to implement this logic:

CREATE PROCEDURE sp_productadd
/* This procedure adds new product to PRODUCT table */
(
v_prodid              NUMBER,
v_prodprice           NUMBER,
v_prodnum             VARCHAR2,
v_proddesc            VARCHAR2,
v_prodstatus          CHAR,
v_prodbrand           VARCHAR2,
v_prodpltwid          NUMBER,
v_prodpltlen          NUMBER,
v_prodnetwgt          NUMBER,
v_prodshipwgt         NUMBER
)
IS
-- Local variable declaration
v_prodcount NUMBER := 0;
v_prodid_existing NUMBER;
BEGIN
-- Check if product with this name already exists
SELECT COUNT (*)
INTO   v_prodcount
FROM   product
WHERE  UPPER(prod_description_s) = UPPER(v_proddesc);
-- Product does not exist
IF v_prodcount = 0 THEN
-- Insert row into PRODUCT based on arguments passed
INSERT INTO product
VALUES
(
v_prodid,
v_prodprice,
v_prodnum,
v_proddesc,
v_prodstatus,
v_prodbrand,
v_prodpltwid,
v_prodpltlen,
v_prodnetwgt,
v_prodshipwgt
);
-- Product with this name already exists
ELSIF v_prodcount = 1 THEN
-- Find the product's primary key number
SELECT prod_id_n
INTO   v_prodid_existing
FROM   product
WHERE  UPPER(prod_description_s) = UPPER(v_proddesc);
-- Update the existing product with values
-- passed as arguments
UPDATE product
SET    prod_price_n = v_prodprice,
prod_description_s = v_proddesc,
prod_status_s = v_prodstatus,
prod_brand_s = v_prodbrand,
prod_pltwid_n = v_prodpltwid,
prod_pltlen_n = v_prodpltlen,
prod_netwght_n = v_prodnetwgt,
prod_shipweight_n = v_prodshipwgt
WHERE  prod_id_n = v_prodid_existing;
END IF;
-- No errors; perform COMMIT
COMMIT;
-- Exception section -- the execution flow goes here
-- if an error occurs during the execution
EXCEPTION
WHEN OTHERS THEN
-- Enable standard output
DBMS_OUTPUT.ENABLE;
-- Put line into the standard output
DBMS_OUTPUT.PUT_LINE('Error');
-- Rollback all changes
ROLLBACK;
END sp_productadd;
/

The above stored procedure can be compiled directly from SQL*Plus command line or from a GUI tool like TOAD or Oracle Enterprise Manager and then called using the following syntax:

EXEC SP_PRODUCTADD
(1, 23.67, 1, 'TEST PRODUCT', 'Y', 'TEST', 1, 3, 45, 33);

DB2 UDB

The structure of a DB2 stored procedure is similar to one of PL/SQL except it does not have an EXCEPTION section in it. The syntax to create our stored procedure using DB2 SQL procedural language is as follows:

CREATE PROCEDURE sp_productadd
/* This procedure adds new product to PRODUCT table */
(
v_prodid              INTEGER,
v_prodprice           DECIMAL(10,2),
v_prodnum             VARCHAR(44),
v_proddesc            VARCHAR(10),
v_prodstatus          CHAR(1),
v_prodbrand           VARCHAR(20),
v_prodpltwid          DECIMAL(5,2),
v_prodpltlen          DECIMAL(5,2),
v_prodnetwgt          DECIMAL(10,3),
v_prodshipwgt         DECIMAL(10,3)
)
LANGUAGE SQL
-– Transaction has to be atomic if we
-- want to be able to roll back changes
BEGIN ATOMIC
-- Local variable declaration
DECLARE v_prodcount INTEGER;
DECLARE v_prodid_existing INTEGER;
DECLARE v_result_set_end INTEGER DEFAULT 0;
DECLARE UNDO HANDLER FOR SQLEXCEPTION
BEGIN
SET v_result_set_end = 1;
END;
SET v_prodcount = 0;
SELECT COUNT (*)
INTO   v_prodcount
FROM   product
WHERE  UPPER(prod_description_s) = UPPER(v_proddesc);
IF (v_prodcount) = 0 THEN
-- Insert row into PRODUCT based on arguments passed
INSERT INTO product
VALUES
(
v_prodid,
v_prodprice,
v_prodnum,
v_proddesc,
v_prodstatus,
v_prodbrand,
v_prodpltwid,
v_prodpltlen,
v_prodnetwgt,
v_prodshipwgt
);
ELSEIF (v_prodcount = 1) THEN
-- Find the product's primary key number
SELECT prod_id_n
INTO   v_prodid_existing
FROM   product
WHERE  UPPER(prod_description_s) = UPPER(v_proddesc);
-- Update the existing product with values
-- passed as arguments
UPDATE product
SET    prod_price_n = v_prodprice,
prod_description_s = v_proddesc,
prod_status_s = v_prodstatus,
prod_brand_s = v_prodbrand,
prod_pltwid_n = v_prodpltwid,
prod_pltlen_n = v_prodpltlen,
prod_netwght_n = v_prodnetwgt,
prod_shipweight_n = v_prodshipwgt
WHERE  prod_id_n = v_prodid_existing;
END IF;
-- perform COMMIT
COMMIT;
END
@@

To compile the foregoing stored procedure using DB2's Command Line Processor tool (CLP), use this syntax (assuming the above code is saved in file C:\SQLBIB\DB2\SP_PRODUCT.SQL):

db2 –td@ -vf "C:\SQLBIB\DB2_ACME\SP_PRODUCT.SQL"

MS SQL Server 2000

A MS SQL Server Transact-SQL stored procedure also consists of the header and the body; the variable declarations are done in the procedure body. The syntax to create the stored procedure with the foregoing functionality might be as follows:

CREATE PROCEDURE sp_productadd
/* This procedure adds new product to PRODUCT table */
@v_prodid              INTEGER,
@v_prodprice           MONEY,
@v_prodnum             VARCHAR (10),
@v_proddesc            VARCHAR (44),
@v_prodstatus          CHAR,
@v_prodbrand           VARCHAR (20),
@v_prodpltwid          DECIMAL(5, 2),
@v_prodpltlen          DECIMAL(5, 2),
@v_prodnetwgt          DECIMAL(10, 3),
@v_prodshipwgt         DECIMAL(10, 3)
AS
-- Local variable declaration and preassignment
declare @v_prodcount INTEGER
select @v_prodcount = 0
declare @v_prodid_existing INTEGER
BEGIN
-- Begin transaction
BEGIN TRANSACTION
-- Check if product with this name already exists
SELECT @v_prodcount=COUNT(*)
FROM   product
WHERE  UPPER(prod_description_s) = UPPER(@v_proddesc)
-- Check for errors
IF @@error <> 0 GOTO E_General_Error
-- Product does not exist
IF @v_prodcount = 0
-- Insert row into PRODUCT based on arguments passed
INSERT INTO product
VALUES
(
@v_prodid,
@v_prodprice,
@v_prodnum,
@v_proddesc,
@v_prodstatus,
@v_prodbrand,
@v_prodpltwid,
@v_prodpltlen,
@v_prodnetwgt,
@v_prodshipwgt
)
-- Check for errors
IF @@error <> 0 GOTO E_General_Error
-- Product with this name already exists
ELSE IF @v_prodcount = 1
-- Find the product's primary key number
SELECT @v_prodid_existing = PROD_ID_N
FROM   product
WHERE  UPPER(prod_description_s) = UPPER(@v_proddesc)
-- Check for errors
IF @@error <> 0 GOTO E_General_Error
-- Update the existing product with
-- values passed as arguments
UPDATE product
SET    prod_price_n = @v_prodprice,
prod_description_s = @v_proddesc,
prod_status_s = @v_prodstatus,
prod_brand_s = @v_prodbrand,
prod_pltwid_n = @v_prodpltwid,
prod_pltlen_n = @v_prodpltlen,
prod_netwght_n = @v_prodnetwgt,
prod_shipweight_n = @v_prodshipwgt
WHERE  prod_id_n = @v_prodid_existing
-- Check for errors
IF @@error <> 0 GOTO E_General_Error
-- No errors; perform COMMIT and exit
COMMIT TRANSACTION
RETURN
-- If an error occurs, rollback and exit
E_General_Error:
PRINT 'Error'
ROLLBACK TRANSACTION
RETURN
END

You probably notice some differences between Oracle (or DB2 UDB) and MS SQL Server syntax; for example, in MS SQL Server arguments are not enclosed in parentheses, the error handling is done in a different way, the variables are prefixed with at-signs, and so on. The stored procedure can be compiled using OSQL command-line tool, Query Analyzer, or other MS SQL Server-compatible tools; to execute this stored procedure you can use the following syntax:

EXEC SP_PRODUCTADD
1, 23.67, 1, 'TEST PRODUCT', 'Y', 'TEST', 1, 3, 45, 33

Removing a stored procedure

The basic syntax to remove a stored procedure is identical for all three databases:

DROP PROCEDURE [qualifier.]<procedure_name>