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.
The CREATE PROCEDURE syntax is different among RDBMS implementations. The simplified syntaxes for SQL99 and all our three major RDBMS vendors are provided below:
CREATE PROCEDURE <procedure_name> <procedure_definition>
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;
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
CREATE PROC[EDURE] <procedure_name> [@<parameter_name> <datatype> [ = <default>] [OUTPUT] ] ,... AS <procedure_body>
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.
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);
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"
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
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. |