Procedures and triggers store procedural SQL statements in a database for use by all applications. They can include control statements that allow repetition (LOOP statement) and conditional execution (IF statement and CASE statement) of SQL statements. Batches are sets of SQL statements submitted to the database server as a group. Many features available in procedures and triggers, such as control statements, are also available in batches.
Procedures are invoked with a CALL statement, and use parameters to accept values and return values to the calling environment. SELECT statements can also operate on procedure result sets by including the procedure name in the FROM clause.
Procedures can return result sets to the caller, call other procedures, or fire triggers. For example, a user-defined function is a type of stored procedure that returns a single value to the calling environment. User-defined functions do not modify parameters passed to them, but rather, they broaden the scope of functions available to queries and other SQL statements.
Triggers are associated with specific database tables. They fire automatically whenever someone inserts, updates or deletes rows of the associated table. Triggers can call procedures and fire other triggers, but they have no parameters and cannot be invoked by a CALL statement.
Benefits of procedures, triggers, and user-defined functions
The structure of procedures, triggers, and user-defined functions
Cursors in procedures, triggers, user-defined functions, and batches
Error and warning handling
EXECUTE IMMEDIATE used in procedures, triggers, user-defined functions, and batches
Transactions and savepoints in procedures, triggers, and user-defined functions
Tips for writing procedures, triggers, user-defined functions, and batches
Statements allowed in procedures, triggers, events, and batches
Hiding the contents of procedures, functions, triggers, events, and views
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|