|
   
11.
Stored Procedures
 11.1
Creating Stored Procedures
11.2
Executing Stored Procedures
11.3
Dropping A Stored Procedure
11.4
Getting Procedure Information
11.5
Creating A Procedure From Files
11.6
Security
11. Stored Procedures
A stored
procedure is a special kind of user-defined function which contains
embedded SQL statements. Once the stored procedure is created, it is
stored in an executable format in the database as an object of the database.
This allows you to bypass repeated SQL compilation and optimization,
increasing the performance of frequently repeated tasks. You can execute
a stored procedure as a command in interactive SQL, or invoke it in
application programs, trigger actions, or other stored procedures.
You can
accomplish a wide range of objectives with stored procedures, including
improving database performance, simplifying the writing of applications,
and limiting or monitoring access to a database.
Because
a stored procedure is stored in an executable object in the database,
it is available to every application running on the database. Several
applications can use the same stored procedure so development time for
an application is reduced.
11.1 Creating Stored Procedures
You write
an ESQL/C program to create a stored procedure. You can use stored procedures
to perform any function a C application can, including calling other
C functions and system calls.
An ESQL/C
program for a stored procedure consists of a CREATE PROCEDURE statement,
a declare section if needed, and the code section.
Example
1
EXEC SQL CREATE PROCEDURE a_phone (CHAR(13) name, CHAR(13) phone OUTPUT)
RETURNS STATUS;
{
EXEC SQL BEGIN CODE SECTION;
EXEC SQL SELECT PHONE FROM TBL WHERE NAME = :name INTO :phone;
EXEC SQL RETURNS STATUS SQLCODE;
EXEC SQL END CODE SECTION;
}
|
Example
1 is a procedure with one input parameter, one output parameter and
a return value (status). The structure of this program will be explained
in the following sections.
Create Procedure Syntax
In the
head of a procedure definition is a CREATE PROCEDURE statement. The
syntax of the CREATE PROCEDURE statement is:
<procedure creation> ::= CREATE PROCEDURE <procedure def name>
[ <procedure parameters> ]
[ <procedure return result> ]
<procedure def name> ::= [ <module name> '.'] <procedure name>
<module name> ::= IDENT
<procedure name> ::= IDENT
<procedure parameters> ::= <procedure parameter>
[',' <procedure parameters> ]
<procedure parameter> ::= <parameter type>
<parameter name>
[ <parameter mode> ]
<parameter type> ::= <SQL type>
<parameter name> ::= IDENT
<parameter mode> ::= IN | OUT | INPUT | OUTPUT
<procedure return result> ::= RETURNS STATUS |
RETURNS [ STATUS',' ] <procedure results>
<procedure results> ::= <procedure result> [',' <procedure results>]
<procedure result> ::= <result type> <result name>
<result type> ::= <SQL type>
<result name> ::= IDENT
|
Examples
CREATE PROCEDURE p1 (INTEGER n IN) RETURNS STATUS;
CREATE PROCEDURE m1.p2 (INTEGER n1 IN, INTEGER n2 OUT)
RETURNS CHAR(12) nm;
CREATE PROCEDURE m1.p3 (CHAR(10) par1 OUT, SMALLINT par1)
RETURNS STATUS, TIMESTAMP ret1, FLOAT ret2;
|
In a create
procedure statement the procedure name must be provided. The module
name in front of the procedure name is optional. The module name will
be explained later.
Using Parameters
If parameters
are needed, a list of type-name pairs for the parameters must be given
in parentheses. IN/OUT (or INPUT/OUTPUT) parameter attributes must be
put after each type-name pair. If there is no parameter attribute followed,
IN is used by default. Input parameters are used to pass a value to
a procedure. As in example 1, it has one input parameter, name.
Whenever you execute the procedure, you need to provide a value for
the input parameter.
Output
parameters are used to get a single result (not a result set) after
the procedure is executed. As in example 1, procedure a_phone
has an output parameter, phone. You must assign a buffer for
the output parameter for receiving the result. After the procedure executes,
you can get the phone number of the name you input from the buffer (if
any).
A stored
procedure may also retrieve a result set of tuples from the database,
in this case the result list are needed. If the procedure does not return
any selected result then there is no need for result list. The keyword
RETURNS is used to start the result list. It is a list of type name
pairs (see example below).
Example
EXEC SQL CREATE PROCEDURE t19 (FLOAT ifl) RETURNS STATUS,
FLOAT fl,
DOUBLE db;
{
EXEC SQL BEGIN CODE SECTION;
EXEC SQL RETURNS STATUS SQLCODE;
EXEC SQL RETURNS SELECT fl, db FROM t8
WHERE fl < :ifl into :fl, :db;
EXEC SQL END CODE SECTION;
}
|
Another
keyword STATUS is used to indicate there is a integer value returned
after the procedure is executed. This is like the return code of a C
int function.
For both
input and output parameters, DBMaker now supports the following data
types: INTEGER, SMALLINT, SERIAL, CHAR(), DATE, TIME, TIMESTAMP, FLOAT,
DOUBLE, REAL.
Return Select Statement
A procedure
can return a result set. The result set must use the host variable mechanism
to pass out to the caller who execute the stored procedure. We shall
explain this later in execution of a stored procedure. However, in the
code of stored procedure you need to use the RETURNS keyword to let
preprocessor generate host variable related C code. The RETURNS keyword
precede the select statement that produces the result set.
Example
EXEC SQL CREATE PROCEDURE get_all_phone
RETURNS CHAR(12) name, CHAR(12) phone;
{
EXEC SQL BEGIN CODE SECTION;
EXEC SQL RETURNS SELECT NAME, PHONE FROM TBL INTO :name, :phone;
EXEC SQL END CODE SECTION;
}
|
There are
two RETURNS in this simple piece of code, one in the create procedure
statement and the other in the select statement, which form a pair.
That is, if there is a result set returned, you need to declare output
parameters with RETURNS in the create procedure statement and put the
RETURNS keyword in the select statement. In example 2, the RETURNS keyword
is also used to return the status.
Module Names
Users can
write several stored procedures in one ESQL source file. The module
name is used to tell the database server which module contains the procedures.
The default module name is the procedure name.
Code Section
Except
for variable declaration, all other statements should be in the code
section. Any non-declaration statement before the code section may cause
problems (compile errors or wrong results). Statements after the CODE
SECTION will not be executed.
11.2 Executing Stored Procedures
You can
invoke a stored procedure in dmSQL, in a C program (ODBC or ESQL), in
another stored procedure, or in a trigger action.
Execute Stored Procedures in dmSql
The syntax
for executing a stored procedure in dmSQL is:
<procedure call> ::= CALL <procedure calling name>
[( <call parameters> )]
<procedure calling name> ::= [<owner name> '.'] <procedure name>
<owner name> ::= IDENT
<call parameters> ::= <call parameter> [ ',' <call parameters> ]
<call parameter> ::= ':' IDENT
|
Examples
dmSQL> CALL p1 (3);
dmSQL> CALL SYSADM.p2 (5, :n2);
dmSQL> CALL SYSADM.p3 (:par1, 7);
|
If the
procedure returns a result set, dmSQL automatically handles the output
parameters and displays the result set on the screen. The result set
appears on the screen just like you type in a SELECT statement in dmSQL.
dmSql> call a_phone('jeff');
STATUS PHONE
=========================
0 408-255-2689
dmSql> call sel_all_phone;
NAME PHONE
============================
Jerry 02-775-8615
Jeff 408-255-2689
|
Executing Stored Procedures in ESQL programs
The syntax
for executing a stored procedure in an ESQL program is:
<procedure call> ::= [<status> '=' ] CALL <procedure calling name>
[(<call parameters>)] [INTO <return parameters>]
<status> ::= ':' IDENT
<procedure calling name> ::= [<owner name> '.'] <procedure name>
<owner name> ::= IDENT
<call parameters> ::= <call parameter> [ ',' <call parameters> ]
<call parameter> ::= ':' IDENT
|
Examples
EXEC SQL :s = CALL p1 (3);
EXEC SQL CALL SYSADM.p2 (5, :n2) INTO :nm;
EXEC SQL :s = CALL jack.p3 (:par1, 7) INTO :ret1, :ret2;
|
The syntax
used in an ESQL program is almost the same as in dmSQL, except the status
is automatically received by dmSQL. In ESQL, users must use a host variable
to receive the status.
Executing Stored Procedures within a Stored Procedure
Since a
stored procedure program is an ESQL/C program, so you can invoke a stored
procedure inside a stored procedure exactly the way you would use it
in an ESQL/C program. There is only one exception: regular ESQL programs
cannot use the RETURNS keyword but the stored procedure can use the
RETURNS keyword when invoking another stored procedure.
Assume
stored procedure sel_all_phone returns a multiple tuple result
set. A regular ESQL program needs to use a cursor to fetch the tuples
when invoking this procedure as shown in last section. Within another
stored procedure sp2, you can use the same method to fetch tuples
and examine the data. But you can also return the whole result set of
the called stored procedure to the caller directly from within the current
stored procedure. For example you can have this following statement
inside stored procedure sp2.
EXEC SQL RETURNS CALL sel_all_phone INTO :oName, :oPhone;
|
Remember
when a stored procedure returns another stored procedure's result set,
then the caller must have exactly same result set list, or the same
as the first n result columns of the called procedure.
Executing Stored Procedures in ODBC programs
You can
also call a stored procedure in an ODBC program. To do this, you have
to bind parameters for procedure parameters, and bind columns for procedure
to return the result set. In an ODBC program, you can bind partial columns
of the result set. After the procedure executes, output parameters are
returned in the host variables. You must fetch like a SELECT command
to get the result set.
Procedure
declaration:
CREATE PROCEDURE proc1(CHAR(12) p1, CHAR(12) p2 OUTPUT)
RETURNS INTEGER i;
|
Example
ODBC program that calls proc1:
SQLPrepare(cmdp,(UCHAR*)"call proc1(?, ?)", SQL_NTS);
strcpy(bpname, "12345");
SQLBindParameter(cmdp, 1, SQL_PARAM_INPUT_OUTPUT, SQL_C_CHAR, SQL_CHAR,
20, 0, &p1, 20, NULL);
SQLBindParameter(cmdp, 2, SQL_PARAM_INPUT_OUTPUT, SQL_C_CHAR, SQL_CHAR,
20, 0, &p2, 20, NULL);
SQLBindCol(cmdp, 1, SQL_C_LONG, &i, sizeof(long), NULL);
SQLExecute(cmdp); /* get p2 */
while ((rc=SQLFetch(cmdp))!=SQL_NO_DATA_FOUND) /* fetch result set */
|
11.3 Dropping A Stored Procedure
The syntax
of the DROP PROCEDURE statement is:
<drop procedure> ::= DROP PROCEDURE <procedure def name>
<procedure def name> ::= [ <module name> '.'] <procedure name>
<module name> ::= IDENT
<procedure name> ::= IDENT
|
Examples
DROP PROCEDURE proc1;
DROP PROCEDURE m1.proc2;
|
11.4 Getting Procedure Information
Users using
dmSQL can view two system views to get information of procedure: SYSPROCINFO
and SYSPROCPARAM.
SELECT * FROM SYSPROCINFO;
SELECT * FROM SYSPROCPARAM;
|
ODBC functions
SQLProcedure() and SQLProcedureColumns() are used to get procedure and
parameter information in programs.
11.5 Creating A Procedure From Files
Users can
create procedures from files which contain procedure source. The syntax
is:
<create proc from file> ::= CREATE PROCEDURE FROM <file path name>
<file path name> ::= <string>
|
Examples
CREATE PROCEDURE FROM 'proc1.ec';
CREATE PROCEDURE FROM '../esql/proc2.ec';
CREATE PROCEDURE FROM '/usr1/paxtsai/sp/esql/proc3.ec';
|
<file
path name> can be a relative or absolute path. The file must be an
ESQL source. The client will translate the contents of the file to the
server, and the server will automatically call the preprocessor to generate
the C code and call the C compiler and linker to generate target file.
11.6 Security
A stored
procedure can be executed by DBAs and its owner. Other users can execute
the procedure when the execution privilege is granted to them or PUBLIC.
The GRANT/REVOKE
syntax is:
GRANT EXECUTE ON PROCEDURE <procedure name> TO <auth user list>;
REVOKE EXECUTE ON PROCEDURE <procedure> FROM <auth user list>;
|
|
