Procedures

From DBArtisan
Jump to: navigation, search

Go Up to Supported Objects

Procedures are a reusable block 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.

Creating and editing

Note: Creation and editing of objects of this type is not supported against Sybase IQ datasources.

DBMS platform availability and object actions/operations supported

The following table lists object actions available for this object type. For an introduction to object actions and details on usage of specific actions, see Object actions.

DB2 LUW DB2 z/OS ORCL SQL SVR SYB ASE SYB IQ

Build

Compile

Create Synonym

Describe

Drop

Execute

Extract

Hide Text

Migrate

Object Properties

Rename

Report

Transfer Ownership

DB2 z/OS Procedures

Only IBM DB2 for OS/390 and z/OS SQL stored procedures created by DBArtisan, or IBM's Stored Procedure Builder can be retrieved by DBArtisan.

Sybase ASE Procedures

Procedures perform procedural logic in your Sybase ASE applications. They are batches of SQL statements that are compiled and stored in the system catalog. Procedures execute faster than embedded SQL statements because they are pre-compiled and have execution plans for use by the optimizer. When you create a procedure, Sybase ASE builds a query tree, which it stores in a system table. When you execute a procedure for the first time, Sybase ASE loads it from the system table, compiles, and optimizes it. Sybase ASE places the resulting query plan in the procedure cache where it remains on a most recently used basis. In addition to better performance, procedures yield other benefits, including easier code maintenance, additional security and reduced network traffic.

Note: Extended procedures are also supported. For details, see Extended Procedures.

Executing Statements Before and After Procedure or Function Creation

With respect to functions and stored procedures, it can be necessary to have statements executed before and after creation of the procedure or function. This can be useful for example, if you need to create or drop temporary tables used by the function or procedure. Two tag pairs, ETStart and ETEnd, let you embed statements in the first comment block of a stored procedure or function. The following shows the expected syntax:

create procedure dbo.procname(@a numeric) as

/*

<ETStart>SQL Statement</ETStart>

<ETEnd>SQL Statement</ETEnd>

*/

begin

...