Understand the use of Mysql stored procedures

Stored procedures and functions are a collection of SQL statements that have been compiled and stored in the database. Calling stored procedures and functions can simplify a lot of work for application developers, reduce data transmission between the database and the application server, and improve the efficiency of data processing.

The difference between a stored procedure and a function is that a function must have a return value, whereas a stored procedure does not.

Function: is a procedure that returns a value;

Procedure: is a function that returns no value;

Simply put, it is a collection of one or more Mysql statements saved for future use.

Function:

Using the saved process, many similar deletion, update, add and other operations become easy, and easy to manage later!
Stored procedures run faster because SQL statements are preprogrammed.
Stored procedures can take parameters, output parameters, return single or multiple result sets, and return values. The cause of the error can be returned to the program.
Stored procedures run stably and do not have many errors. As long as the success once, will follow this procedure to run.
Stored procedures run primarily on the server, reducing the stress on the client.
Stored procedures can contain program flows, logic, and queries to a database. At the same time, the entity can encapsulate and hide the data logic.
A stored procedure can execute a series of SQL statements in a single stored procedure.
Stored procedures can reference other stored procedures from within their own, which simplifies a series of complex statements.

Why use stored procedures

Simplify complex operations (as described in the previous example) by encapsulating processing in easy-to-use units.
This ensures data integrity because there is no requirement to set up a series of processing steps repeatedly. If all developers and applications use the same (trial and test) stored procedures, the code used is the same. An extension of this is error prevention. The more steps you have to perform, the more likely it is that something will go wrong. Error prevention ensures data consistency.
Simplify management of change. If the table name, column name, or business logic (or something else) changes, you only need to change the code of the stored procedure. The people who use it don’t even need to know about these changes. An extension of this is security. Limiting access to the underlying data through stored procedures reduces the opportunity for data corruption (unintended or otherwise).
Improve performance. Because using stored procedures is faster than using individual SQL statements.
There are MySQL elements and features that can only be used in a single request, and stored procedures can use them to write more powerful and flexible code.

Summary: simple, safe and high performance.

Before converting SQL code to stored procedures, however, you must also be aware of its pitfalls:

Generally, stored procedures are more complex to write than basic SQL statements and require higher skills and more experience to write stored procedures.
You may not have secure access to create stored procedures. Many database administrators restrict the creation of stored procedures, allowing users to use stored procedures but not to create them.

Mysql separates the security and access of writing stored procedures from the security and access of executing stored procedures. This way you can’t (or don’t want to) write your own stored procedures, and you can still execute other stored procedures when appropriate.

Creating a stored procedure

Syntax format:

CREATE
    [DEFINER = { user | CURRENT_USER}]PROCEDUREsp_name ([proc_parameter[,...]]) [characteristic ...]  routine_body proc_parameter: [IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY{DEFINER | INVOKER} routine_body: ValidSQL routine statement
 
[begin_label:] BEGIN[statement_list]...END [end_label]
Copy the code

IN input parameter: Indicates that the caller passes a value to the procedure (the value can be a literal or a variable)
OUT Output parameter: indicates that the procedure sends a value to the caller (multiple values can be returned) (outgoing values can only be variables)
INOUT Input/output parameters: indicates that both the caller passes a value to the procedure and the procedure passes a value to the caller (the value can only be a variable)

Declaration statement terminator:

DELIMITER $$
或
DELIMITER //
Copy the code

Declare stored procedures:

CREATE PROCEDURE demo_in_parameter(IN p_in int)  
Copy the code

Stored procedure start and end symbols: — more than one can be nested

Each nested block and each statement within it must end with a semicolon. Begin-end blocks (also known as compound statements) that indicate the end of a procedure body do not need a semicolon.

BEGIN.END
Copy the code

Label statement blocks:

[begin_label:] BEGIN[statement_list]END [end_label]
Copy the code

Variable assignment:

SET @p_in=1
Copy the code

Variable definition:

-- Declere variable name data type...
DECLARE l_int int unsigned default 4000000; 
Copy the code

Create Mysql stored procedure, stored function;

CREATE PROCEDUREStored procedure name (parameter)Copy the code

Stored procedure body:

CREATE FUNCTIONStore function names (parameters)Copy the code

Ex:

First, create a file in the start process of the database of your choice and write the stored procedure you want to write inThe sourceSave the file.

CREATE DEFINER=`root`@`localhost` 
PROCEDURE `mysql_scirpt`.`01.CreateStorage`( IN 01. Create a stored procedure VARCHAR ( 50))BEGIN
#Routine body goes here...
SELECT
	AVG( prod_price ) AS "Average commodity" 
FROM
	products;

END
Copy the code

Then, choose where you aredatabaseClick stored procedure

Finally, a SQL statement is generated, and you can see the result when you execute it directly

{ CALL mysql_scirpt.`01.CreateStorage`(:01_ Create stored procedure)}Copy the code

Running results, as shown in figure:

How do I write stored procedures directly in an SQL file

USE mysql_scirpt;

DROP PROCEDURE IF EXISTS mysql_scirpt.myTry;

DELIMITER //
CREATE PROCEDURE myTry(INThe name of the tablevarchar(50))

BEGIN
	pass...
END

DELIMITER ;
Copy the code

Deleting a stored procedure

Syntax format:

DROP PROCEDUCE/FUNCTION xxx
Copy the code

DROP PROCEDURE myTry 
Copy the code

If the specified Procedure does not exist, Drop Procedure will generate an error. Delete the procedure if it exists. You can use Drop Procedure If Exists

Checking stored Procedures

To display the CREATE statement used to CREATE a stored procedure, use the SHOW CREATEPROCEDURE statement:

SHOW CREATE PROCEDURE XXX;
Copy the code

To get a list of stored procedures with details about when and by whom they were created, use SHOW PROCEDURE STATUS.

Result: SHOW PROCEDURE STATUS lists all stored procedures. To limit its output, you can specify a filtering mode using LIKE, for example:

SHOW PROCEDURE STATUS LIEK 'XXX';
Copy the code

Stored procedure syntax

Stored procedures are programmable, meaning they can use variables, expressions, and control structures to perform complex functions.

variable

Variable A specific location in memory used to temporarily store data.

When called, this statement does not display any data. It returns variables that can be displayed later (or used in other processing).

Syntax format:

DECLARE var_name[,...] type [DEFAULT value]
Copy the code

DECLARE allows you to define a local variable whose scope can only be defined in BEGIN… In the END block, and must be at the beginning of the compound statement, before any other statement; Can be used in nested blocks, except for those that declare variables with the same name.

To provide a DEFAULT value for a variable, use the DEFAULT clause (the value can be a constant or can be specified as an expression). If there is no DEFAULT clause, the initial value is NULL.

DROP PROCEDURE IF EXISTS mysql_scirpt.`03.CreateVariable`;

DELIMITER $$
$$
CREATE PROCEDURE mysql_scirpt.`03.CreateVariable`(OUT num1 int)
BEGIN
	DECLARE num2 int DEFAULT 200;
	SET num1 = num2;
END$$
DELIMITER ;

CALL `03.CreateVariable`(@num);
SELECT @num;
Copy the code

Scope of a local variable: The scope of a block in which a variable can be used without error.

In the case of nested blocks, variables declared in the outer block can be used directly in the inner block; Variables declared in an inner block can only be used in an inner block.

The variable declared by @ acts as a placeholder on the outside. The call must be made using the call method.

CALL `03.CreateVariable`(@num);
SELECT @num;
Copy the code

IF conditional judgment

Syntax format:

IF … Then single judgment:

Syntax format:

IF expression THEN 
   statements;
END IF;
Copy the code

IF expression evaluates to TRUE, statements are executed, otherwise control flow is passed to the next statement after END IF.

Execution flow chart:

Else double judgment:

Syntax format:

IF expression THEN
   statements;
ELSE
   else-statements;
END IF;
Copy the code

IF the statement is executed when the expression evaluates to FALSE, use an IF ELSE statement

Flow chart:

Multi-conditional judgment:

IF expression THEN statements; ELSEIF elseif-expression THEN elseif-statements; . ELSE else-statements;END IF;
Copy the code

IF expression evaluates to TRUE, statements in the IF branch will be executed; If the expression evaluates to FALSE, MySQL will execute elseif-expression if elseif_expression evaluates to TRUE, or ELSE ELSE statements in the ELSE branch.

Flow chart:

Case study:

DROP PROCEDURE IF EXISTS mysql_scirpt.`04.CreateIF`;

/* Requirements: * 180 height -- Tall * 170-180 height -- Medium * 170 or less -- Average * */

DELIMITER $$
$$
CREATE PROCEDURE mysql_scirpt.`04.CreateIF`(a)BEGIN
	DECLARE height int DEFAULT 175;
	DECLARE description varchar(50);
	
	IF height >= 180 THEN
		SET description = "High";
	ELSEIF height >=170 AND height < 180 THEN 
		SET description = "In";
	ELSE 
		SET description = "General";
	END IF;
	
	SELECT description;
END$$
DELIMITER ;

CALL mysql_scirpt.`04.CreateIF`(a);Copy the code

Passing parameters

Stored procedures under development almost always require parameters, which make passing parameters more flexible.

IN Mysql, there are three modes for parameters: IN, OUT, and INOUT

IN: Is the default mode. When an IN parameter is defined IN a stored procedure, the calling program must pass the parameter to the stored procedure. In addition,INThe value of the parameter is protected. This means that even if changes are made in the stored procedureINThe value of the argument retains its original value after the stored procedure ends. Stored procedures just useINParameter copy.
OUT: can be changed from a stored procedureOUTParameter, and passes its changed new value back to the caller. Note that the stored procedure does not have access to the initial value of the OUT parameter at startup.
INOUTThe: INOUT parameter is a combination of the IN and OUT parameters. This means that the caller can pass parameters, and the stored procedure can modify the INOUT parameters and pass the new values back to the caller.

Syntax format:

create procedure procedure_name([in/out/inout] Parameter name Parameter type)Copy the code

Case 1: Use of the IN parameter

DROP PROCEDURE IF EXISTS mysql_scirpt.`05.CreateParameter`;

DELIMITER $$
$$
CREATE PROCEDURE mysql_scirpt.`05.CreateParameter`(IN height int)
BEGIN
	DECLARE description varchar(50) DEFAULT ' ';
	
	IF height >= 180 THEN
		SET description = "High";
	ELSEIF height >=170 AND height < 180 THEN 
		SET description = "In";
	ELSE 
		SET description = "General";
	END IF;
	
	SELECT concat('Height:',height,'Figure:',description) AS 'description';

END$$
DELIMITER ;
Copy the code

Execute the stored procedure:

{ CALL mysql_scirpt.`05.CreateParameter`(Enter height here)}Copy the code

Case 2: Using the OUT parameter

DROP PROCEDURE IF EXISTS mysql_scirpt.`06.CreateParamOut`;

DELIMITER $$
$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `mysql_scirpt`.`06.CreateParamOut`(
	OUT id int
)
BEGIN
	SELECT id AS id_inner_1; -- 1
	IF id IS NOT NULL THEN -- 3. Because of the OUT parameter, we cannot get the value passed in from outside
		SET id = id + 1;
		SELECT id AS id_inner_2;
	ELSE
		SELECT 1 INTO id; -- 4. Enter the ELSE statement and assign id to 1
	END IF;
	
	SELECT id AS id_inner_3; -- result 2

END$$
DELIMITER ;

SET @id = 10;
CALL `mysql_scirpt`.`06.CreateParamOut`(@id);
SELECT @id AS id_out; -- 5. Get id 1
Copy the code

After the run, we can see three results:

mysql> set @id = 10;
mysql> call pr_param_out(@id);
+------------+
| id_inner_1 |
+------------+
|       NULL |
+------------+
+------------+
| id_inner_3 |
+------------+| | + 1------------+
 
mysql> select @id as id_out;
+--------+
| id_out |
+--------+| | + 1--------+
Copy the code

As you can see, even though we set the user-defined variable @id to 10, after passing @ID to the stored procedure, the initial value of id inside the stored procedure is always NULL (ID_inner_1). Finally, the ID value (ID_out = 1) is passed back to the caller. Therefore, the OUT parameter cannot get the parameter passed in from outside.

Example: INOUT parameter usage

DROP PROCEDURE IF EXISTS mysql_scirpt.`07.CreateParamInOut`;

DELIMITER $$
$$
CREATE PROCEDURE mysql_scirpt.`07.CreateParamInOut`(
	INOUT id int
)
BEGIN
	SELECT id AS id_inner_1; -- 2. id = 10
	
	IF id IS NOT NULL THEN
		SET id = id+1; - 2. id+1 = 11
		SELECT id AS id_inner_2; -- 3. id = 11
	ELSE
		SELECT 1 INTO id;
	END IF;
	
	SELECT id AS id_inner_3; -- 4. id = 11

END$$
DELIMITER ;

SET @id = 10;
CALL mysql_scirpt.`07.CreateParamInOut`(@id); -- 1. Pass in parameters
SELECT @id AS id_out; -- 5. id = 11
Copy the code

Execution Result:

mysql> set @id = 10;
mysql> call pr_param_inout(@id);
+------------+
| id_inner_1 |
+------------+| | + 10------------+
+------------+
| id_inner_2 |
+------------+| | + 11------------+
+------------+
| id_inner_3 |
+------------+| | + 11------------+
mysql> select @id as id_out;
+--------+
| id_out |
+--------+| | + 11--------+
Copy the code

If you just want to pass data to a MySQL stored procedure, use an “in” parameter; If only the value is returned from the MySQL stored procedure, use the parameter of type “out”. If you need to pass data to a MySQL stored procedure and then do some calculation and get it back to us, use an “inout” parameter.

Case structure

Simple syntax format:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
Copy the code

Case_value is an expression that equals the when_value value in each when clause:

If equal to a when_value value in a WHEN clause, the statement statement_list following the corresponding THEN clause is executed.
If no when_value values are equal, the statement_list following the else clause is executed.

Case study:

DROP PROCEDURE IF EXISTS mysql_scirpt.`08.CreateCaseOne`;

DELIMITER $$
$$
CREATE PROCEDURE mysql_scirpt.`08.CreateCaseOne`(a)BEGIN
	DECLARE num int DEFAULT 3; -- 1. Give num a default value of 3
	
	CASE num -- 2
		WHEN 2 THEN SELECT num; -- num = 2 is the result itself
		WHEN 3 THEN SELECT 0; If num is 3, the output is 0
	END CASE;

END$$
DELIMITER ;
Copy the code

Retrieve case syntax:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE
Copy the code

For each when clause, determine whether the following Boolean expression search_condition is true:

If a condition of a WHEN clause is true, the statement statement_list following the corresponding THEN clause is executed;
If all when clauses are not true, the statement statement_list following the else is executed.

Case study:

DROP PROCEDURE IF EXISTS mysql_scirpt.`09.CreateCaseTwo`;

DELIMITER $$
$$
CREATE PROCEDURE mysql_scirpt.`09.CreateCaseTwo`(
	IN p1 int.IN p2 int.OUT p3 int
)
BEGIN
	CASE  -- 2. Judge
		WHEN p1 > p2 THEN SET p3 = 1;
		WHEN p1 = p2 THEN SET p3 = 2;
		ELSE SET p3 = 3;
	END CASE;
	SELECT p3; - output
END$$
DELIMITER ;
CALL mysql_scirpt.`09.CreateCaseTwo`(200.100,@p3); -- 1. Pass in parameters
SELECT @p3; - output
Copy the code

Note:

If in case none of the when clauses compare true and there is no else part, then throw an exception: ‘Case not found for case Statement’;
Statement_list If there are multiple statements, use begin… The end block is enclosed (compound statement).

The while loop

Grammatical structure:

[begin_label:] WHILE search_condition DO
    statement_list;
END WHILE [end_label];
Copy the code

First check whether the loop start condition search_condition is true(loop end condition) :

If true, the statement statement_list in the body of the loop is executed. After each execution, the condition search_condition is rejudged to be true;
If the condition search_condition is false, the loop ends.

Feature: Judge first, then execute.

Example: Calculate the value from 1 to n

DROP PROCEDURE IF EXISTS mysql_scirpt.`10.CreateWhile`;

DELIMITER $$
$$
CREATE PROCEDURE mysql_scirpt.`10.CreateWhile`(IN n int)
BEGIN
	DECLARE total int DEFAULT 0;
	DECLARE num int DEFAULT 1;
	
	WHILE num<=n do -- Conditional statement
		SET total = total + num;
		SET num = num + 1; -- num increments, if not written, an infinite loop is entered
	END WHILE;

	SELECT total;

END$$
DELIMITER ;

CALL mysql_scirpt.`10.CreateWhile`(10);
Copy the code

Repeat loop

Syntax format:

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]
Copy the code

The statement statement_list in the body of the loop is repeatedly executed until the condition search_condition is true, and the loop ends

Feature: Execute first, judge later

Conditional loop control statements that exit the loop when the condition is met. While is executed when the condition is met, while repeat is exited when the condition is met.

Example: Calculate the value from 1 to n

DROP PROCEDURE IF EXISTS mysql_scirpt.`11.CcreateRepeat`;

DELIMITER $$
$$
CREATE PROCEDURE mysql_scirpt.`11.CcreateRepeat`(IN n int)
BEGIN
	DECLARE total int DEFAULT 0;
	
	REPEAT 
		SET total = total + n;
		SET n = n - 1;
		until n = 0
	END REPEAT;

	SELECT total;
	
END$$
DELIMITER ;

CALL mysql_scirpt.`11.CcreateRepeat`(10)
Copy the code

The loop cycle

Syntax format:

[begin_label:] LOOP
    statement_list ;
END LOOP [begin_label];
Copy the code

Execute the statement in the body of the loop repeatedly until the end of the loop; The loop ends with the leave statement.

The LOOP statement can be used to implement a simple infinite LOOP if an exit LOOP statement is not added to the statement_list.

Leave statement

Use to exit a labeled block or loop. Use in BEGIN… END or LOOP (LOOP, REPEAT, WHILE)

Syntax format:

LEAVE  label ;
Copy the code

Examples: Loop and Leave

DROP PROCEDURE IF EXISTS mysql_scirpt.`12.CreateLoopAndLeave`;

DELIMITER $$
$$
CREATE PROCEDURE mysql_scirpt.`12.CreateLoopAndLeave`(
	IN n int
)
BEGIN
	DECLARE total int DEFAULT 0;
	-- ins：begin_label
	ins: LOOP
		IF n <= 0 THEN
			LEAVE ins;
		END IF;
	
	SET total = total + n;
	SET n = n - 1;
	
	END LOOP ins;
	
	SELECT total;

END$$
DELIMITER ;

CALL mysql_scirpt.`12.CreateLoopAndLeave`(10);
Copy the code

Iterate statement

Can only appear in loops, REPEAT, and WHILE (with tags)

Meaning: Break out of this loop and start a new loop.

Syntax format:

ITERATE  label;
Copy the code

Cursor/cursor

Cursor: Data type that can only be used to store query result sets.

Read-only: Data in the underlying table cannot be updated with the cursor.
Non-scrollable: Rows can only be retrieved in the order determined by the SELECT statement. Rows cannot be retrieved in reverse order. In addition, you cannot skip rows or jump to a specific row in the result set.
Sensitive: There are two types of cursors: sensitive and insensitive. Sensitive cursors point to actual data, while insensitive cursors use a temporary copy of the data. A sensitive cursor executes faster than an insensitive cursor because it does not require a temporary copy of data. However, any changes made to data on other connections will affect the data used by the sensitive cursor, so it is safer not to update the data used by the sensitive cursor. MySQL cursors are sensitive.

Cursor: In a stored procedure or function, the cursor can be used to loop through a result set.

Sometimes, you need to move one or more rows forward or backward in the retrieved rows. That’s why cursors are used. A cursor is a database query stored on the MySQL server. It is not a SELECT statement, but a result set retrieved by the statement. Once the cursor is stored, the application can scroll through or browse the data as needed.

Cursors are primarily used in interactive applications, where users need to scroll through data on the screen and browse or make changes to the data.

Cursors involve several distinct steps:

Before a cursor can be used, it must be declared (defined). This procedure doesn’t actually retrieve the data, it just defines the SELECT statement to use.
Once declared, the cursor must be opened for use. This procedure actually retrieves the data using the SELECT statement defined earlier.
For cursors filled with data, fetch (retrieve) rows as needed.
You must close the cursor when you end its use.

Cursor use:

Declaration cursor:

DECLARE cursor_name CURSOR FOR select_statement;
Copy the code

Cursor_name: the name of the cursor, which is set by the user. Select_statement: a complete query statement that queries column names in a table.

Open cursor:

OPEN cursor_name;
Copy the code

Cursor_name: specifies the cursor name for the declaration.

Capture the cursor

FETCH cursor_name INTO var_name... ; (... There can be more than oneCopy the code

Cursor_name: specifies the cursor name for the declaration. Var_name: user-defined variable name

Close the cursor

CLOSE cursor_name;
Copy the code

Example:

Initialization script

create table emp(
  id int(11) not null auto_increment ,
  name varchar(50) not null comment 'name',
  age int(11) comment 'age',
  salary int(11) comment 'salaries',
  primary key(`id`))engine=innodb default charset=utf8 ;

insert into emp(id.name,age,salary) values(null.The Golden Lion.55.3800), (null.The White-browed Eagle King..60.4000), (null.'The Green-winged Bat King'.38.2800), (null.The Purple King..42.1800);
Copy the code

To generate the script

DROP PROCEDURE IF EXISTS mysql_scirpt.`13.CreateCursor`;

DELIMITER $$
$$
-- Query emP table data and display it row by row
CREATE PROCEDURE mysql_scirpt.`13.CreateCursor`(a)BEGIN
	Declare a variable
	DECLARE e_id int(11);
	DECLARE e_name varchar(50);
	DECLARE e_age int(11);
	DECLARE e_salary int(11);
	-- Set cursor
	DECLARE emp_result CURSOR FOR SELECT * FROM emp;
	-- Open the cursor
	OPEN emp_result;

	-- Capture the cursor
	FETCH emp_result INTO e_id,e_name,e_age,e_salary;
	SELECT concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary);

	-- Close the cursor
	CLOSE emp_result;
END$$
DELIMITER ;
Copy the code

Retrieve cursor data through a loop

DROP PROCEDURE IF EXISTS mysql_scirpt.`14.CreateGetCursor`;

DELIMITER $$
$$
CREATE PROCEDURE mysql_scirpt.`14.CreateGetCursor`(a)BEGIN
	
 	DECLARE id int(11);
 	DECLARE name varchar(50);
 	DECLARE age int(11);
 	DECLARE salary int(11);
	DECLARE has_data int default 1;

	DECLARE emp_result CURSOR FOR SELECT * FROM emp;
	DECLARE EXIT handler FOR NOT FOUND SET has_data = 0;
	
	OPEN emp_result;
	
	REPEAT
		FETCH emp_result INTO id ,name,age,salary;
		SELECT concat('id='.id.',name='.name.',age=',age,',salary=',salary);
		until has_data = 0
	END REPEAT;

	CLOSE emp_result;

END$$
DELIMITER ;
Copy the code

Read it line by line, whatever data is in the table will be read out.

Stored procedure function

An error occurs if you write directly to an SQL file

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
Copy the code

The reason: If we enable bin-log, we must specify whether our function is 1 DETERMINISTIC 2 NO SQL NO SQL statement, of course NO DATA modification 3 READS SQL DATA just READS DATA, 4 MODIFIES SQL DATA 5 CONTAINS SQL CONTAINS SQL statements

Only DETERMINISTIC, NO SQL, and READS SQL DATA are supported for function. If we have bin-log enabled, we must specify a parameter for our function.

Input statement:

set global log_bin_trust_function_creators=TRUE;
Copy the code

Syntax format:

CREATE FUNCTION fn_name ([func_parameter[...]])
RETURNS type[characteristic ...]  routine_bodyCopy the code

Parameters:

Parameter fn_name: indicates the name of the storage function.
Func_parameter: represents the parameter list of a stored function;
RETURNS Type: Specifies the type of the return value.
Characteristic parameters: Specifies the characteristics of a stored function. The values of these parameters are the same as those of stored procedures.
The routine_body argument: represents the content of SQL code, using BEGIN… END indicates the beginning and END of the SQL code.

Note: When you create a function specifically, the name of the function cannot be the same as an existing function. In addition to the above requirements, it is recommended that the function name (identifier) be function_xxx or func_xxx.

The difference between stored procedures and stored functions

The limit that a function can return only one variable. Stored procedures can return more than one. There are many restrictions on functions, such as not using temporary tables, only table variables. Some functions are not available and so on. Stored procedures are less restrictive.

Characteristic difference:

In general, the functions implemented by stored procedures are more complex, while the functions implemented are more targeted. Stored procedures, which are powerful enough to perform a range of database operations, including modifying tables; User-defined functions cannot be used to perform a set of operations that modify the state of the global database.
Stored procedures can return parameters, such as recordsets, whereas functions can only return values or table objects. A function can only return one variable; Stored procedures can return more than one. A stored procedure can have three types of parameters: IN,OUT, and INOUT. A function can have only the IN class. ~~ A stored procedure declaration does not require a RETURN type, but a function declaration must describe the RETURN type, and the function body must contain a valid RETURN statement.
Stored procedures, which can use nondeterministic functions, are not allowed to have nondeterministic functions built into user-defined function bodies.
A stored procedure is typically executed as a separate part (EXECUTE statement), while a function can be called as part of a query (SELECT call). Since a function can return a table object, it can be placed after the FROM keyword in a query. Instead of using stored procedures in SQL statements, you can use functions.

use

Example: Define a stored procedure that requests the total number of records that meet the criteria;

DROP FUNCTION IF EXISTS mysql_scirpt.`15.CreateFunction`;

DELIMITER $$
$$
CREATE FUNCTION mysql_scirpt.`15.CreateFunction`(vendID int)
RETURNS INT
BEGIN
	
	DECLARE num int;
	SELECT count(*) INTO num FROM products WHERE vend_id = vendID;
	
	RETURN num;
END$$
DELIMITER ;

SELECT mysql_scirpt.`15.CreateFunction`(1001);
Copy the code

Understand the use of Mysql stored procedures

Why use stored procedures

Creating a stored procedure

Deleting a stored procedure

Checking stored Procedures

Stored procedure syntax

variable

IF conditional judgment

Passing parameters

Case structure

The while loop

Repeat loop

The loop cycle

Leave statement

Iterate statement

Cursor/cursor

Stored procedure function

The difference between stored procedures and stored functions

use

Related Posts

Nginx proxy caching mechanism

Question 26 – Remove duplicates from ordered arrays

Detailed JVM escape analysis