Detailed discussion of MySQL stored procedures and stored functions

1 Stored Procedure

1.1 What is a stored procedure

A stored procedure is a set of SQL statements to complete a specific function. In essence, it is a piece of code stored in the database. It can be composed of declarative SQL statements (such as CREATE, UPDATE, SELECT, etc.) and procedural SQL statements (such as IF...THEN...ELSE control structure statements). The idea of ​​stored procedures is very simple, which is to encapsulate and reuse codes at the database SQL language level.

1.2 Advantages and Disadvantages of Stored Procedures

advantage:

1. It can enhance the functionality and flexibility of the SQL language. The stored procedure can be written in a process control language, which has strong flexibility and can complete complex judgments and more complex calculations.

2. Good encapsulation After a stored procedure is created, it can be called multiple times in the program without having to worry about rewriting the SQL statement that writes the stored procedure.

3. After a high-performance stored procedure is executed once, its execution plan resides in the cache memory. Subsequent operations only require calling the compiled binary code from the cache memory to execute, thereby improving system performance.

shortcoming:

Stored procedures are often customized to specific databases because they support different programming languages. When switching to a database system from another manufacturer, the original stored procedure needs to be rewritten.

1.3 Create a stored procedure

1.3.1 DELIMITER

In SQL, the server uses a semicolon as the end mark of a SQL statement by default. However, when creating a stored procedure, the stored procedure body may contain multiple SQL statements. If these SQL statements still use a semicolon as the statement terminator, the server will use the semicolon at the first SQL statement as the end mark of the entire program and will no longer process the subsequent SQL statements.
To solve this problem, the DELIMITER command is usually used to temporarily change the end character of the SQL statement to other symbols.

DELIMITER syntax format:

DELIMITER $$

$$ is a user-defined terminator, usually this symbol can be some special symbols. Also avoid using backslash, as it is an escape character.
If you want to change back to the default semicolon as the end mark, just enter the following SQL statement in the command line.

DELIMITER ;

1.3.2 Stored Procedure Creation

In MySQL, use the CREATE PROCEDURE statement to create a stored procedure.

CREATE PROCEDURE p_name([proc_parameter[,...]])
routine_body

The syntax format of the syntax item "proc_parameter" is:

[IN|OUT|INOUT]parame_name type

1. "p_name" is used to specify the name of the stored procedure.

2. "proc_parameter" is used to specify the parameter list in the stored procedure. The syntax item "parame_name" is the parameter name, and "type" is the parameter type (the type can be any valid data type in MySQL). Mysql stored procedures support three types of parameters, namely input parameters IN, output parameters OUT, and input and output parameters INOUT. Input parameters enable data to be passed to a stored procedure; output parameters are used for an operation result that a stored procedure needs to return; input and output parameters can serve as both input parameters and output results.
The parameter names should not be the same as the column names in the table. Otherwise, although no error message will be returned, the SQL statement in the stored procedure will treat the parameter name as the column name, causing unpredictable errors.

3. The syntax item "rountine_body" represents the main part of the stored procedure, also known as the stored procedure body, which contains the SQL that needs to be executed. The procedure body begins with the keyword BEGIN and ends with the keyword END. If there is only one SQL statement, the BEGIN....END sign can be ignored.

1.3.3 Local variables

Local variables can be declared in the stored procedure body to store temporary results in the procedure body. In MySQL, DECLARE statement is used to declare local variables.

DECLARE var_name type [DEFAULT value]

"var_name" is used to specify the name of the local variable; "type" is used to declare the type of the variable; "DEFAULT" is used to specify the default value, which is NULL if not specified.

Note: Local variables can only be used in the BEGIN...END statement block of the stored procedure body; local variables must be declared at the beginning of the stored procedure body; the scope of local variables is limited to the BEGIN...END statement block in which it is declared, and statements in other statement blocks cannot use it.

1.3.4 User variables

User variables usually start with @.

Note: Misusing user variables can make your program difficult to understand and manage.

1.3.5 SET Statement

In MySQL, the format of assigning values ​​to local variables through the SET statement is:

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

1.3.6 SELECT ... INTO Statement

In MySQL, you can use the SELECT...INTO statement to store the values ​​of the selected columns in local variables. The format is:

SELECT col_name[,..] INTO var_name[,....] table_expr

"col_name" is used to specify the column name; "var_name" is used to specify the variable name to be assigned; "table_expr" represents the part after FROM in the SELECT statement.

Note: The result set returned by the SELECT...INTO statement can only have one row of data.

1.3.7 Flow Control Statements

Conditional Statements

if-then-else statement :

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc2(IN parameter int) 
 -> begin 
 -> declare var int; 
 -> set var=parameter+1; 
 -> if var=0 then 
 -> insert into t values(17); 
 -> end if; 
 -> if parameter=0 then 
 -> update t set s1=s1+1; 
 -> else 
 -> update t set s1=s1+2; 
 -> end if; 
 -> end; 
 -> && 
mysql > DELIMITER ; 

case statement:

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc3 (in parameter int) 
 -> begin 
 -> declare var int; 
 -> set var=parameter+1; 
 -> case var 
 -> when 0 then 
 -> insert into t values(17); 
 -> when 1 then 
 -> insert into t values(18); 
 -> else 
 -> insert into t values(19); 
 -> end case; 
 -> end; 
 -> && 
mysql > DELIMITER ;

Loop Statements
while ···· end while:

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc4() 
 -> begin 
 -> declare var int; 
 -> set var=0; 
 -> while var<6 do 
 -> insert into t values(var); 
 -> set var=var+1; 
 -> end while; 
 -> end; 
 -> && 
mysql > DELIMITER ;

repeat···· end repeat:

It checks the result after performing the operation, while while checks before performing the operation.

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc5 () 
 -> begin 
 -> declare v int; 
 -> set v=0; 
 -> repeat 
 -> insert into t values(v); 
 -> set v=v+1; 
 -> until v>=5 
 -> end repeat; 
 -> end; 
 -> && 
mysql > DELIMITER ;

repeat
 --Loop body until loop condition end repeat;

loop ·····endloop:

The loop does not require an initial condition, which is similar to the while loop. Like the repeat loop, it does not require an end condition. The leave statement is used to leave the loop.

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc6 () 
 -> begin 
 -> declare v int; 
 -> set v=0; 
 -> LOOP_LABLE:loop 
 -> insert into t values(v); 
 -> set v=v+1; 
 -> if v >=5 then 
 -> leave LOOP_LABLE; 
 -> end if; 
 -> end loop; 
 -> end; 
 -> && 
mysql > DELIMITER ;

ITERATE iteration:

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc10 () 
 -> begin 
 -> declare v int; 
 -> set v=0; 
 -> LOOP_LABLE:loop 
 -> if v=3 then 
 -> set v=v+1; 
 -> ITERATE LOOP_LABLE; 
 -> end if; 
 -> insert into t values(v); 
 -> set v=v+1; 
 -> if v>=5 then 
 -> leave LOOP_LABLE; 
 -> end if; 
 -> end loop; 
 -> end; 
 -> && 
mysql > DELIMITER ;

1.3.8 Cursors

The cursor in MySQL can be understood as an iterable object (similar to iterable objects such as lists and dictionaries in Python). It can be used to store the result set of a select statement. This result set can contain multiple rows of data, so that we can use an iterative method to retrieve each row of data from the cursor in turn.

Features of MySQL cursor:
1. Read-only: Data in the base table cannot be updated through the cursor.
2. Non-scrollable: rows can only be obtained in the order determined by the select statement. It is not possible to fetch rows in reverse order. Additionally, you cannot skip rows or jump to a specific row in the result set.
3. Sensitive: There are two types of cursors: sensitive cursors and insensitive cursors. Sensitive cursors point to the actual data, and insensitive cursors use a temporary copy of the data. A sensitive cursor executes faster than an insensitive cursor because it does not require temporary copying of data. MySQL cursors are sensitive.

1. Declare a cursor

The cursor declaration must come after the variable declaration. If you declare a cursor before a variable declaration, MySQL issues an error. A cursor must always be associated with a select statement.

declare cursor_name cursor for select_statement;

2. Open the cursor

Use the open statement to open the cursor. Only after the cursor is opened can data be read.

open cursor_name;

3. Read the cursor

Use the fetch statement to retrieve a row of data pointed to by a cursor and move the cursor to the next row in the result set.

fetch cursor_name into var_name;

4. Close the cursor

Use the close statement to close the cursor.

close cursor_name;

When a cursor is no longer in use, it should be closed. When using MySQL cursors, you must also declare a notfound handler to handle the case when the cursor does not find any rows. Because each time the fetch statement is called, the cursor attempts to read each row of data in the result set in turn. When the cursor reaches the end of the result set, it will be unable to obtain data and a condition will be generated. The handler is used to handle this situation.

declare continue handler for not found set type = 1;

type is a variable that indicates that the cursor has reached the end of the result set.

delimiter $$
create PROCEDURE phoneDeal()
BEGIN
 DECLARE id varchar(64); -- id
 DECLARE phone1 varchar(16); -- phone
 DECLARE password1 varchar(32); -- password DECLARE name1 varchar(64); -- id
 --Traversal data end mark DECLARE done INT DEFAULT FALSE;
 -- Cursor DECLARE cur_account CURSOR FOR select phone,password,name from account_temp;
 -- Bind the end flag to the cursor DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 -- Open the cursor OPEN cur_account; 
 -- Traverse read_loop: LOOP
 -- Get values ​​from multiple fields FETCH NEXT from cur_account INTO phone1,password1,name1;
 IF done THEN
 LEAVE read_loop;
 END IF;
 
 -- The operation you want to do insert into account(id,phone,password,name) value(UUID(),phone1,password1,CONCAT(name1,'的父'));
 END LOOP;
 
 -- Close the cursor CLOSE cur_account;
END $$

1.3.7 Calling a stored procedure

Calling a stored procedure using the call statement

call sp_name[(parameters passed)];

1.3.8 Deleting a stored procedure

Use the drop statement to delete a stored procedure

DROP PROCEDURE sp_name

2 Storage Functions

2.1 What is a stored function?

Stored functions, like stored procedures, are code blocks consisting of SQL and statements.
A stored function cannot have input parameters and can be called directly without a call statement, and must contain a RETURN statement.

2.2 Creating a stored function

Use the CREATE FUNCTION statement in MySQL to create:

CREATE FUNCTION fun_name (par_name type[,...])
RETURNS type
[characteristics] 
fun_body

Where fun_name is the function name, and the name is unique and cannot be the same as that of a stored procedure. par_name is the specified parameter, type is the parameter type; the RETURNS clause is used to declare the return value and return value type. fun_body is the function body. All SQL statements in the stored procedure can also be used in the stored function. However, the stored function body must contain a RETURN statement.
characteristics specifies the characteristics of the stored procedure and has the following values:

• LANGUAGE SQL: indicates that the routine_body part is composed of SQL statements. The language supported by the current system is SQL. SQL is the only value of the LANGUAGE property.
• [NOT] DETERMINISTIC: Indicates whether the result of the stored procedure execution is determined. DETERMINISTIC means that the result is deterministic. Each time the stored procedure is executed, the same input will produce the same output. NOT DETERMINISTIC means that the result is uncertain. The same input may produce different outputs. If no value is specified, the default is NOT DETERMINISTIC.
• [CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA]: Specifies restrictions on the use of SQL statements by the subroutine. CONTAINS SQL indicates that the subroutine contains SQL statements but does not contain statements for reading and writing data; NO SQL indicates that the subroutine does not contain SQL statements; READS SQL DATA indicates that the subroutine contains statements for reading data; MODIFIES SQL DATA indicates that the table name subroutine contains statements for writing data. By default, CONTAINS SQL is specified.
• SQL SECURITY[DEFINER|INVOKER]: Specifies who has permission to execute. DEFINER means that it can only be executed by the definer. INVOKER means that the caller with UFIDA permissions can execute it. By default, the system is designated as DEFINER.
• COMMENT 'string': Comment information used to describe the stored procedure or function.

delimiter $$
create function getAnimalName(animalId int) RETURNS VARCHAR(50)
DETERMINISTIC
begin
 declare name VARCHAR(50);
 set name=(select name from animal where id=animalId);
 return (name);
end$$
delimiter;

-- Call select getAnimalName(4)

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.