Introduction to Stored Procedures in Sybase SQL Anywhere
Handling Sybase Events with Triggers and Procedures Using SQL – Part 6
Division 5
Forward: In this part of the series, I introduce you to Stored Procedures in Sybase SQL Anywhere.
By: Chrysanthus Date Published: 27 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
A Procedure
A trigger is an immediate response to an event. I trigger is called automatically from an update, insert or delete event in a table. A procedure is like a trigger, but it is not called automatically. It has more flexibility in its coding and can do more analysis than a trigger. A trigger can call a procedure to broaden it activity and power.
A procedure can return a result set (columns and rows) but I will not go into that in this series.
Procedure Owner
A procedure is own just as a table is owned. To can convert an ordinary user to an owner using the GRANT statement as we saw in one of the previous parts of the series. After that the owner can create his own procedures. The DBA can also create procedures. Remember that the DBA owns everything. When a procedure is created it is automatically saved. You do not need any special statement to save a procedure. In other words, when a procedure is created it is stored.
In simple terms the CREATE PROCEDURE Statement is:
CREATE [ OR REPLACE] PROCEDURE [ owner.]procedure-name
( [ parameter, ... ] )
compound-statement
parameter :
parameter-mode parameter-name data-type [ DEFAULT expression ]
parameter-mode :
IN
| OUT
| INOUT
Remember, anything in square brackets is optional. | means and/or in its position.
procedure-name
The procedure-name is the name you give to the procedure.
parameter
parameters are separated by commas; non is obligatory. The syntax of each parameter is:
parameter-mode parameter-name data-type [ DEFAULT expression ]
parameter-mode can be IN, or OUT or INOUT. parameter-name is a name of your choice. data-type is a table column data type. expression is usually a value, which is a number or a string. When you use DEFAULT, it means you are giving the parameter-name a value.
IN means the parameter-name represents an expression (value) that the procedure would receive from outside and use.
OUT means the parameter-name represents a value that can be read from outside the procedure. This value is normally given within the procedure after the procedure has done some analysis.
INOUT stands for both IN and OUT.
A compound-statement is a BEGIN statement.
Dropping a Procedure
Only the owner of a procedure or a DBA can drop (delete from disk) a procedure. The syntax is:
DROP PROCEDURE [ IF EXISTS ] [ owner.]procedure-name;
Calling a Procedure
A compound statement or the compound statement of a trigger can call a procedure. When a procedure is called, the statements in the procedure’s compound statement are executed. The calling environment can then make use of the results of the procedure. The syntax of the SQL Calling statement is:
[variable = ] CALL procedure-name ( [ parameter-name = expression, ... ] )
We shall see examples of all these syntaxes in the following parts of the series.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT