5 others

5.3 stored procedures and functions

• Similar to C + + functions

5.3.1 stored procedure

Meaning: a set of SQL statements with pre compiled numbers (batch statements)

Benefits:

1. Improve code reusability
2. Simplify operation
3. The compilation times are reduced, the connection times with the database server are reduced, and the efficiency is improved

#1, Create syntax
CREATE PROCEDURE Stored procedure name(parameter list)
BEGIN
	Stored procedure body(A combination method SQL sentence)
END

/*be careful:
1,The parameter list consists of three parts
 Parameter mode parameter name parameter type */
# give an example:
IN stuname VARCHAR(20)

# Parameter mode:
IN: This parameter can be used as input, and the value needs to be passed in by the caller
OUT: This parameter can be used as output and return value
INOUT: It can be used as input or output. This parameter requires both incoming and return values

2,If the stored procedure body has only one sentence, BEGIN END Can be omitted;
Each in the stored procedure body SQL A semicolon must be added at the end of the statement;
The end of the stored procedure can be used DELIMITER Reset 
Syntax:
DELIMITER End tag
# For example:
DELIMITER $



#2, Call syntax
CALL Stored procedure name(Argument list);

Case:

#1. Empty parameter list
#Case: five records inserted into the admin table
SELECT * FROM admin;
DELIMITER $

CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username, `password`)
	VALUES('john1', '0000'),
	('lily', '0000'),('rose', '0000'),('jack', '0000'),('tom', '0000');
END $
#call
CALL myp1()$


#2. Create a stored procedure with in mode parameters
#Case 1: create a stored procedure to query the corresponding male god information according to the goddess name
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b
	ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#call
CALL myp2('Xiao Zhao')$

#Case 2: create a stored procedure implementation, and check whether the user logs in successfully
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0; #Declare and initialize
	
	SELECT COUNT(*) INTO result #assignment
	FROM admin
	WHERE admin.username = username
	AND admin.`password` = PASSWORD;

	SELECT IF(result>0, 'success', 'fail') Login status; #use
END $
#call
CALL myp3('Fei Zhang', '8888')$


#3. Create a stored procedure with out mode
#Case 1: return the corresponding male god name according to the goddess name
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#call
CALL myp4('Xiao Zhao',@bName)$
SELECT @bName$


#Case 2: return the corresponding male god name and male god charm value according to the goddess name
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
	SELECT bo.boyName, bo.userCP INTO boyName, userCP
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#call
CALL myp5('Xiao Zhao', @bName, @userCP)$


#4. Create a stored procedure with inout mode parameters
#Case 1: pass in two values a and b, and finally a and b double and return
CREATE PROCEDURE myp6(INOUT a INT, INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $
#call
SET @m=10, @n=20$  #Define two variables for input value + store return value
CALL myp6(@m, @n)$
SELECT @m, @n$

Delete and view stored procedures

#2, Delete stored procedure (cannot delete more than one)
Syntax: DROP PROCEDURE Stored procedure name
DROP PROCEDURE myp1$

#3, Viewing stored procedure information
DESC myp3$ #no way
SHOW CREATE PROCEDURE myp3$

5.3.2 function

Meaning: a set of SQL statements with pre compiled numbers (batch statements)

Benefit: same as stored procedure

difference:

• Stored procedure: there can be 0 returns or multiple returns (suitable for batch insertion and update)
• Function: there is only one return (suitable for returning a result after processing data)

#1, Create syntax
CREATE FUNCTION Function name(parameter list) RETURNS Return type
BEGIN
	Function body
END $


# be careful:
1.The parameter list consists of two parts: parameter name and parameter type

2.Function body: there must be return Statement. If there is no statement, an error will be reported
 if return The statement is not placed at the end of the function body, and no error is reported, but it is not recommended

3.When the function body has only one sentence, begin end Can be omitted
4.use delimiter Statement set end tag


#2, Call syntax
SELECT Function name(parameter list)

Case:

#1. Return without parameters
#Case: return the number of employees in the company
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0; #Define variables
	SELECT COUNT(*) INTO c #assignment
	FROM employees;
	RETURN c;
END $

#call
SELECT myf1()$


#2. Return if there are parameters
#Case 1: return salary according to employee name
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0; #Define user variables
	SELECT salary INTO @sal #assignment
	FROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END $

#call
SELECT myf2('Kochhar')$


#Case 2: return the average salary of the department according to the Department name
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE;
	SELECT AVG(salary) INTO sal
	FROM employees e
	INNER JOIN departments d
	ON d.department_id = e.department_id
	WHERE d.department_name = deptName;
	
	RETURN sal;
END $

#call
SELECT myf3('IT')$


#3, View function
SHOW CREATE FUNCTION myf3;

#4, Delete function
DROP FUNCTION myf3;


#Case: create a function to pass in two float s and return the sum of the two
CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM = num1 + num2;
	RETURN SUM;
END $

SELECT test_fun1(1.3, 2.5)$

5.4 process control structure

• Sequential structure: the program is executed from top to bottom
• Branch structure: a program selects one of two or more paths to execute
• Loop structure: the program repeatedly executes a piece of code on the basis of meeting certain conditions

5.4.1 branch structure

1.if function

• Function: realize simple double branch
• Syntax: if (expression 1, expression 2, expression 3); If expression 1 is true, return expression 2; otherwise, return the value of expression 3
• Application: anywhere

2.case structure

Case 1: Switch case similar to C + + is generally used to realize equivalence judgment

• Syntax:

	CASE variable|expression|field
	WHEN Value to judge THEN Returned value 1 or statement 1;
	WHEN Value to judge THEN Returned value 2 or statement 2;
	...
	ELSE Value to return n Or statement n;
	END CASE;

Case 2: similar to if, if else and if, it is generally used to realize interval judgment

• Syntax:

	CASE 
	WHEN Conditions to judge 1 THEN Returned value 1 or statement 1;
	WHEN Conditions to judge 2 THEN Returned value 2 or statement 2;
	...
	ELSE Value to return n Or statement n;
	END CASE;

characteristic:

1. It can be used as an expression nested in other statements:
   It can be placed anywhere, in begin end or outside
   It can be used as an independent statement and can only be placed in begin end

2. If the value in when satisfies or the condition holds, execute the statement after the corresponding then and end the case; If none is satisfied, execute the statement in ·

3. Else can be omitted; If else is omitted and all when are not satisfied, null is returned

#Case: create a stored procedure. According to the passed scores, the display levels are 90-100(A),80-90(B),60-80(C), and the rest are D
CREATE PROCEDURE test_case1(IN score INT)
BEGIN
	CASE 
	WHEN score>=90 THEN SELECT 'A';
	WHEN score>=80 THEN SELECT 'B';
	WHEN score>=60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE; 
END $

CALL test_case1(95)$	

3.if structure

• Function: realize multiple branches

• Syntax:

if Condition 1 then Statement 1;
elseif Condition 2 then Statement 2;
...
[else sentence n;]
end if;

• Application: it can only be used in begin end

#Case 1: create a stored procedure. According to the results passed in, the return levels are 90-100(A),80-90(B),60-80(c), and the rest are D
CREATE FUNCTION test_if1(score INT) RETURNS CHAR
BEGIN
	IF score>=90 THEN RETURN 'A';
	ELSEIF score>=80 THEN RETURN 'B';
	ELSEIF score>=60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END $

SELECT test_if1(86);

5.4.2 circulation structure

• Classification: while, loop, repeat
• Cycle control:
  • iterate is similar to continue to end this cycle and continue the next time
  • leave is similar to break to end the current loop

1.**while * * syntax:

[label:]while Cycle condition do
	Circulatory body;
end while [Label];

similar while，Judgment before execution

2.**loop * * syntax:

[label:]loop
	Circulatory body;
end loop [Label];

It can be used to simulate a simple dead cycle

3.**repeat * * syntax:

[label:]repeat
	Circulatory body;
until Conditions for ending the cycle
end repeat[Label];

similar do..while，Execute once before judging

Case:

#1. No loop control statement is added
#Case 1: batch insert, insert multiple records in the admin table according to the number of times
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username, `password`)
		VALUES(CONCAT('Rose',i), '666');
		SET i = i+1;
	END WHILE;
END $

CALL pro_while1(100)$


#2. Add leave statement
#Case: batch insert. Insert multiple records in admin table according to the number of times. If the number of times > 20, stop
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i <= insertCount DO
		INSERT INTO admin(username,`password`)
		VALUES(CONCAT('xiaohua',i), '0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $

CALL pro_while1(100)$


#3. Add iterate statement
#Case: batch insert. Insert multiple records in admin table according to the number of times. If the number of times > 20, stop
CREATE PROCEDURE pro_While1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		
		INSERT INTO admin(username,`password`)
		VALUES(CONCAT('xiaohua',i),'0000');
	END WHILE a;
END $

CALL pro_while1(100)$