PB call stored procedure [Oracle]

Document directory

• 1. No input parameter stored procedure call
• 2. input parameter stored procedure call
• Iii. INOUT process call

I. general format of the call PROCESS IN Pb

Declare my_proce procedure for p_get_name; // call execute my_proce During the definition process; // the execution process. If sqlca does not need other parameters here. sqlcode <> 0 then // end iffetch my_proce into: ls_returnname; // get the parameter value, that is, the out variable. If there is no out variable, this statement does not need if sqlca. sqlcode <> 0 then // An error occurred while obtaining data processing end ifclose my_proce; // close the call

II. The following describes how to define different stored procedures. I. No input parameter stored procedure call

Create or replace procedure p_get_name (v_name out varchar2) as // stored procedure, there is an out type parameter, no input parameter begin v_name: = 'jone'; end; // define declare my_proce procedure for p_get_name in Pb; // correct declare my_proce procedure for p_get_name (); // This is correct. // if you try to pass the declare my_proce procedure for p_get_name (: ls_name) parameter, an error will be reported during execution.

2. input parameter stored procedure call

Create or replace procedure p_get_name (v_first in varchar2, v_last in varchar2, v_name out varchar2) asbegin v_name: = v_first | v_last; end; // defined in Pb, name a and B here, mainly because it does not affect the idea of declare my_proce procedure for p_get_name (: ls_a,: ls_ B) // correct, the variables here correspond to procedural variables in sequence, that is, ls_a corresponds to v_first and ls_ B corresponds to v_lastdeclare my_proce procedure for p_get_name (: ls_ B,: ls_a) // correct, the variables here correspond to procedural variables in sequence, that is, ls_ B corresponds to v_first and ls_a corresponds to v_lastdeclare my_proce procedure for p_get_name ("Michael", "Jordan") // error, you cannot directly use the value. You must use the variable // to specify the corresponding relationship. This does not depend on the order you entered. Declare my_proce procedure for p_get_name (v_last =>: ls_a, v_first =>: ls_ B) Declare my_proce procedure for p_get_name (v_first =>: ls_ B, v_last =>: ls_a) // The two definitions above are exactly the same. Declare my_proce procedure for p_get_name (v_first1 =>: ls_first, v_last =>: ls_last) // This definition is incorrect, because no declare my_proce procedure for p_get_name (: ls_a,: ls_ B,: ls_c) parameter named v_first1 is found at the root in the process. // error, if you try to pass an out parameter into the parameter, it will be wrong./* Note: If the out parameter defined by the stored procedure is not at the end, if the parameter is located in the middle or front of the input parameter, the corresponding parameter must be specified in Pb. Otherwise, an error will be reported, for example, */create or replace procedure p_get_name (v_name out varchar2, v_first in varchar2, v_last in varchar2) asbegin v_name: = v_first | v_last; end; // This is the definition of v_first =>, v_last =>. Otherwise, it is wrong.

Iii. inout process call

// When Declare is used in PB, stored procedures with in out parameters cannot be called. Therefore, external interfaces are used. // directly step 1. create a transaction object uo_transcation, new-> pb object-> standart class-> transcation, named uo_transcation 2. define the stored procedure in the Local External Functions of uo_transcation, for example, subroutine p_get_name (string v_First, string v_Last, ref string v_name) rpcfunc 1. the subroutine process has no return value. 2. rpcfunc doesn't understand. Just write it. ref indicates that the variable can be of the out and in out types. use uo_transcation as the default transaction object 1. find the application, that is, the place where your program writes the first open 2. click view> propreties in the menu to open the properties of the application. 3. click additional Properties-> variable Types-> sqlca and change it to uo_transcation 4. OK4. use sqlca directly in the program. p_get_name (a, B, c) is the same as normal functions. Note: in fact, this is also a method for PB to call the stored procedure.