Stored procedures

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