Stored Procedures [EA User Guide]

What is a Stored Procedure?

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. You can compile and execute stored procedures with different parameters and results, and they can have any combination of input, output and input/output parameters.

Enterprise Architect models stored procedures as operations of a Class in accordance with the UML Profile for Data Modeling. Alternatively, you can model stored procedures as individual Classes.

Note: Stored procedures are currently supported for DB2, SQL Server, Firebird/Interbase, Informix, Ingres, Oracle 9i and 10g, MySQL, PostgreSQL, Sybase Adaptive Server Enterprise (ASE) and Sybase Adaptive Server Anywhere (ASA).

Create a Stored Procedure as an Operation of a Container Class

To create a stored procedure container Class, follow the steps below:

1.	Select the Settings \| UML menu option. The UML Types dialog displays, at the Stereotypes tab.

2.	In the Stereotype field, type stored procedures.

3.	In the Base Class field, type class.

4.	Click on the Save button, and on the Close button.

5.	Select a suitable diagram.

6.	Select the More tools \| UML \| Class menu option in the Enterprise Architect UML Toolbox.

7.	Click on the Class element in the list of elements and then click on the diagram. If the Class Properties dialog does not automatically display, double-click on the element.

8.	In the Name field, type a name for the Class. Typically, this is the database name.

9.	In the Stereotype field, click on the drop-down arrow and select stored procedures.

10.	Click on the OK button to close the dialog. You now have a stored procedures container.

11.	Open the Class Properties dialog again and in the Database field click on the drop-down arrow and select the target DBMS to model. (The field displays the default database if it has already been set.)

12.	On the Properties dialog, select the Procedures Detail tab and click on the Stored Procedures?/span> button. (Alternatively:

·	Select the stored procedures container and press [F10], or

·	Select Features \| Operations from the context menu.)

The <class name> Operation dialog displays.

13.	In the Name field, type the name of the stored procedure.

14.	In the Return Type field click on the drop-down arrow and select the return type (or use the default value resultset).

15.	In the Stereotype field, ensure that the value is proc.

16.	Click on the Save button.

17.	To add parameters, click on the procedure name in the Operations panel and click on the Edit Parameters button. The Parameters dialog displays.

18.

In the Name field, type the parameter name, and in the Type field click on the drop-down arrow and select the parameter type.

If the parameter is a length type, add the length after the parameter type. For example, select VARCHAR from the drop-down list and type (5) just after it, as the length.

You can also type the values of the Type field directly into the field.

19.	Click on the Save button, and then the Close button. The <class name> Operation dialog redisplays.

20.	Click on the Behavior tab. In the Initial Code field, type the text of the procedure.

Note:

·	If using the parameter feature as described above, you only have to add the procedure statements after the AS clause.

·	If you prefer not to use the parameter feature as described above, insert the entire stored procedure text in the Initial Code field.

·	In either case, the create procedure... text or create or replace procedure... text must be the first line in the Initial Code field.

StoredProcedure

StoredProcedureBehavior

21.	Click on the Save button, and then the Close button.

Create a Stored Procedure as an Individual Class

To create a stored procedure as an individual Class, follow the steps below:

1.	Select the Settings \| UML menu option. The UML Types dialog displays, at the Stereotypes tab.

2.	In the Stereotype field, type procedure.

3.	In the Base Class field, type class.

4.	Click on the Save button, and on the Close button.

5.	Select a suitable diagram.

6.	Select the More tools \| UML \| Class menu option in the Enterprise Architect UML Toolbox.

7.	Click on the Class element in the list of elements and then click on the diagram. If the Class Properties dialog does not automatically display, double-click on the element.

8.	In the Name field, type a name for the procedure.

9.	In the Stereotype field, click on the drop-down arrow and select procedure.

10.	Click on the OK button to close the dialog. The new procedure element displays.

11.	Double-click on the procedure element. The Procedure <name> dialog displays.

12.	In the Procedure definition field, type the entire procedure text.

13.	Click on the Save button, and then the Close button.