esProc Accesses Databases: Calling Database Stored Procedures

Besides data tables, store procedures are also very important for a database. esProc can call database stored procedures conveniently. This article will illustrate in detail how to call various stored procedures with db.proc() function in esProc.

 1. Call stored procedures without return values

Stored procedures are used to return query results or only to implement the database operations without returning results. Let’s first look at how to call stored procedures without return values. Take an oracle stored procedure as an example. The stored procedure proc1 has only one input parameter, and no output parameter:

  create or replace procedure proc1
  (pid IN VARCHAR)
  as
  begin
    insert into emp values(pid,’mike’);
    update emp set name=’rose’ where id=pid;
    commit;
  end;

execute function or proc function can be used in esProc to call this stored procedure:

A1 connects to the database. A2 uses execute function o call the stored procedure, with the input parameter value being 4. When the stored procedure is executed, connection will be closed in A3.

A1 connects to the database and A3 closes the connection. Different from the above code, a description is inserted after the parameter when A2 calls the stored procedure proc1. esProc’s proc function can be used to call stored procedures without returned parameters, like proc1, though it is mainly used to call stored procedures that return values and result sets.

Define the complete format of each parameter as a:t:m:v, in which a represents the parameter value, t represents the parameter’s data type, m represents the input/output model of the parameter and v represents the parameter name of the returned result if the stored procedure has one, when calling a stored procedure. In this piece of code, 4:0:”i”: defines an input parameter, in which 4 is the input parameter value, 0 represents that the type of input parameter is automatically identified by esProc and “i” represents the input type. If the parameter type needs to be specified manually, the code can be written as 4:1:”i”:, in which the 1 in the middle represents Integer(int). For more information about parameter types supported by esProc, please see Appendix: Definition of Parameter Types.

2.Call stored procedures which return a single value

Stored procedures with returned values can be called only by proc function. The stored procedure below will return one parameter value, so proc function, instead of execute function, will be used.

  create or replace procedure testb
  (para1 in varchar2,para2 out varchar2)
  as
  begin
    select name into para2 from emp where id= para1;
  end testb;

The code for calling this stored procedure by esProc is:

A1 connects to the database and A4 closes the connection.

A2 uses proc function to call the stored procedure testb. Here two parameters are used: one is 1:0:”i” representing that the value is 1 and the type of the input parameter is automatically identified; the other is 11:”o”:name in which 11 represents string type (see Appendix: Definition of Parameter Types for more), ”o” represents the type of output parameter and name defines an esProc variable to receive the return value.

A3 gets the value of variable name, which is the output result of the stored procedure. The type of the variable value is determined by the stored procedure. In this example, name is used to return the name of the employee of specified number and the data type of the returned result is string.

There are 3 input/output types: input parameter, output parameter as well as input/output parameter, which are represented respectively by “i”,”o” and “io”. The input/output parameter in a stored procedure can both input data and get assigned by the stored procedure.

3. Call stored procedures which return a single result set

In more cases, stored procedures return result sets. Stored procedure RQ_TEST_CUR returns a single result set:

  CREATE OR REPLACE PROCEDURE RQ_TEST_CUR
  (
    V_TEMP OUT TYPE.RQ_REF_CURSOR,
    PID IN VARCHAR
  )
  AS
  BEGIN
    OPEN V_TEMP FOR SELECT * FROM TEST WHERE ID =PID;
  END RQ_TEST_CUR;

The stored procedure has an input parameter and returns a result set using a cursor. The code for calling the stored procedure in esProc is as follows:

A2 uses proc function to call the stored procedure: =A1.proc(“{call RQ_TEST_CUR(?,?)}”, :101:”o”:table1, 1:0:”i”:). The following will explain the input parameters of proc function.

The SQL strings “{call RQ_TEST_CUR(?,?)}” contains the name of the stored procedure. The question marks represent SQL’s parameters.

Parameters in the stored procedure:

:101:”o”:table1 defines an output parameter. That it begins directly with a colon means it hasn’t an input value. 101 represents that its data type is cursor, ”o” represents that it is an output parameter and table1 defines a variable which is used to reference the returned result.

1:0:”i”: defines an input parameter, in which 1 is the value of the input parameter, 0 represents that the parameter type is automatically identified by esProc , “i” represents that it is an input parameter and that it ends directly with a colon means there is no need to output the result.

A3 uses output variable in A2 to reference the execution result of the stored procedure. The computed result is a table sequence containing query results from table TEST, which is the same as that in A2.

4. Call stored procedures which return multiple result sets

Stored procedures can also return multiple result sets. For example, the following oracle stored procedure returns two result sets using the cursor:

  create or replace procedure proAA
  (
    out_var out sys_refcursor,
    out_var2 out sys_refcursor
    )
  as
  begin
    open out_var for select * from emp;
    open out_var2 for select * from test;
  end;

The simple stored procedure returns result sets of two tables: emp and test. Call the stored procedure in esProc and the program for receiving the two result sets is as follows:

A2 uses proc function to call the stored procedure: =A1.proc(“{call proAA(?,?)}”,:101:”o”:a,:101:”o”:b) , which returns two result sets (table sequences) to form a sequence, i.e., a set of table sequences. The sequence assigns value to A2. The following will explain the input parameters in proc function.

SQL string “{call proAA(?,?)}” contains the stored procedure’s name. The question marks represent the SQL’s parameters.

Output parameter 1

:101:”o”:a defines an output parameter, in which 101 represents that its data type is cursor, ”o” represents it is an output parameter and a defines a variable which is used to output the returned results to this variable.

Output parameter 2

:101:”o”:b defines an output parameter, in which 101 represents that its data type is cursor, ”o” represents it is an output parameter and b defines a variable which is used to output the returned results to this variable.

A3 returns A2’s first table sequence (table emp’s result set)

A4 and A5 use respectively the output variables a and b in A2 to obtain the execution results of corresponding stored procedures. a corresponds to data in table emp and assigns value to A4; b corresponds to data in table test and assigns value to A5. Actually, the result of A2 is the sequence composed of the table sequences in A4 and A5.

Appendix: Definition of Parameter Types

Values of type in stored procedures are:

public final static byte DT_DEFAULT = (byte) 0;            //default, automatically identify

public final static byte DT_INT = (byte) 1;            //a 32-bit integer

public final static byte DT_LONG = (byte) 2;          //a 64-bit long integer

public final static byte DT_SHORT = (byte) 3;         //a 16-bit short integer

public final static byte DT_BIGINT = (byte) 4;        //big integer

public final static byte DT_FLOAT = (byte) 5;        //a 32-bit floating point

public final static byte DT_DOUBLE = (byte) 6;    //a 64-bit double-precision floating point

public final static byte DT_DECIMAL = (byte) 7;     //a big decimal

public final static byte DT_DATE = (byte) 8;        //date

public final static byte DT_TIME = (byte) 9;        //time

public final static byte DT_DATETIME = (byte) 10;   //date/time

public final static byte DT_STRING = (byte) 11;     //string

public final static byte DT_BOOLEAN = (byte) 12;   //boolean

public final static byte DT_INT_ARR = (byte) 51;    //a sequence of integer

public final static byte DT_LONG_ARR = (byte) 52;  //a sequence of long integer

public final static byte DT_SHORT_ARR = (byte) 53;  // a sequence of short integer

public final static byte DT_BIGINT_ARR = (byte) 54;  //a sequence of big integer

public final static byte DT_FLOAT_ARR = (byte) 55;  //a sequence of floating point

public final static byte DT_DOUBLE_ARR = (byte) 56;  //a sequence of double-precision floating point

public final static byte DT_DECIMAL_ARR = (byte) 57;  //a sequence of big decimal

public final static byte DT_DATE_ARR = (byte) 58;     //a sequence of date

public final static byte DT_TIME_ARR = (byte) 59;     // a time sequence

public final static byte DT_DATETIME_ARR = (byte) 60;  //a sequence of data/time

public final static byte DT_STRING_ARR = (byte) 61;   //a string sequence

public final static byte DT_BYTE_ARR = (byte) 62;     //a sequence of byte

public final static byte DT_CURSOR = (byte) 101;     //cursor

public final static byte DT_AUTOINCREMENT = (byte) 102;  //self-increment field