Calling Database Stored Procedures

Read(404) Label: call stored procedure, proc,

Besides data tables, store procedures are also very important for database management. esProc is able to call database stored procedures conveniently. This part illustrates how to call various stored procedures with db.proc() function.

Calling 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 pro1

(pid IN VARCHAR)

as

begin

insert into emp values(pid,'mike');

update emp set name='rose' where id=pid;

commit;

end;

 

You can use execute function or proc function to call this stored procedure:

 

A

1

=connect("ora")

2

>A1.execute("{call proc1(?)}",4)

3

>A1.close()

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

 

A

1

=connect("ora")

2

>A1.proc("{call proc1(?)}",4:0:"i":)

3

>A1.close()

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

The complete format of each parameter for calling a stored procedure is a:t:m:v, in which a is the parameter value, t represents the parameter’s data type, m is an input or output mode of the parameter and v is the name of the variable representing the returned result if the stored procedure has one. In this piece of code, 4:0:"i": defines a parameter whose value is 4, whose data type, specified by 0, will be automatically identified by esProc and which is an input mode specified by "i". To specify the data type manually, the code can be written as 4:1:"i":, where the 1 in the middle represents the integer type(int). For more information about the parameter data types supported by esProc, refer to Appendix: Definitions of Parameter Data Types.

Calling stored procedures that return a single value

Stored procedures with returned values can be called only by proc function. The stored procedure below returns 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;

Here’s the code for calling this stored procedure by esProc:

 

A

1

=connect("ora")

2

=A1.proc("{call testb(?,?)}",1:0:"i":,:11:"o":name)

3

=name

4

>A1.close()

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" whose value is 1 and which is an input mode with automatically identified data type; the other is 11: "o":name in which 11 represents the string type (see Appendix: Definitions of Parameter Data Types for more), "o" represents an output mode and name defines an esProc variable to receive the return value.

A3 gets the value of the variable name, which is the output result of the stored procedure. The data 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.

For a stored procedure, there’re 3 parameter modes – input, output and input&output – represented respectively by "i","o" and "io". The input&output parameter can both input data and get assigned by the stored procedure.

Calling stored procedures that return a single result set

In more cases, stored procedures return result sets. The following 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. Here’s the code for calling the stored procedure in esProc:

 

A

1

=connect("ora")

2

=A1.proc("{call RQ_TEST_CUR(?,?)}",:101:"o":table1,1:0:"i":)

3

=table1

4

>A1.close()

A2 uses proc function to call the stored procedure: =A1.proc("{call RQ_TEST_CUR(?,?)}",:101:"o":table1, 1:0:"i":). The following explains the parameters defined in proc function.

The SQL string "{call RQ_TEST_CUR(?,?)}" contains the stored procedure name. The question marks represent SQL parameters.

Let’s look at the stored procedure parameters:

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

1:0:"i": defines an input parameter. 1 is the parameter value, 0 indicates that the its data type is automatically identified by esProc, and "i" represents an input mode. That it ends directly with a colon means that there is no need to output the result.

A3 references the result of executing the stored procedure using A2’s output variable. Both A2 and A3 have the same result, which is a table sequence containing results of querying the TEST table.

Calling stored procedures that 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

openout_var for select * from emp;

open out_var2 for select * from test;

end;

The simple stored procedure returns the result sets of querying two tables emp and test. Below is the esProc program for calling the stored procedure and for receiving the two result sets:

 

A

1

=connect("ora")

2

=A1.proc("{call proAA(?,?)}",:101:"o":a,:101:"o":b)

3

=A2(1)

4

=a

5

=b

6

>A1.close()

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 set of table sequences, i.e. a sequence, to be assigned to A2. The following explains the parameters used in proc function.

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

:101:"o":a defines an output parameter. 101 represents the cursor data type, "o" represents an output mode and a is a variable for storing the returned result.

:101:"o":b defines another output parameter. 101 represents the cursor data type, "o" represents an output mode and b is a variable for storing the returned result.

A3 returns A2’s first table sequence (result set of querying the emp table).

A4 and A5 respectively use the output variables a and b in A2 to obtain the corresponding result of executing the stored procedure. a corresponds to the result of querying the emp table and is assigned to A4; b corresponds to the result of querying the test table and is assigned to A5. Actually, the result of A2 is the sequence composed of the table sequences in A4 and A5.

Appendix for 4.5 Section: Definitions of Parameter Types

Below are values of the parameter data types in stored procedures:

public final static byte DT_DEFAULT = (byte) 0;  //default value, auto-identification

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;  //a 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;  //datetime

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 integers

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

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

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

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

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

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

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

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

public final static byte DT_DATETIME_ARR = (byte) 60;  //a sequence of datetime values

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

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

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

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