Stored Routine Editor

Top Previous Next

The Stored Routine Editor is provided for altering stored procedures/functions. All the parameters of stored procedure are valid for stored function, so you can switch between them easily.

To open the the Stored Routine Editor, simply double-click a stored procedure or function on the diagram or select the Properties item from the context menu.

The Stored Routine Editor consists of several tabs, each of which will be described below.

General

editor-sp-general

This tab allows you to tune the basic properties of a stored procedure or function.

Stored Routine Name
The name of the stored routine must be unique within a schema. To check your diagram for the uniqueness of names use the Check Diagram tool.

Security Type
This option can be used to specify whether the routine should be executed using the permissions of the user who creates the routine or the user who invokes it. The default value is DEFINER. The creator or invoker must have permission to access the database with which the routine is associated.

Routine is deterministic
This option adds DETERMINISTIC clause to CREATE PROCEDURE statement. A procedure or function is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise.

SQL Data Access
This option provides information about the nature of data use by the routine. In MySQL, these characteristics are advisory only. The server does not use them to constrain what kinds of statements a routine will be allowed to execute.

Stored routine definer
This optional parameter specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the DEFINER security type.

Comment
A native database comment for your routine.

Generate
Set this option off to exclude the routine from the default selection in the Database Generation and Database Modification tools.

Definition

editor-sp-definition

The Definition tab allows you to choose the routine type (stored procedure or function), determine the list of parameters, edit routine text.

Routine Type
This options helps you switch between stored procedure and function.

Parameters
This grid contains the list of all procedure parameters. To add a new parameter simply proceed to the first empty node and fill in the cells.

Returns
This option is available only if you choose Function in Routine Type drop-down list. It sets the type of variable being returned by the function.

Routine Body
This text editor represents the routine text, which appears between BEGIN and END keywords in CREATE PROCEDURE statement.

Quick Create button
This button allows to execute CREATE PROCEDURE statement with current procedure body. This can be useful when you want to test procedure without closing editor.

Script

This tab allows you to set SQL statements, which will be executed before (use Begin tab) and after (use End tab) generation of the stored routine.

Preview

The Preview tab displays the SQL statement, which will be executed during the database generation. This statement is made up according to the changes you have made using previous tabs. Note, that the text within the editor is read-only.

Notes

The Notes tab allows you to define a description and an annotation for the edited stored routine. This properties will not affect the physical database, but they can be useful for your diagram development.

Format

These options allow you to set the routine line and fill color for displaying on the diagram, different from the default colors.