Stored procedures and functions

concept

Stored procedures and functions are a collection of SQL statements that have been compiled and stored in the database. Calling stored procedures and functions can simplify a lot of work for application developers, reduce data transmission between the database and the application server, and improve the efficiency of data processing.

The difference between a stored procedure and a function is that a function must have a return value, whereas a stored procedure does not.

Function: is a procedure that returns a value;
Procedure: is a function that returns no value;

Use of stored procedures

Creating a stored procedure

Delimiter Custom end symbol for example, $$CREATE PROCEDUREStored procedure name ([in.out.inout] Parameter name Data type...)begin
	- SQL statement
endDelimiter, a custom end symbol;Copy the code

Example:

delimiter $$
create procedure pro_test1()
begin
	select 'Hello Mysql' ;
end $$
delimiter ;
Copy the code

DELIMITER: Declares a DELIMITER for SQL statements, telling the MySQL interpreter whether the command has ended and whether MySQL can execute it. By default, delimiter is a semicolon (;) . On the command line client, if a command line ends with a semicolon, mysql will execute the command when you press enter.

Calling a stored procedure

callStored procedure name ();Copy the code

Viewing stored Procedures

Query all stored procedures in db_name database
select name from mysql.proc where db='db_name';

-- Query the status of stored procedures
show procedure status;

-- Queries the definition of a stored procedure
show create procedure test.pro_test1 \G;
Copy the code

Deleting a stored procedure

DROP PROCEDURE  [IF EXISTSSp_name;Copy the code

Stored procedure syntax

Stored procedures are programmable, meaning they can use variables, expressions, and control structures to perform complex functions.

variable

Local variables: PassDECLAREYou can define a local variable whose scope can only be defined in BEGIN… In the END block.
User variable: The variable name is preceded by the “@” symbol, called user session variable, user – defined variable. Current session (link) valid, BEGIN… Also valid outside the END block, similar to Java member variables.
System variable: a variable preceded by the “@@” symbol is called a system variable. System variables The system has been created, but you cannot define system variables or modify them yourself. System variables are divided into global variables and session variables
- Global variables: Global variables are automatically initialized to default values by the server at MYSQL startup. These default values can be changed by changing the my.ini file. S
- Session variables: Session variables are initialized by MYSQL each time a new connection is created. MYSQL will make a copy of the values of all current global variables. As a session variable.

That is, if the values of session variables and global variables have not been manually changed since the session was established, then all of these variables have the same value. It’s just a different scope.
The difference between a global variable and a session variable is that changes to a global variable affect the entire server, whereas changes to a session variable affect only the current session (that is, the current database connection).
Some system variables can be changed dynamically using statements, but some system variables are read-only. For those that can be changed, set statements can be used to change them.

A local variable

Local variable creation syntax:
DECLAREVariable name type [DEFAULTThe default value]Copy the code

Example:

 delimiter $$

 create procedure pro_test() 
 begin 
 	declare num int default 5; Create a local variable of type int num, default value 5
 	select num + 10;  -- Prints the value of the variable num
 end $$

 delimiter ;
 
Call the stored procedure
CALL pro_test();
Copy the code

Execution Result:

The user variables

Grammar:

@user variable name-- No need to declare in advance, just declare when using
Copy the code

Such as:

DELIMITER $$
CREATE PROCEDURE pro_test4 () BEGIN
	
	SET @var_name = 'wk'; Create user variable @var_name and assign 'wk'
	
END $$
DELIMITER;

CALL pro_test4 (); Call the stored procedure pro_test4

SELECT @var_name; -- View user variables
Copy the code

Execution Result:

System variables

Global variable syntax:

@@global.var_name
Copy the code

Such as:

show.global variables;Look at global variables

select @@globa1.auto_increment_increment; View a global variable

set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000 ; -- Changes the value of a global variable

Copy the code

Session variable syntax:

@@session.var_name
Copy the code

Such as:

show session variables;-- View session variables
select @@session. auto_increment increment;-- View a session variable

set session sort buffer size = 50000 ;-- Modify the value of session variablesset @@session. sort_buffer_size = 50000 ;-- Modify the value of session variablesCopy the code

The assignment

`SET`Assignment:

To assign a local variable directly, use SET. You can assign constants or expressions.

Grammar:

 SETThe variable name1 = 值1;SETThe variable name2 = 值2; .Copy the code

Example:

 DELIMITER $

  CREATE  PROCEDURE pro_test3() Create a stored procedure named pro_test3
  BEGIN
  	DECLARE NAME VARCHAR(20); Create a local variable named NAME of type VARCHAR;
  	SET NAME = 'MYSQL';-- Assign the local variable NAME to MYSQL
  	SELECT NAME ;Print this local variable
  END$

  DELIMITER ;
Copy the code

Execution Result:

`select ... into` The assignment operation

In addition to the set assignment, use select… Into can also perform assignments.

Grammar:


DELIMITER $$

CREATE  PROCEDURE pro_test5()
BEGIN
	declare countnum int;
	select count(*) into countnum from city; -- Assign the value of count(*) to countnum
	select countnum; -- Output variable
END $$

DELIMITER ;
Copy the code

parameter

Syntax format:

delimiter $$
create procedure procedure_name([in/out/inout] Parameter name Parameter type)begin.end $$
delimiter;

-- IN: This parameter can be used as input, which requires the caller to pass IN a value, by default
-- OUT: This parameter is output, that is, this parameter can be returned
-- INOUT: can be used as either input or output parameters
Copy the code

IN

Requirements:

Determines the type of body that the current height belongs to according to the defined height variable. Example:


- a case
delimiter $$
create procedure pro_test5(in height int)
begin
    declare description varchar(50) default ' ';
  if height > = 180 then
    set description='身材高挑';
  elseif height > = 170 and height < 180 then
    set description='Standard size';
  else
    set description='Average body';
  end if;
  select concat('height', height , 'Corresponding body type is :',description); -- Output statement
end $$
delimiter ;
Call the stored procedure
call pro_test5(1.80);

- the second case
delimiter $$
create procedure pro_test6(in height_ int)
begin
    select * from emp where emp.height = height_;
end $$
delimiter ;

Call the stored procedure
call pro_test5(1.80);

Copy the code

OUT

Requirement: Get the user name and password based on the incoming ID.

Example:

delimiter $$
CREATE PROCEDURE pro_test8(IN in_id VARCHAR(20),OUT out_name VARCHAR(20),OUT out_pass VARCHAR(200))
BEGIN
	SELECT username,password INTO out_name,out_pass FROM sys_user WHERE id = in_id;Username = out_name; password = out_pass;
END $$
	delimiter;
	
CALL pro_test8(1.@o_name,@o_pass);Call the stored procedure and assign the query out_name value to the user variable @o_name; Out_pass is assigned to the user variable @o_pass;

SELECT @o_name;-- Query @o_name;
SELECT @o_pass;-- Query user variable @o_pass;
Copy the code

INOUT

Requirement: Pass in a number and print 10 times that number

Example:

delimiter $$
CREATE PROCEDURE pro_test9(INOUT num int)
BEGIN
	SET num = num * 10;
END $$
delimiter;
	
SET @into_num = 2;-- Set @into_num to 2;

CALL pro_test9(@into_num);Call the stored procedure, passing in the @into_num argument;

SELECT @into_num;-- Query user variable @into_num;
Copy the code

Branch statements

IF statement

Case 1:

delimiter $$
CREATE PROCEDURE pro_test10(in score INT)
BEGIN
	IF score < 60 
		THEN SELECT 'Fail'; 
	ELSEIF score > = 60 AND score < 80 
		THEN SELECT 'pass'; 
	ELSEIF score > = 80 AND score < 90 
		THEN SELECT 'good'; 
	ELSEIF score > = 90 AND score < = 100 
		THEN SELECT'good';
		ELSE 
		SELECT 'Grade error';
	END IF; -- The end needs to add;
END $$
delimiter;
	
CALL pro_test10(100);-- Call statement
Copy the code

Case 2:

delimiter $$
create PROCEDURE pro_teset11(in in_name varchar(20))
BEGIN
	DECLARE var_sal DECIMAL(7.2); Declare a salary variable
	DECLARE result VARCHAR(20); Declare a result variable
		SELECT sal INTO var_sal FROM emp WHERE ename = in_name; -- Execute SQL statements
	if val_sal < 1000 
		THEN SET result = 'Trial salary';
	ELSEIF var_sal < = 20000
		THEN SET result = 'Regular salary';
	ELSE
		SET result = 'Senatorial salary';
	END IF;
SELECT result;
END $$
delimiter ;

CALL pro_test11('Wu Ke');
Copy the code

A CASE statement

Grammar 1:

When case_value is consistent with when_value, the statement statement_list is executed
When case_value is inconsistent with when_value, else statement is executed
case case_value
    when when_value then statement_list
    [when when_value then statement_list]
        ...
    [else statement_list]
    end case
Copy the code

Syntax 2:

-- Search_condition is true, execute statement_list
If none of the above search_condition is true, statement_list is executed
case 
    when search_condition then statement_list
    [when search_condition then statement_list]
        ...
    [else statement_list]
end case
Copy the code

Case 1:

-- The first format:
/* Payment method: 1. Wechat Pay 2. Alipay 3
delimiter $$
CREATE PROCEDURE pro_test12(in pay_type int)
BEGIN
	CASE pay_type
		WHEN 1 THEN SELECT 'wechat Pay';
		WHEN 2 THEN SELECT 'Alipay pay';
		WHEN 3 THEN SELECT 'Card payment';
		ELSE SELECT 'Payment by other means';
	END CASE;
END $$
delimiter ;

CALL pro_test12(1);Call the stored procedure


-- Second format:

delimiter $$
CREATE PROCEDURE pro_test13(in score int)
BEGIN
	CASE 
		WHEN score < = 50 THEN SELECT 'Fail';
		WHEN score > 50 AND score < = 80  THEN SELECT 'good';
		WHEN score >80 AND score < = 100 THEN SELECT 'good';
		ELSE SELECT 'Incorrect result input';
	END CASE;
END $$;
delimiter ;

CALL pro_test13(88);
Copy the code

Looping statements

The while loop

Grammatical structure:

WHILE loop condition DO loop bodyEND
END WHILE;
Copy the code

Example:

Compute the increment from 1 to n
delimiter $$
create procedure pro_test14(n int)
begin
  declare total int default 0; Create the total variable, default to 0
  declare num int default 1; Create num variable, default value 1
  while num < = n do -- Loop condition num <= n
    set total = total + num;
    set num = num + 1;
  end while;
  select total;
end $$
delimiter ;

call pro_test14(10);

Insert data into a table
delimiter $$
create procedure proc16_while(in insertCount int)
begin
    declare i int default 1;
    label:while i < = insertCount do -- label: Just a label (optional) indicating where the loop starts
    insert into user (uid, username, password)values(i,concat(user,i),'123456');
    set i = i + 1;
    end while label;-- label indicates which loop to end
    end $$
delimiter ;

call proc16_while(10);


-- while + leave
-- leave: Directly breaks out of the while loop

delimiter $$
create procedure proc17_while_leave(in insertCount int)
begin
    declare i int default 1;
    label:while i < = insertCount do
    insert into user(uid,username,password)values(i,concat('user-',i),'123456');
    if i = 5 then
    leave label; -- When I = 5, the loop starting with the label is broken;
    end if;
    set i = i + 1;
    endwhile label; End a loop that begins with a label: label;select 'End of loop';
end $$
delimiter ;

call proc17_while_leave(10);

-- while + iterate
Iterate: Skip the rest of the code for this loop and go to the next loop
delimiter $$
create procedure proc17_while_iterate(in insertCount int)
begin
      declare i int default 0;
      label:while i < insertCount do
      set i = i + 1;
      if i = 5 then iterate label;-- Execute the following code when I = 5, and proceed directly to the next loop.
      end if;
      insert into user2(uid, username, password) values(i,concat('user-',i),'123456');
      end while label;
      select 'End of loop';
end $$
delimiter ;
 
call proc17_while_iterate(10);  -- 1,2,3,4,6,7,8,9 will skip 5
Copy the code

Repeat statement

Conditional loop control statements that exit the loop when the condition is met. While is executed when the condition is met, while repeat is exited when the condition is met.

Grammatical structure:

[tag:]REPEAT; UNTIL conditional expressionThe loop is broken UNTIL the condition is true
END REPEAT;
Copy the code

Example:

 
delimiter $$
create procedure proc18_repeat(in insertCount int)
begin
     declare i int default 1;
     label:repeat
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         until  i  > insertCount -- When I > insertCount, we jump out of the loop
     end repeat label;
     select 'End of loop';
end $$
delimiter ;
 
call proc18_repeat(100);
Copy the code

Loop statements

LOOP implements a simple LOOP. The exit condition of the LOOP needs to be defined by another statement, usually using the LEAVE statement. The syntax is as follows:

[tag :] LOOP body; If conditional expressionthenLeave [label];ENDIF;ENDLOOP;Copy the code

The LOOP statement can be used to implement a simple LOOP IF an exit statement is not added to the IF.

Case study:

delimiter $$
create procedure proc19_loop(in insertCount int)
begin
 declare i int default 1;
 label: loop
  insert into user(uid, username, password) values(i,concat('user-',i),'123456');
  set i = i + 1 
    if i > insertCount then leave label; -- If I > insertCount, the loop exits;
  end if ;  
  end loop label; -- End loop
end $$
delimiter ;
 
call proc19_loop(100);
Copy the code

Cursor/cursor

Cursors are data types used to store the result set of a query, which can be looping over with the cursor in stored procedures and functions. Cursor usage includes cursor declaration, OPEN, FETCH, and CLOSE, with the following syntax, respectively.

Declare cursor:

DECLARE cursor_name CURSOR FOR select_statement 
Copy the code

Open the cursor:


OPEN cursor_name ;
Copy the code

Get values by cursor:

FETCH cursor_name INTO var_name [, var_name] ...
Copy the code

Close the cursor:

CLOSE cursor_name ;
Copy the code

Example:

Initialization script:


create table emp(
  id int(11) not null auto_increment ,
  name varchar(50) not null comment 'name',
  age int(11) comment 'age',
  salary int(11) comment 'salaries'.primary key(`id`)
)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values(null.The Golden Lion.55.3800), (null.The White-browed Eagle King..60.4000), (null.'The Green-winged Bat King'.38.2800), (null.The Purple King..42.1800); Copy the codeCopy the code

Delimiter $$CREATE PROCEDURE PRO_test11 () BEGIN delimiter $$CREATE PROCEDURE PRO_test11 () BEGIN define variable declare e_id int(11); declare e_name varchar(50); declare e_age int(11); declare e_salary int(11); -- Define cursor declare EMP_result cursor for SELECT * from EMp; -- Open the cursor open emp_result; Fetch emp_result into e_id,e_name,e_age,e_salary; Select concat('id=',e_id, ', name=',e_name, ', age=', e_age, ', e_age, ', salary =', e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; Select concat (' id = 'e_id,', the name = 'e_name,', the age = 'e_age,', salary is: 'e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; Select concat (' id = 'e_id,', the name = 'e_name,', the age = 'e_age,', salary is: 'e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; Select concat (' id = 'e_id,', the name = 'e_name,', the age = 'e_age,', salary is: 'e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; Select concat (' id = 'e_id,', the name = 'e_name,', the age = 'e_age,', salary is: 'e_salary); close emp_result; end $$ delimiter ;Copy the code

Get cursor data through loop structure:

DELIMITER $$

create procedure pro_test12()
begin
  DECLARE id int(11);
  DECLARE name varchar(50);
  DECLARE age int(11);
  DECLARE salary int(11);
  DECLARE has_data int default 1;

  DECLARE emp_result CURSOR FOR select * from emp;
  DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;

  open emp_result;

  repeat
    fetch emp_result into id , name , age , salary;
    select concat('id',id, ', the name of ' ,name , ', the age for ' ,age , ', salary: ', salary);
    until has_data = 0
  end repeat;

  close emp_result;
end $$

DELIMITER ;
Copy the code

Exception handling

-- cursor + handle
/* DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement handler_action: { CONTINUE | EXIT | UNDO } condition_value: {mysql_error_code | condition_name | SQLWARNING | NOT FOUND special note: in syntax, variable declarations, cursor statement, handler declaration must be in accordance with the order of writing, or create a stored procedure is wrong. * /
use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;
-- Requirement: Enter a department name, query the number, name, and salary of the employees in the department, and add a cursor to the query result set
delimiter $$
create procedure proc21_cursor_handler(in in_dname varchar(50))
begin
  Define local variables
    declare var_empno int;
    declare var_ename varchar(50);
    declare var_sal decimal(7.2);
    -- Define tag values
    declare flag int default 1;
     
    -- Declare cursors
    declare my_cursor cursor for
        select empno,ename,sal
        from dept a, emp b
        where a.deptno = b.deptno and a.dname = in_dname;
         
    Define handles: Define how exceptions are handled
    /* 1: How to execute the program after the exception is handled continue: continue the remaining code exit: directly terminate the program undo: not supported 2: trigger condition Condition code: 1329 SQLWARNING NOT FOUND SQLEXCEPTION 3: What code is executed after the exception is triggered to set the value of flag -- 0 */
         
    declare continue handler for 1329  set flag = 0;
         
    -- Open the cursor
    open my_cursor;
    Fetch values via cursors
    label:loop
        fetch my_cursor into var_empno, var_ename,var_sal;
        -- Check flag. If flag is 1, the command is executed. Otherwise, the command is not executed
        if flag = 1 then
         select var_empno, var_ename,var_sal;
      else
         leave label;
      end if;
    end loop label;
     
    -- xxxxx
    -- Close the cursor
    close my_cursor;
end $$;
 
delimiter ;
 
call proc21_cursor_handler('Teaching and Research Department');
Copy the code

practice

Practice -
User_2021_12_01, user_2022_12_02,... Requirement Description: We need to use a table to record a lot of data, such as record so-and-so users search, purchase behavior (note that this is a hypothesis with a database to store), when the record is large, every day if put all data records to a table is too large, need table, our request is that one table every day, the statistics of the day, Just ask to produce these tables ahead of time -- create the next day's tables at the end of each month! * /
Create table user_2021_11_01 to user_2020_11_30; And execute the CREATE statement.
create database mydb18_proc_demo;
use mydb18_proc_demo;
drop procedure if exists proc22_demo;
 
delimiter $$
create procedure proc22_demo()
begin
    declare next_year int;  -- Next month's year
    declare next_month int; -- The month of the following month
    declare next_month_day int;-- the last day of the following month
         
    declare next_month_str varchar(2);  -- Month string for next month
    declare next_month_day_str varchar(2);-- The day string for the next month
     
    -- Handles daily table names
    declare table_name_str varchar(10);
     
    declare t_index int default 1;
    -- declare create_table_sql varchar(200);
     
    -- Gets the year of the next month
    set next_year = year(date_add(now(),INTERVAL 1 month)); - 2021
    -- Get what month is next month
    set next_month = month(date_add(now(),INTERVAL 1 month)); 11 -
    -- What's the last day of next month
    set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month))); 30 -
     
    if next_month < 10
        then set next_month_str = concat('0',next_month); -- "01
    else
        set next_month_str = concat(' ',next_month); 12 -
    end if;
     
     
    while t_index < = next_month_day do
         
        if (t_index < 10)
            then set next_month_day_str = concat('0',t_index);
        else
            set next_month_day_str = concat(' ',t_index);
        end if;
         
        -- 2021_11_01
        set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str);
        -- Splice create SQL statements
        set @create_table_sql = concat(
                    'create table user_',
                    table_name_str,
                    '(`uid` INT ,`uname` varchar(50) ,`information` varchar(50)) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
        -- Local variables cannot be used after FROM!
        prepare create_table_stmt FROM @create_table_sql;
        execute create_table_stmt;
        DEALLOCATE prepare create_table_stmt;
         
        set t_index = t_index + 1;
         
    end while;  
end $$
 
delimiter ;
 
call proc22_demo();
Copy the code

concept

Use of stored procedures

Creating a stored procedure

Calling a stored procedure

Viewing stored Procedures

Deleting a stored procedure

Stored procedure syntax

variable

A local variable

The user variables

System variables

The assignment

SETAssignment:

select ... into The assignment operation

parameter

IN

OUT

INOUT

Branch statements

IF statement

A CASE statement

Looping statements

The while loop

Repeat statement

Loop statements

Cursor/cursor

Exception handling

practice

Related Posts

Websocket-based single machine support millions of connections distributed chat (IM) system

AQS team entry and exit process

RocketMQ Online exception information printing analysis

`SET`Assignment:

`select ... into` The assignment operation