This section explains how to call a stored procedure in the database. You can choose to skip this section if you are not a professional programmer. It won’t affect your learning about the other contents of this Tutorial.
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.
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.
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.
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 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.
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.
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_SERIES = (byte) 51; |
//a sequence of integers |
|
– public final static byte DT_LONG_SERIES = (byte) 52; |
//a sequence of long integers |
|
– public final static byte DT_SHORT_SERIES = (byte) 53; |
// a sequence of short integers |
|
– public final static byte DT_BIGINT_SERIES = (byte) 54; |
//a sequence of big integers |
|
– public final static byte DT_FLOAT_SERIES = (byte) 55; |
//a sequence of floating points |
|
– public final static byte DT_DOUBLE_SERIES = (byte) 56; |
//a sequence of double-precision floating points |
|
– public final static byte DT_DECIMAL_SERIES = (byte) 57; |
//a sequence of big decimals |
|
– public final static byte DT_DATE_SERIES = (byte) 58; |
//a sequence of dates |
|
– public final static byte DT_TIME_SERIES = (byte) 59; |
// a sequence of time values |
|
– public final static byte DT_DATETIME_SERIES = (byte) 60; |
//a sequence of datetime values |
|
– public final static byte DT_STRING_SERIES = (byte) 61; |
//a string sequence |
|
– public final static byte DT_BYTE_SERIES = (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 |