The CREATE PROCEDURE statement defines a procedure, which is a subroutine that has no return value.
For information on the use of CONSTANT statements, see the DECLARE statement.
Supply the procedure's name using ProcedureName and the procedure's parameters using ParameterList. The procedure is implemented using the single ESQL statement Statement. This statement can also be a compound statement, for example BEGIN ... END. Alternatively, you can use the EXTERNAL clause instead of Statement; this calls the named routine as a stored procedure in the database that is identified by the containing node's data source property (see External stored procedures).
ESQL procedures and stored procedures, unlike ESQL functions, can have OUT and INOUT parameters in addition to IN parameters. This allows them to return several values to the caller.
Procedures defined in a module are local in scope to the current node only. To use the same procedure in more than one node, define it in a schema.
Overloaded internal procedures are not supported. (An overloaded procedure is one that has the same name as another procedure in the same broker schema which has a different number of parameters, or parameters with different types.) If the broker detects that a procedure has been overloaded, it raises an exception.
OUT parameters passed into a procedure (whether internal or external) always contain a NULL value of the correct type when they are received by the procedure. This happens irrespective of their value before the CALL.
IN and INOUT parameters can be NULL when received by the procedure. For example, the following passes NULL to the procedure testProc:
DECLARE nullChar CHARACTER; CALL testProc( nullChar );
This happens because nullChar was not assigned a value before the CALL.
To call a stored procedure, you must define the procedure in the database and in the broker in ESQL. When writing stored procedures, in languages like C, you must use NULL indicators to ensure that your procedure can process the data correctly.
You can specify either a qualified or unqualified name in the EXTERNAL clause (the qualifier is the name of the database schema in which the procedure is defined). If you do not provide a schema name, the database connection user name is used as the default schema. If the required procedure does not exist in this schema, you must give an explicit schema name:
CREATE PROCEDURE ..... EXTERNAL NAME "mySchema.myProc";
If the procedure belongs to an Oracle package, you must provide an explicit qualified name of the form:
...EXTERNAL NAME "mySchema.myPackage.myProc"
You cannot use SQL wildcards for schema, package, or procedure names except to specify a dynamic schema name. The wildcards (percent, representing one or more characters, and underscore, representing a single character) are modified by the broker to include the database escape character immediately before each wildcard. The database therefore receives these as literal characters.
The clause below is modified by the broker and mySchema.Proc\_ is passed to the database (this assumes that the database escape character is a backslash);
...EXTERNAL NAME "mySchema.Proc_"
If you specify a database schema name as a single percent character when you create the procedure, the broker handles this as a special case and resolves the name at runtime. The clause below is valid and identifies a dynamic database schema:
...EXTERNAL NAME "%.myProc"
If you use this technique, you must include the EXTERNAL SCHEMA clause on the CALL statement that you use to invoke this procedure to identify the appropriate schema.
Example 1: An external stored procedure demonstrates how to define and call a stored procedure in Oracle and DB2. Although the database definitions vary between the databases, the ESQL does not. The names given to parameters in the ESQL do not have to match the names they are given on the database side. However, the external name of the procedure must match its defined name in the database, including any package or container specifications. Enclose ExternalRoutineName in quotation marks if it contains a character that is not allowed in an identifier.
All external procedures have the following restrictions:
ESQL Definition: DECLARE inputParm CHARACTER; DECLARE outputParm CHARACTER; DECLARE inputOutputParm CHARACTER; SET inputParm = 'Hello'; SET inputOutputParm = 'World'; CALL swapParms( inputParm, outputParm, inputOutputParm ); CREATE PROCEDURE swapParms ( IN parm1 CHARACTER, OUT parm2 CHARACTER, INOUT parm3 CHARACTER ) EXTERNAL NAME dbSwapParms;
To register this stored procedure with DB2, copy the following script to a file (for example, test1.sql) and execute:
db2 -td@ -vf test1.sql from DB2 command prompt. -- DB2 Example Stored Procedure DROP PROCEDURE dbSwapParms @ CREATE PROCEDURE dbSwapParms ( IN in_param CHAR(32), OUT out_param CHAR(32), INOUT inout_param CHAR(32)) LANGUAGE SQL BEGIN SET out_param = inout_param; SET inout_param = in_param; END @
To register this stored procedure with Oracle, copy the following script to a file (for example, test1.sql) and execute:
sqlplus <userid>/<password> @test1.sql CREATE OR REPLACE PROCEDURE dbSwapParms ( in_param IN VARCHAR2 , out_param OUT VARCHAR2, inout_param IN OUT VARCHAR2 ) AS BEGIN out_param := inout_param; inout_param := in_param; END; /
You can expect the following results:
The following example shows the same procedure as in Example 1: An external stored procedure, but implemented as an ESQL internal procedure. The CALL syntax for this procedure is the same, as well as the result.
CREATE PROCEDURE swapParms ( IN parm1 CHARACTER, OUT parm2 CHARACTER, INOUT parm3 CHARACTER ) BEGIN SET parm2 = parm3; SET parm3 = parm1; END;
The following example procedure parses a tree, visiting all places at and below the given starting point, and reports what it has found:
SET OutputRoot.MQMD = InputRoot.MQMD; DECLARE answer CHARACTER; SET answer = ''; CALL navigate(InputRoot.XML, answer); SET OutputRoot.XML.Data.FieldNames = answer; CREATE PROCEDURE navigate (IN root REFERENCE, INOUT answer CHARACTER) BEGIN SET answer = answer || 'Reached Field... Type:' || CAST(FIELDTYPE(root) AS CHARACTER)|| ': Name:' || FIELDNAME(root) || ': Value :' || root || ': '; DECLARE cursor REFERENCE TO root; MOVE cursor FIRSTCHILD; IF LASTMOVE(cursor) THEN SET answer = answer || 'Field has children... drilling down '; ELSE SET answer = answer || 'Listing siblings... '; END IF; WHILE LASTMOVE(cursor) DO CALL navigate(cursor, answer); MOVE cursor NEXTSIBLING; END WHILE; SET answer = answer || 'Finished siblings... Popping up '; END;
When given the following input message:
<Person><Name>John Smith</Name><Salary period='monthly' taxable='yes'>-1200</Salary></Person>
the procedure produces the following output, which has been manually formatted:
Reached Field... Type:16777232: Name:XML: Value :: Field has children... drilling down Reached Field... Type:16777216: Name:Person: Value :: Field has children... drilling down Reached Field... Type:16777216: Name:Name: Value :John Smith: Field has children... drilling down Reached Field... Type:33554432: Name:: Value :John Smith: Listing siblings... Finished siblings... Popping up Finished siblings... Popping up Reached Field... Type:16777216: Name:Salary: Value :-1200: Field has children... drilling down Reached Field... Type:50331648: Name:period: Value :monthly: Listing siblings... Finished siblings... Popping up Reached Field... Type:50331648: Name:taxable: Value :yes: Listing siblings... Finished siblings... Popping up Reached Field... Type:33554432: Name:: Value :-1200: Listing siblings... Finished siblings... Popping up Finished siblings... Popping up Finished siblings... Popping up Finished siblings... Popping up
Related concepts
ESQL
Related tasks
Developing ESQL
Invoking stored procedures
Related reference
Syntax preference
ESQL statements
CALL statement
Notices |
Trademarks |
Downloads |
Library |
Support |
Feedback
![]() ![]() |
ak04970_ |