MySQL stored procedures and storage functions

In MySQL, the basic form of creating stored procedures is as follows:

1. CREATE PROCEDURE sp_name ([proc_parameter[,...]])
2. [Characteristic ...] Routine_body

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 properties of the stored procedure, the Routine_body parameter is the contents of the SQL code, you can use the BEGIN ... End to flag the start and end of the SQL code.

Each parameter in the Proc_parameter is made up of 3 parts. These 3 sections are input and output types, parameter names, and parameter types, respectively. The form is as follows:

1. [In | Out | INOUT] Param_name Type

Where in represents the input parameter; out represents an output parameter; The inout represents either an input or an 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: Description The routine_body part is made up of SQL language statements, which are the default languages of the database system.

[NOT] Deterministic: Indicates whether the execution result of the stored procedure is deterministic. Deterministic indicates that the result is deterministic. The same input will get the same output each time the stored procedure is executed. Not deterministic indicates that the result is indeterminate, and the same input may get different output. By default, the result is non-deterministic.

{CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}: Indicates the limitations of the use of SQL statements by subroutines. CONTAINS SQL indicates that a subroutine contains SQL statements, but does not contain statements that read or write data; No SQL indicates that the subroutine does not contain SQL statements; READS SQL data represents the statements in the subroutine that contain read data; modifies SQL Data represents a statement in a subroutine that contains 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 definition can execute itself; Invoker indicates that the caller can execute it. By default, the system-specified permissions are definer.

COMMENT ' string ': Comment information.

Tip: When you create a stored procedure, the system specifies contains SQL by default, which means 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 to set to no SQL. Also, it is best to make a simple comment on the stored procedure in the comment section of the stored procedure so that it is more convenient to read the stored procedure's code later.

Under Example 14-1, create a stored procedure named Num_from_employee. The code is as follows:

1. create  procedure  num_from_employee  (IN EMP_ id int, out count_num int )  
2.            reads sql data  
3.            begin  
4.                select  count (*)   into  count_num  
6.               WHERE   d_id=emp_id ;  
7.           END 

In the preceding code, the stored procedure name is Num_from_employee, the input variable is emp_id, and the output variable is count_num. The SELECT statement queries the employee table for records with the d_id value equal to emp_id, and calculates the number of records with the same d_id value with count (*) and finally deposits the results into count_num. The result of the code execution is as follows:

2. MySQL> DELIMITER &&
4. MySQL> CREATE PROCEDURE num_from_employee
   (in emp_id int, out count_num int)
6. -> READS SQL DATA
8. -> BEGIN
10. -> SELECT COUNT (*) into Count_num
12. -> from employee
14. -> WHERE d_id=emp_id;
16. -> END &&
18. Query OK, 0 rows affected (0.09 sec)
20. MySQL> DELIMITER;

Once the code has been executed, no error message is reported indicating that the storage function has been successfully created. This stored procedure can be called later, and the SQL statements in the stored procedure are executed in the database.

Description: The default statement terminator in MySQL is a semicolon (;). The SQL statement in the stored procedure requires a semicolon to end. To avoid conflicts, first use "DELIMITER &&" to set the MySQL terminator to &&. And finally the "DELIMITER;" To restore the Terminator to the component number. This is the same as when you create a trigger.

14.1.2 Creating a storage function

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

1. CREATE FUNCTION sp_name ([func_parameter[,...])
2. RETURNS type
3. [Characteristic ...] Routine_body

Where the Sp_name parameter is the name of the stored function, Func_parameter represents the parameter list of the stored function, RETURNS type specifies the kind of return value, and the characteristic parameter specifies the property of the stored function. The value of this parameter is the same as the value in the stored procedure, please refer to the contents of the 14.1.1 section; The Routine_body parameter is the contents of the SQL code, you can use the BEGIN ... End to flag the start and end of the SQL code.

Func_parameter can consist of multiple parameters, each of which consists of a parameter name and a parameter type, in the form of the following:

1. Param_name type

Where the Param_name parameter is the parameter name of the stored function, and the type parameter specifies the parameter type of the stored function, which can be any data type of the MySQL database.

Create a storage function named Name_from_employee under "Example 14-2". The code is as follows:

1. create  function  name_from_employee  (emp_id  int )  
2.           returns varchar (20)  
3.           begin  
4.               RETURN   (SELECT   name  
5.                from  employee  
6.                where  num= emp_id );  
7.           end 

In the preceding code, the name of the stored function is Name_from_employee; the function has a parameter of emp_id; The return value is a varchar type. The SELECT statement queries the employee table for a record that has a num value equal to emp_id and returns the value of the record's name field. The result of the code execution is as follows:

2. MySQL> DELIMITER &&
4. MySQL> CREATE FUNCTION name_from_employee (emp_id INT)
6. -> RETURNS VARCHAR (a)
8. -> BEGIN
10. -> RETURN (SELECT name
12. -> from employee
14. -> WHERE num=emp_id);
16. -> end&&
18. Query OK, 0 rows Affected (0.00 sec)
20. MySQL> DELIMITER;

The result shows that the stored function has been created successfully. The use of this function is the same as the use of the MySQL intrinsic function.

Use of 14.1.3 variables

In stored procedures and functions, variables can be defined and used. 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 ... End of the program segment. This section explains how to define variables and assign values to variables.

1. Defining variables

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

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

Where the DECLARE keyword is used to declare a variable, the Var_name parameter is the name of the variable, and you can define multiple variables at the same time; the type parameter is used to specify the types of the variables; the default value clause sets the variable defaults to value. When you do not use the default clause, it is null.

"Example 14-3" defines the variable my_sql, the data type is int, and the default value is 10. The code is as follows:

1. DECLARE my_sql INT DEFAULT 10;

2. Assigning a value to a variable

You can use the Set keyword to assign a value to a variable in MySQL. The basic syntax for the SET statement is as follows:

1. SET var_name = expr [, var_name = expr] ...

Where the Set keyword is used to assign a value to a variable; the Var_name parameter is the name of the variable; The expr parameter is an assignment expression. A SET statement can assign values to multiple variables at the same time, separating the assignment statements of each variable with commas.

Under Example 14-4, the variable my_sql is assigned a value of 30. The code is as follows:

1. SET my_sql = ;

MySQL can also use the Select ... The INTO statement assigns a value to the variable. The basic syntax is as follows:

1. SELECT col_name[,...] Into var_name[,...]
2. 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.

Under Example 14-5, query the employee table for the record with ID 2 and assign the record's d_id value to the variable my_sql. The code is as follows:

1. SELECT d_id into My_sql
2. From employee Wehre id=2;

14.1.4 defining conditions and handlers

Defining conditions and handlers is a pre-defined problem that you might encounter during program execution. You can also define ways to resolve these problems in your handlers. This way you can anticipate potential problems in advance and propose solutions. This will enhance the ability of the program to handle problems, and prevent the program from stopping abnormally. In MySQL, the conditions and handlers are defined by the DECLARE keyword. This section will explain in detail how to define conditions and handlers.

1. Defining conditions

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

1. DECLARE condition_name Condition for Condition_value
2. Condition_value:
3. SQLSTATE [VALUE] sqlstate_value | Mysql_error_code

Where the Condition_name parameter represents the name of the condition, the Condition_value parameter represents the type of condition, and the Sqlstate_value parameter and Mysql_error_code parameter can represent the MySQL error. For example, in error 1146 (42S02), the Sqlstate_value value is the 42s02,mysql_error_code value is 1146.

"Error 1146 (42S02)" is defined under "Example 14-6" with the name Can_not_find. It can be defined in two different ways, with the following code:

1. Method One: Use Sqlstate_value
2. DECLARE can_not_find CONDITION for SQLSTATE ' 42S02 ';
3. Method Two: Use Mysql_error_code
4. DECLARE Can_not_find CONDITION for 1146;

2. Defining handlers

You can use the DECLARE keyword to define a handler in MySQL. The basic syntax is as follows:

1. DECLARE Handler_type handler for
   condition_value[,...] Sp_statement
2. Handler_type:
3. CONTINUE | EXIT | UNDO
4. Condition_value:
5. SQLSTATE [VALUE] sqlstate_value |
   Condition_name | SQLWarning
6. | Not FOUND | SQLEXCEPTION | Mysql_error_code

Where the Handler_type parameter indicates how the error is handled, and the parameter has 3 values. These 3 values are continue, exit, and Undo, respectively. Continue indicates that an error has not been processed, continues to execute downward, exits immediately after an error has been encountered, and Undo indicates that the operation was not supported until the error was encountered and is temporarily unsupported in MySQL.

Note: Typically, errors encountered during execution should immediately stop executing the following statement and recall the previous action. However, the undo operation is not currently supported in MySQL. Therefore, it is best to perform an exit operation if an error is encountered. If the error type can be predicted beforehand and processed accordingly, the continue operation can be performed.

The Condition_value parameter indicates the type of error, which has 6 values. Sqlstate_value and Mysql_error_code are the same meaning as in conditional definitions. Condition_name is the condition name defined by declare. SQLWarning represents all sqlstate_value values that begin with 01. Not found represents all sqlstate_value values that begin with 02. SqlException represents all sqlstate_value values that are not captured by sqlwarning or not found. Sp_statement represents some execution statements for stored procedures or functions.

"Example 14-7" below are several ways to define handlers. The code is as follows:

2. Method One: Capture Sqlstate_value
4. DECLARE CONTINUE HANDLER for SQLSTATE ' 42S02 '
   SET @info=' CAN not FIND ';
6. Method Two: Capture Mysql_error_code
8. DECLARE CONTINUE HANDLER for 1146 SET @info=' CAN not FIND ';
10. Method Three: Define the condition first, and then call the
12. DECLARE Can_not_find CONDITION for 1146;
14. DECLARE CONTINUE HANDLER for Can_not_find SET
    @info=' CAN not FIND ';
16. Method Four: Use SQLWarning
18. DECLARE EXIT HANDLER for sqlwarning SET @info=' ERROR ';
20. Method Five: Use not FOUND
22. DECLARE EXIT HANDLER for Don't FOUND SET @info=' CAN not FIND ';
24. Method Six: Use SqlException
26. DECLARE EXIT HANDLER for SQLEXCEPTION SET @info=' ERROR ';

The above code is 6 ways to define handlers. The first method is to capture the Sqlstate_value value. If you encounter a Sqlstate_value value of 42S02, perform a continue operation and output "CAN not FIND" information. 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" message is output. The third method is to define the condition before calling the condition. The can_not_find condition is defined first, and the continue operation is performed with a 1146 error. The fourth method is to use SQLWarning. SQLWarning captures all Sqlstate_value values starting with 01, then executes the exit operation and outputs the "ERROR" information. The fifth method is to use not FOUND. Not found captures all sqlstate_value values starting with 02, then performs the exit operation and outputs "CAN not FIND" information. The sixth method is to use SqlException. SqlException captures all sqlstate_value values that are not captured by the sqlwarning or not found, and then performs the exit operation and outputs the "ERROR" information.

Conditional judgment
If then, ELSEIF, ELSE, END if

DELIMITER $$

DROP PROCEDURE IF EXISTS discounted_price$$
CREATE PROCEDURE Discunted_price (Normal_price NUMERIC (8, 2), Out Discount_price NUMERIC (8, 2))
BEGIN
IF (Normal_price >) Then
SET Discount_price = Normal_price *. 8;
ELSEIF (Normal_price >) Then
SET Discount_price = Normal_price *. 9;
ELSE
SET discount_price = Normal_price;
END IF;
end$$

DELIMITER;

Cycle
Loop, END Loop

DELIMITER $$

DROP PROCEDURE IF EXISTS simple_loop$$

CREATE PROCEDURE Simple_loop (out counter INT)
BEGIN
SET counter = 0;
My_simple_loop:loop
SET counter = counter+1;
IF counter = Ten Then
LEAVE My_simple_loop;
END IF;
END LOOP My_simple_loop;
end$$

DELIMITER;

While do, END while

DELIMITER $$

DROP PROCEDURE IF EXISTS simple_while$$

CREATE PROCEDURE Simple_while (out counter INT)
BEGIN
SET counter = 0;
While counter! = Ten Do
SET counter = counter+1;
END while;
end$$

DELIMITER;

REPEAT, untill

DELIMITER $$

DROP PROCEDURE IF EXISTS simple_repeat$$

CREATE PROCEDURE simple_repeat (out counter INT)
BEGIN
SET counter = 0;
REPEAT
SET counter = counter+1;
UNTIL counter = ten END REPEAT;
end$$

DELIMITER;

Cursor query results for processing multiple rows of records

DELIMITER $$

DROP PROCEDURE IF EXITS cursor_example$$
CREATE PROCEDURE cursor_example ()
READS SQL DATA
BEGIN
DECLARE l_employee_id INT;
DECLARE l_salary NUMERIC (8,2);
DECLARE l_department_id INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR for SELECT employee_id, salary, department_id from employees;
DECLARE CONTINUE HANDLER for not FOUND SET done=1;

OPEN Cur1;
Emp_loop:loop
FETCH cur1 into l_employee_id, l_salary, l_department_id;
IF Done=1 Then
LEAVE Emp_loop;
END IF;
END LOOP Emp_loop;
CLOSE Cur1;
end$$
DELIMITER;

The SELECT statement is used by the stored procedure to return a result set

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_emps_in_dept$$
CREATE PROCEDURE sp_emps_in_dept (in_employee_id INT)
BEGIN
SELECT employee_id, surname, FirstName, Address1, Address2, ZipCode, Date_of_birth from Employees WHERE Department_id=in_ employee_id;
end$$

DELIMITER;

Non-query statements, such as UPDATE, INSERT, DELETE, CREATE table, can also be embedded in the stored procedure

DELIMITER $$

DROP PROCEDURE IF EXITS sp_update_salary$$
CREATE PROCEDURE sp_update_salary (in_employee_id INT, In_new_salary NUMERIC (8,2))
BEGIN
IF In_new_salary < in_new_salary > 500000 Then
Select "Illegal salary:salary must be between $5000 and 000";
ELSE
UPDATE Employees SET salary=in_new_salary WHERE employee_id=in_employee_id;
END IF:
end$$

DELIMITER;

Calling a stored program using call

DELIMITER $$

DROP PROCEDURE IF EXISTS call_example$$
CREATE PROCEDURE call_example (employee_id INT, Employee_type VARCHAR (20))
NO SQL
BEGIN
DECLARE l_bonus_amount NUMERIC (8,2);

IF employee_type= ' MANAGER ' then
Call Calc_manager_bonus (employee_id, L_bonus_amount);
ELSE
Call Calc_minion_bonus (employee_id, L_bonus_amount);
END IF;
Call Grant_bonus (employee_id, L_bonus_amount);
end$$
DELIMITER;

The difference between stored procedures and stored functions:

1. General statement
stored functions and stored procedures are collectively referred to as storage routines (stored routine). The two definitions are similar in syntax, but they are different content.
There are more storage function restrictions, such as the inability to use temporary tables, only table variables. There are also some functions that are not available, and so on. The limit of stored procedures is relatively small.
In general, the function of the stored procedure implementation is a little more complicated, and the function implementation is more specific.


2. Differences in return values
The stored function returns one to the caller and returns only one result value.
The stored procedure returns one or more result sets (the function does not do this) or simply implements an effect or action without returning a value.


3. Differences in invocation mode
Storage functions are embedded in SQL and can be called in Select, just like the built-in functions such as cos (), Hex ()
Stored procedures can only be called by a call statement


4, the different parameters
The parameter type of the stored function is similar to the in parameter
There are three types of parameters for stored procedures, in parameters, out parameters, inout parameters

Reference: http://www.cnblogs.com/exmyth/p/3303470.html

Reference: http://fyb613.blog.163.com/blog/static/325460922010044417672/

MySQL stored procedures and storage functions