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:
|·||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.
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.
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.
Learning Center topics
|·||(Alt+F1) | Enterprise Architect | Database Engineering | Physical Data Model | Stored Procedures|