| Prev | Next |
Database Procedures
Database Procedures (sometimes referred to as Stored Procedures or Procs) are subroutines that can contain one or more SQL statements that perform a specific task. They can be used for data validation, access control, or to reduce network traffic between clients and the DBMS servers. Extensive and complex business logic can be embedded into the subroutine, thereby offering better performance.
Database Procedures are similar to Database Functions. The major difference is the way in which they are invoked - Database Functions can be used like any other expression within SQL statements, whereas Database Procedures must be invoked using the CALL or EXEC statement, depending on the DBMS.
In Enterprise Architect, Database Procedures can be modeled in one of two ways:
- As individual objects (the default method) or
- As operations in a container
Functionally the two methods result in the same DDL being produced. The main difference is visual - by having several Operations in one container, you have fewer elements and less clutter on the diagram.
Individual objects
Database Procedures modeled as individual objects are UML Classes with the stereotype «procedure»; you create these either:
- Within the Database Builder or
- By dragging the 'Procedure' icon from the Data Modeling Toolbox onto a diagram
Add a Database Procedure using the Database Builder
Step |
Action |
See also |
|---|---|---|
|
1 |
Open the Database Builder. |
The Database Builder |
|
2 |
Load or create a Data model. |
|
|
3 |
Right-click on the Procedures Package and select 'Add New Procedure'. |
|
|
4 |
Overtype the default name with the appropriate name for the Procedure, and press the . |
|
|
5 |
Double-click on the new Procedure, or right-click on it and select 'SQL Object Properties'. The SQL Object Editor screen displays. |
Add a Database Procedure to a diagram
Step |
Action |
See also |
|---|---|---|
|
1 |
Open your Data Modeling diagram and, if necessary, display the 'Data Modeling' page of the Diagram Toolbox (click on |
|
|
2 |
Drag the 'Procedure' icon onto the diagram.
This generates the Procedure element:
|
|
|
3 |
Right-click on the new Procedure element and select 'SQL Object Properties'. The SQL Object Editor screen displays. |
to display the 'Find Toolbox Item' dialog and specify 'Data Modeling').
SQL Object Editor
The 'SQL Object Editor' dialog is shared by a number of SQL-based database objects (Views, Procedures and Functions); it helps you to manage the various properties of the SQL-based object.
Option |
Action |
See also |
|---|---|---|
|
Database |
If it has already been set, the default database type displays. If the default has not been set, or you want to change the database type for this Procedure, click on the drop-down arrow and select the target DBMS to model. |
Set the Database Type |
|
Notes |
If necessary, type in a comment on the current Procedure. |
|
|
Definition |
Type the full SQL Procedure definition, including the CREATE PROCEDURE syntax. The code editor provides Intelli-sense for basic SQL keywords, functions and names of all objects in the current data model. |
Options - Code Editors Intelli-sense |
Operations in a Container
Database Procedures modeled as operations have a container object, this being a UML Class with the stereotype «procedures» (with an s on the end). Each Database Procedure is an operation with the stereotype «proc». The system provides a dedicated Maintenance window through which you can easily manage the Database Procedures defined as operations.
Learn more
