ODBC Server Reference Manual

HP NonStop ODBC Server Reference Manual—429151-002
5-1
5 Stored Procedures
This section describes the HP NonStop ODBC Server support for execution of stored
procedures by an ODBC application or a DB-LIBRARY application connected to a
NonStop ODBC server on a HP server. The term “NonStop ODBC Server” is used to
distinguish the server process itself from the overall NonStop ODBC Server product.
This section covers the following topics:
•
An introduction to what stored procedures are and how they are used
•
An overview of the NonStop ODBC Server implementation of stored procedures
•
Executing stored procedures in either CORE SQL or Transact-SQL
•
Development of stored procedures, including the use of the NonStop ODBC Server
library of stored procedure execution functions (SPELIB)
•
Installation of stored procedures using the NonStop ODBC Server catalog utilities
and mapping tables
•
Transaction management, security, sharing of stored procedures, and other such
considerations
Introduction to Stored Procedures
A stored procedure is a mechanism to define and store a user program in a database
system and invoke it at a later time. A stored procedure is a database object that can
contain many elements of 3GL programming language procedures (parameters, flow
control, local variables, and so on) in addition to SQL statements.
The ability to write your own stored procedures greatly enhances the power, efficiency,
and flexibility of the SQL database language. Many vendors support stored
procedures, but all have variations in the definition syntax and the execution
semantics.
The following is an example of a stored procedure body and how it can be used:
1. A stored procedure body for a procedure called DEBIT_ACCOUNT:
int DEBIT_ACCOUNT ( char *name /* Account name */
, int amount /* Amount to adjust */
)
{
if (!name) /* Name parameter is required */
encode_print_message ("You must supply account name");
else
{
exec sql update =ACCOUNT
set BALANCE = BALANCE + setscale (:amount , 2)
where ACCNT_NAME = :name;
}
} /* DEBIT_ACCOUNT */