Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of stored procedures and functions of MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

I. variables

System variable

System variables are divided into global variables and session variables, which are provided by the system.

Global variable scope: each startup of the server will initialize all global variables and cannot be restarted across.

Session variable scope: valid for the current session only.

Global variables scope: each startup of the server will initialize all global variables and cannot be restarted across # 1. View all the global variables SHOW GLOBAL VARIABLES;# 2. 0. View part of the global variable SHOW GLOBAL VARIABLES LIKE'% char%';# 3. Check the value of a global variable SELECT @ @ global.autocommit;# to see if SELECT @ @ global.tx_isolation;# is automatically submitted to view quarantine level # 4. Assign SET @ @ global.autocommit=0; to a specified global variable scope: valid for the current session only. # 1. View all session variables SHOW SESSION VARIABLES;SHOW VARIABLES;# and omit session# 2. 0 by default. View part of the session variable SHOW SESSION VARIABLES LIKE'% char%';# 3. View the value of a session variable SELECT @ @ tx_isolation;SELECT @ @ session.tx_isolation;# 4. Assign SET @ @ session.autocommit=0 to a specified session variable

Custom variable

Custom variables are divided into user variables and local variables, which are user-defined.

Compare scope definition and use location syntax user variables anywhere in the current session must be marked with an @ symbol, without defining the type local variable BEGIN. END can only be found in BEGIN. In END, the @ symbol is generally not added to the first sentence, and the scope of the type [user variable] needs to be defined: it is valid for the current session and has the same scope as the session variable # declare and initialize # the following three ways can be SET @ count=1; # set @ count:=1;# select @ count:=1;# assignment SELECT COUNT (*) INTO @ count FROM employees;# to view the user variable SELECT @ count [local variable] scope: valid only in defined begin end, the first sentence applied in begin end # declares DECLARE variable name type; # declare variable name type default value; # assigned SET local variable name = value; # or set local variable name: = value; # select @ local variable name: = value # uses SELECT local variable name [case] # case: declare two variables and assign an initial value, sum, print # user variable SET @ mendacitSet @ sum=@m+@n;SELECT @ sum;# local variable [can only run in begin...end] DECLARE m INT DEFAULT 1 itDECLAREDECLARE n INT DEFAULT 2Tring declare SUM INT;SET SUM=m+n;SELECT SUM

II. Stored procedures

Definition: a collection of sql statements that are pre-compiled and stored in a database.

Advantages of stored procedures: ① improves code reuse ② reduces the number of compilations and connections ③ improves efficiency.

Create syntax:

CREATE PROCEDURE stored procedure name (parameter list)

BEGIN

Stored procedure body (a set of legal sql statements)

END Terminator

Invocation syntax:

CALL stored procedure name (argument list)

Delete syntax:

DROP PROCEDURE stored procedure name

View the syntax:

SHOW CREATE PROCEDURE stored procedure name

The parameters of a stored procedure provide three parameters:

IN: input is required, and the caller is required to pass in the value.

OUT: can be output, can be used as a return value.

INOUT: you can input and output, both input parameters and return values are required.

Note:

If the stored procedure has only one sentence, you can omit BEGIN END.

Each sql statement in the body of a stored procedure must end with a semicolon.

The end of the stored procedure can use the delimiter rewrite end flag and the DELIMITER end tag.

[empty parameter stored procedure] # case: insert 5 records into admin table # reset Terminator $DELIMITER create stored procedure CREATE PROCEDURE myp1 () BEGIN INSERT INTO admin (username,PASSWORD) VALUES ('join1','000'), (' join2','000'), ('join3','000'), (' join4','000'), ('join5','000') END $# call stored procedure CALL myp1 () $[stored procedure with in mode] # case: create a stored procedure to query the corresponding boyfriend information 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 according to the name of the goddess END$CALL myp2 ('Liuyan') $# case: create a stored procedure to check whether the user has logged in successfully CREATE PROCEDURE myp3 (IN username VARCHAR (10), IN PASSWORD VARCHAR (10)) BEGIN DECLARE result INT DEFAULT 0 * declare and initialize SELECT COUNT (*) INTO result# assignment FROM admin WHERE admin.username=username AND PASSWORD=PASSWORD; SELECT IF (result,' successful', 'failed') # print variable END$# calls CALL myp3 ('john','8888') $[stored procedure with out mode] # case: according to the name of the goddess, return the corresponding male name CREATE PROCEDURE myp4 (IN beautyName VARCHAR (20), OUT boyName VARCHAR (20)) BEGIN SELECT bo.boyName INTO boyName # assign FROM boys bo INNER JOIN beauty b ON b.boyfriend_id=bo.id WHERE b.name=beautyName END $CALL myp4 ('Reba', @ bName) $# is not defined, but directly populate with user variables # call SELECT @ bName$# case: according to the name of the goddess, return the corresponding male name and male charm value CREATE PROCEDURE myp5 (IN beautyName VARCHAR (20), OUT boyName VARCHAR (20), OUT userCP INT) BEGIN SELECT bo.boyName,bo.userCP INTO boyName,userCP # assign FROM boys bo INNER JOIN beauty b ON b.boyfriend_id=bo.id WHERE b.name=beautyName END $CALL myp5 ('Reba', @ bName,@usercp) $# is not defined, but directly populates with user variables # call SELECT @ bName,@userCP$ [stored procedure with inout mode] # case: pass in two values, an and b, and eventually double both an and b and return CREATE PROCEDURE myp6 (INOUT an INT,INOUT b INT) BEGIN SET astat2; SET breadbaked 2nd end $SET @ mcm 10$ SET @ 20$ CALL myp6 (@ mMJR n) $SELECT @ mJN $$

After learning about stored procedures, try to complete the following exercises

The answers to the exercises are as follows: ↓

[exercise answer] # exercise 1: create a stored procedure to pass in a user name and password, and insert DELIMITER $CREATE PROCEDURE test_1 (IN username VARCHAR (10), IN loginPwd VARCHAR (10)) BEGIN INSERT INTO admin (admin.username,PASSWORD) VALUES (username,loginPwd) into the admin table END $CALL test_1 ('admin','111') $# exercise 2: create a stored procedure or function to pass in the goddess number, and return the goddess name and phone number CREATE PROCEDURE test_2 (IN id INT,OUT NAME VARCHAR (20), OUT phone VARCHAR (20)) BEGIN SELECT b.nametemerge b.phone INTO NAME,phone FROM beauty b WHERE b.idmasidentEND $CALL test_2 (1jinnjinjingp) $SELECT @ njingrep # exercise 3: create a stored procedure or function implementation to pass in two goddesses' birthdays, and return the size CREATE PROCEDURE test_3 (IN birth2 DATETIME,IN birth3 DATETIME,OUT result INT) BEGIN SELECT DATEDIFF (birth2,birth3) INTO result END $CALL test_3 ('1998-1-1 result$# now (), @ result) $SELECT @ result$# exercise 4: create a stored procedure or function implementation, pass in a date, format it into xx xx month xx day and return CREATE PROCEDURE test_4 (IN mydate DATETIME,OUT strdate VARCHAR (50)) BEGIN SELECT DATE_FORMAT (mydate,'%y year% m% d') INTO strdate END $CALL test_4 (NOW (), @ str) $SELECT @ str $# exercise 5: create a stored procedure or function implementation to pass in the goddess name and return: the string CREATE PROCEDURE test_5 (IN beautyName VARCHAR (20), OUT str VARCHAR (50)) BEGIN SELECT CONCAT (beautyName,'and',IFNULL (boyName,'null')) INTO str FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id=bo.id WHERE b.name=beautyName in the male format of the goddess and END $CALL test_5 ('Reba', @ str) $SELECT @ str $# exercise 6: create a stored procedure or function to query the record of the beauty table CREATE PROCEDURE test_6 (IN startIndex INT,IN size INT) BEGIN SELECT * FROM beauty LIMIT startIndex,size;END $CALL test_6 (3mem5) based on the number of entries passed in and the starting index

Third, function

What is the difference between stored procedures and functions?

Stored procedures can have 0 or more returns; functions have one and only one return.

Stored procedures are suitable for batch inserts and batch updates; functions are suitable for processing data and return a result.

Create syntax:

CREATE FUNCTION function name (argument list) RETURNS return type

BEGIN

Function body (there must be a return statement)

END Terminator

Invocation syntax:

SELECT function name (argument list)

View the function:

SHOW CREATE FUNCTION my_f3

Delete function:

DROP FUNCTION my_f3

[return with no parameters] # return the number of employees of the company CREATE FUNCTION my_f1 () RETURNS INTBEGIN DECLARE n INT DEFAULT 0 position # define variable SELECT COUNT (*) INTO n # assign FROM employees; RETURN nterend $SELECT my_f1 () $[return with parameters] # return salary CREATE FUNCTION my_f2 (empName VARCHAR (20)) RETURNS DOUBLEBEGIN SET @ sal=0 based on the employee name # define the user variable SELECT salary INTO @ sal # assign FROM employees WHERE last_name=empName; RETURN @ sal;END $SELECT my_f2 ('Kochhar') $# 3. Based on the name of the department, return the average salary of that department CREATE FUNCTION my_f3 (deptName VARCHAR (20)) RETURNS DOUBLEBEGIN DECLARE sal DOUBLE; SELECT AVG (Salary) INTO sal FROM employees e JOIN departments d ON e.department_id=d.department_id WHERE d.departmentroomnamedeptName; RETURN sal;END $SELECT my_f3 ('IT') $

After learning the functions of mysql, try to complete the following exercises

Answer:

1 、

CREATE FUNCTION test_1 (num1 FLOAT,num2 FLOAT) RETURNS FLOAT

BEGIN

DECLARE SUM FLOAT DEFAULT 0

SET SUM=num1+num2

RETURN SUM

END $

SELECT test_1 (1) 2) $

2 、

CREATE FUNCTION test_2 (jobName VARCHAR (20)) RETURNS INT

BEGIN

DECLARE number INT DEFAULT 0; # define variables

SELECT COUNT (employee_id) INTO number # assignment

FROM employees e

JOIN jobs j ON e.job_id=j.job_id

WHERE j.job_title=jobName

RETURN number

END $

SELECT test_2 ('President') $

3 、

CREATE FUNCTION test_3 (empName VARCHAR (20)) RETURNS VARCHAR (20)

BEGIN

DECLARE managerName VARCHAR (20) DEFAULT'; # define variables

SELECT e1.last_name AS managerName INTO managerName # assignment

FROM employees e1

WHERE e1.employeeclassiid = (SELECT e2.manager_id FROM employees e2 WHERE e2.last_name=empName)

RETURN managerName

END $

SELECT test_3 ('Kochhar') $

This is the end of this article on "sample analysis of MySQL stored procedures and functions". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.