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

1. Stored Procedures

1.1 Variable

1. System variables

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;

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;

2. Functions

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);

3. Process Control Structure

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);

Tags: Database MySQL

Posted on Thu, 21 Oct 2021 13:38:07 -0400 by andriy

Programmer Help

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

1. Stored Procedures

1.1 Variable

1.2 Stored Procedures and Functions

1.2.1 Creation

1.2.2 Call, delete, view

2. Functions

Creation of 2.1 Function

2.2 View function details, delete

Use case of 2.3 function

3. Process Control Structure

3.1 Branch Structure

3.2 Cycle Structure

Hot Tags