1. Stored procedures and stored functions

     1. Stored procedure and stored function: MySQL provides the mechanism of stored procedure and stored function. Let's collectively call stored procedure and stored function stored programs. Unlike ordinary SQL statements, which need to be compiled first and then executed immediately, a stored program is a set of SQL statements to complete specific functions. It is compiled and stored in the database. It will be executed only when the user calls it by specifying the name of the stored program and giving parameters (if the stored program has parameters).

    2. There are the following differences between stored functions and stored procedures:

    (1) There are many restrictions on storage functions. For example, temporary tables can not be used, only table variables can be used, while there are fewer restrictions on storage procedures. The implementation functions of storage procedures are more complex, and the implementation functions of functions are more targeted.

    (2) The return values are different. The storage function must have a return value and only return one result value; Stored procedures can return no value, but can return result sets (out,inout).

    (3) Different when calling. The storage function is embedded in SQL, and the function name (variable value) can be stored in select; The stored procedure calls the call stored procedure name through the call statement.

     (4) Different parameters. The parameter type of the storage function is similar to the IN parameter, and there are no parameters similar to out and INOUT. There are three parameter types for stored procedures: IN, out, and INOUT:

• in: data is only imported from the outside and used internally (value transfer). It can be a value or a variable

• out: only internal use of the process (without external data) is allowed. For external use (reference passing: external data will be cleared before entering the internal), only variables can be used

• inout: external can be used internally, and internally modified can also be used externally. Typical reference passing can only pass variables.

2. Creating stored procedures and functions

    1. Create stored procedure

CREATE PROCEDURE  Process name([[IN|OUT|INOUT] Parameter name data type[,[IN|OUT|INOUT] Parameter name data type]]) [characteristic ...]
BEGIN
    Process body
END

    Create a file named count_of_student's stored procedure to count the number of words in the student table

delimiter $$
#If the stored procedure name already exists, discard the stored procedure.
DROP PROCEDURE IF EXISTS count_of_student $$
create procedure count_of_student(OUT count_num INT)
reads sql data
begin
select count(*) into count_num from student;
end$$

    2. Create storage function

CREATE FUNCTION function_name(param1,param2,...)
RETURNS datatype
[NOT] DETERMINISTIC statements
SQL

    Create a named t_employee's data table and insert records

CREATE TABLE t_employee(
id INT(4),
name VARCHAR(20),
gender VARCHAR(6),
age INT(4),
salary INT(6),
deptno INT(4));
use mydatabases;
INSERT INTO t_employee(id,name,gender,age,salary,deptno)
VALUES (1001,'Alici Florric','Female',33,1000,1),
(1002,'Kalinda Sharma','Female',31,9000,1),
(1003,'Cary Agos','Male',27,9000,1),
(1004,'Eli Gold','Male',44,9000,2),
(1005,'Peter Florric','Male',24,7000,3),
(1006,'Diane Lockhart','Female',34,8000,3),
(1007,'Maia Rindell','Female',25,9000,2);

    Create a file named func_employee function, which has a parameter of type INT (4) and name id, and the return value is of type INT (6). SELECT statement from table t_ In employee, query the record whose field id value is equal to the passed in parameter id value, and return the field salary value of the record.

CREATE FUNCTION func_employee(id INT(4))
        RETURNS INT(6)
COMMENT 'Query the salary of each employee'
BEGIN
RETURN (SELECT salary
    FROM t_employee
    WHERE t_employee.id=id);
END;
$$

3. Calling stored procedures and stored functions    

    1. Call stored procedure

    Use the CALL statement to CALL stored procedures. The syntax is as follows:

CALL sp_name([parameter[,......]]);

    2. Call storage function

SELECT function_name([parater[,......]]);

4. Viewing stored procedures and stored functions     

    1. SHOW STATUS statement

SHOW {PROCEDURE | FUNCTION}STATUS[LIKE 'pattern']

    2. SHOW CREATE statement

SHOW CREATE{PROCEDURE | FUNCTION}sp_name;

5. Modify stored procedures and stored functions     

     Modify stored procedure and stored function syntax

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

• sp_name indicates the name of the stored procedure or function

• The characteristic parameter specifies the properties of the stored procedure or function

6. Delete stored procedures and stored functions     

    Syntax:

DROP {PROCEDURE | FUNCTION } [IF EXISTS] sp_name