MySql database stored procedure learning _ MySQL

I used to hear from others about the stored procedure at work. I thought it was a very advanced thing. I used my spare time to read the relevant knowledge. now I will summarize my learning knowledge as follows, I hope to provide some help to people who are new to learning. Development Environment: NavicatForMysql. MySQL Stored Procedure 1. I used to hear from others about the stored procedure at work. I thought it was a very advanced thing. I used my spare time to read the relevant knowledge. now I will summarize my learning knowledge as follows, I hope to provide some help to people who are new to learning.

Development Environment: Navicat For Mysql.

MySQL stored procedure

1.1 create procedure (CREATE)

Create procedure stored PROCEDURE name (parameter list)

BEGIN

SQL statement code block

END

Note:

Parameter columns enclosed by parentheses must always exist. If no parameter exists, use an empty parameter column (). Each parameter is an IN parameter by default. To specify other parameters, you can use the keyword OUT or INOUT before the parameter name.

Instance drill:

Eg1, stored procedure with (output parameter) return value:

1. create a table

Create table abin5 (

Id int,

Name5 VARCHAR (39)

)

2. create a stored procedure

Create procedure pabin5 (out n int)

BEGIN

Select count (*) from abin5;

END

3. test the stored procedure

Call pabin5 (@ n)

Eg2, stored procedure with input parameters:

1. create a stored procedure

Create procedure pabin6 (in n int)

BEGIN

SELECT * FROM abin5 where id = n;

END

2. test the stored procedure

SET @ n = 1;

CALL pabin6 (@ n)

Or

CALL pabin6 (1)

When the mysql client defines the stored procedure, use the delimiter command to change the statement delimiter from; //.

When using the delimiter command, you should avoid using the backslash ('"'), because it is an escape character of MySQL.

For example:

Mysql> delimiter //

Mysql> create procedure simpleproc (OUT param1 INT)

-> BEGIN

-> Select count (*) INTO param1 FROM t;

-> END

-> //

Query OK, 0 rows affected (0.00 sec)

1.2 alter procedure (Modify)

Alter procedure stored PROCEDURE name SQL statement code block

This statement can be used to change the features of a stored program.

1.3 drop 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. you can only call another stored procedure.

1.4 show create procedure (similar to show create table, view an existing Stored PROCEDURE)

Show create procedure stored PROCEDURE name

1.5 show procedure status (list all stored procedures)

SHOW PROCEDURE STATUS

1.6 CALL statement (stored procedure CALL)

CALL stored procedure name (parameter list)

The CALL statement calls a previously created program using create procedure.

The CALL statement can return a value to its caller using the INOUT parameter declared as OUT or.

The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters passed

1.7 BEGIN... END (compound statement)

[Begin_label:]

BEGIN

[Statement_list]

END

[End_label]

You can use the in... END compound statement to store subprograms to contain multiple statements.

Statement_list indicates the list of one or more statements. Each statement in statement_list must end 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.8 DECLARE statement (used to DECLARE local variables)

The DECLARE statement is used to place different projects to a sub-program: local variables

DECLARE is only used in the in... END compound statement and must BEGIN with the compound statement before any other statement.

1.9 variables in the storage program

1.1 DECLARE local variables

DECLARE var_name [,...] type [DEFAULT value]

This statement is used to declare local variables.

To provide a DEFAULT value for a variable, include a DEFAULT clause.

The value can be specified as an expression and does not need to be a constant.

If no DEFAULT clause exists, the initial value is NULL.

The scope of a local variable is within the declared BEGIN... END block.

It can be used in nested blocks, except those that declare variables with the same name.

1.2 SET statement

SET var_name = expr [, var_name = expr]

The SET statement in the storage program is an extended version of the general SET statement.

The referenced variable may be a variable declared in the 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 the SET a = x, B = y,... extension syntax.

Different variable types (local declaration variables and global and collective variables) can be mixed.

This also allows you to combine local variables with some options that only make sense to system variables.

1.3 SELECT... INTO statement

SELECT col_name [,...] INTO var_name [,...] table_expr

This SELECT syntax stores the selected columns directly to variables.

Therefore, only a single row can be retrieved.

SELECT id, data INTO x, y FROM test. t1 LIMIT 1;

Note that User variable names are case-insensitive in MySQL 5.1.

Important: The SQL variable name cannot be the same as the column name. If an SQL statement such as SELECT... INTO contains a reference to a column and a local variable with the same name as the column, MySQL interprets the reference as the name of a variable.

1.10 MySQL stored procedure parameter types (in, out, inout)

MySQL Stored Procedure parameters (in)

MySQL stored procedure "in" parameter: similar to the value passing of function parameters in C language, MySQL stored procedure may modify this parameter internally, but modify the in type parameter, not visible for callers ).

MySQL Stored Procedure parameters (out)

MySQL stored procedure "out" parameter: transfers a value from the stored procedure to the caller. In a stored procedure, the initial value of this parameter is null, regardless of whether the caller sets a value for the stored procedure parameter.

MySQL Stored Procedure parameters (inout)

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

Summary

If you only want to pass data to the MySQL stored procedure, use the "in" type parameter. if you only return values from the MySQL stored procedure, use the "out" type parameter; if you need to pass the data to the MySQL stored procedure, you need to pass it back to us after some computation. in this case, you need to use the "inout" type parameter.

1.11 example:

1.1 Create a stored procedure

Stored procedure with (output parameter) return value:

-- Delete a stored procedure

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 calls the stored procedure

CALL proc_employee_getCount (@ n );

Stored procedure with input parameters:

-- Delete a stored procedure

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

-- Define variables

SET @ n = 1;

-- Call a stored procedure

CALL proc_employee_findById (@ n );

Note the following when operating the stored procedure:

1. when deleting a stored procedure, you only need to specify the name of the stored procedure without parentheses;

2. when creating a stored procedure, brackets are required no matter whether the stored procedure has any parameters;

3. The SET syntax rules should be followed when using SET to define variables;

SET @ variable name = initial value;

4. when defining the stored procedure parameter list, note that the parameter name is different from the field name in the database. otherwise, unexpected results may occur.

1.12 Java code call stored procedure (JDBC)

Related APIs: java. SQL. CallableStatement

The java. SQL. CallableStatement interface is used to call stored procedures;

Obtaining this object depends on java. SQL. Connection;

The CallableStatement object is returned using the prepareCall () method of the Connection instance.

PrepareCall () is a fixed internally written {call stored procedure name (parameter list 1, parameter list 2)} is available? Placeholder

Eg: connection. prepareCall ("{call proc_employee (?)} ");

Processing parameters in stored procedures:

Input parameter: assign values using the setXXX () method of the java. SQL. CallableStatement instance. the usage is equivalent to that of java. SQL. PreparedStatement.

Output parameters: values are assigned using the registerOutParameter (parameter location, parameter type) method of the java. SQL. CallableStatement instance. the parameter Types mainly use the Types defined in java. SQL. Types.

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

Publicvoid executeProcedure () {try {/*** callableStatementjava. SQL. callableStatement * connectionjava. SQL. connection * jdbc call stored procedure prototype * {call stored procedure name (parameter list 1, parameter list 2)} available? Replace */callableStatement = connection. prepareCall ("{call proc_employee_findById (?)} "); CallableStatement. setInt (1, 1); // Set the input parameter resultSet=callableStatement.exe cuteQuery (); // execute the 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 output parameters (returns the number of records in the database)

Publicvoid executeProcedure () {try {/*** callableStatementjava. SQL. callableStatement * connectionjava. SQL. connection * jdbc call stored procedure prototype * {call stored procedure name (parameter list 1, parameter list 2)} available? Replace */callableStatement = connection. prepareCall ("{call proc_employee_getCount (?)} "); // Set the output parameter callableStatement. registerOutParameter (1, Types. INTEGER); // execute the stored procedure resultSet=callableStatement.exe cuteQuery (); if (resultSet. next () {System. out. println (resultSet. getInt (1) ;}} catch (SQLException e) {e. printStackTrace ();}}