MySQL Optimized Notes (6) - Stored Procedures and Stored Functions

Posted by tili on Sun, 16 Jun 2019 19:23:17 +0200

When a large system is set up, it will be found that many SQL operations overlap, and individual calculations are the same, for example, in a business system, how a single sheet is calculated.When these situations arise, using stored procedures is a great optimization.

This series: demo download

(1) MySQL Optimized Notes (1) - Basic operations of libraries and tables and data addition and deletion

(2) MySQL Optimized Notes (2) - Find Optimizations (1) (Non-indexed design)

(3) MySQL Optimized Notes (2) - Find Optimizations (2) (Outer Joins, Multi-Table Joint Queries, and Query Notes)

(4) MySQL Optimized Notes (3) - Use, Principle and Design Optimizations of Indexes

(5) MySQL Optimized Notes (IV) - Design and Optimization of Tables (Single Table, Multiple Table)

(6) MySQL Optimized Notes (5) - Database Storage Engine

The structure of this article: (1) overview of stored procedures; (2) use and optimization of stored procedures; (3) overview of stored functions; (4) use and optimization of stored functions.

1. Overview of stored procedures:

(1) What is a stored procedure?

A set of SQL statements that are compiled and stored in a database to perform a specific function.The user executes a stored procedure by specifying its name and giving parameters, if the stored procedure has parameters.

Is a precompiled collection of SQL and optional control flow statements stored under a name and processed as a unit.Stored procedures are stored in a database and can be executed by an application through a single call, allowing users to declare variables, perform conditionally, and other powerful programming functions.Stored procedures are compiled on the server when they are created, so they execute faster than a single SQL statement.

(2) Stored procedure advantages:

1) Stored procedures are only compiled at creation time and do not need to be recompiled each time they are executed in the future, whereas general SQL statements are compiled every time they are executed, so using stored procedures can speed up database execution.

2) When performing complex operations on a database (such as Update,Insert,Query,Delete on multiple tables), this complex operation can be encapsulated using stored procedures in conjunction with transaction processing provided by the database.

Stored procedures can contain program flows, logic, and queries to databases.Data logic can also be encapsulated and hidden by entities.

3) Stored procedures can be reused to reduce the workload of database developers.

4) High security allows you to configure that only this user has access to the specified stored procedure.

For example, if a table does not directly allow a user to access it, but requires that the user be allowed to access and modify one or more fields, that can be accomplished through a stored procedure and allowed to use the stored procedure.

(3) Disadvantages of stored procedures:

1) If the changes are large enough to require changes to the parameters of the input stored procedure or to change the data returned by it, you still need to update the code in the assembly to add parameters, update the GetValue() call, and so on, which can be cumbersome to estimate.

2) Poor portability.Stored procedures are poorly migrated because they must be closely integrated with your business logic to make sense.

2. Use and optimization of stored procedures:

(1) Use of stored procedures

(1) Stored procedure creation:

create procedure Stored procedure name([parameter])   
[characteristics]
[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] 
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
begin   
MySQL Sentence;   
end;

[parameter] Specifies the list of parameters for the stored procedure.The format is as follows:

[IN|OUT|INOUT] param_name type

Where in denotes the input parameter, out denotes the output parameter, in out denotes both input and output, param_name denotes the parameter name, and type denotes the type of parameter.

At the same time, while creating the grammar is as simple as that (optional in middle brackets), in fact, we have some risk aversion requirements: (for the middle brackets section)

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement or statements

DELIMITER: Stored procedures are usually created with the following sentence at the beginning and end to avoid MySQL from internally storing the stored procedure; "interpreted as an end symbol and finally notified of the end of the stored procedure by"DELIMITER;".Note that the purpose of the "DELIMITER //" statement is to set the end character of MYSQL to //, since the default statement end character of MYSQL is a semicolon; to avoid conflicts with the end of SQL statements in stored procedures, you need to use DELIMITER to change the end character of stored procedures and end them with "END//" (or other forms).

Backslash (\) characters should be avoided when using the DELIMITER command, because backslashes are the escape characters of MYSQL!!!

DELIMITER$$.....END$$ DELIMITER

LANGUAGE SQL: Used to illustrate that the statement part is a SQL statement and may support other types of statements in the future.

NOT] DETERMINISTIC: A program or thread is considered "deterministic" if it always produces the same result for the same input parameter, otherwise it is "non-deterministic".If neither DETERMINISTIC nor NOT DETERMINISTIC is given, the default is NOT DETERMINISTIC (non-deterministic) CONTAINS SQL: a statement indicating that the subprogram does not contain read or write data.

NO SQL: Indicates that the subprogram does not contain SQL statements.

READS SQL DATA: Indicates that the subprogram contains statements that read data, but not statements that write data.

MODIFIES SQL DATA: Indicates that the subprogram contains statements that write data.

SQL SECURITY DEFINER: Indicates that a program executing a stored procedure is executed with the privileges of the user who created the stored procedure.

SQL SECURITY INVOKER: Indicates that a program executing a stored procedure is executed with the permissions of the user calling the stored procedure.(For example, the stored procedure above I wrote was executed with the permissions of the user calling the stored procedure, the current stored procedure is used to query the Employee table, and if the user currently executing the stored procedure does not have permission to query the Employee table, an error of insufficient permissions will be returned. If DEFINER is used instead, if the stored procedure was created by a ROOT user then any user will log inCalls to stored procedures can be executed because the permissions to execute stored procedures become root)

COMMENT'string': Remarks, the same as field notes that create tables.

Note for characteristic:

When writing stored procedures and functions, it is recommended that you explicitly specify the state of the above characteristic section, especially in environments where replication exists. If you create a function that does not explicitly specify these states, you will get an error. If you do not explicitly specify DETERMINISTIC, NO SQL, or READS SQL DATA from a non-replication environment, you will also migrate a database with functions to a machine in a replication environment.Report errors.

Inside WITH:

RECOMPILE: Indicates that SQL Server will not cache the plan for the process, which will be recompiled at run time.Use the RECOMPILE option when using atypical or temporary values without overwriting the execution plan cached in memory.

ENCRYPTION: Represents an entry in the SQL Server encrypted syscomments table that contains the text of the CREATE PROCEDURE statement.Using ENCRYPTION prevents procedures from being published as part of SQL Server replication.Describes that during the upgrade process, SQL Server recreates the encryption process using cryptographic comments stored in syscomments.

FOR REPLICATION: Specifies that stored procedures created for replication cannot be executed at the Subscriber.Stored procedures created with the FOR REPLICATION option can be used as stored procedure filtering and can only be executed during replication.This option cannot be used with the WITH RECOMPILE option.

BEGIN-END: Specifies the action to be performed by the process.

sql_statement: Any number and type of Transact-SQL statements to be included in the procedure.But there are some limitations.

Create examples: Multiple application examples:

1) Stored procedures that return only a single recordset - examples where no parameters are passed:

//Establish
delimiter $$
drop procedure if exists ptest;
create procedure ptest()
begin
select * from use1;
end $$
delimiter ;

//call
call ptest();

2) Stored procedures with return values without input or output:

//Establish
delimiter $$
drop procedure if exists ptest1;
create procedure ptest1()
begin
insert into user1 (name) values ('auxiliary');
end $$
delimiter ;

//call
call ptest();

3) No return value - examples of passing parameters:

//Establish
delimiter $$
drop procedure if exists ptestUpdate;
create procedure ptestUpdate(IN n int)
begin
DECLARE i int;
DECLARE s int;
SET i = 0;

WHILE i <= n 
DO
    update user1 c set c.name='fuzhu' where id =i;
     set i = i + 1;
    END WHILE;
    SELECT s;
end $$
delimiter ;

//Call:
set @number = 6;
call ptestUpdate(@number);

4) Stored procedure parameters:

//Create an example
delimiter $$
drop procedure if exists ptest2;
create procedure ptest2(IN i int )
begin
DECLARE i int;
select * from user1  where id = 1;
end $$
delimiter ;

//Call:
set @id = 10;

call ptest2(@id);
------------
select @id as id_out;

//The above call returns two result sets

5) Stored procedure return value (used by OUT):

//Establish
delimiter $$
CREATE PROCEDURE testOut(
 OUT total INT,
 OUT firstform INT,
 OUT secondform INT)
BEGIN
 -- Number of total tables
 SELECT
            count(*) INTO total
        FROM
            alluser
        WHERE
            1=1;
 -- Number of Subtables I
 SELECT
            count(*) INTO firstform
        FROM
            user1
        WHERE
            1=1;
 -- Number of Subtables 2
 SELECT
            count(*) INTO secondform
        FROM
            user2
        WHERE
           1=1;
END $$
delimiter ;

//Call:
call testOut(@total,@firstform,@secondform);
SELECT @total,@firstform,@secondform;

6) Stored procedure return set, input and output parameter use (inout):

//Establish
delimiter $$
drop procedure if exists pr_param_inout;
create procedure pr_param_inout
(
   inout id int
)
begin
   select id as id_inner_1;  -- id Value is the value passed in by the caller, outputting the first data result set

   if (id is not null) then  -- if..else..Output second data result set in 
      set id = id + 1;

      select id as id_inner_2;
   else
      select 1 into id;
   end if;

   select id as id_inner_3; -- Output third data result set
end $$
delimiter ;


//Call:
set @id = 10;

call pr_param_inout(@id);
------------------
//Simple Test Input
select @id as id_out;

(2) Modify stored procedure function syntax

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

characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

(3) Delete the stored procedure function syntax: (ptest is the stored procedure name, the same as)

DROP {PROCEDURE | FUNCTION} [IF EXISTS] ptest

(4) View stored procedures and functions:

show procedure status like 'ptest'

The database name, stored procedure name, PROCEDURE parameter represent query stored procedure, modification time, creation time, security type, comment (comment), character set, table encoding, database character set.

(5) View the syntax for creating stored procedures and functions:

SHOW CREATE {PROCEDURE | FUNCTION} ptest

SHOW CREATE PROCEDURE ptest;

These are stored procedure name, SQL mode, statement created, character set, table code, database code.

(6) View stored procedure and function details:

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='ptest'

You can see the stored procedure's SQL and its permissions in red.

(7) Execution of stored procedures and functions requires EXECUTE privileges

Stored procedures and stored functions are collections of SQL statements stored on the server side. To use these defined stored procedures and stored functions, they must be invoked.

Stored procedures are called through CALL statements.Stored functions are used in the same way as MySQL's internal functions.

Information about EXECUTE permissions is stored in the USER_PRIVILEGES table under the information_schema database

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

(8) Stored procedure flow control functions:

1. IF statement:

The syntax is as follows: Specific examples are used above.

//The search_condition parameter represents the conditional judgment statement; the statement_list parameter represents the execution statement for different conditions.

IF search_condition THEN statement_list 
[ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list] 
END IF

2.CASE statement:

CASE statements are also used to make conditional judgments, which can make conditional judgments more complex than IF statements.The syntax is as follows:

/*
    case_value A parameter represents a variable for conditional judgment;
    when_value The parameter represents the value of the variable;
    statement_list Parameters represent execution statements with different when_value values.
*/
CASE case_value 
WHEN when_value THEN statement_list 
[WHEN when_value THEN statement_list] ... 
[ELSE statement_list] 
END CASE

There is another form of CASE statement.The following:

/*
    search_condition Parameters represent conditional judgment statements;
    statement_list Parameters represent execution statements of different conditions.
*/
CASE 
WHEN search_condition THEN statement_list 
[WHEN search_condition THEN statement_list] ... 
[ELSE statement_list] 
END CASE

CASE example:

CASE age 
WHEN 20 THEN SET @count1=@count1+1; 
ELSE SET @count2=@count2+1; 
END CASE ;

It can also be written as follows:

CASE 
WHEN age=20 THEN SET @count1=@count1+1; 
ELSE SET @count2=@count2+1; 
END CASE ;

The CASE statement here is slightly different from the CASE statement for the SQL CASE expression described in the Control Flow Function.The CASE statement here cannot have ELSE NULL clause and terminate with END CASE instead of END!!

3.LOOP statement:

LOOP statements allow certain statements to be executed repeatedly, creating a simple loop.However, the LOOP statement itself does not have a statement that stops the loop halfway. It must encounter a LEAVE statement, etc. to stop the loop (which becomes an endless loop).The syntax is as follows:

/*
    begin_label The parameter and the end_label parameter represent the start and end flags of the loop, respectively, which must be the same and can be omitted.
    statement_list A parameter represents a statement that needs to be executed in a loop.
*/
[begin_label:] LOOP 
statement_list 
END LOOP [end_label]

4.LEAVE statement:

LEAVE statements are mainly used to jump out of loop control.Its syntax is as follows:

//The label parameter represents the flag of the loop.
LEAVE label 

//Example: This example loop executes count plus 1.When count equals 10, the LEAVE statement jumps out of the loop.
add_num: LOOP 
SET @count=@count+1; 
IF @count=10 THEN 
LEAVE add_num ; 
END LOOP add_num ;

5.ITERATE statement:

The ITERATE statement is also used to jump out of the loop.However, the ITERATE statement jumps out of this loop and goes directly to the next one.ITERATE statements can only appear within LOOP, REPEAT, WHILE statements.The syntax is as follows:

//The label parameter represents the flag of the loop.
ITERATE label 

//Example: This example loop executes count plus 1 and ends the loop with a count value of 100.If the value of count divides by 2, you will jump out of this cycle and not execute the following SELECT statement.
add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
ELSE IF MOD(@count,2)=0 THEN 
ITERATE add_num; 
SELECT * FROM employee ; 
END LOOP add_num ;

Note: Both LEAVE and ITERATE statements are used to jump out of a loop.But they are different.

The LEAVE statement is to jump out of the entire loop and execute the program that follows it.The ITERATE statement jumps out of this loop and goes into the next one.

6.REPEAT statement:

REPEAT statements are conditionally controlled looping statements.When certain conditions are met, the loop statement is jumped out.The syntax is as follows:

//Where the statement_list parameter represents the execution statement of the loop, and the search_condition parameter represents the condition under which the loop ends when that condition is met.
[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label] 

//Example: This example loop executes count plus 1 and ends the loop with a count value of 100.
REPEAT 
SET @count=@count+1; 
UNTIL @count=100 
END REPEAT ;

7.WHILE statement:

WHILE statements are also conditionally controlled looping statements.But WHILE statements are different from REPEAT statements.WHILE statements are statements within a loop that execute when a condition is met.The syntax is as follows:

/*
    search_condition The parameter represents the condition under which the loop executes, and the loop executes when the condition is met.
    statement_list The parameter represents the execution statement of the loop.
*/
[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label] 


//Example: This example loop executescountplus1Operation,countValue less than100Executes a loop.
WHILE @count<100 DO 
SET @count=@count+1; 
END WHILE ;

8.Cursor.(The demo of the stored procedure is at the bottom of this section)

Query statements may query multiple records, using cursors in stored procedures and functions to read records in the query result set one by one.

Cursor action:

The purpose of the cursor is to traverse the records returned by the query database for appropriate operations.There are the following properties:

a. The cursor is read-only, that is, it cannot be updated;

b. The cursor cannot scroll, that is, it can only traverse in one direction, it cannot move forward or backward freely between records, and it cannot skip some records.

c. Avoid updating data on tables that already have cursors open.

The use of cursors includes declaring the cursor, opening the cursor, using the cursor, and closing the cursor.Cursors must be declared before handlers and after variables and conditions.

1. Declare cursor:

MySQL uses the DECLARE keyword to declare cursors.The syntax is as follows:

//The cursor_name parameter represents the name of the cursor; the select_statement parameter represents the name of the cursorSELECTThe contents of the statement, returning a result set used to create the cursor
DECLARE cursor_name CURSOR FOR select_statement ;

//Example: The cursor name below is cur_user,SELECT Statement is fromuserFind out in table name，age The value of the field is placed in a result set.
DECLARE cur_user CURSOR FOR SELECT name, age FROM user;

2. Open the cursor:

MySQL uses the OPEN keyword to open the cursor.The syntax is as follows:

//The cursor_name parameter represents the name of the cursor.
OPEN  cursor_name ;

//Example: Open a cursor for cur_user:
OPEN cur_user;

3. Use the cursor:

MySQL uses the FETCH keyword to use the cursor.Grammar:

/*
cursor_name The parameter denotes the name of the cursor; the var_name parameter denotes that the information queried by the SELECT statement in the cursor is stored in the parameter.Var_name must be defined before the cursor is declared
*/
FETCH cur_user INTO var_name[,var_name...] ;


//Example: in cursor cur_userSELECTStore the information queried by the statement user_name and user_age Medium. user_name and user_age Must have been defined before.
FETCH  cur_user  INTO user_name, user_age ;

4. Close the cursor:

MySQL uses the CLOSE keyword to close the cursor.Grammar:

//The cursor_name parameter represents the name of the cursor.You cannot use FETCH to use the cursor after closing.
CLOSE  cursor_name ;

Note: In MYSQL, cursors can only be used in stored procedures and functions!!

Stored procedure cursor DEMO: Copy table test_cursor data to test_cursor2. Incidentally give the SQL exception table

CREATE TABLE IF NOT EXISTS `test_cursor` (  
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;  

CREATE TABLE IF NOT EXISTS `test_cursor2` (  
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;  


 INSERT INTO `test_cursor` VALUES (1, 'Auxiliary 1');  
INSERT INTO `test_cursor` VALUES (2, 'Auxiliary 2');  
INSERT INTO `test_cursor` VALUES (3, 'Auxiliary 3');  
INSERT  INTO `test_cursor` VALUES (4, 'Auxiliary 4');



DELIMITER //
drop procedure if exists p_test_cursor;
CREATE PROCEDURE  p_test_cursor()
Begin
DECLARE ID int(11);                 --  Get a temporary variable ID，To store the found ID
DECLARE done INT DEFAULT 0;         --  Conditional State Quantity
DECLARE  p_name VARCHAR(255);       --  Get a temporary variable name，To store the found name
DECLARE cur_product  CURSOR FOR SELECT * FROM test_engine.test_cursor;      --  Define Cursor
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; --  Define the state variable, which is a conditional process,In the light of NOT FOUND，Is an exception trigger.The 02000 exception is SELECT INTO Statement or INSERT The result of the statement's subquery is an empty table trigger.
OPEN  cur_product;              --  open cursor
REPEAT                      --  Start cycle
FETCH  cur_product INTO ID,p_name;   
IF NOT done THEN
    INSERT INTO test_cursor2 VALUES (ID,p_name);    --  Loop through to insert
END IF;  
UNTIL done END REPEAT;      --  When done=1 Time to end the cycle
CLOSE  cur_product;
end;
//
DELIMITER ;

//call
call  p_test_cursor();

(2) Optimization of stored procedures:

Optimization is all-sided, even if listed here.Let me list the development requirements for stored procedures.Normalizing is also a big optimization.

(1) SQL optimization.SQL optimization for stored procedure encapsulation itself; see the previous articles in this series for details on SQL optimization.

(2) Pre-operation inspection.Before inserting data into a table, determine if the data already exists in the table, and if so, do not insert data.Similarly, before querying data from a table, you need to determine if the data exists in the table before you can find it from the table.

(3) Standardization of result identification.Whether the insertion of data into a table is successful or not, there should be an identifying value that represents the result of the execution, which also facilitates developers'tracking and debugging of program processes.For example, before each leave statement, there should be a return value for which we define an output parameter.

(4) List specific field names.With the addition of fields in future software versions, this insert statement is most likely to fail.So listing the required fields is important for normalization.

(5) Failed return.After the insert statement is executed, the @error_count parameter that comes with MySQL is used to determine whether the insert was successful, allowing developers to track the execution results.For example, if the value of this parameter is not zero, which means the insertion failed, then we use a return parameter value to indicate the operation failed.

DEMO:

@error_count If the value of this parameter is not zero, indicating insertion failure, then we use a return parameter value to indicate operation failure.

drop procedure if exists p_optimization;
delimiter $$
create procedure p_optimization
(
    in    testid int,
    out   p_result    int   -- 0-succ, other-fail
)
         p_optimization_label:begin         -- Loop start
       declare  p_usertype   int;
       declare  p_datacount  int;

       select count(*) into p_datacount from user1 where id=testid;         -- First Result Set
       if p_datacount> 0 then               --  The first judgment begins, p_datacount Jump out of cycle if greater than 0
       begin
           select name into p_usertype from user1 where id=testid;
       end;
       else
       begin
           set p_result = 1;                        
           leave p_optimization_label;      -- End of cycle
       end;
       end if;                                      --  End of First Judgment

       select count(*) into p_datacount from user1 where id=testid;
       if p_datacount = 0 then                  --  Start of Second Judgment
       begin
           insert into user1(name) values('auxiliary');
           if @error_count!=0 then          -- Embedded Judgment Start
           begin
               set p_result= 3;
           end;
           else
           begin
               set p_result= 0;
           end;
           end if;                              -- End of embedded judgement
       end;
       else
       begin
           set p_result = 2;
       end;
       end if;                          --  End of Second Judgment

       leave p_optimization_label;
end;
$$
delimiter ;


//Invoke test:
set @id = 10;
call p_optimization(@id,@p_result);
select @id,@p_result;

3. Overview of storage functions (custom functions):

(1) What is a storage function?

To encapsulate a piece of sql code to accomplish a specific function, the result must be returned.The remaining features are basically the same as stored procedures.

(2) Differences from stored procedures:

1) Stored functions have and only have one return value, while stored procedures cannot have a return value.That is, return can be used.(Functions can return return returned values or table objects, never result sets)

2) Functions can only have input parameters and cannot take in, whereas stored procedures can have multiple in, out, and in out parameters.

3) Statements in stored procedures are more powerful, and stored procedures can implement very complex business logic, but functions have many limitations, such as the inability to use insert,update,delete,create statements in functions; storage functions only complete the query work, can accept input parameters and return a result, that is, function implementation has a strong functional specificity.For example: duration calculation, price calculation.

4) Stored procedures can call storage functions.Functions cannot call stored procedures.

5) Stored procedures are typically executed as a separate part (call calls).Functions can be called as part of a query statement.

(3) Grammar:

Establish:

create function function([Function parameters[,....]]) Returns Return type
　　Begin
　　　　If(
　　　　　　Return (Data returned)
　　　　Else 
　　　　　　Return (Data returned)
　　　　end if;
　　end;

Flow-of-control statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE) are also legal.

Variable declarations (DECLARE) and assignments (SETs) are legal.

Allow condition declaration.

Exception handling declarations are also allowed.

But here, keep in mind that the function has a limitation: it cannot return a result set.Cannot return result set.(You can use select, insert, update, and so on.)

Modify, delete, and view states are essentially the same as stored procedure syntax.It just replaces procedure with function.

This blogger found a detailed collection of MySQL default functions

(4)DEMO: Download Address

(1) helloworld:

//Establish
DELIMITER $$
CREATE FUNCTION hello()
RETURNS VARCHAR(255)
BEGIN
RETURN 'Hello  world,I am auxiliary';
END $$
DELIMITER ;

//Call:
SELECT hello();

(2) Functions with parameters and insert statements, update and delete

//Establish
DELIMITER $$
CREATE FUNCTION hello1(userid int ,pname VARCHAR(20))
 RETURNS INT UNSIGNED
   BEGIN
  INSERT into user1(id,name) VALUES(userid ,pname);
     RETURN LAST_INSERT_ID();
    END $$

//Call: Because I did not get a self-increasing primary key in demo, I can only return one0Yes.
select hello1(3,'auxiliary');

(3) Functions with parameters and query statements are used so that the return is a value - only for single values

DELIMITER //
CREATE FUNCTION getName(id INT)
RETURNS CHAR(50)
RETURN (SELECT name FROM user1 WHERE id=id);
//
DELIMITER ;


//Call:
SELECT getName(1);

Another way to return a single value is to wrap the query in a parameter and return.

DELIMITER //
CREATE FUNCTION getName1(id INT)
RETURNS VARCHAR(50)
Begin
declare number varchar(50);
SELECT count(*)   into number FROM user1 u WHERE u.id=id;
RETURN (number);
end;
//
DELIMITER ;

//Call:
SELECT getName1();

(4) Return for multiple records:

The cursor is used in the same way as described in the stored procedure above.

//Establish
DELIMITER //
drop FUNCTION  if exists p_function_cursor;
CREATE FUNCTION  p_function_cursor()   --  Create function
RETURNS VARCHAR(50)
Begin           
DECLARE done INT DEFAULT 0;     
DECLARE  p_name VARCHAR(255);       --  A temporary variable to store what we've found
DECLARE cur_product  CURSOR FOR SELECT name FROM test_engine.test_cursor;       --  Define Cursor
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
DROP TEMPORARY TABLE IF EXISTS infologs;
 CREATE TEMPORARY TABLE infologs (  --  Create a temporary table to return multiple records

 Id int(11) NOT NULL AUTO_INCREMENT,  

 Msg varchar(255) NOT NULL,  

 PRIMARY KEY (Id)  

 )ENGINE=InnoDB,COLLATE='utf8_general_ci';  

OPEN  cur_product;      --  open cursor
REPEAT                  --  Start cycle 
FETCH  cur_product INTO p_name;   --  Remove the contents of the cursor to a temporary variable
IF NOT done THEN         --  
    INSERT INTO infologs(Msg) VALUES (p_name);   --  Insert into Temporary Table 
END IF;  
UNTIL done END REPEAT;       --  When done=1 Time to end the cycle
CLOSE  cur_product;       --  Close cursor
return (select Msg from infologs where Id=2); --  Return the single record found.But we can access the memory directly infologs surface
end;
//
DELIMITER ;


//Calls: call functions, and look up memory tables directly
#SELECT p_function_cursor();
#select * from infologs;

4. Use and optimization of storage functions:

Same as the second point above - Stored procedure use, optimization.It's just that some grammars are different.

DEMO download address: Stamp here

Okay, MySQL Optimized Notes (6) - Stored procedures and stored functions are finished, and a MySQL Optimized Note is a step in the process. I will continue this series of articles and share my experience with you.Welcome to point out the errors below and learn together!!Your compliment is my best support!!

Programmer Think

MySQL Optimized Notes (6) - Stored Procedures and Stored Functions

When a large system is set up, it will be found that many SQL operations overlap, and individual calculations are the same, for example, in a business system, how a single sheet is calculated.When these situations arise, using stored procedures is a great optimization.

This series: demo download

(1) MySQL Optimized Notes (1) - Basic operations of libraries and tables and data addition and deletion

(2) MySQL Optimized Notes (2) - Find Optimizations (1) (Non-indexed design)

(3) MySQL Optimized Notes (2) - Find Optimizations (2) (Outer Joins, Multi-Table Joint Queries, and Query Notes)

(4) MySQL Optimized Notes (3) - Use, Principle and Design Optimizations of Indexes

(5) MySQL Optimized Notes (IV) - Design and Optimization of Tables (Single Table, Multiple Table)

(6) MySQL Optimized Notes (5) - Database Storage Engine

The structure of this article: (1) overview of stored procedures; (2) use and optimization of stored procedures; (3) overview of stored functions; (4) use and optimization of stored functions.

1. Overview of stored procedures:

(1) What is a stored procedure?

A set of SQL statements that are compiled and stored in a database to perform a specific function.The user executes a stored procedure by specifying its name and giving parameters, if the stored procedure has parameters.

(2) Stored procedure advantages:

1) Stored procedures are only compiled at creation time and do not need to be recompiled each time they are executed in the future, whereas general SQL statements are compiled every time they are executed, so using stored procedures can speed up database execution.

2) When performing complex operations on a database (such as Update,Insert,Query,Delete on multiple tables), this complex operation can be encapsulated using stored procedures in conjunction with transaction processing provided by the database.

Stored procedures can contain program flows, logic, and queries to databases.Data logic can also be encapsulated and hidden by entities.

3) Stored procedures can be reused to reduce the workload of database developers.

4) High security allows you to configure that only this user has access to the specified stored procedure.

For example, if a table does not directly allow a user to access it, but requires that the user be allowed to access and modify one or more fields, that can be accomplished through a stored procedure and allowed to use the stored procedure.

(3) Disadvantages of stored procedures:

1) If the changes are large enough to require changes to the parameters of the input stored procedure or to change the data returned by it, you still need to update the code in the assembly to add parameters, update the GetValue() call, and so on, which can be cumbersome to estimate.

2) Poor portability.Stored procedures are poorly migrated because they must be closely integrated with your business logic to make sense.

2. Use and optimization of stored procedures:

(1) Use of stored procedures

(1) Stored procedure creation:

[parameter] Specifies the list of parameters for the stored procedure.The format is as follows:

Where in denotes the input parameter, out denotes the output parameter, in out denotes both input and output, param_name denotes the parameter name, and type denotes the type of parameter.

At the same time, while creating the grammar is as simple as that (optional in middle brackets), in fact, we have some risk aversion requirements: (for the middle brackets section)

Backslash (\) characters should be avoided when using the DELIMITER command, because backslashes are the escape characters of MYSQL!!!

LANGUAGE SQL: Used to illustrate that the statement part is a SQL statement and may support other types of statements in the future.

NO SQL: Indicates that the subprogram does not contain SQL statements.

READS SQL DATA: Indicates that the subprogram contains statements that read data, but not statements that write data.

MODIFIES SQL DATA: Indicates that the subprogram contains statements that write data.

SQL SECURITY DEFINER: Indicates that a program executing a stored procedure is executed with the privileges of the user who created the stored procedure.

COMMENT'string': Remarks, the same as field notes that create tables.

Note for characteristic:

Inside WITH:

RECOMPILE: Indicates that SQL Server will not cache the plan for the process, which will be recompiled at run time.Use the RECOMPILE option when using atypical or temporary values without overwriting the execution plan cached in memory.

BEGIN-END: Specifies the action to be performed by the process.

sql_statement: Any number and type of Transact-SQL statements to be included in the procedure.But there are some limitations.

Create examples: Multiple application examples:

1) Stored procedures that return only a single recordset - examples where no parameters are passed:

2) Stored procedures with return values without input or output:

3) No return value - examples of passing parameters:

4) Stored procedure parameters:

5) Stored procedure return value (used by OUT):

6) Stored procedure return set, input and output parameter use (inout):

(2) Modify stored procedure function syntax

(3) Delete the stored procedure function syntax: (ptest is the stored procedure name, the same as)

(4) View stored procedures and functions:

The database name, stored procedure name, PROCEDURE parameter represent query stored procedure, modification time, creation time, security type, comment (comment), character set, table encoding, database character set.

(5) View the syntax for creating stored procedures and functions:

These are stored procedure name, SQL mode, statement created, character set, table code, database code.

(6) View stored procedure and function details:

You can see the stored procedure's SQL and its permissions in red.

(7) Execution of stored procedures and functions requires EXECUTE privileges

Stored procedures and stored functions are collections of SQL statements stored on the server side. To use these defined stored procedures and stored functions, they must be invoked.

Stored procedures are called through CALL statements.Stored functions are used in the same way as MySQL's internal functions.

Information about EXECUTE permissions is stored in the USER_PRIVILEGES table under the information_schema database

(8) Stored procedure flow control functions:

1. IF statement:

The syntax is as follows: Specific examples are used above.

2.CASE statement:

CASE statements are also used to make conditional judgments, which can make conditional judgments more complex than IF statements.The syntax is as follows:

There is another form of CASE statement.The following:

CASE example:

It can also be written as follows:

The CASE statement here is slightly different from the CASE statement for the SQL CASE expression described in the Control Flow Function.The CASE statement here cannot have ELSE NULL clause and terminate with END CASE instead of END!!

3.LOOP statement:

LOOP statements allow certain statements to be executed repeatedly, creating a simple loop.However, the LOOP statement itself does not have a statement that stops the loop halfway. It must encounter a LEAVE statement, etc. to stop the loop (which becomes an endless loop).The syntax is as follows:

4.LEAVE statement:

LEAVE statements are mainly used to jump out of loop control.Its syntax is as follows:

5.ITERATE statement:

The ITERATE statement is also used to jump out of the loop.However, the ITERATE statement jumps out of this loop and goes directly to the next one.ITERATE statements can only appear within LOOP, REPEAT, WHILE statements.The syntax is as follows:

Note: Both LEAVE and ITERATE statements are used to jump out of a loop.But they are different.

The LEAVE statement is to jump out of the entire loop and execute the program that follows it.The ITERATE statement jumps out of this loop and goes into the next one.

6.REPEAT statement:

REPEAT statements are conditionally controlled looping statements.When certain conditions are met, the loop statement is jumped out.The syntax is as follows: