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 |
+------+

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/124346.html

標籤：MySQL

上一篇：MySQL 配置錯誤

下一篇：資料庫索引總結