Physical Modeling › Stored Procedures

Stored Procedures

Stored procedures are a named set of precompiled SQL statements that are stored on the server and are invoked from the client by a remote procedure call. Stored procedures are supported at the model level and for tables and views. For Oracle only, support is also provided for stored procedures for materialized views.

You can reverse engineer the code for a stored procedure or write a stored procedure. After you write stored procedures, you can attach them to individual tables, views, or to the model. You can create a stored procedure and include it in the schema generation script. When you generate a database schema, any template code expands in the stored procedure and is included in the CREATE PROCEDURE statement for each procedure attached to a table, view, or model. You can also drop previously defined stored procedures in the generated schema. You can execute a DROP statement for each CREATE procedure statement in the generated schema.

Some benefits of using stored procedures are as follows:

• Reduces network traffic and execution time
• Eliminates long locks held when the front end requests a transaction
• Write code only once
• Automates complex or sensitive transactions
• Provides permissions that can be granted on procedures, and not on the underlying tables
• Accepts and returns parameters, values, and messages (unlike triggers)

Note: CA ERwin Data Modeler does not support built-in stored procedure templates since there is no standard functionality provided by a stored procedure.

The order in which you add stored procedures to your model is important. The stored procedure code is generated according to the order the stored procedures display in the Stored Procedure Editor. You can change this order at any time by using the up and down arrow buttons on the Object Creation Order tab in the Stored Procedure Editor.

More information:

DBMS Support for Stored Procedures

DBMS Migration

Add a Stored Procedure for a Table or View

Define a Stored Procedure

Define the Stored Procedures SQL Code

Review the Expanded SQL Code for a Stored Procedure

Enter a Comment for a Stored Procedure

Enter Stored Procedure User-Defined Property Values