MySQL 存儲過程的簡單使用

首先創建一張 students 表

SQL腳本如下：

create table students(
    id int primary key auto_increment,
    age int,
    name varchar(20),
    city varchar(20)
) character set utf8;

insert into students values(null, 22, 'lisa', '杭州');
insert into students values(null, 16, 'rock', '上海');
insert into students values(null, 20, 'jack', '深圳');
insert into students values(null, 21, 'rose', '北京');

不帶參數的存儲過程

-- 查詢學生個數
drop procedure if exists select_students_count;

delimiter ;; -- 替換分隔符
    create procedure select_students_count() 
        begin 
            select count(id) from students; 
        end ;;
delimiter ;

執行存儲過程：

call select_students_count();

帶參數的存儲過程

-- 根據城市查詢總數
delimiter ;;
    create procedure select_students_by_city_count(in _city varchar(255))
        begin
            select count(id) from students where city = _city;
        end;;
delimiter ;

執行存儲過程：

call select_students_by_city_count('上海');

帶有輸出參數的存儲過程

MySQL 支持 in (傳遞給存儲過程)，out (從存儲過程傳出) 和 inout (對存儲過程傳入和傳出) 類型的參數。存儲過程的代碼位於 begin 和 end 語句內，它們是一系列 select 語句，用來檢索值，然後保存到相應的變數 (通過 into 關鍵字)

-- 根據姓名查詢學生信息，返回學生的城市
delimiter ;;
create procedure select_students_by_name(
    in _name varchar(255),
    out _city varchar(255), -- 輸出參數
    inout _age int(11)
)
    begin 
        select city from students where name = _name and age = _age into _city;
    end ;;
delimiter ;

執行存儲過程：

set @_age = 20;
set @_name = 'jack';
call select_students_by_name(@_name, @_city, @_age);
select @_city as city, @_age as age;

帶有通配符的存儲過程

delimiter ;;
create procedure select_students_by_likename(
    in _likename varchar(255)
)
    begin
        select * from students where name like _likename;
    end ;;
delimiter ;

執行存儲過程：

call select_students_by_likename('%s%');
call select_students_by_likename('%j%');

使用存儲過程進行增加、修改、刪除

增加

delimiter ;;
create procedure insert_student(
    _id int,
    _name varchar(255),
    _age int,
    _city varchar(255)
)
    begin
        insert into students(id,name,age,city) values(_id,_name,_age,_city);
    end ;;
delimiter ;

執行存儲過程：

call insert_student(5, '張三', 19, '上海');

執行完後，表中多了一條數據，如下圖：

修改

delimiter ;;
create procedure update_student(
    _id int,
    _name varchar(255),
    _age int,
    _city varchar(255)
)
    begin
        update students set name = _name, age = _age, city = _city where id = _id;
    end ;;
delimiter ;

執行存儲過程：

call update_student(5, 'amy', 22, '杭州');

刪除

delimiter ;;
create procedure delete_student_by_id(
    _id int
)
    begin
        delete from students where id=_id;
    end ;;
delimiter ;

執行存儲過程：

call delete_student_by_id(5);

students 表中 id 為5的那條記錄成功刪除。如下圖：

查詢存儲過程

查詢所有的存儲過程：

select name from mysql.proc where db='資料庫名';

查詢某個存儲過程：

show create procedure 存儲過程名;

本文永久更新地址：https://github.com/nnngu/LearningNotes/blob/master/MySQL/01%20MySQL%20%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%9A%84%E7%AE%80%E5%8D%95%E4%BD%BF%E7%94%A8.md