Procedures

A stored procedure is a named set of SQL statements that is stored in the database in compiled form so that a number of programs can share it.

Using Director, you can create or delete user procedures. You can also set permissions and view their properties.

System procedures are generated internally by the database to support database objects such as constraints and foreign keys. System procedures cannot be created, modified, or deleted. They are stored in a separate folder under the Procedures folder.

Note: Procedures are not available for Actian Vector.

The Database Procedure dialog contains the following pages:

Lets you specify a name for the procedure and enter the queries to be executed when the procedure is run. Procedure names must follow Object Naming Rules.

Lets you specify parameters to use in the procedure. Right-clicking a table row and selecting Insert Parameter creates a new parameter. For each parameter, you may specify:

To delete a parameter, right-click on its table row and select Delete Parameter from the context menu.

Lets you declare variables to use in the procedure. Right-clicking a table row and selecting Insert Variable creates a new variable. For each variable, you may specify:

To delete a variable, right-click on its table row and select Delete Variable from the context menu.

For more information, see Create a Procedure.

Before you can create a database procedure, the installation containing the database must be connected in the Instance Explorer (see Connect to an Actian Database Instance).

Note: Procedures are not available for Actian Vector.

You create procedures using the New Database Procedure dialog (see Procedure Dialog). You can open this dialog the following ways:

• From the context menu of a database’s Procedure folder or procedure node: New Procedure

• On the main menu ribbon when a Database node is selected in the Instance Explorer: Database, Procedure, New Procedure (see Database Tab)

1. Open the New Database Procedure dialog in one of the ways explained previously.

2. On the General page, specify a name for the procedure (see Object Naming Rules).

3. In the text box, enter the queries to be executed when the procedure is called.

The “BEGIN” and “END” markers are provided to indicate which parts of the procedure definition should be entered in the text box.

4. Click the Parameters link in the “Select a page” portlet.

5. In the first empty row of the Database Procedure Parameters table, right-click and select Insert Parameter from the context menu.

6. Specify a parameter name, select a data type and a parameter type, choose whether nulls are allowed, and select whether the parameter type is the default type.

To delete a parameter, right-click on the row and select Delete Parameter from the context menu.

8. Click the Declared Variables link in the “Select a page” portlet.

9. In the first empty row of the Parameter Variable Declarations table, right-click and select Insert Variable from the context menu.

10. Specify a variable name, select a data type, choose whether nulls are allowed, and select whether the data type is the default type.

To delete a variable, right-click on the row and select Delete Variable from the context menu.

12. When you are finished, click OK on the General page.

The procedure is created and stored in the Procedures folder of the database.

Before you can modify a database procedure, the installation containing the database must be connected in the Instance Explorer (see Connect to an Actian Database Instance).

When you modify a procedure, Director creates SQL to drop and create the original stored procedure and places it in a new tab in the Multiple Document Interface. You may modify the CREATE PROCEDURE statement, execute it, or save the edited SQL to a file.

You modify a procedure with the Modify Procedure document. You can open this document the following ways:

• From the context menu of a database’s procedure node: Modify

• On the main menu ribbon when a sequence node is selected in the Instance Explorer: Database, Procedure, Modify Procedure (see Database Tab)

1. In the Instance Explorer, right-click the procedure node and select Modify from the context menu.

A new tab is opened in the Multiple Document Interface, displaying the SQL text of the procedure.

3. Execute and/or save the statements to a file (see Save a Query).

Before you can delete a database procedure, the installation containing the database must be connected in the Instance Explorer (see Connect to an Actian Database Instance).

You delete procedures using the Delete Procedure dialog. You can open this dialog the following ways:

• From the context menu of a procedure node: Delete

• On the main menu ribbon when a procedure node is selected in the Instance Explorer: Database, Procedure, Delete Procedure (see Database Tab)

1. Open the Delete Database Procedure dialog in one of the ways explained previously.

The procedure is removed from the Procedures folder of the database.

Before you can view the properties of a database procedure, the installation containing the database must be connected in the Instance Explorer (see Connect to an Actian Database Instance).

You view procedure properties using the Procedure Properties dialog. You can open this dialog the following ways:

• From the context menu of a procedure node: Properties

• On the main menu ribbon when a procedure node is selected in the Instance Explorer: Database, Procedure, Procedure Properties (see Database Tab)

1. Open the Procedure Properties dialog in one of the ways explained previously.

The name and owner of the procedure are displayed.

2. Click the page links to view parameters and the procedure definition.

3. Click specific properties to display their descriptions at the bottom of the dialog.

You may copy the procedure definition by clicking in the text area, selecting all text, and then clicking Copy.