An Oracle stored procedure

Open Plsql, create a new stored procedure in the Packages folder

Run the following statement in the SQL window

Create or Replace package SY_USER_PKG1 is

TYPE MyCursor is REF CURSOR;

--Write your own test stored procedure
PROCEDURE T_chcode1
(
V_phone in VARCHAR2,
V_usertype in number,
V_type in number,
V_err_code out number
);
End Sy_user_pkg1;

The stored procedure tag appears under the Packages folder, which is equivalent to an interface in Java!

Then: write the specific stored procedure, it is equivalent to the Java Interface Implementation Class bar

Create or Replace package body SY_USER_PKG1 is

PROCEDURE T_chcode1
(
V_phone in VARCHAR2,
V_usertype in number,
V_type in number,
V_err_code out number
)
Is
V_usercount number;
BEGIN

IF V_type=1 Then
SELECT COUNT (1) into V_usercount from T_user_register_info A WHERE A.account=v_phone and A.user_type_id=v_usertype;
IF V_usercount=1 Then
V_err_code:=1;
Else
v_err_code:=0;
END IF;
End If;

IF v_type=2 Then
SELECT COUNT (1) into V_usercount from T_user_register_info A WHERE A.account=v_phone and A.user_type_id=v_usertype;
IF V_usercount=0 Then
v_err_code:=2;
Else
v_err_code:=0;
END IF;
END IF;

End T_chcode1;
End Sy_user_pkg1;

After this code is run by F8, this will appear under the Packages Bodyies folder.

This is a complete stored procedure, there are interfaces, there are implementations

Now let's test it.

Right-click, there will be test options

Fill in parameters, such as, F8 run, results such as

You can see that you've got the returned result value 0

It can then be called in the Java code, as in the mapper configuration file:

<insert id= "Chcode" parametertype= "Java.util.Map" statementtype= "callable" >
<! [cdata[
{Call SY_USER_PKG1. T_chcode1 (

#{v_phone,mode=in,jdbctype=nvarchar},
#{v_usertype,mode=in,jdbctype=numeric},
#{v_type,mode=in,jdbctype=numeric},
#{v_err_code,mode=out,jdbctype=numeric}
)
}

]]>

</insert>

Called directly in the service implementation class

Ocode is the return value you get.

At this point, the process of invoking a stored procedure is over!

An Oracle stored procedure