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>

Note 

Transact-SQL lets you drop multiple procedures within a single DROP PROCEDURE statement. The procedure names have to be separated by commas.