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:
- Improve code reusability
- Simplify operation
- 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:
-
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 -
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 ·
-
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)$