“This is the second day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021”

The stored procedure

Meaning: A collection of pre-compiled SQL statements, understood as batch statements

1. Improve code reuse

2. Simplify operations

3, reduce the number of compilations and reduce the number of connections with the database server, improve efficiency

Create a grammar

CREATE PROCEDURE Name of the stored PROCEDURE (parameter list) BEGIN Body of the stored PROCEDURE (a group of valid SQL statements) ENDCopy the code

Note:

1. The parameter list contains three parts

Parameter Mode Parameter name Parameter type

For example:

IN stuname VARCHAR(20)
Copy the code

Parameter mode:

IN: This parameter can be used as input, that is, it needs to be passed IN by the caller

OUT: This parameter can be output, that is, this parameter can be returned

INOUT: This parameter can be used as both input and output, that is, this parameter requires both passing in and returning values

2. If the stored procedure body contains only one sentence, BEGIN END can be omitted

A semicolon is required at the end of each SQL statement in the stored procedure body.

The end of the stored procedure can be reset using DELIMITER

Storage syntax:

DELIMITER End tag

Specific use:

DELIMITER $

Call syntax:

Call stored procedure name (argument list);

Create a stored procedure with in-mode parameters

Create a stored procedure with out mode parameters

Create a stored procedure with inout mode parameters

Drop procedure Syntax: Drop PROCEDURE name

DROP PROCEDURE p1;
Copy the code

View information about stored procedures

SHOW CREATE PROCEDURE myp2;
Copy the code

function

I. Meaning:

A collection of pre-compiled SQL statements, understood as batch statements

1. Improve code reuse

2. Simplify operations

3, reduce the number of compilations and reduce the number of connections with the database server, improve efficiency

Differences from stored procedures:

Stored procedure: can have 0 return, can also have multiple return, suitable for batch insert, batch follow new

Function: one and only one return, suitable for processing data after returning a result

Create a grammar

CREATE FUNCTION FUNCTION name (parameter list) RETURNS Return type BEGIN FUNCTION body ENDCopy the code

Note:

1. The parameter list contains two parts:

Function name parameter type

2. Function body: There must be a return statement, if there is no error

If the return statement is not placed at the end of the function body, no error is reported, but a return value is not recommended.

3. If there is only one sentence in the function body, you can omit begin end

4. Set the end tag with the DELIMiter statement

Call syntax

SELECT function name (list of functions)Copy the code

Third, look at functions

SHOW CREATE FUNCTION myf3;
Copy the code

Delete function

DROP FUNCTION myf3
Copy the code

Case study:

CREATE FUNCTION MY2(A DOUBLE,B DOUBLE) RETURN DOUBLE BEGIN DECLARE RESULT INT DEFAULT 0; SELECT A+B INTO RESULT; SET RESULT=A+B; RETURN RESULT; END $Copy the code

Flow control structure

Sequential structure: Programs are executed from top to bottom

Branch structure: a program chooses one of two or more paths to execute

Loop structure: a program executes a piece of code repeatedly on the basis of certain conditions

First, branch structure

If the function

Function: simple double branch

Grammar:

If (expression 1, expression 2, expression 3)

Order of execution:

The if function returns the value of expression 2 if expression 1 is true, and 3 otherwise

Application: Anywhere

If the structure

Function: Realize multiple branches

Grammar:

If condition 1 then statement 1; If condition 2 then statement 2; . Else statement n end if;Copy the code

Apply to the begin end statement block

Case structure

Case 1: Similar to the Switch statement in Java, generally used to achieve equivalence judgment

Grammar:

CASE variable expression | | field the WHEN to determine the value of the THEN returns the value of 1 or 1; WHEN The value to be judged THEN returns the value 2 or statement 1; ELSE The value n or statement 1 to return; END CASE;Copy the code

Case 2: Similar to multiple if statements in Java, commonly used for interval judgment

Grammar:

CASE WHEN condition 1 THEN return 1 or statement 1; WHEN condition 2 THEN returns the value 2 or statement 1; ELSE The value n or statement 1 to return; END CASE;Copy the code

Features:

Can be used as expressions nested within other statements, and can be placed anywhere within or outside of BEGIN END

Can be used as a separate statement, only in BEGIN END

2, if the value of WHEN is true, execute the statement after the corresponding THEN, and technical CASE

If none is met, the statement or value in ELSE is executed

ELSE can be omitted. If omitted, and all WHEN conditions are not met, NULL is returned

Case study:

CREATE PROCEDURE test_case(IN score INT)
BEGIN
        CASE
        WHEN score>=90 AND score<=100 THEN SELECT 'A';
        WHEN score>=80 THEN SELECT 'B';
        WHEN score>=60 THEN SELECT 'C';
        ELSE SELECT 'D';
        END CASE;
END $
​
CALL  test_case(95)$
Copy the code

Second, cycle structure

Classification:

While, loop, repeat

Cycle control:

Iterate is similar to continue, which means to continue again

Leave is similar to break and terminates the current loop

While the structure

Grammar:

【 tag 】 while loop condition do loop body; End whileCopy the code

The loop structure

Grammar:

【 tag 】 loop body; End loop [tag];Copy the code

Note: this loop does not end on its own and needs to be coordinated with loop control; Often used to simulate an infinite loop;

Repeat structure

Grammar:

【 tag 】 repeat; End repeat 【 tag 】;Copy the code

Examples:

Use the leave control structure

DELIMITER $ DROP PROCEDURE test$ CREATE PROCEDURE test(IN insertcount INT) BEGIN DECLARE i INT DEFAULT 0; A :WHILE I <=insertcount DO INSERT INTO admin(username,password) VALUES(CONCAT(' xiaohua ', I),001); IF(i>=20) THEN LEAVE a; END IF; set insertcount=insertcount+1; END WHILE A; END $Copy the code

Use the ITERATE control structure

DROP PROCEDURE test1$ CREATE PROCEDURE test(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(' I ', I),001); END WHILE a; END $ CALL test(20)$Copy the code