存儲過程
1. 創(chuàng)建存儲過程
CREATE PROCEDURE 存儲過程名稱([參數(shù)列表])
BEGIN
SQL語句
END;
2. 調(diào)用存儲過程
CALL 名稱([參數(shù)])
3. 查看存儲過程
SELECT * FROM IMFORMATION_SCHEMA_ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; #查詢指定數(shù)據(jù)庫的存儲過程及狀態(tài)信息
SHOW CREATE PROCEDURE 存儲過程名稱; #查詢某個存儲過程的定義
4. 刪除存儲過程
DROP PROCEDURE [IF EXISTS] 存儲過程名稱;
注意：在命令行中 執(zhí)行創(chuàng)建存儲過程的SQL語句時 需要通過關(guān)鍵子delimiter指定sql語句的結(jié)束符 否則會在BEGIN END的語句塊中的sql語句作為結(jié)尾
變量
1. 系統(tǒng)變量
- 系統(tǒng)變量由Mysql服務(wù)器提供 不是用戶定義 屬于服務(wù)器層面 分為GLOBAL（全局變量和SESSION（會話變量 會話變量只針對當(dāng)前客戶端有效
查看系統(tǒng)變量
SHOW [SESSION|GLOBAL] VARIABLES LIKE '';
SELECT @@[SESSION|GLOBAL] 系統(tǒng)變量名; #查看指定變量的值
設(shè)置系統(tǒng)變量
SET [SESSION|GLOBAL] 系統(tǒng)變量名 = 值;
SET @@[SESSION|GLOBAL]系統(tǒng)變量名 = 值;
如果沒有指定SESSION|GLOBAL 默認(rèn)是會話變量SESSION
mysql服務(wù)器重新啟動之后 所設(shè)置的全局參數(shù)會失效 可以在my.cnf或者my.ini中配置
2. 用戶變量
- 用戶定義變量是用戶根據(jù)需要自己定義的變量 用戶變量不用提前聲明 在用的時候直接使用 @變量名 使用就可以 作用域為當(dāng)前連接的客戶端
賦值
SET @var_name = expression;
SET @var_name := expression;
SELECT @var_name := expression;
SELECT 字段名 INTO @var_name from 表名;
使用
SELECT @var_name;
例如
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
3. 局部變量
- 在存儲過程中聲明
begin
declare var_name var_type;
end
常用語法
1. if語句
begin
if 條件 then
```
elseif 條件 then
```
else
```
end if;
end
2. 傳入?yún)?shù)
CREATE PROCEDURE 存儲過程名稱 ([IN/OUT/INOUT 參數(shù)名 參數(shù)類型])
BEGIN
```
END
INOUT : 即可作為輸入?yún)?shù) 也可以作為輸出參數(shù)
例如
create procedure p(in score int, out resule varchar(10))
begin
if score >= 85 then
set result := '優(yōu)秀';
elseif score>= 60 then
set result := '及格';
else
set result := '不及格';
end if;
end;
call p(60, @p_result);
select @p_result; #查詢返回結(jié)果
3. case
CASE case_value
WHEN when_value1 THEN statement_list1
WHEN when_value2 THEN statement_list2
ELSE default_statement_list
END CASE;
4. WHILE
BEGIN
while 條件 do
```
end while;
END;
5. REPEAT
BEGIN
repeat
```
until 條件
end repeat;
END;
例如
create procedure p(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;
6. 游標(biāo)
- 游標(biāo) ：用來存儲查詢結(jié)果集的數(shù)據(jù)類型 在存儲過程和函數(shù)中可以使用游標(biāo)對結(jié)果集進行循環(huán)的處理
- 聲明游標(biāo)
DECLARE 游標(biāo)名稱 CURSOR FOR 查詢語句;
- 打開游標(biāo)
OPEN 游標(biāo)名稱;
- 獲取游標(biāo)記錄
FETCH 游標(biāo)名稱 INTO 變量;
- 關(guān)閉游標(biāo)
CLOSE 游標(biāo)名稱;
游標(biāo)例子
7. 存儲函數(shù)
- 存儲函數(shù)是有返回值的存儲過程 存儲函數(shù)的參數(shù)只能是IN類型的
CREATE FUNCTION 存儲函數(shù)名稱([參數(shù)列表])
RETURNS type [characteristic ...]
BEGIN
sql語句
RETURN ...;
END;
characteristic說明：
- DETERMINSTIC：相同的輸入?yún)?shù)總是產(chǎn)生相同結(jié)果
- NO SQL：不包含SQL語句
- READ SQL DATA：包含讀取數(shù)據(jù)的語句 但不包含寫入數(shù)據(jù)的語句
