MySQL Stored procedure call

MySQL Stored procedure Example tutorial

release Time: 2014-04-09 Editor: JB01

This article mainly describes the use of MySQL stored procedures, the MySQL stored procedure Example tutorial, there is a need for friends reference.

1.1create procedure (created)
Create procedure stored procedure name (parameter list)
Begin
SQL Statement code block
End
Attention:
The parameter column surrounded by parentheses must always exist. If there are no parameters, you should also use an empty parameter column (). Each parameter is an in parameter by default. To be specified as a different parameter, use the keyword out or inout before the parameter name
Use the delimiter command when the MySQL client defines the stored procedure to change the statement delimiter from;
When using the delimiter command, you should avoid using the backslash (' "') character, because that is the escape character of MySQL.
Such as:

Copy Code code example:Mysql> delimiter//
Mysql> CREATE PROCEDURE Simpleproc (out param1 int)
Begin
Select COUNT (*) to param1 from T;
-End
//
Query OK, 0 rows Affected (0.00 sec)

1.2alter procedure (modified)
Alter procedure stored procedure name SQL statement code block
This statement can be used to change the characteristics of a stored program.
1.3drop procedure (delete)
drop procedure if exists stored procedure name

Eg:drop procedure if exists proc_employee (Proc_employee stored procedure name)

This statement is used to remove a stored program. You cannot delete another stored procedure in one stored procedure, only another stored procedure can be called
1.4show CREATE PROCEDURE (similar to show create table to view an existing stored procedure)
Show create procedure stored procedure name
1.5show Procedure status (list all stored procedures)
Show procedure Status

1.6call statements (calls to stored procedures)
Call stored procedure name (parameter list)

The call statement invokes a program that was previously created with CREATE procedure.
The call statement can return a value to its caller with a parameter declared as out or the inout parameter.
The stored procedure name must be appended with parentheses, even if the stored procedure has no parameters passed
1.7begin ... end (compound statement)
[Begin_label:]
Begin
[Statement_list]
End
[End_label]
A storage subroutine can use the begin ... end Compound statement to contain multiple statements.

Statement_list represents a list of one or more statements. Each statement within Statement_list must be terminated with a semicolon (;).

Compound statements can be marked. Unless Begin_label exists, End_label cannot be given, and if both exist, they must be the same.

1.8declare statement (used to declare a local variable)
Declare statements are used to place different items into a sub-program: Local Variables

Declare is only used in the begin ... end compound statement, and must be at the beginning of the compound statement before any other statements.

1.9 Variables in the stored program
1.1 Declare local variables

Declare var_name[,...] Type [default value]
This statement is used to declare a local variable.
To provide a default value for a variable, include one of the defaults clauses.
The value can be specified as an expression and does not need to be a constant.
If there is no default clause, the initial value is null.
The scope of the local variable is within its declared begin ... end block.
It can be used in nested blocks, except those that declare variables with the same name.
1.2 Variable SET statement

Set var_name = expr [, var_name = expr]
The SET statement in the stored program is an extended version of the generic SET statement.
The referenced variable may be a variable declared within a subroutine, or a global server variable.
The SET statement in the stored program is implemented as part of the pre-existing set syntax. This allows set a=x, B=y, ... Such an extension syntax.
The different types of variables (local declaration variables and global and collective variables) can be mixed together.
This also allows the merging of local variables and some options that are meaningful only for system variables.
1.3 Select ... into statement

Select col_name[,...] into var_name[,...] table_expr
This select syntax stores the selected columns directly into the variable.
Therefore, only a single row can be retrieved.
Select Id,data into X, y from test.t1 limit 1;
Note that the user variable name is case insensitive in MySQL 5.1.
IMPORTANT: The SQL variable name cannot be the same as the column name. If the SELECT ... into SQL statement contains a reference to a column and contains a local variable with the same name as the column, MySQL currently interprets the reference as a variable name.

1.10 MySQL Stored procedure parameter type (in, out, inout)
MySQL stored procedure parameters (in)

MySQL stored procedure "in" parameter: similar to the value of the C language function parameter, this parameter may be modified inside the MySQL stored procedure, but modifications to the in type parameter are not visible to the caller (caller).

MySQL stored procedure parameters (out)

The MySQL stored procedure "out" parameter: passes the value from inside the stored procedure to the caller. Inside the stored procedure, the initial value of the parameter is NULL, regardless of whether the caller sets a value for the stored procedure parameter

MySQL stored procedure parameters (InOut)

The MySQL stored procedure inout parameter is similar to out, and can be passed from inside the stored procedure to the caller. The difference is that the caller can also pass the value to the stored procedure through the InOut parameter.

Summarize

If you just want to pass the data to the MySQL stored procedure, use the "in" type parameter, and if you only return the value from the MySQL stored procedure, use the "out" type parameter, and if you need to pass the data to the MySQL stored procedure, and then pass it back to us after some calculation, we use " InOut "type parameter.

1.11 Examples:
1.1 Creating a stored procedure
Stored procedure with the return value (output parameter):

Copy Code code example:--Delete stored procedures
drop procedure if exists proc_employee_getcount
--Create a stored procedure
CREATE PROCEDURE Proc_employee_getcount (out n int)
Begin
Select COUNT (*) from employee;
End
--mysql Calling stored Procedures
Call Proc_employee_getcount (@n);

Stored procedure with input parameters:

Copy Code code example:

--Delete stored procedures
drop procedure if exists Proc_employee_findbyid;

--Create a stored procedure
CREATE PROCEDURE Proc_employee_findbyid (in n int)
Begin
SELECT * from employee where id=n;
End
--Defining variables
Set @n=1;
--Call the stored procedure
Call Proc_employee_findbyid (@n);

You should be aware when manipulating stored procedures:

1. Delete stored procedures only need to specify the name of the stored procedure, without parentheses;

2. When you create a stored procedure, you need parentheses, regardless of whether or not the stored procedure has parameters;

3. The set syntax rules should be followed when defining variables using Set;

SET @ variable name = initial value;

4. When defining a stored procedure parameter list, be aware that the parameter name differs from the field name in the database, otherwise unexpected results will occur

1.12 Java code Call stored procedure (JDBC)
Related api:java.sql.callablestatement

Use to the Java.sql.callablestatement interface, which is specifically used to invoke stored procedures;

The acquisition of the object depends on the java.sql.connection;

Returns the CallableStatement object through the Preparecall () method of the connection instance

Preparecall () internal is a fixed notation {call stored procedure name (parameter list 1, parameter List 2)} available? placeholder

Eg:connection.preparecall ("{Call Proc_employee (?)}");

parameter handling in stored procedures:

Input parameters: Assigned by the Setxxx () method of the Java.sql.callablestatement instance, the usage is equivalent to Java.sql.preparedstatement

Output parameter: Assigns a value through the Registeroutparameter (parameter position, parameter type) method of the Java.sql.callablestatement instance, where the parameter type primarily uses the type defined in Java.sql.types

Java code calls a stored procedure with input parameters (query employee information based on input ID)

Copy Code code example:

Publicvoid Executeprocedure ()
{
try {
/**
*callablestatementjava.sql.callablestatement
*connectionjava.sql.connection
*JDBC calling a stored procedure prototype
*{call stored procedure name (parameter list 1, parameter List 2)} available? instead
*/

Callablestatement=connection.preparecall ("{Call Proc_employee_findbyid (?)}");
Callablestatement.setint (1, 1); Setting input parameters
Resultset=callablestatement.executequery ();//Execute stored Procedure
if (Resultset.next ())
{
System.out.println (Resultset.getint (1) + "T" +resultset.getstring (2));
}

} catch (SqlException e) {
E.printstacktrace ();
}
}

Java code calls a stored procedure with an output parameter (returns the number of records in the database)

Copy Code code example:

Publicvoid Executeprocedure ()
{
try {
/**
*callablestatementjava.sql.callablestatement
*connectionjava.sql.connection
*JDBC calling a stored procedure prototype
*{call stored procedure name (parameter list 1, parameter List 2)} available? instead
*/

Callablestatement=connection.preparecall ("{Call Proc_employee_getcount (?)}");

Setting output parameters
Callablestatement.registeroutparameter (1, Types.integer);

Executing stored procedures
Resultset=callablestatement.executequery ();

if (Resultset.next ())
{
System.out.println (Resultset.getint (1));
}

} catch (SqlException e) {
E.printstacktrace ();
}
}

MySQL Stored procedure call