Creating Stored Procedures

A stored procedure is a function in database management systems used for calling procedure tracking.

Context

A stored procedure enables a sequence of stored commands to be executed. Flows that you frequently use and would normally have to carry out lots of individual commands from the client to be run can be stored as stored procedures on the database system. You can then carry out the sequence of commands in one go (CALL or EXECUTE). This improves performance as less data has to be exchanged between client and database system and the database management system normally runs on powerful servers.

As well as the normal syntax query language (usually Structured Query Language (SQL)), you can also add additional commands for flow management in stored procedures. The SQL used is often extended to include manufacture-specific functions.

Stored procedures add to the reliability of an application. As the client does not normally require DELETE, INSERT or SELECT access rights, attackers cannot execute SQL commands themselves. The client can only call predefined procedures; this enables developers to avoid undesired third parties being elicited.

Various implementing languages are used for stored procedures.

Database system	Implementing language
DB2 (IBM)	SQL PL1A subset of ISO-standardized SQLPM (SQL Persistent Modules), C++ and Java can be used on the database side of things.
Informix (IBM)	SPL2Stored Procedure Language is an Informix extension of SQL that provides flow management functions (e.g. sequence generation or creation of branches and loops).
Oracle (Oracle)	PL/SQL3Procedural Language/SQL is a proprietary programming language of the firm Oracle. It combines the query language SQL with a procedural programming language. Variables, conditions, loops and exception handling are supported. and Java
Microsoft SQL Server (Microsoft)	TSQL4Transact SQL is an extension of the SQL standard created by Sybase and Microsoft. It deals with error handling, row processing and variable declaration. and diverse .NET Framework languages
MySQL (Oracle)	SQL:2003

Creating a Stored Procedure

Prerequisites

A database schema is present.

Context

The aim is to model the saved procedure of a database management system.

How to proceed

Select the database schema in the model structure below which you want to create the new saved procedure.
Select Start>New>New Element>Saved procedure.

A new saved procedure is created below the selected database schema. The name of the saved procedure is automatically selected for renaming.
Enter the name for the saved procedure and confirm with the [Enter] key.
Select New SubelementProcedure expression in the context menu.

A new procedure expression is created below the selected procedure.

Modifying a Stored Procedure

How to proceed

Select the saved procedure in the model structure.

The associated procedure expressions are listed in the Details tool window.
In the Details tool window, select a procedure expression.
In the Properties tool window, change the value in the Expression field.

Deleting a Saved Procedure

How to proceed

Select the saved procedure in the model structure.
Select Start>Organize>Delete from Model (Shift+Del).