Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to write stored procedures and functions in MYSQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

What is a stored procedure?

To put it simply, it is a set of SQL statements, which is powerful and can implement some complex logic functions, similar to the methods in the JAVA language.

Ps: stored procedures are similar to triggers in that they are a set of SQL sets, but stored procedures are called actively and are more powerful than triggers, which are called automatically after something is triggered.

What are the characteristics?

There are input and output parameters, variables can be declared, if/else, case,while and other control statements, by writing stored procedures, you can achieve complex logic functions

General features of functions: modularization, encapsulation, code reuse

Fast, only the first execution needs to go through the compilation and optimization steps, and the subsequent calls can be executed directly, omitting the above steps

The creation of MySQL stored procedure

Grammar

CREATE PROCEDURE sp_name ([proc_parameter [,...]]) [characteristic...] Routine_body

CREATE PROCEDURE procedure name ([[IN | OUT | INOUT] parameter name data type [, [IN | OUT | INOUT] parameter name data type …]]) [features.] Process body

DELIMITER / / CREATE PROCEDURE myproc (OUT s int) BEGIN SELECT COUNT (*) INTO s FROM students; END / / DELIMITER

Separator

MySQL defaults to ";". If the delimiter is not declared, the compiler will treat the stored procedure as a SQL statement, so the compilation process will report an error, so declare the current segment delimiter with "DELIMITER / /" in advance, so that the compiler will not execute the code between the two "/ /" as the code of the stored procedure; "DELIMITER;" means to restore the delimiter.

Parameters.

Stored procedures may have input, output, input and output parameters as needed, and if there are multiple parameters, they may be separated by ",". The parameters of MySQL stored procedures are used in the definition of stored procedures. There are three parameter types, IN,OUT,INOUT:

The value of the IN parameter must be specified when calling the stored procedure. The value that modifies the parameter in the stored procedure cannot be returned and is the default value.

OUT: this value can be changed inside the stored procedure and can be returned

INOUT: specified when called, and can be changed and returned

Where the sp_name parameter is the name of the stored procedure; proc_parameter represents the parameter list of the stored procedure; the characteristic parameter specifies the characteristics of the stored procedure; the routine_body parameter is the content of the SQL code, which can be used with BEGIN. END to mark the beginning and end of the SQL code.

Each parameter in proc_parameter consists of three parts. These three parts are input and output type, parameter name and parameter type, respectively. It takes the following form:

[IN | OUT | INOUT] param_name type

Where IN represents the input parameter; OUT represents the output parameter; INOUT represents either input or output; the param_name parameter is the parameter name of the stored procedure; and the type parameter specifies the parameter type of the stored procedure, which can be any data type of the MySQL database.

The characteristic parameter has multiple values. The values are described as follows:

LANGUAGE SQL: indicates that the routine_body part is made up of SQL language statements, which is also the default language of the database system.

[NOT] DETERMINISTIC: indicates whether the execution result of the stored procedure is determined. DETERMINISTIC indicates that the result is certain. Each time the stored procedure is executed, the same input gets the same output. NOT DETERMINISTIC indicates that the result is indeterminate, and the same input may get different output. By default, the result is indeterminate.

{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}: specify the restrictions on the use of SQL statements by subroutines. CONTAINS SQL indicates that the subroutine contains SQL statements, but does not contain statements to read or write data; NO SQL indicates that the subroutine does not contain SQL statements; READS SQL DATA indicates that the subroutine contains statements to read data; and MODIFIES SQL DATA indicates that the subroutine contains statements to write data. By default, the system is specified as CONTAINS SQL.

SQL SECURITY {DEFINER | INVOKER}: indicates who has permission to execute. DEFINER means that only the definer can execute; INVOKER means that the caller can execute. By default, the permission specified by the system is DEFINER.

COMMENT 'string': comment information.

Tip: when creating a stored procedure, the system specifies CONTAINS SQL by default, indicating that the SQL statement is used in the stored procedure. However, if the SQL statement is not used in the stored procedure, it is best set to NO SQL. Also, it is best to simply comment the stored procedure in the COMMENT section of the stored procedure to make it easier to read the code of the stored procedure later.

[example 1] Let's create a stored procedure called num_from_employee. The code is as follows:

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT) READS SQL DATA BEGIN SELECT COUNT (*) INTO count_num FROM employee WHERE d_id=emp_id; END

In the above code, the stored procedure is named num_from_employee;. The input variable is emp_id;. The output variable is count_num. The SELECT statement queries the employee table for records whose d _ id value is equal to emp_id, calculates the number of records with the same d _ id value with COUNT (*), and finally stores the result in count_num. The execution result of the code is as follows:

Mysql > DELIMITER & & mysql > CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT)-> READS SQL DATA-> BEGIN-> SELECT COUNT (*) INTO count_num-> FROM employee-> WHERE d_id=emp_id;-> END & & Query OK, 0 rows affected (0.09 sec) mysql > DELIMITER

After the code is executed, no error message is reported to indicate that the storage function has been created successfully. The stored procedure can be called later, and the SQL statement in the stored procedure is executed in the database.

Note: the default statement Terminator in MySQL is the semicolon (;). SQL statements in stored procedures require semicolons to end. To avoid conflicts, first set the MySQL Terminator to & & with "DELIMITER & &". Finally, use "DELIMITER;" to restore the Terminator to a semicolon. This is the same as when you create a trigger.

Function

In MySQL, the basic form of creating a storage function is as follows:

CREATE FUNCTION sp_name ([func_parameter [,...]]) RETURNS type [characteristic...] Routine_body

Among them, the sp_name parameter is the name of the storage function; func_parameter represents the parameter list of the storage function; RETURNS type specifies the type of return value; the characteristic parameter specifies the characteristics of the storage function, and the value of this parameter is the same as that in the stored procedure, please refer to section 14.1.1; routine_body parameter is the content of SQL code, you can use BEGIN … END to mark the beginning and end of the SQL code.

A func_parameter can consist of multiple parameters, each of which consists of a parameter name and a parameter type, in the form of: param_name type

Where the param_name parameter is the parameter name of the storage function; the type parameter specifies the parameter type of the storage function, which can be any data type of the MySQL database.

[example 2] Let's create a storage function called name_from_employee. The code is as follows:

CREATE FUNCTION name_from_employee (emp_id INT) RETURNS VARCHAR (20) BEGIN RETURN (SELECT name FROM employee WHERE num=emp_id); END

In the above code, the name of the stored function is name_from_employee;, and the argument to the function is emp_id;. The return value is of type VARCHAR. The SELECT statement queries a record with a numb value equal to emp_id from the employee table and returns the value of the record's name field. The execution result of the code is as follows:

Mysql > DELIMITER & & mysql > CREATE FUNCTION name_from_employee (emp_id INT)-> RETURNS VARCHAR (20)-> BEGIN-> RETURN (SELECT name-> FROM employee-> WHERE num=emp_id);-> END&& Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER

The results show that the storage function has been created successfully. This function is used in the same way as MySQL internal functions.

The use of variables

Variables can be defined and used in stored procedures and functions. Users can use the DECLARE keyword to define variables. You can then assign a value to the variable. The scope of these variables is BEGIN... In the END program segment. This section explains how to define variables and assign values to variables.

1. Define variables

You can use the DECLARE keyword in MySQL to define variables. The basic syntax for defining variables is as follows:

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

Where the DECLARE keyword is used to declare variables; the var_name parameter is the name of the variable, and multiple variables can be defined at the same time; the type parameter is used to specify the type of variable; the DEFAULT value clause sets the default value of the variable to value, and when the DEFAULT clause is not used, the default value is NULL.

[example 3] the variable my_sql is defined below, the data type is int, and the default value is 10. The code is as follows:

DECLARE my_sql INT DEFAULT 10

2. Assign values to variables

You can use the SET keyword in MySQL to assign values to variables. The basic syntax of the SET statement is as follows:

SET var_name = expr [, var_name = expr].

Where the SET keyword is used to assign values to variables; the var_name parameter is the name of the variable; and the expr parameter is the assignment expression. A SET statement can assign values to multiple variables at the same time, and the assignment statements of each variable are separated by commas.

[example 4] the variable my_sql is assigned a value of 30 below. The code is as follows:

SET my_sql = 30

SELECT can also be used in MySQL. The INTO statement assigns values to variables. Its basic syntax is as follows:

SELECT col_name [,...] INTO var_name [,...] FROM table_name WEHRE condition

Where the col_name parameter represents the field name of the query; the var_name parameter is the name of the variable; the table_name parameter refers to the name of the table; and the condition parameter refers to the query condition.

[example 5] Let's query the record with id 2 from the employee table and assign the d_id value of the record to the variable my_sql. The code is as follows:

SELECT d_id INTO my_sql FROM employee WEHRE id=2

Define conditions and handlers

Defining conditions and handlers is a problem that may be encountered during the execution of a pre-defined program. And solutions to these problems can be defined in the handler. This approach can predict possible problems in advance and propose solutions. This can enhance the ability of the program to deal with problems and prevent the program from stopping abnormally. Conditions and handlers are defined through the DECLARE keyword in MySQL. This section explains in detail how to define conditions and handlers.

1. Define conditions

Conditions can be defined using the DECLARE keyword in MySQL. Its basic syntax is as follows:

DECLARE condition_name CONDITION FOR condition_value condition_value: SQLSTATE [VALUE] sqlstate_value | mysql_error_code

Where the condition_name parameter represents the name of the condition; the condition_value parameter indicates the type of the condition; and both the sqlstate_value parameter and the mysql_error_code parameter can indicate the error of the MySQL. For example, in ERROR 1146 (42S02), the sqlstate_ value is 42S02, and the mysqlkeeper error _ code value is 1146.

[example 6] the error "ERROR 1146 (42S02)" is defined below, named can_not_find. You can define it in two different ways, with the following code:

/ / method 1: use sqlstate_value DECLARE can_not_find CONDITION FOR SQLSTATE '42S02'; / / method 2: use mysql_error_code DECLARE can_not_find CONDITION FOR 1146

2. Define handlers

The DECLARE keyword can be used in MySQL to define handlers. Its basic syntax is as follows:

DECLARE handler_type HANDLER FOR condition_value [,...] Sp_statement handler_type: CONTINUE | EXIT | UNDO condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

Where the handler_type parameter indicates how to handle the error, which has three values. These three values are CONTINUE, EXIT and UNDO, respectively. CONTINUE means to continue execution without handling errors; EXIT means to exit immediately when you encounter an error; UNDO means to withdraw the previous operation if you encounter an error. This method is not supported in MySQL yet.

Note: in general, if you encounter an error during execution, you should immediately stop executing the following statement and withdraw the previous action. However, UNDO operations are not yet supported in MySQL. Therefore, it is best to EXIT when you encounter an error. If you can predict the type of error in advance and handle it accordingly, you can perform a CONTINUE operation.

The condition_value parameter, which indicates the error type, has six values. Sqlstate_value and mysql_error_code have the same meaning as the condition definition. Condition_name is the condition name defined by DECLARE. SQLWARNING represents all sqlstate_ values that begin with 01. NOT FOUND represents all sqlstate_ value values starting with 02. SQLEXCEPTION represents all sqlstate_ values that have not been captured by SQLWARNING or NOT FOUND. Sp_statement represents the execution statement of some stored procedure or function.

[example 7] here are several ways to define handlers. The code is as follows:

/ / method 1: capture sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'SET @ info='CAN NOT FIND'; / / method 2: capture mysql_error_code DECLARE CONTINUE HANDLER FOR 1146 SET @ info='CAN NOT FIND'; / / method 3: define the condition first, and then call DECLARE can_not_find CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR can_not_find SET @ info='CAN NOT FIND'; / / method 4: use SQLWARNING DECLARE EXIT HANDLER FOR SQLWARNING SET @ info='ERROR' / / method 5: use NOT FOUND DECLARE EXIT HANDLER FOR NOT FOUND SET @ info='CAN NOT FIND'; / / method 6: use SQLEXCEPTION DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @ info='ERROR'

The above code is six ways to define handlers.

The first method is to capture the sqlstate_ value. If a sqlstate_ value of 42S02 is encountered, the CONTINUE operation is performed and the "CAN NOT FIND" information is output.

The second method is to capture the mysql_error_ code value. If a mysql_error_ code value of 1146 is encountered, the CONTINUE operation is performed and the "CAN NOT FIND" information is output.

The third method is to define the condition before invoking the condition. Here, the can_not_find condition is defined, and the CONTINUE operation is performed when a 1146 error is encountered.

The fourth method is to use SQLWARNING. SQLWARNING captures all sqlstate_ values that begin with 01, then performs the EXIT operation, and outputs "ERROR" information.

The fifth method is to use NOT FOUND. NOT FOUND captures all sqlstate_ values that start with 02, then performs the EXIT operation, and outputs "CAN NOT FIND" information.

The sixth method is to use SQLEXCEPTION. SQLEXCEPTION captures all sqlstate_ values that are not captured by SQLWARNING or NOT FOUND, then performs an EXIT operation and outputs "ERROR" information.

Summary of MySQL stored procedure Writing

1. Create a no-parameter stored procedure.

Create procedure product () begin select * from user;end

A simple stored procedure creation statement is called at this time:

Call procedure ()

# # Note: if you write it under the command line, there will be a syntax error in this way, that is, at the end of the select sentence.

Mysql will explain, and you should first change the ending character:

Delimiter / / create procedure product () begin select * from user;end / /

And then change it back.

Delimiter

2. Create a stored procedure with parameters

The storage with parameters includes two parameters.

One is the incoming parameter

One is the outgoing parameter.

For example, a stored procedure:

Create procedure procedure2 (out p1 decimal), out p2 decimal (8L2), in p3 int) beginselect sum (uid) into p1 from user where order_name = p3 select avg (uid) into p2 from user; end

As you can see from the above sql statement, p1 and p2 are the values used to retrieve and pass out, while p3 is the specific value that must be passed in to the call.

Look at the specific calling process:

Call product (); / / No parameter

Call procedure2 (@ userSum,@userAvg,201708); / / has a parameter

When you are finished, you can directly query the values of userSum and userAvg:

Select @ userSum, @ userAvg

The results are as follows:

+-+ +

| | @ userSum | @ userAvg |

+-+ +

| | 67.00 | 6.09 | |

+-+ +

1 row in set (0.00 sec)

3. Delete stored procedures

A statement: drop procedure product; / / without parentheses

4. An example of a complete stored procedure:

-- Name: drdertotal-- Parameters: onumber = order number-- taxable = 0 if not taxable,1if taxable-- ototal = ordertotal variable create procedure ordertotal (in onumber int, in taxable boolean, out ototal decimal (8L2)) commit 'Obtain ordertotal, optionally adding tax' begin-- Declare variable for total declare total decimal (8Med 2);-- Declare tax percentage declare taxrate int default 6;-- Get the ordertotal select Sum (item_price*quantity) from orderitems where order_num = onumber into total -- Is this taxable? If taxable then-Yes, so add taxrate to the total select total+ (total/100*taxrate) into total; end if;-Add finally, save to out variable select total into ototal; end

The above stored procedure is similar to the business processing of high-level language, it is not difficult to understand, pay attention to the details of writing

Commit keyword: it is not required, but if given, it will be given in the results of show procedure status.

If statement: this example shows the basic usage of the mysqlif statement, which also supports elseif and else clauses.

Through show procedure status, you can make a detailed list of all stored procedures, and you can add a

Like+ specifies the filtering mode for filtering.

This is the end of the article on "how to write stored procedures and functions in MYSQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.