Database Procedures

Database Procedures (sometimes referred to as Stored Procedures or Procs) are a feature of some DBMSs. They 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:

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 by dragging the Procedure icon onto a diagram from the Data Modeling Toolbox.

Toolbox icon

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

Learning Center topics