The CREATE ROUTINE statement defines a routine. For Version 6.0, the CREATE FUNCTION and CREATE PROCEDURE statements have been combined as a single statement, the CREATE ROUTINE statement. This is because functions and procedures have been advanced to the point where they are virtually identical. The small number of differences remaining between functions and procedures are noted below the syntax diagram.
For information on the use of CONSTANT statements, see the DECLARE statement.
Supply the routine's name using RoutineName and the routine's parameters using ParameterList. In ESQL the routine 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; (see External stored procedures, or External Java methods if you are using Java).
ESQL procedures, stored procedures, and Java methods (unlike ESQL functions) can have OUT and INOUT parameters in addition to IN parameters. This allows them to return several values to the caller, although they have no RETURN value as such.
Routines defined in a module are local in scope to the current node only. To use the same routine in more than one node, define it in a schema.
Using
the RETURNS clause allows you to invoke a routine as a procedure or a function.
A procedure cannot return values of the ESQL reference data types. The optional
RETURNS clause is required if a LANGUAGE DATABASE stored procedure returns
a single scalar value as a return value. The datatype of the return value
must match the datatype of the scalar value to be returned from the stored
procedure in the same way that the dataypes must match for the OUT direction
parameters.
>>--"-- className---.---methodName--"--------------><where
An example EXTERNAL NAME is "com.ibm.broker.test.MyClass.myMethod". The broker loads the Java class from the broker's CLASSPATH. If the specified class cannot be found an exception is thrown.
Overloaded LANGUAGE ESQL routines are not supported. (An overloaded routine is one that has the same name as another routine in the same broker schema which has a different number of parameters, or parameters with different types.) If the broker detects that a routine 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 ROUTINE ..... 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:
CREATE ROUTINE myProc1(IN P1 INT, OUT P2 INT) LANGUAGE DATABASE RETURNS INTEGER EXTERNAL NAME "myschema.myproc";
Use this ESQL to invoke the myProc1 routine:
/*using procedure invocation syntax*/ CALL myProc1(intVar1, intVar2) INTO intReturnVar3;or
/*using function invocation syntax*/ SET intReturnVar3 = myProc1(intVar1, intVar2);
ESQL Definition: DECLARE inputParm CHARACTER; DECLARE outputParm CHARACTER; DECLARE inputOutputParm CHARACTER; SET inputParm = 'Hello'; SET inputOutputParm = 'World'; CALL swapParms( inputParm, outputParm, inputOutputParm ); CREATE ROUTINE 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 ROUTINE 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 ROUTINE 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 ROUTINE 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
The following example defines a stored procedure representing a procedure that returns two result sets as well as an out parameter.
CREATE ROUTINE myProc1 (IN P1 INT, OUT P2 INT) LANGUAGE DATABASE DYNAMIC RESULT SETS 2 EXTERNAL NAME "myschema.myproc";
Use the following ESQL to invoke myProc1:
/* using a field reference */ CALL myProc1(intVar1, intVar2, Environment.RetVal[], OutputRoot.XML.A[])or
/* using a reference variable*/ CALL myProc1(intVar1, intVar2, myReferenceVariable.RetVal[], myRef2.B[])
public static <return-type> <method-Name> (< 0 - N parameters>)where:
CREATE ROUTINE myProc1( IN P1 INTEGER, OUT P2 INTEGER, INOUT P3 INTEGER ) RETURNS INTEGER LANGUAGE JAVA EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod1";You can use the following ESQL to invoke myProc1:
CALL myProc1( intVar1, intVar2, intVar3) INTO intReturnVar3; -- or SET intReturnVar3 = myProc1( intVar1, intVar2, intVar3);
CREATE ROUTINE myProc2( IN P1 INTEGER, OUT P2 INTEGER, INOUT P3 INTEGER ) LANGUAGE JAVA EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod2";You must use the following ESQL to invoke myProc2:
CALL myProc2(intVar1, intVar2, intVar3);
package com.ibm.broker.test; class MyClass { public static Long myMethod1( Long P1, Long[] P2 Long[] P3) { ... } public static void myMethod2( Long P2, Long[] P2 Long[] P3) { ... } /* When either of these methods is called: P1 may or may not be NULL (depending on the value of intVar1). P2[0] is always NULL (whatever the value of intVar2). P3[0] may or may not be NULL (depending on the value of intVar3). This is the same as with current ESQL procedures. When these methods return: intVar1 is unchanged intVar2 may still be NULL or may have been changed intVar3 may contain the same value or may have been changed. This is the same as with current ESQL procedures. When myMethod1 returns: intReturnVar3 is either NULL (if the method returns NULL) or it contains the value returned by the method. */ }
Notices |
Trademarks |
Downloads |
Library |
Support |
Feedback
![]() ![]() |
ak20210_ |