MySql database Stored Procedure Learning

MySql database Stored Procedure Learning

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 PROCEDUREStored Procedure name(Parameter List)    BEGIN SQLStatement 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)

InMysqlWhen the 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 PROCEDUREStored Procedure nameSQLStatement code block

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

1.3 drop procedure (delete)

DROP PROCEDURE IF EXISTSStored Procedure name Eg: drop procedure if exists proc_employee (proc_employeeStored 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 PROCEDUREStored Procedure name

1.5 show procedure status (list all stored procedures)

SHOW PROCEDURE STATUS

1.6 CALL Statement (Stored Procedure CALL)

CALLStored 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, includeDEFAULTClause. The value can be specified as an expression and does not need to be a constant. If noDEFAULTClause. The initial value isNULL. The scope of a local variable is that it is declaredBEGIN... ENDBlock. 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] InSETThe statement is normal.SETStatement extension version. The referenced variable may be a variable declared in the subroutine or a global server variable. InSETStatement as a pre-existingSETA part of the syntax. This allowsSET a = x, B = y ,...Such 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 ThisSELECTSyntax 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 the user variable name is inFor MySQL 5.1Is case insensitive.

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)

The content of this section is from:

See: http://www.blogjava.net/nonels/archive/2009/04/22/233324.html

MySQLStored 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 ).

MySQLStored 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.

MySQLStored 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.1Create a stored procedure

Stored Procedure with (output parameter) return value:

--Delete 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 -- MYSQLCall Stored Procedure CALL proc_employee_getCount (@ n );

Stored Procedure with input parameters:

--Delete 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 Stored Procedure CALL proc_employee_findById (@ n );

Note when operating stored procedures:

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)

RelatedAPI: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)

PublicVoidExecuteProcedure () { 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 (); // executes 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)

PublicVoidExecuteProcedure () { 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 output parameters 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 (); } }