Chapter 18: Rose Data Modeler

In the Fill Factor field (or PCT Threshold field for Oracle), optionally enter the free percentage (1–100) of the index.

Adding Stored Procedures

Like a trigger, a stored procedure is a piece of functionality in the database. It is essentially a small program that can be invoked directly by the application or by a trigger. It can accept input parameters and return one or more values, called output parameters.

Some DBMS packages support two types of stored procedures: regular stored procedures and functions. A function returns a value, while a stored procedure does not. A stored procedure can, however, return an output parameter.

In Rose, a stored procedure is modeled as an operation with the stereotype <<SP>>. It is created within a special class with a stereotype of <<SP Container>>. A stored procedure is not specific to a table, and is therefore created underneath the schema rather than underneath a table.

To add a stored procedure:

1.

Right−click the schema in the browser.

2.

Select Data Modeler → New → Stored Procedure.

3.

Rose will create a stored procedure container and place the new stored procedure in it. To create a stored procedure inside an existing container, right−click the existing container and select New → Stored Procedure.

4.

636

Chapter 18: Rose Data Modeler

Right−click the new stored procedure and select Open Specification.

5.

On the General tab, enter the following:

♦

In the Name field, enter the name of the stored procedure.

♦

In the Language field, enter the language for the stored procedure. In most cases, the language will be SQL. Some DBMS packages also support other languages, such as C, Java, or COBOL.

♦

In the External Name field, enter the path or library for the procedure. This field is not needed if the language is set to SQL.

♦

In the Type field, enter the type (procedure or function) of stored procedure. A function returns a value while a stored procedure does not. Not all DBMS packages support functions.

♦

In the Return Type field, enter the data type of the value returned from a function. This field is not needed unless the stored procedure is a function.

♦

In the Length field, enter the number of characters in the return value. This field is not needed for all data types.

♦

In the Precision field, enter the number of digits to the right of the decimal point in the return value. This field is not needed for all data types.

♦

In the Scale field, enter the number of digits in the return value. This field is not needed for all data types.

♦

The Null Input Action (DB2) field controls what should happen if the function receives a null parameter. Return Null will return a null value from the function. Call Procedure will cause the function to run even with a null parameter.

♦

The Parameter Style (DB2) field sets the way parameters should be sent to and received from a stored procedure.

637

Chapter 18: Rose Data Modeler

6.

On the Parameters tab, enter any needed parameters. Right−click the white space and select Insert to add a new parameter. Enter the following:

♦

Parameter data type.

♦

Length, precision, and scale, if necessary.

♦

Direction: In is used for an input parameter. Out is used for an output parameter, which is similar to a return value. In Out is used for an input parameter that may be changed by the stored procedure and is then output to the calling application or trigger.

♦

Default value, if needed.

638