Mysql Base 03-Stored Procedure-Function [2021-10-21]

1.1 Variable

Variables are provided by the system.

Global variables:

Session variables: for one connection

Grammar used:

• View system variables

SHOW VARIABLES;

View global variables

SHOW GLOBAL VARIABLES;

View session variables [Do not write session defaults to session variables]

SHOW SESSION VARIABLES;

• View some of the variables that meet the criteria using like

SHOW GLOBAL VARIABLES LIKE '%char%';

• View the value of a specified system variable

# SELECT @@System Variable Name SELECT @@character_set_system SELECT @@global.character_set_system

• Assigning a value to a system variable

# SET GLOBAL system variable name=value; # SET @@global.System variable name=value;

If it is a global variable, the GLOBAL keyword must be added. If not, the default is session

Global variables are valid for all connections, but cannot be restarted across restarts. To restart still work, you need to modify the configuration file.

SET @m = 1; SET @n = 2; SET @sum = @n + @m; SELECT @sum;

2. Custom variables

User-defined, not provided by the system. Scope is valid for current session

User variables

• Declare and initialize

SET @User variable name=value; # Mode 2 SET @User variable name:=value; # Mode 3 SELECT @User variable name:=value;

• assignment

SET @User variable name=value; # Mode 2 SET @User variable name:=value; # Mode 3 SELECT @User variable name:=value; #Mode 4 SELECT field INTO @Variable Name FROM surface;

SET @count=100; SELECT COUNT(*) INTO @count FROM employees;

• See

SELECT @User variable name; SELECT @count;

User variables can be placed anywhere

Local variable: valid only in begin end where it is defined, first sentence applied in begin end

Declarations:

DECLARE Variable Name Type; DECLARE Variable Name Type DEFAULT value;

Assignment:

SET Local variable name=value; # Mode 2 SET Local variable name:=value; # Mode 3 SELECT @Local variable name:=value; #Mode 4 SELECT field INTO Variable Name FROM surface;

See:

SELECT Local variable name;

1.2 Stored Procedures and Functions

Stored procedures and functions are similar to methods in java

Stored procedure: A collection of precompiled sql statements.

With stored procedures, you can reduce the number of compilations (which are compiled once after the stored procedure), simplify operations, reduce the number of connections to the database, and provide efficiency.

1.2.1 Creation

grammar

CREATE PROCEDURE Stored procedure name(parameter list) BEGIN Stored Procedure Body END

The parameter list consists of three parts: parameter mode, parameter name, parameter type

For example:

IN stuname VARCHAR(20)

1. There are three parameter modes:

IN, import, this parameter can be used as input, need caller to pass in value

OUT, export, this parameter can be used as output, that is, as return value

INOUT import and export, this parameter can be used as input as well as output, the caller needs to pass in the value, will also be returned to the caller.

2. begin end is equivalent to a pair of braces. When the stored procedure body has only one sentence, it can be omitted from writing.

3. Each sql statement in the stored procedure body must have a semicolon at the end, and the end of the stored procedure itself can be reset using delimiter. grammar

delimiter end tag

delimiter $

1.2.2 Call, delete, view

CALL Stored procedure name(Argument List);

1. Create a stored procedure with no parameters

DELIMITER $ CREATE PROCEDURE myp1() BEGIN INSERT INTO admin(username,PASSWORD) VALUES('john1','0000'),('lily','0000'), ('rose','0000'),('jack','0000'), ('tom','0000'); END $

CALL myp1();

1. Create a stored procedure with IN

Create stored procedures to query male information based on female information

# Create a stored procedure for an IN type parameter CREATE PROCEDURE myp2(IN beantyName VARCHAR(20)) BEGIN SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beantyName; END; # call CALL myp2('Reba');

Create a stored procedure implementation, whether the user logged in successfully

CREATE PROCEDURE myp3(IN username VARCHAR(20), IN `password` VARCHAR(20)) BEGIN DECLARE result VARCHAR(20) DEFAULT ''; # Local variable declaration and initialization SELECT COUNT(*) INTO result #assignment FROM admin WHERE admin.username = username AND admin.`password` = `password`; SELECT result; # Query Variable Value END; # call CALL myp3('john1', '10000');

Add IF to see

CREATE PROCEDURE myp4(IN username VARCHAR(20), IN `password` VARCHAR(20)) BEGIN DECLARE result INT DEFAULT 0; # Local variable declaration and initialization SELECT COUNT(*) INTO result #assignment FROM admin WHERE admin.username = username AND admin.`password` = `password`; SELECT IF(result>0, 'Success', 'fail') AS Result; # Use END; CALL myp4('john1', '10000');

3. Create a stored procedure with OUT

# Create a stored procedure to query the corresponding male name based on female information CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20)) BEGIN SELECT bo.boyName INTO boyName FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName; END; # call CALL myp5('Reba', @boyName); SELECT @boyName;

Multiple OUT s

# Create stored procedures to query for male names and glamour values based on female information CREATE PROCEDURE myp6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT) BEGIN SELECT bo.boyName, bo.userCP INTO boyName, userCP FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName; END; CALL myp6('Reba', @boyName, @userCP); SELECT @boyName, @userCP;

4. Stored Procedures with INOUT

# By passing in a and b, both a and B eventually doubled CREATE PROCEDURE myp7(INOUT a INT, INOUT b INT) BEGIN SET a=a*2; SET b=b*2; END # call SET @a = 3; SET @b = 5; CALL myp7(@a, @b); SELECT @a,@b;

How do I delete stored procedures?

DROP PROCEDURE Stored procedure name; # Example SHOW CREATE PROCEDURE myp1;

Delete stored procedures can only be deleted one at a time.

View stored procedure information

SHOW CREATE PROCEDURE Stored procedure name; # Example SHOW CREATE PROCEDURE myp2;

Logical statements inside stored procedures cannot be modified; they need to be modified to delete and rewrite one.

# Create a stored procedure to pass in a date and format the output. CREATE PROCEDURE dateformatefunc(IN date DATETIME, OUT dateStr VARCHAR(20)) BEGIN SELECT DATE_FORMAT(date, '%y-%m-%d') INTO dateStr; END; CALL dateformatefunc(NOW(), @str); SELECT @str;

Differences between functions and stored procedures:

Functions can have either 0 or more returns.

Function: There is only one return value.

Stored Procedures Suitable for Bulk Insertion, Bulk Update

The function is suitable for processing data and returns a result.

Creation of 2.1 Function

CREATE FUNCTION Function name(parameter list) RETURNS Return type BEGIN Function Body END

The parameter list consists of two parts: parameter name, parameter type. There are no IN, OUT, INOUT

The function body must have a return statement, and if it does not, no errors will be reported.

If return is not placed at the end of the body, no error will be reported, but it is not recommended.

When the body of a function has only one sentence, begin end can be omitted

Use delimiter to set the end tag.

Function calls using SELECT

SELECT Function name(parameter list)

1. No reference

case

# Number of employees returning to the company CREATE FUNCTION myf1() RETURNS INT BEGIN DECLARE c INT DEFAULT 0; SELECT count(*) INTO c FROM employees; RETURN c; END; SELECT myf1();

2. Reference

case

# Return salary based on employee name CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS INT BEGIN DECLARE c INT DEFAULT 0; SELECT salary INTO c FROM employees WHERE employees.last_name = empName; RETURN c; END; SELECT myf2('Ernst');

case

# Return average wage based on department name CREATE FUNCTION myf3(depName VARCHAR(20)) RETURNS DOUBLE BEGIN DECLARE c DOUBLE DEFAULT 0; SELECT AVG(salary) INTO c FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name=depName; RETURN c; END; SELECT myf3('adm');

2.2 View function details, delete

# SHOW CREATE FUNCTION function name; SHOW CREATE FUNCTION myf3; Delete function # DROP FUNCTION function name; DROP FUNCTION myf1;

Use case of 2.3 function

# Create a function that returns the sum of two float s passed in CREATE FUNCTION testfunc1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT BEGIN DECLARE sum FLOAT DEFAULT 0; SET sum = num1 + num2; RETURN sum; END; SELECT testfunc1(1,4);

There's nothing to say about the sequence structure.

3.1 Branch Structure

1. if function

SELECT IF(Expression 1, Expression 2, Expression 3) Execution order: Returns the value of expression 2 if expression 1 is valid, otherwise returns the value of expression 3

This can be used anywhere, including stored procedures and functions.

2. case structure

Case 1: Similar to switch ing, it is generally used for equivalence judgment

CASE variable|Expression|field WHEN Value to Judge THEN Return value 1 or statement 1; WHEN Value to Judge THEN Return value 2 or statement 2; WHEN Value to Judge THEN Return value 3 or statement 3; else Value to return n Or statement n； END Case；

Scenario 2: Multiple IF s to achieve interval judgment

CASE WHEN Condition 1 to be judged THEN Return value 1 or statement 1; WHEN Condition 2 to be judged THEN Return value 2 or statement 2; WHEN Condition 3 to be judged THEN Return value 3 or statement 3; else Value to return n Or statement n； END Case；

Characteristic:

• It can be used as an expression, nested in other statements, and placed anywhere; It can also be a separate statement, but only in BEGIN END.

• When executed, if the value or condition in WHEN is true, the statement following the corresponding THEN is executed and the case is terminated

If none is satisfied, else is executed.

• Else can be omitted and returns null if else is omitted and all when conditions are not met

Case: Create a stored procedure to show ranks based on incoming results

CREATE PROCEDURE test_case(IN score INT, OUT class CHAR(1)) BEGIN CASE WHEN score BETWEEN 0 and 59 THEN SELECT 'D' INTO class; WHEN score BETWEEN 60 and 69 THEN SELECT 'C' INTO class; WHEN score BETWEEN 70 and 89 THEN SELECT 'B' INTO class; WHEN score BETWEEN 90 and 95 THEN SELECT 'A' INTO class; ELSE SELECT 'S' INTO class; END CASE; END; CALL test_case(85, @class); SELECT @class;

3. IF structure

If structure is different from if function, function is to implement multiple branches

Grammar:

IF Condition 1 then Statement 1; elseif Condition 2 then Statement 2; elseif Condition 3 then Statement 3; ... else Sentence n; end if；

else can also be omitted.

Note that the if structure can only be used in begin end.

Case: Create a function to show ranks based on incoming results

CREATE FUNCTION test_if(score INT) RETURNS VARCHAR(20) BEGIN IF score BETWEEN 0 and 59 THEN RETURN 'D' ; ELSEIF score BETWEEN 60 and 69 THEN RETURN 'C' ; ELSEIF score BETWEEN 70 and 89 THEN RETURN 'B' ; ELSEIF score BETWEEN 90 and 95 THEN RETURN 'A'; ELSE RETURN'S'; END IF; END; SELECT test_if(85);

3.2 Cycle Structure

Loop structure must be placed between BEGIN END s

Classify while, loop, repeat

Loop control: iterate is like continue, leave is like break

while syntax: judge before execute

[Label:] while Cyclic condition do Circulatory body; end while[Tags);

If you need to match the loop control, you need to write a label.

Loop syntax: unconditional dead loop

[Label:] loop Circulatory body; end loop[Tags);

Can be used to simulate a dead cycle

repeat syntax: execute before Judge

[Label:] repeat Circulatory body; until End condition end repeat[Tags);

Case:

DROP PROCEDURE IF EXISTS pro_while; CREATE PROCEDURE pro_while(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 0; a:WHILE i<insertCount DO INSERT INTO admin(username, password) VALUES(concat('rose', i), '666'); SET i = i + 1; END WHILE a; END; CALL pro_while(10);

Add Cycle Control Cases

DROP PROCEDURE IF EXISTS pro_while; CREATE PROCEDURE pro_while(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 0; a:WHILE i<insertCount DO INSERT INTO admin(username, password) VALUES(concat('haha', i), '000'); IF i > 5 THEN LEAVE a; END IF; SET i = i + 1; END WHILE a; END; CALL pro_while(10);

DROP PROCEDURE IF EXISTS pro_while; CREATE PROCEDURE pro_while(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; # mod function is redundant END IF; INSERT INTO admin(username, password) VALUES(concat('iter', i), '111'); END WHILE a; END; CALL pro_while(10);

Add new comment

0 comments