Examples of procedures

Example 1: Access check

The CUSTOMERS_LOGIN procedure below implements a simple form of access check for customers. It belongs to the sample procedures in the demonstration database of SESAM/SQL (see the “ Core manual”).

In the demonstration database you will find further, detailed examples of sample procedures embedded in an order system.

The CUSTOMERS_LOGIN procedure uses only the CONTACTS table from the demonstration database. A check is made to see whether the customer is already stored in the table.

***********************************************************************
* Define CUSTOMERS_LOGIN procedure
***********************************************************************
SQL CREATE PROCEDURE CUSTOMERS_LOGIN                                    (1)
( -
   IN  PAR_CUST_NUM     INTEGER,                                        (2)
   IN  PAR_CONTACT_NUM  INTEGER, 
   OUT PAR_STATUS       CHAR(40), 
   OUT PAR_TITLE        CHAR(20), 
   OUT PAR_LNAME        CHAR(25) 
) 
READS SQL DATA                                                          (3)
BEGIN                                                                   (4)
   /* Variables definition     */                                       (5)
   DECLARE VAR_EOD SMALLINT DEFAULT 0; 
   /* Handler definition       */                                       (6)
   DECLARE CONTINUE HANDLER FOR NOT FOUND 
      SET VAR_EOD = 1;                                                  (7)
   /* Statements              */                                        (8)
   SET PAR_TITLE   = ' '; 
   SET PAR_LNAME = ' '; 
   /* Check whether customer is already known      */ 
   SELECT TITLE, LNAME INTO PAR_TITLE, PAR_LNAME 
      FROM CONTACTS 
      WHERE CONTACT_NUM = PAR_CONTACT_NUM 
      AND   CUST_NUM  = PAR_CUST_NUM; 
   IF VAR_EOD = 1 THEN                                                 (9)
      SET PAR_STATUS = 'Customer unknown'; 
   ELSE 
      SET PAR_STATUS = 'Login successful'; 
   END IF; 
END                                                                    (10)

1. Procedure header with details of the procedure name (the database and schema names are predefined).

2. List of the procedure parameters.

3. The procedure can contain SQL statements for reading data, but no SQL statements for updating data.

4. The (only) procedure statement is a (non-atomic) COMPOUND statement. This executes further procedure statements in a common context.

5. Definition of local procedure variables.

6. Definition of exception handling in accordance with the SQLSTATE. In this case the procedure is continued if an SQLSTATE of class 02xxx (no data) occurs.

7. In the event of an exception, the local variable VAR_EOD is set.

8. The procedure statements will follow.

9. The procedure's output fields are supplied with values in accordance with the result of the query statement.

10. End of the COMPOUND statement and procedure.

Example 2: Complex COMPOUND statement

The MyTables procedure below consists of a complex COMPOUND statement and shows the various methods of exception handling. In the central base table mySchema.myTabs it stores the names of the tables which the current authorization identifier may access.

The input parameter par_type specifies whether base tables or views must taken into account. In the case of par_type='B' the names of the base tables are stored, and in the case of par_type='V' the names of the views. The following output parameters are returned:

par_nbr_tables

Total number of table names of the table type concerned (base table or view) which is stored for the current user

par_nbr_new_tables

Number of table names stored in addition for the current user by the procedure call

par_message

Message text (OK or error message)

-- Procedure header
CREATE PROCEDURE ProcSchema.MyTables
   ( IN par_type CHAR(1), OUT par_message CHAR(80),
     OUT par_nbr_tables INTEGER, OUT par_nbr_new_tables INTEGER )
   MODIFIES SQL DATA
-- Procedure body, COMPOUND statement, declaration section
myTab: BEGIN ATOMIC
   DECLARE var_table_type CHAR(18);
   DECLARE var_schema_name,var_table_name CHAR(31);
   DECLARE var_eot SMALLINT DEFAULT 0;
   DECLARE var_nbr_old_tables INTEGER DEFAULT 0;
   DECLARE myCursor CURSOR FOR
           SELECT table_schema, table_name
           FROM information_schema.tables
           WHERE table_type = var_table_type;
-- Error routines
   DECLARE EXIT HANDLER FOR SQLSTATE '42SND'
           SET par_message = 'catalog ' || CURRENT_REFERENCED_CATALOG 
                                        || ' not accessible';
   DECLARE CONTINUE HANDLER FOR SQLSTATE '23SA5'
   -- Primary key not unique
           SET var_nbr_old_tables = var_nbr_old_tables + 1;
   DECLARE EXIT HANDLER FOR SQLSTATE '42SQK'
           SET par_message = 'table MyTabs not accessible';
   DECLARE UNDO HANDLER FOR SQLEXCEPTION
           BEGIN -- COMPOUND statement
           SET par_message = 'unexpected error';
           SET par_nbr_tables = 0;
           SET par_nbr_new_tables = 0;
           END;
   DECLARE CONTINUE HANDLER FOR SQLWARNING
           SET par_message = 'warning ignored';
   DECLARE CONTINUE HANDLER FOR NOT FOUND
           SET var_eot = 1;
-- Set initial values
   SET par_message = 'OK';
   SET par_nbr_tables = 0;
   SET par_nbr_new_tables = 0;
   IF par_type = 'V' THEN SET var_table_type = 'VIEW';
      ELSEIF par_type = 'B' THEN SET var_table_type = 'BASE TABLE';
      ELSE SET par_message = 'wrong input parameter par_type';
           LEAVE myTab;
   END IF;
-- Procedure statements
   OPEN myCursor;
   loop1: LOOP
          FETCH myCursor INTO var_schema_name, var_table_name;
          IF var_eot = 1 -- Set by error handler for error class 'not found'
             THEN LEAVE loop1; -- End of tables reached
          END IF;
          INSERT INTO mySchema.myTabs VALUES
                 (var_schema_name, var_table_name, var_table_type,
                  current_user, current_date);
          SET par_nbr_tables = par_nbr_tables + 1;
   END LOOP loop1;
   CLOSE myCursor;
   SET par_nbr_new_tables = par_nbr_tables - var_nbr_old_tables;
   -- var_nbr_old_tables set by error handler for SQLSTATE '23SA5'
END myTab

Example 3: Different CALLs

The min_service_price procedure returns the lowest service record for this order on the basis of the order number transferred.
If the NULL value was transferred as the order number, the value -999 is returned as the service record.
If the order number exists but the service record is not significant in any of the rows concerned, the NULL value is returned.
If the order number does not exist, the CALL statement is terminated with SQLSTATE ("no data").

-- Procedure header
   CREATE PROCEDURE min_service_price
   ( IN in_anr CHAR(8), OUT out_service_price NUMERIC(6) ) 
   READS SQL DATA
-- Procedure body
   IF in_anr IS NULL THEN out_service_price = -999; 
      ELSE SELECT MIN(service_price) INTO out_service_price FROM service
      WHERE anr = in_anr; 
   END IF

The reactions to various CALLs of the procedure are illustrated using this procedure.

It must be noted that the in_anr and out_service_price parameters have no indicators (not permitted). The significance of in_anr is checked directly via IS NULL. Output parameter out_service_price can be assigned the NULL value directly in the INTO clause.

Various static CALL statements will now be examined. The argument for the input value can be presented in very different ways. On the other hand a host variable must always be specified as an argument for the output value. It must have a numeric data type (compatible with NUMERIC(6)). It also makes sense to use an indicator variable which must be initialized with -1 before the CALL. Otherwise the host variable itself must have been initialized with a correct value (according to its data type).

CALL min_service_record(:anr, :service_price INDICATOR :ind-service_price)

The input value is transferred as a host variable. As the NULL value can be returned, it makes sense to specify an indicator variable for the output value.

CALL min_service_record(:anr :ind-anr, :service_price :ind-service_price)

As above, but setting :ind-anr to -1 means that the NULL value can also be transferred.

CALL min_service_record('A#123456', :service_price)

The specific input value is A#123456. If the NULL value is to be returned for this, the specification of an indicator variable is missing, which results in an SQLSTATE SEW2202.

CALL min_service_record(CAST(NULL AS CHAR (8)), :service_price)

As the input value is NULL, the value -999 is returned. As the host variable :service_price has no indicator, it must have been initialized with the correct value (according to its data type) before the call.

CALL min_service_record((SELECT MAX(anr) FROM leistung),:service_price :indservice_price)

The input value is the highest order number. As the NULL value can be returned, it makes sense to specify an indicator variable for the output value. If the service table is empty, the NULL value is then returned.