Creating and Editing Procedures
Go Up to Creating and Editing SQL Procedures
Procedures are reusable blocks of PL/SQL stored in the database that applications can call. Procedures streamline code development, debugging, and maintenance by being reusable. Procedures enhance database security by letting you write procedures granting users execution privileges to tables rather than letting them access tables directly. Also, procedures can increase the efficiency of your database. They can consolidate and centralize logic that might otherwise have existed in multiple applications that access the same database, making the code easier to maintain. Because the procedure executable is the only part stored on the server, decreasing its storage requirements, and the procedure is executed on the server, reducing network traffic.
Procedures are similar to functions except that functions can be used within SQL statements, whereas procedures must be invoked using the CALL statement.
ER/Studio Data Architect supports the following procedure types:
- Regular or scripted procedures: Specific to the table on which they are created. You can write scripted procedures in SQL You can create a scripted procedure through the Table Editor - Dependencies Tab or though the Procedure SQL Editor, which you can access by double-clicking the procedure name in the Procedures node of the Data Model Explorer.
- Templated Procedures: Specific to the table on which they are created or modified. You can write templated procedures in BASIC. Once created, they do not appear in the list of procedures on the Procedures node of the Data Model Explorer.
- Reusable procedures: Created in the data dictionary, reusable procedures can become templated if they are modified through the Table Editor. You can write templated procedures in BASIC. Reusable procedures are templated procedures written in BASIC code. These procedures can be applied to any table, since they use the table's context in the code. The Reusable Procedure node in the Data Dictionary includes DBMS platform nodes. ER/Studio Data Architect organizes reusable procedures by platform. When you want to create, edit, or delete a reusable procedure, right-click the target DBMS node and then click the appropriate short cut menu.
The following database platforms support Procedures:
- Amazon Redshift
- IBM DB2 for LUW
- InterBase
- Microsoft Access
- Microsoft Azure Synapse Analytics
- Microsoft SQL Server
- Oracle
- PostgreSQL
- Snowflake
- Sybase ASA
- Sybase Adaptive Server IQ
- Sybase ASE
- The Northwind.dm1 sample model illustrates how procedures can be used to gather information and perform computations on them to provide summary information.
Notes
- You can view the templated procedures associated with a table on the Dependencies tab of the Table Editor.
- You can view the scripted procedures you create under the Procedures tab of the node.
- In order for code to be generated for procedural logic such as triggers and procedures, the results of the code must be stored in a variable called resultstring. For example, in the Northwind.dm1 sample model, the Data Dictionary lists a reusable trigger, SYSUPDATE, for the Oracle platform. The code for this trigger includes the following statement, which is required for code to be generated for the trigger:
resultstring = trigBody