Show TOC

Stored Procedures and Functions (PDM)Locate this document in the navigation structure

You can define stored procedures and functions for any DBMS that supports them.

A stored procedure is a precompiled collection of SQL statements stored under a name and processed as a unit. Stored procedures are stored within a database; can be executed with one call from an application; and allow user-declared variables, conditional execution, and other programming features.

The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time).

A user-defined function is a form of procedure that returns a value to the calling environment for use in queries and other SQL statements.


In this section:
    Creating a Stored Procedure or Function
    You can create a stored procedure or function from a table property sheet or from the Toolbox, Browser, or Model menu.
    Procedure Properties
    To view or edit a procedure's properties, double-click its diagram symbol or Browser or list entry. The property sheet tabs and fields listed here are those available by default, before any customization of the interface by you or an administrator.
    Tracing Trigger and Procedure Dependencies
    When you write a trigger or procedure, PowerDesigner automatically creates dependencies to any table, view, procedure, or database package referenced in the code. These dependencies are taken into account when performing an impact analysis prior to deleting the trigger or procedure or objects on which they depend. For procedures, if the procedure has a symbol in your diagram, then any dependencies will be shown graphically by way of arrows linking the procedure to these objects.
    Attaching a Stored Procedure to a Table
    You can attach a stored procedure to a table when your current DBMS supports stored procedures. This feature lets you update the table or retrieve information from this table.
    Procedure Templates (PDM)
    PowerDesigner procedure templates allow you to write table procedures in a modular reusable fashion. We provide basic templates for insert, select, update, and delete procedures. You can modify the code specified in these templates or create your own templates in the DBMS definition file.
    Creating SQL/XML Queries with the Wizard
    You can use the SQL/XML Wizard to insert a SQL/XML query in the definition of the body of a trigger, stored procedure, or function to store or retrieve data, in an XML format, from relational databases supporting SQL/XML. The wizard, allows you to select tables and views from a PDM to build a mapped XML model. This XML model (which does not appear in the workspace) is used to generate SQL/XML queries from global elements.