Database Procedures

A database procedure is a set of SQL statements and control statements in a begin/end block that are stored as a unit in the database. It usually contains at least one query into the database, which is stored in compiled form with a Query Execution Plan. In addition to the advantage of rapid execution, a database procedure can have the execute permission granted on it.

Database procedures provide the following benefits to the user:

• They enhance performance by reducing the amount of communication needed between the application and the DBMS.
• They provide the DBA with an extra level of control over data access and modification.
• One procedure can be used in many applications in a database, which reduces coding time.
• You can use database procedures in conjunction with rules to enforce referential and general integrity constraints. For more information on rules, see the chapter "Ensuring Data Integrity."
• You can use database procedures in conjunction with security alarms to enhance the built-in security-auditing features.

Ways to Work with Procedure Objects

In VDBA, database procedures are implemented using procedure objects. Using the Procedures branch for a particular database in the Database Object Manager window, you can:

• Create database procedures

  Note: By default, any user can create a database procedure, but this ability can be restricted using database permissions.

• View existing database procedures, including the detailed properties of each individual object
• Drop database procedures

For the detailed steps for performing these procedures, see the Procedures section of online help.

You can also accomplish these tasks using the create procedure, help procedure, and drop procedure SQL statements. For more information, see the SQL Reference Guide.

Implement a Database Procedure

To implement a database procedure, follow these basic steps:

1. Create the procedure using the appropriate Procedures branch in the Database Object Manager window of VDBA. For details, see online help.
2. Grant execute permission on the database procedure to specified users, groups, or roles, as described in Object Permissions.
3. Invoke the database procedure by issuing the execute procedure statement, firing a rule, or triggering a security alarm (users must have the execute permission to perform this step).

Example: Database Procedure

For 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. The code for the procedure, which has a procedure name of move_emp, is shown as it is entered in the VDBA Create Procedure dialog:

Parameters:

id integer not null

Statements:

insert into emptrans

  select * from employee where id = :id;

  delete from employee where id = :id;

Database Procedures and Control Over Database Access

Database procedures provide the DBA with greater control over database access.

The DBA can grant permission to execute a database procedure even if the user has no direct access to the underlying tables referenced in the procedure. With execute permissions, the DBA can give users limited, specific access to tables without needing to give the users full query grants (such as select) on the tables. In this way, the DBA controls exactly what operations a user can perform on a database.

For example, both tables used in the previous example can be inaccessible to users except through the procedure. The DBA grants execute permission, for example, to allow users in the acctg group to access the tables for this procedure only.

When the procedure is invoked, the executing application passes a single integer parameter.

For example, the following statement calls the move_emp procedure for the employee ID "56742":

exec sql execute procedure move_emp (id = 56742);