catalogue

1. Concepts of stored procedures and functions

2. Benefits of stored procedures and functions

3. Differences between stored procedures and functions

4. Create stored procedure

5. Call stored procedure

6. View stored procedures

7. Delete stored procedure

8. Stored procedure syntax

8.1 introduction to stored procedure syntax

8.2 use of variables

8.3 use of if statement

8.4 transfer of parameters

8.5 use of case statements

8.6 while loop

8.7 repeat cycle

8.8 loop loop

1. Concepts of stored procedures and functions

• Stored procedures and functions are a collection of SQL statements that have been compiled in advance and stored in the database

2. Benefits of stored procedures and functions

• Stored procedures and functions can be reused to reduce the workload of developers. Similar to the method in java, it can be called multiple times

• Reduce network traffic. Stored procedures and functions are located on the server. When calling, you only need to pass names and parameters

• Reducing data transmission between database and application server can improve the efficiency of data processing

• Implementing some business logic at the database level can reduce the business processing at the code level

3. Differences between stored procedures and functions

• Function must have a return value

• The stored procedure has no return value

4. Create stored procedure

• Little knowledge

  /*
  This keyword is used to declare the separator of sql statement and tell MySQL that the command has ended!
  The default separator of sql statements is semicolon, but sometimes we need a function. Semicolons are included in sql statements, but they are not used as end identifiers.
  At this time, you can use DELIMITER to specify the separator!
  */
  --Standard grammar
  DELIMITER delimiter

• Data preparation

  -- establish db8 database
  CREATE DATABASE db8;
  
  -- use db8 database
  USE db8;
  
  -- Create student table
  CREATE TABLE student(
  	id INT PRIMARY KEY AUTO_INCREMENT,	-- student id
  	NAME VARCHAR(20),					-- Student name
  	age INT,							-- Student age
  	gender VARCHAR(5),					-- Student gender
  	score INT                           -- Student achievement
  );
  -- Add data
  INSERT INTO student VALUES (NULL,'Zhang San',23,'male',95),(NULL,'Li Si',24,'male',98),
  (NULL,'Wang Wu',25,'female',100),(NULL,'Zhao Liu',26,'female',90);
  
  -- Group according to gender and query the total score of each group of students. Sort according to the ascending order of the total score
  SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;

• Create stored procedure syntax

  --Modify the separator to$
  DELIMITER $

  --Standard grammar
  CREATE PROCEDURE stored procedure name (parameter...)
  BEGIN
  sql statement;
  END$

  --Change separator to semicolon
  DELIMITER ;

• Create stored procedure

  -- Modify the separator to $
  DELIMITER $
  
  -- Create a stored procedure to encapsulate the method of grouping and querying students' total score sql sentence
  CREATE PROCEDURE stu_group()
  BEGIN
  	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
  END$
  
  -- Change separator to semicolon
  DELIMITER ;

5. Call stored procedure

• Call stored procedure syntax

  --Standard grammar
  CALL stored procedure name (actual parameter);

  --Call stu_group stored procedure
  CALL stu_group();

6. View stored procedures

• View stored procedure syntax

  • --Standard syntax for querying all stored procedures in a database
    SELECT * FROM mysql.proc WHERE db = 'database name';

7. Delete stored procedure

• Delete stored procedure syntax

  --Standard grammar
  DROP PROCEDURE [IF EXISTS] stored procedure name;

  --Delete stu_group stored procedure
  DROP PROCEDURE stu_group;

8. Stored procedure syntax

8.1 introduction to stored procedure syntax

• Stored procedures are programmable. It means that you can use variables, expressions, conditional control statements, loop statements, etc. to complete more complex functions!

8.2 use of variables

• Define variables

  --Standard grammar
  Define variable name data type [DEFAULT];
  --Note: DECLARE defines local variables and can only be used within the BEGIN END range

  -- Define a int Type variable and assign a default value of 10
  DELIMITER $
  
  CREATE PROCEDURE pro_test1()
  BEGIN
  	DECLARE num INT DEFAULT 10;   -- Define variables
  	SELECT num;                   -- Query variable
  END$
  
  DELIMITER ;
  
  -- call pro_test1 stored procedure
  CALL pro_test1();

 

• Assignment of variable 1

  --Standard grammar
  SET variable name = variable value;

  -- Define string type variables and assign values
  DELIMITER $
  
  CREATE PROCEDURE pro_test2()
  BEGIN
  	DECLARE NAME VARCHAR(10);   -- Define variables
  	SET NAME = 'stored procedure';       -- Assign values to variables
  	SELECT NAME;                -- Query variable
  END$
  
  DELIMITER ;
  
  -- call pro_test2 stored procedure
  CALL pro_test2();

• Assignment of variables 2

  --Standard grammar
  SELECT column name INTO variable name FROM table name [WHERE condition];

  -- Define two int Variable to store the total scores of male and female students
  DELIMITER $
  
  CREATE PROCEDURE pro_test3()
  BEGIN
  	DECLARE men,women INT;  -- Define variables
  	SELECT SUM(score) INTO men FROM student WHERE gender='male';    -- Calculate the total score of male students and assign it to men
  	SELECT SUM(score) INTO women FROM student WHERE gender='female';  -- Calculate the total score of female students and assign it to women
  	SELECT men,women;           -- Query variable
  END$
  
  DELIMITER ;
  
  -- call pro_test3 stored procedure
  CALL pro_test3();

 

8.3 use of if statement

• Standard grammar

  --Standard grammar
  IF judgment condition 1 sql statement 1 executed by then;
  [ELSEIF judgment condition 2 sql statement executed by then;]
  ...
  [sql statement executed by ELSE n;]
  END IF;

• Case presentation:

  /*
  	Define an int variable to store the total grade of the class
  	Define a varchar variable to store the score description
  	According to the total score:
  		380 Score or above, excellent in learning
  		320 ~ 380     Good study
  		320 The following learning is general
  */
  DELIMITER $
  
  CREATE PROCEDURE pro_test4()
  BEGIN
  	-- Define total score variable
  	DECLARE total INT;
  	-- Define score description variables
  	DECLARE description VARCHAR(10);
  	-- Assign a value to the total score variable
  	SELECT SUM(score) INTO total FROM student;
  	-- Total judgment score
  	IF total >= 380 THEN 
  		SET description = 'Excellent learning';
  	ELSEIF total >= 320 AND total < 380 THEN 
  		SET description = 'Good study';
  	ELSE 
  		SET description = 'General learning';
  	END IF;
  	
  	-- Query total score and description information
  	SELECT total,description;
  END$
  
  DELIMITER ;
  
  -- call pro_test4 stored procedure
  CALL pro_test4();

   

8.4 transfer of parameters

• Syntax for parameter passing

  DELIMITER $

  --Standard grammar
  CREATE PROCEDURE stored procedure name ([IN|OUT|INOUT] parameter name data type)
  BEGIN
  Executed sql statements;
  END$
  /*
  IN: represents an input parameter, and the actual data needs to be passed by the caller. default
  OUT: represents an output parameter, which can be used as a return value
  INOUT: represents that it can be used as either an input parameter or an output parameter
  */
  DELIMITER ;

• input parameter
  • Standard grammar

    DELIMITER $

    --Standard grammar
    CREATE PROCEDURE stored procedure name (IN parameter name data type)
    BEGIN
    Executed sql statements;
    END$

    DELIMITER ;

    Case presentation:

    /*
    	Enter the total score variable to represent the student's total score
    	Define a varchar variable to store the score description
    	According to the total score:
    		380 Score or above, excellent in learning
    		320 ~ 380    Good study
    		320 The following learning is general
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test5(IN total INT)
    BEGIN
    	-- Define score description variables
    	DECLARE description VARCHAR(10);
    	-- Total judgment score
    	IF total >= 380 THEN 
    		SET description = 'Excellent learning';
    	ELSEIF total >= 320 AND total < 380 THEN 
    		SET description = 'Good study';
    	ELSE 
    		SET description = 'General learning';
    	END IF;
    	
    	-- Query total score and description information
    	SELECT total,description;
    END$
    
    DELIMITER ;
    
    -- call pro_test5 stored procedure
    CALL pro_test5(390);
    CALL pro_test5((SELECT SUM(score) FROM student));

• Output parameters
  • Standard grammar

    DELIMITER $

    --Standard grammar
    CREATE PROCEDURE stored procedure name (OUT parameter name data type)
    BEGIN
    Executed sql statements;
    END$

    DELIMITER ;

  • Case demonstration

    /*
    	Enter the total score variable to represent the student's total score
    	The output score description variable represents the description of students' total score
    	According to the total score:
    		380 Score or above, excellent in learning
    		320 ~ 380    Good study
    		320 The following learning is general
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test6(IN total INT,OUT description VARCHAR(10))
    BEGIN
    	-- Total judgment score
    	IF total >= 380 THEN 
    		SET description = 'Excellent learning';
    	ELSEIF total >= 320 AND total < 380 THEN 
    		SET description = 'Good study';
    	ELSE 
    		SET description = 'General learning';
    	END IF;
    END$
    
    DELIMITER ;
    
    -- call pro_test6 stored procedure
    CALL pro_test6(310,@description);
    
    -- Query total score description
    SELECT @description;

  • Tips:

    @Variable name: this kind of variable should be preceded by the "@" symbol, which is called the user session variable, which represents the whole session process. It is functional, which is similar to the global variable.

    @@Variable name: this kind of variable is called system variable by adding "@ @" sign before it

8.5 use of case statements

• Standard syntax 1

  --Standard grammar
  CASE expression
  WHEN value 1 THEN execute sql statement 1;
  [WHEN value 2 THEN execute sql statement 2;]
  ...
  [ELSE execute sql statement n;]
  END CASE;

• Standard syntax 2

  --Standard grammar
  CASE
  WHEN judgment condition 1 THEN executes sql statement 1;
  [WHEN judgment condition 2 THEN execute sql statement 2;]
  ...
  [ELSE execute sql statement n;]
  END CASE;

• Case demonstration

  /*
  	Enter the total score variable to represent the student's total score
  	Define a varchar variable to store the score description
  	According to the total score:
  		380 Score or above, excellent in learning
  		320 ~ 380    Good study
  		320 The following learning is general
  */
  DELIMITER $
  
  CREATE PROCEDURE pro_test7(IN total INT)
  BEGIN
  	-- Define variables
  	DECLARE description VARCHAR(10);
  	-- use case judge
  	CASE
  	WHEN total >= 380 THEN
  		SET description = 'Excellent learning';
  	WHEN total >= 320 AND total < 380 THEN
  		SET description = 'Good study';
  	ELSE 
  		SET description = 'General learning';
  	END CASE;
  	
  	-- Query score description information
  	SELECT description;
  END$
  
  DELIMITER ;
  
  -- call pro_test7 stored procedure
  CALL pro_test7(390);
  CALL pro_test7((SELECT SUM(score) FROM student));

8.6 while loop

• Standard grammar

  --Standard grammar
  Initialization statement;
  WHILE conditional judgment statement DO
  Loop body statement;
  Conditional control statement;
  END WHILE;

• Case demonstration

  /*
  	Calculate the even sum between 1 and 100
  */
  DELIMITER $
  
  CREATE PROCEDURE pro_test8()
  BEGIN
  	-- Define summation variables
  	DECLARE result INT DEFAULT 0;
  	-- Define initialization variables
  	DECLARE num INT DEFAULT 1;
  	-- while loop
  	WHILE num <= 100 DO
  		-- Even judgment
  		IF num%2=0 THEN
  			SET result = result + num; -- accumulation
  		END IF;
  		
  		-- Give Way num+1
  		SET num = num + 1;         
  	END WHILE;
  	
  	-- Query summation results
  	SELECT result;
  END$
  
  DELIMITER ;
  
  -- call pro_test8 stored procedure
  CALL pro_test8();

8.7 repeat cycle

• Standard grammar

  --Standard grammar
  Initialization statement;
  REPEAT
  Loop body statement;
  Conditional control statement;
  UNTIL conditional judgment statement
  END REPEAT;

  --Note: the repeat loop stops when the condition is met. The while loop is executed when the condition is met

• Case demonstration

  /*
  	Calculate the sum between 1 and 10
  */
  DELIMITER $
  
  CREATE PROCEDURE pro_test9()
  BEGIN
  	-- Define summation variables
  	DECLARE result INT DEFAULT 0;
  	-- Define initialization variables
  	DECLARE num INT DEFAULT 1;
  	-- repeat loop
  	REPEAT
  		-- accumulation
  		SET result = result + num;
  		-- Give Way num+1
  		SET num = num + 1;
  		
  		-- Stop cycle
  		UNTIL num>10
  	END REPEAT;
  	
  	-- Query summation results
  	SELECT result;
  END$
  
  DELIMITER ;
  
  -- call pro_test9 stored procedure
  CALL pro_test9();

8.8 loop loop

• Standard grammar

  --Standard grammar
  Initialization statement;
  [LOOP Name:] LOOP
  Conditional judgment statement
  [LEAVE loop name;]
  Loop body statement;
  Conditional control statement;
  END LOOP loop name;

  --Note: loop can implement a simple loop, but exiting the loop needs to be defined with other statements. We can use the leave statement to complete!
  --If you do not add a statement to exit the loop, it becomes an endless loop.

• Case demonstration

  /*
  	Calculate the sum between 1 and 10
  */
  DELIMITER $
  
  CREATE PROCEDURE pro_test10()
  BEGIN
  	-- Define summation variables
  	DECLARE result INT DEFAULT 0;
  	-- Define initialization variables
  	DECLARE num INT DEFAULT 1;
  	-- loop loop
  	l:LOOP
  		-- If the condition holds, stop the cycle
  		IF num > 10 THEN
  			LEAVE l;
  		END IF;
  	
  		-- accumulation
  		SET result = result + num;
  		-- Give Way num+1
  		SET num = num + 1;
  	END LOOP l;
  	
  	-- Query summation results
  	SELECT result;
  END$
  
  DELIMITER ;
  
  -- call pro_test10 stored procedure
  CALL pro_test10();