Examples: Create Procedure

The following are Create Procedure statement examples:

1. This database procedure, mark_emp, accepts an employee ID number and a label string as input. The employee matching that ID is labeled and an indication is returned.

   create procedure mark_emp
       (id integer not null, label varchar(100)) as
   begin
       update employee
       set comment = :label
       where id = :id;
       if iirowcount = 1 then
       message 'Employee was marked';
       commit;
       return 1;
       else
       message'Employee was not marked - record error';
       rollback;
       return 0;
       endif;
   end;

2. In this example, the database procedure, add_n_rows, accepts as input a label, a base number, and a number of rows. It inserts the specified number of rows into the table blocks, starting from the base number. If an error occurs, the procedure terminates and the current row number is returned.

   create procedure add_n_rows
       (base integer not null, n integer,
       label varchar(100)) as
   declare
       limit integer;
       err integer;
   begin
       limit = base + n;
       err = 0;
       while (base < limit) and (err = 0) do
       insert into blocks values (:label, :base);
       if iierrornumber > 0 then
           err = 1;
       else
           base = base + 1;
       endif;
       endwhile;
       return :base;
   end;

3. The following example illustrates the use of global temporary tables as procedure parameters. The database procedure, gttproc, accepts as input a surrogate table name; gtt1 is defined as a "set of" parameter to the gttproc procedure and is used in the from clause of a select statement in the body of the procedure.

   create procedure gttproc
       (gtt1 set of (col1 int, col2 float not null, col3 char(8))) as
   begin
   ...
       insert into table1
       select * from gtt1;
   ...
   end;

4. The following example illustrates the use of parameter modes to effect the return of the customer name and zipcode column values for a given cusomer number to the calling procedure.

   create procedure getnamezip (IN custno int not null, OUT custname, OUT custzip) as

   begin

   ...

       select c_name, c_zip into :custname, :custzip from customer where c_id = :custno;

   ...

   end;