How to Implement a Database Procedure¶

To implement a database procedure, follow these basic steps:

1. Create the procedure using the CREATE PROCEDURE statement. You can do this interactively or in Embedded SQL.

2. Grant EXECUTE permission on the database procedure to specified users, groups, or roles, as described in Object Permissions (Grants).

3. Invoke the database procedure by issuing an EXECUTE PROCEDURE statement or triggering a security alarm. Any user who has been granted EXECUTE permission can perform this step.

Database Procedure Example¶

The following database procedure accepts as input an employee ID number. The employee matching that ID is moved from the employee table and added to the emptrans table.

CREATE PROCEDURE move_emp

   (id INTEGER NOT NULL) AS

BEGIN

   INSERT INTO emptrans

      SELECT * FROM employee

         WHERE id = :id;

   DELETE FROM employee

      WHERE id = :id;

END;