MySQL stored procedure (Java)

This is a few things I care about when I study the MySQL stored procedure, and I hope that it will help you learn the stored procedure.

Grammar:

CREATE PROCEDURE Sp_name ([proc_parameter[,...]])

[Characteristic ...]
Begin
.......
End

Proc_parameter: A list of parameters for a stored procedure, separated by commas between multiple parameters
Format [in| Out| INOUT] Param_name Type

In (input parameter), out (output parameter), INOUT (output parameter and input);

Param_name is the name of the parameter;

Type is the data type of the parameter.

Characteristic: Used to describe storage characteristics

Stored procedures use logical syntax:

Logical judgment:

1. If judgment

IF expression then commands

[ELSEIF expression then commands]

[ELSE Commands]

END IF;

2. Case Judgment

Case Case_expression

When when_expression then commands

When when_expression then commands

ELSE commands

END case;

Cycle Judgment:

1. While ... Do ... END while
2. REPEAT ... UNTIL END REPEAT
3. LOOP ... END LOOP
4. GOTO

Java for Stored procedure operations

1. Get the stored procedure output value

--Stored procedure SQL
Createprocedure Tb_proint)begin    set=ten End

Java operations:

CallableStatement cs = con.preparecall (sql); Cs.registeroutparameter (1, Java.sql.Types.INTEGER); // the type of the out type parameter of the registered stored procedure, which must be registered before use ; Cs.execute (); System.out.println (Cs.getint (// get out output result

2. Get the query result set (from a select query), and how are multiple result sets handled?

--Stored procedure SQL
Createprocedure  Bach_pro ()beginSelect* from table1 ; Select *  from table2; End

Java operations:

CallableStatement cs =con.preparecall (SQL); Cs.execute (); ResultSet ResultSet=Cs.getresultset (); //iterate through the first set of results  while(Resultset.next ()) {System.out.println (Resultset.getint (1));//output Result set }        //get the next result setResultSet rs2; while(Cs.getmoreresults ()) {rs2=Cs.getresultset ();  while(Rs2.next ()) {System.out.println (Rs2.getint (1));//output result set columns      } }

3, when we want to process the query result set in the stored procedure, we need to use to the cursor, below is the cursor a simple use example

beginDeclareStopint default 0;--you need to declare a parameter before the cursorDeclareId_tempint; DeclareCur1cursor  for(SelectId fromXinguan);Declare ContinueHandler for  notFoundSetStop= 1;--DECLARE cursor after scan set value, used to end loopOpenCur1;cur1_loop:loopFetchCur1 intoId_temp;--Assigning a value to a variable from the cursor    ifStop Thenleave Cur1_loop; End if;Endloop Cur1_loop;CloseCur1;End

MySQL stored procedure (Java)