stored procedure

know

In some programming languages, such as pascal, there is a concept called "process" and "function", such as sub. Java, Python, PHP in VB, no process, only function.

Procedure: Several statements are encapsulated and executed when invoked.

Function: A process with a return value (ps: Python function is both a process and a function)

Storage procedure: encapsulate several sql and name it as a procedure. Store the procedure in the database, that is, stored procedure.

Stored Procedures - Create Syntax

-- Establish
create procedure procedureName()
begin
    SQL Statement 1;
    SQL Statement 2;.....
end

-- call
call procedureName();

First stored procedure

helloWorld

-- First stored procedure: Printing hello world
delimiter //
drop procedure if exists p1;
create procedure p1()
begin
    select "hello world!";
    select 1 + 1;
end //
delimiter ;

-- CALL call
call p1;
-- See: show procedure status;
show show procedure status;

Effect

mysql> -- First stored procedure: Printing hello world
delimiter //
drop procedure if exists p1;
create procedure p1()
begin
    select "hello world!";
    select 1 + 1;
end //
delimiter ;
Query OK, 0 rows affected (0.16 sec)

Query OK, 0 rows affected (0.16 sec)

mysql> call p1();
+--------------+
| hello world! |
+--------------+
| hello world! |
+--------------+
1 row in set (0.06 sec)

+-------+
| 1 + 1 |
+-------+
|     2 |
+-------+
1 row in set (0.21 sec)

Query OK, 0 rows affected (0.00 sec)

Introducing variable-declare locality

Stored procedures are programmable, which means that complex functions can be accomplished with variables, expressions and control structures.

In the stored procedure, use declare variable name variable type [default default default].

-- Variable introduction
drop procedure if exists p2;
delimiter //
create procedure p2()
begin
    declare age int default 18;
    declare height int default 180;
    -- concat Stitching output
    select concat("The age of oil brother is:", age, "Height is:", height);
end //
delimiter ;

call p2();

Effect:

call p2();
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------------------------------------------------+
| concat("The age of oil brother is:", age, "Height is:", height) |
+-------------------------------------------------+
| The age of oil brother is:18 Height is:180                       |
+-------------------------------------------------+
1 row in set (0.10 sec)

Query OK, 0 rows affected (0.04 sec)

Import operation

During storage, variables can be introduced into legitimate operations in sql statements, such as + - * /. Values should pay attention to how the results of operations are assigned to variables.

set variable name: = expression is a local variable in the stored procedure.

set @ variable name: = expression user (session) variable, also available outside of stored procedures, similar to "global variables".

The declare variable name variable type [default value] is used as a local variable in the process to declare the type.

On the Difference between Assignment = and: =.

:=

  • Standard assignment symbols are assignments in any scenario.

=

  • Only set and update are assignments as well as:= and the others are equal functions.
drop procedure if exists p3;
delimiter //
create procedure p3()
begin
    declare age int default 18;

    select concat("Now the age is:", age);
    -- Variable operation,assignment
    set age := age + 10;
    select concat("10 After year, Age becomes:", age); 
end //
delimiter ;
-- out
mysql> call p3();
+------------------------------+
| concat("Now the age is:", age) |
+------------------------------+
| Now the age is:18              |
+------------------------------+
1 row in set (0.11 sec)

+------------------------------------+
| concat("10 After year, Age becomes:", age) |
+------------------------------------+
| 10 After year, Age becomes:28              |
+------------------------------------+
1 row in set (0.25 sec)

Control structure if - then - else - end if;

-- grammar
if condition then
    statement_01
else
    statement_02
end if;
drop procedure if exists p4;
delimiter //
create procedure p4()
begin
    declare age int default 18;
    
    if age >= 18 then
        select "Grown up";
    else
        select "Under age";
    end if;
end //
delimiter ;

-- test
call p4();

-- out
+--------+
| Grown up |
+--------+
| Grown up |
+--------+
1 row in set (0.09 sec)

Query OK, 0 rows affected (0.00 sec)

Stored Procedure Reference

In brackets of stored procedures, parameters can be declared, and the syntax is [in / out / inout] parameter name parameter type

in denotes transmitting parameters to procedure; out denotes transmitting parameters outward.

-- Input rectangular width, height Finding the Area of Rectangles
drop procedure if exists p5;
delimiter //
create procedure p5(width int, height int)
begin
    select concat("Area is:", width * height);
    if width > height then
        select "More fat";
    elseif width < height then
        select "Thinner";
    else
        select "A ruffian of the party";
    end if;
    
end //
delimiter ;

call p5(12, 13);

-- out
call p5(12, 13);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

+-----------------------------------+
| concat("Area is:", width * height) |
+-----------------------------------+
| Area is:156                        |
+-----------------------------------+
1 row in set (0.11 sec)

+--------+
| Thinner |
+--------+
| Thinner |
+--------+
1 row in set (0.22 sec)

Query OK, 0 rows affected (0.01 sec)

Process control while, repeat, loop

Any programming language, as long as it has the order of control structure, selection, cycle is enough.

The feeling is that the thinking of programming is the same, but the application scenarios of different languages have different grammatical characteristics, and the thinking is the same.

-- while Cyclic Grammar
WHILE search_condition DO
    statement_list
END WHILE [end_label]
-- Seek 1+2+3+...100
drop procedure if exists p6;
delimiter //
create procedure p6()
begin
    declare total int default 0;
    declare num int default 0;
    -- while loop
    while num <= 100 do
        set total := total + num;
        set num := num + 1;
    end while;
    -- Final output
    select concat("1+2+...100 The value is: ", total) as 'sum';
end //
delimiter ;

call p6();

-- out
call p6();
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

+------------------------+
| sum                    |
+------------------------+
| 1+2+...100 The value is: 5050 |
+------------------------+
1 row in set (0.09 sec)

Improvement: Find the sum of 1+2+....N. Here the parameter IN is introduced.

-- Seek 1+2+3+...N
drop procedure if exists p7;
delimiter //
-- Afferent parameter in type
create procedure p7(in n int)
begin
    declare total int default 0;
    declare num int default 0;
    -- while loop
    while num <= n do
        set total := total + num;
        set num := num + 1;
    end while;
    -- Final output
    select concat("1+2+.. The value is: ", total) as 'sum';
end //
delimiter ;

call p7(10000);

-- out
call p7(10000);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------------------------+
| sum                     |
+-------------------------+
| 1+2+.. The value is: 50005000 |
+-------------------------+
1 row in set (0.14 sec)

out-type parameters

drop procedure if exists p8;
delimiter //
create procedure p8(in n int, out total int)
begin
    -- Declare a part(temporary)variable num To store 1..n
    declare num int default 0;
    -- while 
    while num <= n do
        set total := total + num;
        set num := num + 1;
    end while;
    -- select concat("the sum is:", total)
end //
delimiter ;

-- Difference: Not in begin ..end China statement total variable, But in OUT Among the parameters of type.
-- out: Input a variable to receive the output
call p8(100, @cj); 

mysql> select @cj;
+------+
| @cj  |
+------+
| NULL |
+------+
1 row in set (0.10 sec)

The particularity of NULL leads to incorrect output. Solution: Give total a default value.

mysql> select null = null;
+-------------+
| null = null |
+-------------+
| NULL        |
+-------------+
1 row in set (0.09 sec)

mysql> select 1 + null;
+----------+
| 1 + null |
+----------+
| NULL     |
+----------+
1 row in set (0.05 sec)
-- Solve null Particularity
drop procedure if exists p8;
delimiter //
create procedure p8(in n int, out total int)
begin

    -- Declare a part first(temporary)variable num To store 1..n
    declare num int default 0;
    -- Give again out A default value for a variable is sufficient(Order is the first. declare Oh)
    set total := 0;
    -- while 
    while num <= n do
        set total := total + num;
        set num := num + 1;
    end while;
end //
delimiter ;

-- Difference: Not in begin ..end China statement total variable, But in OUT Among the parameters of type.

-- out: Input a variable to receive the output total Variable value
call p8(100, @theSum);
select @theSum;

-- out

mysql> call p8(100, @theSum);
Query OK, 0 rows affected (0.00 sec)

mysql> select @theSum;
+---------+
| @theSum |
+---------+
|    5050 |
+---------+
1 row in set (0.11 sec)

Summary parameters in and out and inout

  • The in type is the in type variable passed to procedure by entering a value.
  • The out type is to enter a variable and receive the value of the out type variable of procedure
  • Input type, the input value enters and the input variable receives the value
-- inout type
drop procedure if exists p9;
delimiter //
create procedure p9(inout age int)
begin
    set age := age + 20;
end //
delimiter ;

-- call When, inout, First, define a"Overall situation(Session variables)", Then it comes in
-- out
mysql> set @age := 100;
Query OK, 0 rows affected (0.00 sec)

mysql> call p9(@age);
Query OK, 0 rows affected (0.00 sec)

mysql> select @age;
+------+
| @age |
+------+
|  120 |
+------+