Mysql stored procedure pioneer _ MySQL

A stored procedure can execute multiple statements at a time to process complicated business logic and complete some calculations. This blog summarizes the basic usage of mysql stored procedures. We will discuss from two aspects: how to write a stored procedure and how to call it: 1. a stored procedure in mysql can execute multiple statements at a time to process complex business logic and complete some calculations.
This blog summarizes the basic usage of mysql stored procedures-mysql stored procedures are deserted. We will discuss how to write a stored procedure and how to call it:

I. usage of stored procedures in mysql

Note that the following example can be run directly in the mysql management tool (my navicat). to use the mysql client (dos window), add the delimiter $ separator.

First, let's look at the first example:
This stored procedure has two int-type input parameters and one varchar-type output parameter.
Perform database operations or calculations before begin and end,
Declare is used to declare an int type variable,
The next part is an if judgment. Note that then and end if need to be followed. this is the complete if judgment.
Select statement for output, you can directly use select'* 'Output, or use theAdd a column name
After the stored procedure is compiled correctly, call it with call. here an output parameter is required, so we define a variable @ p_in.

Use etoak; drop procedure if exists t1; create procedure t1 (in a int, in B int, out d varchar (30) begin declare c int; if a is null then set a = 0; end if; if B is null then set B = 0; end if; set c = a + B;/* select c as sum; */select's 'into d; select d as 'Haha '; -- output an end column;/* call the stored procedure */set @ p_in = 1; call t1 (10, 1, @ p_in );

We use the if then condition to determine the above conditions. The following describes how to use case when to complete more conditions:

drop procedure if exists t1;create procedure t1(in a int,in b int,out c varchar(30))begin declare d int; set d = a+1; case d when 1 then insert into student values(null,'dx',11,now()); when 2 then insert into student values(null,'aa',11,now()); else insert into student values(null,'bb',11,now()); end case; select * from student;end;

Let's look at two cycles: a while do loop and a loop:

/* Use the while do loop */create procedure t1 () begin declare I int DEFAULT 0; while I <5 DO insert into student (name) values (I ); set I = I + 1; end while; select * from student; end;/* use loop */drop procedure if exists t1; create procedure t1 () begin declare I int DEFAULT 0; loop_label: LOOP if I = 3 THEN set I = I + 1; ITERATE loop_label; -- iterate is equivalent to the continue end if in a java LOOP; insert into student values (null, I, I, now (); set I = I + 1; if I> = 5 THEN leave loop_label; end if; end loop; select * from student; end;

There are also common fuzzy queries:

/* Fuzzy query */drop procedure if exists t1; create procedure t1 (in a varchar (30), out c varchar (30) begin declare d int; select * from student where name like concat ('%', a, '%'); end;

In this example, you must note that the concat concatenation string function is used.

II. how to call a stored procedure in java code

Through the above, we know that we can call the stored procedure through call in the mysql client. how can we call the stored procedure in the java code?
Let's take a look at the following example to call a stored procedure with input and output parameters using jdbc:
The stored procedure is as follows to implement simple addition:

create procedure t1(in a int,in b int,out d int)begin declare c int; if a is null then set a = 0; end if; if b is null then set b = 0; end if; set c = a + b; select c into d;end;

Calling through jdbc in java:

Import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. SQLException; import java. SQL. types; public class TestProc {public static void main (String [] args) throws SQLException {TestProc tp = new TestProc (); int a = tp. testPro (5, 6); System. out. println (a); // Print the output value} // Obtain the database connection private static DBConnection dbConnection = null; static {if (null = dbConnection) {dbConnection = new DB Connection () ;}// method for executing the stored procedure public int testPro (int a, int B) throws SQLException {Connection conn = null; CallableStatement stmt = null; int out = 0; String SQL = ""; try {conn = dbConnection. getConnection (); stmt = conn. prepareCall ("{call t1 (?,?,?) } "); Stmt. setInt (1, a); stmt. setInt (2, B); stmt. registerOutParameter (3, Types. INTEGER); stmt.exe cute (); out = stmt. getInt (3); // Obtain the output parameter} finally {dbConnection. close (conn); dbConnection. close (stmt) ;}return out ;}}

Call the stored procedure in mybatis:

Declaration interface:

public Map proc(Map map);

xml:

        {call t1(            #{firstParam,jdbcType=INTEGER,mode=IN},            #{secondParam,jdbcType=INTEGER,mode=IN},            #{outParam,jdbcType=INTEGER,mode=OUT}        )}    

Test:

Map map = new HashMap(); map.put("firstParam",1); map.put("second", 2); bi.proc(map); System.out.println(map.toString());

Note the following:
The put output parameter is not required in the input parameter map of mybatis. after the stored procedure is executed, the output parameter is automatically placed in the map. Therefore, the output is as follows:

{second=2, firstParam=1, outParam=1}