儲存過程
瞭解幾個容易混淆的概念
- 儲存過程
- 檢視
- 事務
- 函式
檢視(view):
可以理解成臨時表, 如果你每次都需要連表查詢, 而且sql老長老長了, 那就試試檢視吧, 你會感謝我的.
事務(transaction):
一組sql語句, 要麼全部執行成功, 要麼都不執行
函式(function):
執行特定功能的程式碼段, 和儲存過程(
procedure)很像 區別在於, 函式有且只有個返回值, 儲存過程對返回值的個數沒有要求, 沒有返回值也是可以的
瞭解儲存過程
儲存過程的概念
- 儲存過程和函式(執行特定功能的程式碼段)很像, 語法略有不同,
- 儲存過程主要用來
增刪改,檢視主要用來查
儲存過程的優缺點
優點:
- 提高效率
- 程式碼複用
- 減少開發人員的工作量
- 對儲存過程設定許可權, 安全性較高
缺點:
- 用起來方便, 改起來費勁...
儲存過程引數介紹
- 函式有引數, 儲存過程也有引數
- 定義引數時, 需要有
引數名,引數型別,引數方向 - 儲存過程的引數方向有三個(
in,out,inout)in: 只把值傳給儲存過程out: 只從儲存過程中返回值inout: 引數在儲存過程中進行運算, 然後返回
如果沒有寫方向, 預設是
in
設計儲存過程
建立儲存過程
使用儲存過程, 為
教師表新增資料, 只需要姓名和職稱
建立教師表
drop table if exists teacher;
CREATE TABLE `teacher` (
`id` int(11) NOT NULL auto_increment primary key,
`salary` int(11) NOT NULL,
`title` char(20) NOT NULL,
`name` char(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `teacher`
(`id`, `salary`, `title`, `name`)
VALUES
(1, 1500, '初級講師', '教師1');
INSERT INTO `teacher`
(`id`, `salary`, `title`, `name`)
VALUES
(2, 5500, '高階講師', '教師2');
INSERT INTO `teacher`
(`id`, `salary`, `title`, `name`)
VALUES
(3, 6500, '金牌講師', '教師3');
INSERT INTO `teacher`
(`id`, `salary`, `title`, `name`)
VALUES
(4, 7500, '教授', '教師4');
INSERT INTO `teacher`
(`id`, `salary`, `title`, `name`)
VALUES
(5, 8500, '金牌教授', '教師5');
複製程式碼建立儲存過程
drop procedure if exists add_teacher;
create procedure add_teacher(
in teacher_name varchar(20),
in teacher_title varchar(20)
) begin
INSERT INTO `teacher`
(`salary`, `title`, `name`)
VALUES
(3500, teacher_title, teacher_name);
end;
複製程式碼呼叫儲存過程
call add_teacher('張三','特高階教師');
call add_teacher('李四','特高階教師他爹');
複製程式碼以上是建立並用儲存過程的例項.
其中我們使用了in型別的引數, 接下來試試out
再看個例子, 新增資料後, 返回該表一共有多少條記錄, 也就是說 新增教師後, 可以檢視已經有多少老師了
建立儲存過程
drop procedure if exists add_teacher;
create procedure add_teacher(
in teacher_name varchar(20),
in teacher_title varchar(20),
out teacher_count int)
begin
INSERT INTO `teacher`
(`salary`, `title`, `name`)
VALUES
(3500, teacher_title, teacher_name);
select count(*) into teacher_count from teacher;
end;
複製程式碼呼叫儲存過程
call add_teacher('張三','特高階教師', @count);
select @count;
call add_teacher('李四','特高階教師他爹', @count);
select @count;
複製程式碼著重講一下 inout
表示該引數, 既用於輸入, 有用於輸出; 比如發年終獎(14薪),最後一個月, 發3個月的工資 我們的需求, 輸入一個數字(每年的薪水, 14薪, 還是15薪), 返回最後一個月需要發多少錢
drop procedure if exists count_salary;
create procedure count_salary(
in month int,
inout salary int
) begin
select salary*(month - 11) into salary;
end;
set @salary = 2000;
call count_salary(14,@salary);
select @salary;
複製程式碼為了方便理解, 我的變數使用中文...
drop procedure if exists 計算工資;
create procedure 計算工資(
in 月份 int,
inout 月工資 int
) begin
select 月工資*(月份 - 11) into 月工資;
end;
set @月工資 = 2000;
call 計算工資(15,@月工資);
select @月工資;
複製程式碼in,out 和 inout的區別何在呢?
假設我們想計算一個數字的平方, 我們分別用
inout和in,out來試一下
drop procedure if exists count_square;
create procedure count_square(inout a int) BEGIN
select a*a into a;
end;
set @a = 3;
call count_square(@a);
select @a;
複製程式碼同樣提供中文版
drop procedure if exists 計算平方;
create procedure 計算平方(inout 數字 int) BEGIN
select 數字*數字 into 數字;
end;
set @數字 = 3;
call 計算平方(@數字);
select @數字;
複製程式碼drop procedure if exists count_square2;
create procedure count_square2(in a int, out b int) begin
select a*a into b;
end;
call count_square2(3,@b);
select @b;
複製程式碼drop procedure if exists 再計算平方;
create procedure 再計算平方(in 第一個數字 int, out 第二個數字 int) begin
select 第一個數字*第一個數字 into 第二個數字;
end;
call 再計算平方(3,@第二個數字);
select @第二個數字;
複製程式碼檢視儲存過程資訊
show procedure status like 'salary';
複製程式碼show create procedure add_teacher;
複製程式碼刪除儲存過程
drop procedure salary;
複製程式碼變數(宣告, 賦值, 使用, 作用域)
- 系統變數
- 全域性變數(
global) 只要不重啟, 都適用 - 會話變數(
session) 只能是當前會話
- 全域性變數(
- 自定義變數
- 全域性/使用者變數(當前會話)
- 區域性/區域性變數(當前函式)
檢視變數(全部)
-- 第一條和第三條效果一樣
show variables;
show global variables;
show session variables;
複製程式碼檢視變數(搜尋)
-- 第一條和第三條效果一樣
show variables like 'auto%';
show global variables like 'auto%';
show session variables like 'auto%';
複製程式碼檢視變數(一個)
select @@global|[session].系統變數;
-- 第一句和第三句一樣
select @@autocommit;
select @@global.autocommit;
select @@session.autocommit;
複製程式碼全域性變數, 重啟失效, 除非改配置檔案
修改變數/變數賦值
語法
set global|[session] 系統變數名 = 值;
set @@global|[session].系統變數名 = 值;
set autocommit = 0;
-- 相當於
-- set session autocommit = 0
-- 或者
-- set @@session.autocommit = 0;
-- 全域性
-- set @@global.autocommit = 0;
-- set global autocommit = 0;
select @@autocommit;
select @@session.autocommit;
select @@global.autocommit;
複製程式碼使用者自定義變數
- 全域性變數(當前會話)
set @variableName = value;
set @variableName := value;
select @variableName := value;
set @hello_world = 'hello world !!!!';
select @hello_world;
set @hello_world := 'hello world !!!!';
select @hello_world;
select @hello_world := 'hello world !!!!';
select @hello_world;
複製程式碼查詢賦值
set @count = 0;
select count(*) into @count from teacher;
select @count;
複製程式碼區域性變數(begin end)
drop PROCEDURE if EXISTS hello_world;
create PROCEDURE hello_world() begin
declare result int;
select count(*) into result from teacher;
select result;
end;
call hello_world;
複製程式碼不能在begin end外使用
drop PROCEDURE if EXISTS hello_world;
create PROCEDURE hello_world() begin
declare result int;
select count(*) into result from teacher;
select result;
end;
call hello_world;
select result;
複製程式碼注意, 必須是begin後的第一行
drop PROCEDURE if EXISTS hello_world;
create PROCEDURE hello_world() begin
select 1+1;
declare result int;
select count(*) into result from teacher;
select result;
end;
call hello_world;
複製程式碼@變數名可以在begin end中使用
drop PROCEDURE if EXISTS hello_world;
set @result = 0;
create PROCEDURE hello_world() begin
select count(*) into @result from teacher;
select @result;
end;
call hello_world;
select @result;
複製程式碼