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 PL1

Informix (IBM)

 SPL2

Oracle (Oracle)

 PL/SQL3 and Java

Microsoft SQL Server (Microsoft)

 TSQL4 and diverse .NET 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

  1. Select the database schema in the model structure below which you want to create the new saved procedure.

  2. 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.

  3. Enter the name for the saved procedure and confirm with the [Enter] key.

  4. 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

  1. Select the saved procedure in the model structure.

    The associated procedure expressions are listed in the Details tool window.

  2. In the Details tool window, select a procedure expression.

  3. In the Properties tool window, change the value in the Expression field.

Deleting a Saved Procedure

How to proceed

  1. Select the saved procedure in the model structure.

  2. Select Start>Organize>Delete from Model (Shift+Del).