天天看點
傳回

Mysql 存儲過程初識

存儲過程

認識

在一些程式設計語言中, 如pascal, 有一個概念叫"過程" procedure, 和"函數" function, 如VB中的sub. Java, Python, PHP, 沒有過程, 隻有function.

過程(procedure) : 封裝了若幹條語句, 調用時, 這些封裝體執行.

函數(function): 是一個有傳回值的 "過程" (ps: Python函數即可是過程, 也是是函數)

存儲過程(sql_procedure): 将若幹條sql封裝起來, 取一個名字, 即為過程, 把此過程存儲在資料庫中, 即存儲過程.

存儲過程-建立文法 

-- 建立
create procedure procedureName()
begin
  SQL語句1;
  SQL語句2;.....
end

-- 調用
call procedureName();

第一存儲過程

helloWorld 

-- 第一個存儲過程: 列印hello world
delimiter //
drop procedure if exists p1;
create procedure p1()
begin
  select "hello world!";
  select 1 + 1;
end //
delimiter ;

-- CALL 調用
call p1;
-- 檢視: show procedure status;
show show procedure status;

效果

mysql> -- 第一個存儲過程: 列印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)

引入變量-declare 局部

存儲過程是可以程式設計的, 意味着可以用變量, 表達式, 控制結構來完成各種複雜的功能.

在存儲過程中, 用 declare 變量名 變量類型 [default 預設值].

-- 變量引入
drop procedure if exists p2;
delimiter //
create procedure p2()
begin
  declare age int default 18;
  declare height int default 180;
  -- concat 拼接輸出
  select concat("油哥的年齡是:", age, "身高是:", height);
end //
delimiter ;

call p2();

效果:

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

Query OK, 0 rows affected (0.00 sec)

+-------------------------------------------------+
| concat("油哥的年齡是:", age, "身高是:", height) |
+-------------------------------------------------+
| 油哥的年齡是:18身高是:180                       |
+-------------------------------------------------+
1 row in set (0.10 sec)

Query OK, 0 rows affected (0.04 sec)

引入運算

在儲存過程中, 變量可以引入sql語句中合法的運算, 如 + - * /, 值的注意的是, 運算的結果,如何指派給變量?

**set 變量名 := expression ** 在存儲過程中的變量是一個局部變量.

set @變量名 := expression 使用者(會話)變量, 在存儲過程外面也能用, 類似"全局變量".

**declare 變量名 變量類型 [default value] **用在過程中的局部變量, 聲明類型.

關于指派 = 與 := 的差別

:=

  • 标準的指派符号, 在任何場景都是指派.
=
  • 隻在 set 和 update 是和 := 一樣是指派, 其他都是等于的作用. 
drop procedure if exists p3;
delimiter //
create procedure p3()
begin
  declare age int default 18;

  select concat("現在的年齡是:", age);
  -- 變量運算,指派
  set age := age + 10;
  select concat("10年後, 年齡變成了:", age); 
end //
delimiter ;      
-- out
mysql> call p3();
+------------------------------+
| concat("現在的年齡是:", age) |
+------------------------------+
| 現在的年齡是:18              |
+------------------------------+
1 row in set (0.11 sec)

+------------------------------------+
| concat("10年後, 年齡變成了:", age) |
+------------------------------------+
| 10年後, 年齡變成了:28              |
+------------------------------------+
1 row in set (0.25 sec)

控制結構 if - then - else - end if; 

-- 文法
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 "已成年";
    else
        select "未成年";
    end if;
end //
delimiter ;

-- test
call p4();

-- out
+--------+
| 已成年 |
+--------+
| 已成年 |
+--------+
1 row in set (0.09 sec)

Query OK, 0 rows affected (0.00 sec)

存儲過程傳參

存儲過程的括号裡, 可以聲明參數, 文法是 [in / out / inout] 參數名 參數類型

in 表示往procedure裡面傳參數; out 表示其往外發射參數

-- 輸入矩形的 width, height 求矩形的面積
drop procedure if exists p5;
delimiter //
create procedure p5(width int, height int)
begin
    select concat("面積是:", width * height);
    if width > height then
        select "比較胖";
    elseif width < height then
        select "比較瘦";
    else
        select "方的一痞";
    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("面積是:", width * height) |
+-----------------------------------+
| 面積是:156                        |
+-----------------------------------+
1 row in set (0.11 sec)

+--------+
| 比較瘦 |
+--------+
| 比較瘦 |
+--------+
1 row in set (0.22 sec)

Query OK, 0 rows affected (0.01 sec)

流程控制 while , repeat, loop

任何程式設計語言, 隻要具備控制結構順序, 選擇, 循環就足夠了.

感覺就是, 程式設計其實思路都是一樣的, 隻是不同語言的應用場景, 文法特性有差别而已, 思路都是一樣的.

-- while 循環 文法
WHILE search_condition DO
  statement_list
END WHILE [end_label]      
-- 求 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 循環
  while num <= 100 do
    set total := total + num;
    set num := num + 1;
  end while;
  -- 最後輸出結果
  select concat("1+2+...100的值是: ", 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的值是: 5050 |
+------------------------+
1 row in set (0.09 sec)

改進: 求 1+2+....N 的和, 這裡引入參數 IN

-- 求 1+2+3+...N
drop procedure if exists p7;
delimiter //
-- 傳入參數 in類型
create procedure p7(in n int)
begin
  declare total int default 0;
  declare num int default 0;
  -- while 循環
  while num <= n do
    set total := total + num;
    set num := num + 1;
  end while;
  -- 最後輸出結果
  select concat("1+2+.. 的值是: ", 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+.. 的值是: 50005000 |
+-------------------------+
1 row in set (0.14 sec)

out 型參數

drop procedure if exists p8;
delimiter //
create procedure p8(in n int, out total int)
begin
  -- 聲明一個局部(臨時)變量num來存儲 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 ;

-- 差別: 沒有在 begin ..end 中聲明 total變量, 而是在 OUT類型的參數中.
-- out: 傳入一個變量去接收輸出
call p8(100, @cj); 

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

NULL 的特殊性, 導緻沒有正确輸出 , 解決: 給total 一個預設值即可

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)      
-- 解決null的特殊性
drop procedure if exists p8;
delimiter //
create procedure p8(in n int, out total int)
begin

    -- 先聲明一個局部(臨時)變量num來存儲 1..n
    declare num int default 0;
    -- 再給out變量一個預設值即可(順序是先declare哦)
    set total := 0;
    -- while 
    while num <= n do
        set total := total + num;
        set num := num + 1;
    end while;
end //
delimiter ;

-- 差別: 沒有在 begin ..end 中聲明 total變量, 而是在 OUT類型的參數中.

-- out: 傳入一個變量去接收輸出的total變量值
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)

小結參數 in 和 out 和 inout

  • in 類型, 是要輸入一個值進去, 傳遞給procedure的in類型變量(傳值)
  • out類型, 是要輸入一個變量進去, 接收procedure的out類型變量的值
  • inout類型, 傳入值進入和傳入變量接收值出來 
-- inout 類型
drop procedure if exists p9;
delimiter //
create procedure p9(inout age int)
begin
  set age := age + 20;
end //
delimiter ;

-- call 的時候, inout, 首先要定義一個"全局(會話變量)", 然後再傳入
-- 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 |
+------+
存儲過程 mysql變量聲明 控制流 mysql 指派
上一篇: ArcGIS 基本概念
下一篇: 小紮在元宇宙長腿的秘密：加裝硬體不靠譜，機器學習才是銀彈

繼續閱讀