MySQL stored procedures

Database stored procedures can reduce the repetition of program code, make the program more concise and understandable

Some interesting questions have recently been encountered in learning MySQL stored procedures:

1, in writing the stored procedure, the stored procedure name must be appended with parentheses, even if there are no parameters to add, otherwise it will be an error

2. Stored procedures without parameters can be enclosed in parentheses or without parentheses when the stored procedure is called

3. Because MySQL is case-insensitive, this is not the same as SQL. So when you give the stored procedure a name, do not use the lowercase letter of the column name, or it will appear to change or manipulate a record, the result is to modify the entire table

 1  create  procedure  update_name (in  name varchar  (in  ID int  )  2   3  update   student  4  set  sname=  name where  id=  id;  5  end  

This code should have changed a record, and the result would have changed the entire table record. Id=id is always true because it is not case-sensitive.

4. Call the main code of the stored procedure in the MyBatis program:

A stored procedure call that is not a parameter is relatively straightforward:

1 < Select id="Selectallstudent" statementtype="callable">2    <! [cdata[  3    {call selectallstudent ()}4    ] ]>5   </Select>

The ID here must be the same as in the interface.

There are two methods for stored procedure calls with parameters:

The first type:

In XML:

1 < Select id="Updatenamebyid" statementtype="callable">2   <! [cdata[  3    {call Update_name (#{id},#{name})}4    ]] > 5  </ Select >

In the DAO layer interface:

1 void Updatenamebyid (@Param ("id") int ID, @Param ("name") of String psid);

The parameter name must correspond, and the failure of the binding will not occur.

The second type:

In XML:

1 <Parametermap type="Java.util.HashMap" id="Update_name">2 <Parameter property="id" jdbctype="INTEGER"Javatype="Java.lang.Integer"Mode="inch"/> 3 <Parameter property="Name" Jdbctype="VARCHAR"Javatype="Java.lang.String" mode="inch"/> 4 </Parametermap>5 6   7 <SelectId="Updatenamebyid" Parametermap="Update_name" StatementType="Callable">8 <![cdata[9 {call Update_psid (?,?)}Ten    ]]>  One  </Select>

In the DAO layer interface:

1 void Updatenamebyid (map<string,object> map);

The parameter name of the Parametermap here cannot be empty, otherwise it will be wrong

5. When calling a stored procedure that returns a result set, there should be a corresponding resultmap, and the parameter cannot be empty, otherwise there will be an inexplicable error. The server will not see this error when it is started, but will appear during testing

The mapper in the program is somehow not corresponding to the reason that result Map has no value in the wrong mapper.