code example of a database stored procedure called by the collector

The collector can easily invoke the stored procedure of the database, here is an example to see the specific program.

• Call a stored procedure with no return value

using Oracle 's stored procedures as an example, the stored procedure has only one input parameter and no output parameters:

Create Orreplace 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 the Execute function or the proc function to invoke this stored procedure in the collector:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/14/wKiom1QOnCCxY8qMAAAz_WmumlA981.jpg "style=" float: none; "title=" 1.jpg "alt=" Wkiom1qonccxy8qmaaaz_wmumla981.jpg "/>

A1: Connect to the database.

A2: Call the stored procedure and enter a parameter value of 4.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/16/wKioL1QOnCzjI_AkAAA0twRyP38447.jpg "style=" float: none; "title=" 2.jpg "alt=" Wkiol1qonczji_akaaa0twryp38447.jpg "/>

The proc function is primarily used to invoke stored procedures with return values and result sets, or to invoke stored procedure Pro1 with no return parameters.

A1: Connect to the database.

A2: Call the stored procedure Pro1, the comma is followed by a description of the parameter, 4:0: "I": an input parameter is defined, 4 is the value of the input parameter, 0 means that the type of the input parameter is automatically recognized by the collector, "I" represents the input type. If you need to manually specify that the parameter type can be written as: 4:1: "I":, the middle 1 represents the shaping int. The parameter types supported by the collector are described in Appendix: parameter type definitions.

Second, call the single return value of the stored procedure

The following stored procedure returns a parameter value, so you cannot use the Execute function, and you need to use the proc function.

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 that the collector calls this stored procedure is:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/16/wKioL1QOnCyDp_fPAABWfKm1lCA204.jpg "style=" float: none; "title=" 3.jpg "alt=" Wkiol1qoncydp_fpaabwfkm1lca204.jpg "/>

A1: Connect to the database.

a2 testb = 1 11 stands for output type, name

A3: assigns the value of the stored procedure output in A2 from the name variable to the A3 cell.

Iii. calling a stored procedure that returns a single result set

Stored Procedures Rq_test_cur Returns a single result set:

CREATE orreplace PROCEDURE Rq_test_cur

(

V_temp out TYPE. Rq_ref_cursor,

PID in VARCHAR

)

As

BEGIN

OPEN v_temp for SELECT * from Testwhere ID =pid;

END rq_test_cur;

The stored procedure enters a parameter and returns a result set. The code for calling this stored procedure in the collector is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/14/wKiom1QOnCHjwF3NAABYmxDUoqM110.jpg "style=" float: none; "title=" 4.jpg "alt=" Wkiom1qonchjwf3naabymxduoqm110.jpg "/>

The A2 cell uses the proc function to call the stored procedure:proc ("{Callrq_test_cur (?,?)}",: 101: "O": table1,1:0: "i":). The following explains in turn the input parameters of the proc rows:

1) SQL string

"{Call Rq_test_cur (?,?)}" contains the name of the calling stored procedure, and the question mark represents the SQL parameter.

2) output Parameters

: 101: "O": an output parameter is defined in Table1,101 means that its data type is a cursor,"O" means that the parameter is an output parameter. Table1 is defined as a variable that can be used to refer back to the result. Input Parameters

1:0: "I": an input parameter is defined,1 is the value of the input parameter, and0 indicates that the type of the input parameter is automatically recognized by the collector.

A3 cell refers to the execution result of a stored procedure by using an output variable in A2, and the result is the same as A2, which is the sequential table that contains the ID and name two fields.

Iv. calling a stored procedure that returns a multi-result set

Write an oracle stored procedure first to return two result sets:

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 stored procedure returns the result set of the EMP and test two tables. Calling this stored procedure in the collector, the program that accepts two result sets is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/16/wKioL1QOnC2gyYtpAABlphW5iBc588.jpg "style=" float: none; "title=" 5.jpg "alt=" Wkiol1qonc2gyytpaablphw5ibc588.jpg "/>

A2 Cell Use proc The function calls the stored procedure: Orac.proc ("{CALLPROAA (?,?)}",: 101: "O": a,:101: "O": b) , two result sets (order tables) are returned, forming the set of a sequence table: A sequence that assigns values to the A2 . The following explains in turn The input parameters of the proc function:

1) SQL string

"{Call PROAA (?,?)}" contains the name of the calling stored procedure, and the question mark represents the SQL parameter.

2) output parameter 1

: 101: "O": an output parameter is defined in a,101 means its data type is a cursor,"O" means that the parameter is an output parameter. a is defined as a variable that can be used to refer back to the result.

3) output parameter 2

: 101: "O": an output parameter is defined in B,101 represents its data type as a cursor,"O" means that the parameter is an output parameter. b is to define a variable that can be used to refer back to the result.

cell A3 returns the First order table of cell A2 ( The result set of the EMP table).

The A4,A5 cells use the output variables a,B in the A2 to get the execution result of the stored procedure, anda corresponds to the data of the EMP table and assigns the value to A4,b corresponds to the data of the test table and assigns the value to A5.

Appendix: Parameter Type definitions

the value of type is:

Public final static byte Dt_default = (byte) 0; default, automatic recognition

Public final static byte Dt_int = (byte) 1;

Public final static byte Dt_long = (byte) 2;

Public final static byte Dt_short = (byte) 3;

Public final static byte Dt_bigint = (byte) 4;

Public final static byte dt_float = (byte) 5;

Public final static byte dt_double = (byte) 6;

Public final static byte Dt_decimal = (byte) 7;

Public final static byte dt_date = (byte) 8;

Public final static byte Dt_time = (byte) 9;

Public final static byte Dt_datetime = (byte) 10;

Public final static byte dt_string = (byte) 11;

Public final static byte Dt_boolean = (byte) 12;

Public final static byte Dt_int_arr = (byte) 51;

Public final static byte Dt_long_arr = (byte) 52;

Public final static byte Dt_short_arr = (byte) 53;

Public final static byte dt_bigint_arr= (byte) 54;

Public final static byte Dt_float_arr = (byte) 55;

Public final static byte dt_double_arr= (byte) 56;

Public final static byte dt_decimal_arr= (byte) 57;

Public final static byte Dt_date_arr = (byte) 58;

Public final static byte Dt_time_arr = (byte) 59;

Public final static Bytedt_datetime_arr = (byte) 60;

Public final static byte dt_string_arr= (byte) 61;

Public final static byte Dt_byte_arr = (byte) 62;

Public final static byte Dt_cursor = (byte) 101;

Public final static bytedt_autoincrement = (byte) 102;

code example of a database stored procedure called by the collector