[From deleting database to running away] MySQL database | Stored procedure | Stored function (use code to assist understanding)

Column [MySQL]
Favorite poem: I am even more happy with the thousands of miles of snow in Minshan Mountain. After the three armies are over, they are all happy.
Music Sharing【The Right Path】
Welcome and thank everyone for pointing out Xiaoji’s problem

Article directory

• Introduction to stored procedures
• Characteristics of stored procedures
• Stored procedure
• • ?create
  • ?transfer
  • ?Check
  • ?delete
• Global variables
• • ?View system variables
  • ?Set system variables
• User-defined variables
• • ?assignment
  • ?use
• Local variables
• • ?statement
  • ?assignment
• if judgment
• parameter
• case
• while
• repeat loop
• loop loop
• Cursor (cursor)
• handler
• Storage function

Introduction to stored procedures

A MySQL stored procedure is a precompiled block of code that accepts parameters, performs specific operations, and returns results. A stored procedure is a way to combine multiple SQL statements together to form a reusable unit of business logic, thereby simplifying application development and maintenance.

Characteristics of stored procedures

Can reduce network traffic: Stored procedures on the database server can reduce network traffic between client applications and the database. This is because the stored procedure only needs to send parameters and execute the statement, rather than sending the complete SQL statement each time.

Can improve database performance: Stored procedures can avoid repeatedly compiling and interpreting SQL statements, thereby improving database execution efficiency.

Reusable code can be achieved: Stored procedures can abstract common business logic and form a reusable code base, thereby simplifying application development and maintenance.

Can protect database security: Stored procedures can protect database security by using parameter verification and permission control.

Stored procedure

?Create

create procedure stored procedure name [(parameter list)]
begin

-- sql statement

end;

?Call

To execute the sql statement in the creation process

call name({parameter});

create procedure p1()
begin
    select count(*) from tb_abc;
end;

call p1();

?View

select * from information_schema.ROUTINES where ROUTINE_SCHEMA=database name’; –Specify the stored procedures and status information of the database

show create procedure p1; –Query the definition of a stored procedure

View details

select * from information_schema.ROUTINES where ROUTINE_SCHEMA='abc';

show create procedure p1;

?Delete

drop procedure [if exists] stored procedure name;

drop procedure if exists p1;

Global variables

?View system variables

show session variables;

If you want to query all system variables starting with auto

show session variables like 'auto%';

The above view is at the session level, which is the current session level.
If we want to view the global level
Just change session to global

show global variables like 'auto%';

Above we used like, which is fuzzy matching
What should we do if we have found the value of the system variable so far?
Methods as below

select @@autocommit;

?Set system variables

0 means turning off the automatic submission switch, 1 means turning on the automatic submission switch

set session autocommit =0;

We executed it again and found that the session-level auto-commit switch had changed to 0.

User-defined variables

?Assignment

set @myname = 'itcast'; -- None:
set @myage :=10; -- Yes:
set @mygender :='male',@myhobby :='java';

?Use

select @myname,@myage,@mygender,@myhobby;

Local variables

?Statement

declare

?Assignment

create procedure p2()
begin
    declare tb_count int default 0; -- Use declare for local variables
    select count(*) into tb_count from tb_abc;
    select tb_count;
end;

call p2();

if judgment

Determine the score level corresponding to the current score based on the defined score variable.
score>=85 excellent
score>=60 & amp; & amp; score M< 85 passing
score<60 failed

create procedure p4()
begin
    declare score int default 58;
    declare result varchar(10);
    if score>=85 then
        set result:='Excellent';
    elseif score>=60 then
        set result:='passed';
    else
        set result:='Failed';
    end if;

    select result; -- display results
end;

call p4;

Parameters

According to the incoming (in) parameter score, determine the score level corresponding to the current score, and return (out)
score>=85 excellent
score>=60 & amp; & amp; score M< 85 passing
score<60 failed

create procedure p5(in score int,out result varchar(10))
begin
# declare score int default 58;
# declare result varchar(10);
    if score>=85 then
        set result:='Excellent';
    elseif score>=60 then
        set result:='passed';
    else
        set result:='Failed';
    end if;

#select result;
end;

call p5(68,@result);
select @result;

Convert the incoming 200-point score to the 100-point scale, and then return the score —->inout

create procedure p6(inout score double)
begin
    set score:=score*0.5;
end;

set @score=198;
call p6(@score);
select @score;

The select @score; in this code means that the query is the value of @score after the execution of call p6(@score); in the stored procedure

case

Determine the quarter based on the month passed in
January-March first quarter
April-June second quarter
July-September third quarter
October-December fourth quarter

create procedure p7(in month int)
begin
    declare result varchar(10);

    case
        when month>=1 and month <=3 then
            set result :='First Quarter';
        when month>=4 and month <=6 then
            set result :='Second Quarter';
        when month>=7 and month <=9 then
            set result :='Third Quarter';
         when month>=10 and month <=12 then
            set result :='Fourth Quarter';
        else
            set result:='Illegal parameter';
    end case;

    select concat('month',month,'quarter',result); -- Use the concat function to concatenate strings

end;

call p7(4);

while

Calculate the value accumulated from 1 to n, n is the parameter value passed in

create procedure p8(in n int)
begin
    declare total int default 0;
    while n>0 do
        set total:=total + n;
        set n:=n-1;
    end while ;
    select total;
end;

call p8(10);

repeat loop

Calculate the value accumulated from 1 to n, n is the parameter value passed in

create procedure p9(in n int)
begin
    declare total int default 0;
    repeat
        set total:=total + n;
        set n:=n-1;
    until n<=0
    end repeat;

    select total;
end;
call p9(10);

while satisfies the condition perform loop
repeat satisfies the condition Exit loop

loop

Calculate the value accumulated from 1 to n, n is the parameter value passed in

create procedure p10(in n int)
begin
    declare total int default 0;

    sum:loop
        if n<=0 then
            leave sum;
        end if;
        set total:=total + n;
        set n:=n-1;
    end loop sum;

    select total;
end;

call p10(100);

Cursor (cursor)

Query employees with ID less than uid and store their information in a new table

After we ran it, we found that the operation failed and an error was reported.
To solve this problem, we have to use the following method

handler

Add this piece of code to the code

declare exit handler for not found close u_course;

Storage function

Calculate the value accumulated from 1 to n, n is the parameter value passed in

create function fun1(n int)
returns int deterministic -- specifies the characteristic deterministic
begin
    declare total int default 0;

    while n>0 do
        set total:=total + n;
        set n:=n-1;
        end while;

    return total;
end;

select fun1(100);