Shulou(Shulou.com)06/01 Report--

Stored procedures and functions of Mysql

A stored procedure is a collection of one or more SQL statements that can be treated as batch files, but its purpose is not just for batch processing.

Stored procedures are divided into: 1, stored procedures 2, functions

Using a call statement to call a stored procedure can only return a value with an output variable.

First, create stored procedures

Syntax:

Create procedure sp_name (proc_parameter) [characteristics...] Routine_body creates a stored function named sp_name, and the stored procedure is named proc_parameter

Specify that the list of storage parameters is:

[IN | OUT | INOUT] param_name type

Where IN represents input parameters, OUT represents output parameters, and INOUT means both input and output

Param_name represents the parameter name

Type represents a parameter type, which can be any type in the Mysql database.

Characteristics specifies the properties of the stored procedure, with the following values:

LANGUAGE SQL: indicates that the routine_body part is made up of SQL statements. The current language supported by the system is that SQL,SQL is the only value of the LANGUAGE feature.

[NOT] DETERMINISTIC: indicates whether the result of the stored procedure execution is correct.

1. DETERMINISTIC indicates that the result is correct. Each time the stored procedure is executed, the same input gets the same output.

2. NOT DETERMINISTIC indicates that the result is uncertain, and the same input may get different output. If no value is specified, the default is NOT DETERMINISTIC.

{CONTAINS SQL | NO SQL | REDAS SQL DATA | MODIFIES SQL DATA}: specify the restrictions on the use of SQL statements by subroutines.

1. CONTAINS SQL: indicates that the subroutine contains SQL statements, but does not contain statements that read and write data.

2. NO SQL: indicates that the subroutine does not contain SQL statements.

3. REDAS SQL DATA: a statement that states that a subroutine contains data.

4. MODIFIES SQL DATA: indicates that the subroutine contains statements to write data. The default is CONTAINS SQL.

SQL SECURITY {DEFINER | INVOKER}: indicates who has permission to execute.

1. DEFINER means that only the definer can execute.

2. INVOKER means that callers with permissions can execute. By default, the system is designated as DEFINER

COMMENT 'string': comment information, which can be used to describe stored procedures or functions.

Routine_body is the content of the SQL code. Begin is usually used. End represents the beginning and end of the SQL code.

Writing a stored procedure is not a simple matter, it may require complex SQL statements in the stored procedure and create the permissions of the stored procedure; but using the stored procedure will simplify the operation, reduce redundant operation steps, and at the same time, reduce errors in the operation process and improve efficiency, so the stored procedure is very useful and should be learned to use as much as possible.

Example 1:

Mysql > delimiter / / # defines the closing symbol of the SQL statement as / /. When using this command, you should avoid the ('\') character, because the backslash is the escape character of Mysql. Mysql > create procedure p1 ()-> begin-> select * from t;-> end / / mysql > delimiter; mysql > show procedure status\ G # View stored procedure information mysql > call p1 # read this stored procedure

Example 2:

Mysql > delimiter / / mysql > create procedure p2 (n int)-> begin-> select * from t where id = n;-> end / / mysql > delimiter; mysql > show procedure status\ Gmysql > call p2 (1) # input value is required

Example 3:

Mysql > create database db_proc;mysql > use db_procmysql > CREATE TABLE `proc_ test` (- > `id` tinyint (4) NOT NULL AUTO_INCREMENT,-> `username` varchar (20) NOT NULL,-> `password` varchar (20) NOT NULL,-> PRIMARY KEY (`id`)-> ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;mysql > delimiter / / mysql > create procedure mytest (in name varchar (20), in pwd varchar (20))-> begin-> insert into proc_test (username,password) values (name,pwd);-> end / / mysql > delimiter Mysql > call mytest ('lxq','password'); mysql > select * from proc_test; # verify that the data is inserted

************************

The parameters of mysql stored procedures are used in the definition of stored procedures. There are three parameter types, IN,OUT,INOUT.

Create procedure | function ([[IN | OUT | INOUT] parameter name data type.])

IN input parameters

The value that represents the parameter must be specified when the stored procedure is called. The value that modifies the parameter in the stored procedure cannot be returned and is the default value.

OUT output parameters

This value can be changed within the stored procedure and can be returned

INOUT input and output parameters

Specified when called and can be changed and returned

Example of IN parameters:

Mysql > DELIMITER / / mysql > CREATE PROCEDURE sp_demo_in_parameter (IN p_in INT)-> BEGIN-> SELECT pairin; # query input parameters-> SET pairin 2; # modify-> select pairin; # View the modified value-> END / / mysql > DELIMITER

Execution result:

Mysql > set @ pawning 1: MySQL > call sp_demo_in_parameter (@ p_in); mysql > select @ pairin. As can be seen above, although p_in is modified in the stored procedure, it does not affect the value of @ p_id.

Example of OUT parameters

Mysql > DELIMITER / / mysql > CREATE PROCEDURE sp_demo_out_parameter (OUT p_out INT)-> BEGIN-> SELECT pumped out; # View output parameters-> SET paired outbound 2; # modify parameter values-> SELECT paired outout; # see if there are any changes-> END / / mysql > DELIMITER

Execution result:

Mysql > SET @ paired outbound 1: MySQL > CALL sp_demo_out_parameter (@ p_out); mysql > SELECT @ paired out paired out is modified in the stored procedure, directly affecting the value of @ p_out

Example of INOUT parameters:

Mysql > DELIMITER / / mysql > CREATE PROCEDURE sp_demo_inout_parameter (INOUT p_inout INT)-> BEGIN-> SELECT paired inout;-> SET paired inoutout 2;-> SELECT paired inoutout;-> END;mysql > DELIMITER

Execution result:

Set @ paired inoutposts 1: call sp_demo_inout_parameter (@ p_inout); select @ p_inout

* *

II. Specific anomalies

In MySQL, specific exceptions require specific handling. These exceptions can be linked to errors, as well as general process control in subprograms. Defining an exception is a problem encountered in the execution of a program in advance. Exception handling defines the way to deal with a problem and ensures that the stored procedure or function can continue to execute when it encounters an error or warning.

1 exception definition

1.1 Grammar

DECLARE condition_name CONDITION FOR [condition_type]

1.2 description

Parameter condition_name indicates the name of the exception; parameter condition_type indicates the type of condition, and condition_type consists of SQLSTATE [VALUE] sqlstate_value | mysql_error_code: both sqlstate_value and mysql_error_code can indicate the error of MySQL; sqlstate_value is the error code of string type with length 5; mysql_error_code is the error code of numeric type

1.3 exampl

Error defining "ERROR 1148 (42000)" with the name command_not_allowed. There are two ways to do this:

# method 1: using sqlstate_valueDECLARE command_not_allowed CONDITION FOR SQLSTATE '42000 strategies # method 2: using mysql_error_codeDECLARE command_not_allowed CONDITION FOR 1148

2 Custom exception handling

2.1 exception handling syntax

DECLARE handler_type HANDLER FOR condition_value [,...] Sp_statement

2.2 Parameter description

Handler_type: CONTINUE | EXIT | UNDO handler_type is the error handling method. Parameter is one of three values. CONTINUE: do not handle the error and continue execution; EXIT: exit immediately if you encounter an error; UNDO: withdraw the previous operation after encountering an error. MySQL does not support rollback operation for the time being; condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code condition_value indicates the error type SQLSTATE [VALUE] sqlstate_value is a string error value containing 5 characters; condition_name represents the error condition name defined by DECLARE CONDITION; SQLWARNING matches all SQLSTATE error codes that begin with 01; NOT FOUND matches all SQLSTATE error codes that begin with 02; SQLEXCEPTION matches all SQLSTATE error codes that are not captured by SQLWARNING or NOT FOUND; mysql_error_code matches numeric type error codes

2.3 exception capture method

Method one: catch sqlstate_value exceptions. This method captures the sqlstate_ value. If you encounter a sqlstate_ value of "42S02", perform the CONTINUE operation and output the "NO_SUCH_TABLE" information DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @ info='NO_SUCH_TABLE'; method 2: catch the mysql_error_code exception this method is to capture the mysql_error_ code value. If you encounter a mysql_error_ code value of 1146, execute the CONTINUE operation and output the "NO_SUCH_TABLE" information DECLARE CONTINUE HANDLER FOR 1146 SET @ info='NO_SUCH_TABLE'; method 3: define the condition first, and then catch the exception DECLARE no_such_table CONDITION FOR 1146bot declare CONTINUE HANDLER FOR NO_SUCH_TABLE SET @ info='NO_SUCH_TABLE'; method 4: use SQLWARNING to catch exception DECLARE EXIT HANDLER FOR SQLWARNING SET @ info='ERROR'; method 5: use NOT FOUND to catch exception DECLARE EXIT HANDLER FOR NOT FOUND SET @ info='NO_SUCH_TABLE' Method 6: use SQLEXCEPTION to catch exceptions DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @ info='ERROR'; to define conditions and handlers: mysql > create table test.t (S1 int,primary key (S1)); mysql > delimiter / / mysql > create procedure handlerdermo ()-> begin-> declare CONTINUE HANDLER FOR SQLSTATE '23000' set @ x2 = 1;-> set @ x = 1;-> insert into test.t values (1);-> set @ x = 2;-> insert into test.t values (1) -> set @ x = 3;-> end / / mysql > delimiter; mysql > call handlerdermo (); mysql > select @ x politics MySQL > select * from test.t

Third, function

Function: improve the reuse rate of code

A function can call methods in a function to implement some functions.

Use now () to implement the null parameter function:

Mysql > select now (); +-+ | now () | +-+ | 2018-08-16 18:19:09 | +-+ mysql > select date_format (now (),'% Y year% m month% d% H point% I% s seconds') +-- + | date_format (now () '% Y year% m month% d% H point% I minutes% s seconds') | +-- + | 18:19:57 on August 16, 2018 | +- -- +

Mysql > create function my_time () returns varchar (50)-> return date_format (now (),'% Y-%m-%d% HMI% iMel% s'); Query OK, 0 rows affected (0.00 sec) mysql > select my_time () +-+ | my_time () | +-+ | 2018-08-16 18-22-10 | +-+

Functions are divided into empty parameter functions and parameter transfer functions.

Note: the function must have a return value type described in returns

Returns is followed by the function body.

If there is only a single item in the function body, it will be described directly.

If there are multiple entries in the function body after returns, the begin starts and the function body ends. Write end to end.

Be sure to determine the return value before end

-

Create a parameter-passing function:

Mysql > CREATE FUNCTION cont_AVG (num1 int,num2 int) RETURNS decimal (8Power2)-> RETURN (num1+num2) / 2persQuery OK, 0 rows affected (0.00 sec) mysql > select cont_AVG (2L2); +-+ | cont_AVG (2L2) | +-+ | 2.00 | +-+ 1 row in set (0.00 sec) mysql > select cont_AVG (3L2) +-+ | cont_AVG (3Jing 2) | +-+ | 2.50 | +-+

Create a multi-function body parameter function to add users to the stu table:

Mysql > desc stu +-+ | Field | Type | Null | Key | Default | Extra | +-+- + | id | int (11) | NO | | NULL | | name | varchar (10) | NO | | NULL | | +-+-+ mysql > select * from stu Empty set (0.00 sec)

Mysql > delimiter / / mysql > create function adduse (u_id int unsigned,u_name varchar (10))-> returns int unsigned-> begin-> insert stu values;-> return last_insert_id ();-> end / / mysql > delimiter; mysql > select adduse +-+ | adduse (1 sec') | +-+ | 0 | +-+ 1 row in set (0.02 sec) mysql > select adduse (2) +-+ | adduse (2 sec ls') | +-+ | 0 | +-+ 1 row in set (0.01 sec) mysql > select adduse (3 recording www.') +-+ | adduse (3 sec) | +-+ | 0 | +-+ 1 row in set (0.02 sec) mysql > select * from stu +-+-- +-+ | id | name | +-+-+ | 1 | zs | | 2 | ls | | 3 | ww | +-+-+ 3 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.