mysql 调用存储过程1

布满荆棘的人生 2022-04-13 04:17 277阅读 0赞

**基础查询**

**首先创建一张students学生表，增加字段与插入数据如下**

![996472-20170601152410914-1552681450.png][]

**创建不带参数的存储过程**

查看学生个数

DROP PROCEDURE IF EXISTS select_students_count;  -- 没有括号()

![复制代码][copycode.gif]

DELIMITER ;;
    CREATE PROCEDURE `select_students_count`()
    BEGIN
    
        SELECT count(id) from students;
    
    END;;
    DELIMITER ;

![复制代码][copycode.gif]

执行存储过程

call select_students_count();

**![996472-20170601163633821-220886623.png][]**

**带参数的存储过程**

根据城市查询总数

DROP PROCEDURE IF EXISTS select_students_by_city_count;

![复制代码][copycode.gif]

DELIMITER ;;
    
    CREATE PROCEDURE `select_students_by_city_count`(
       in _city varchar(225)
    )
    BEGIN
    
        SELECT count(id) from students where city = _city;
    
    END;;
    DELIMITER ;

![复制代码][copycode.gif]

执行存储过程

call select_students_by_city_count('杭州');

**![996472-20170601163524633-445270020.png][]**

**带有输出参数的存储过程**

根据姓名查询的学生信息，返回学生的城市

DROP PROCEDURE IF EXISTS select_students_by_name;

![复制代码][copycode.gif]

DELIMITER ;;
    
    CREATE PROCEDURE `select_students_by_name`(
       in _name varchar(225),   -- 输入参数
       out _city varchar(225),  -- 输出参数
       inout _age int(11)       -- 输入输出参数
    )
    BEGIN
    
        SELECT city  from students where name = _name and age = _age into _city;
    
    END;;
    DELIMITER ;

![复制代码][copycode.gif]

执行存储过程

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

**![996472-20170601164009680-1447129188.png][]**

**MySQL**支持**IN**(传递给存储过程)，**OUT**(从存储过程传出)和**INOUT**(对存储过程传入和传出)类型的参数。存储过程的代码位于**BEGIN**和**END**语句内，如前所见，它们是一系列**SELECT**语句，用来检索值，然后保存到相应的变量(通过指定**INTO**关键字)

**带有通配符的存储过程**

通配符，在参数值赋值时，加上相应的通配符

DROP PROCEDURE IF EXISTS select_students_by_likename;

![复制代码][copycode.gif]

DELIMITER ;;
    
    CREATE PROCEDURE `select_students_by_likename`(
       in _likename varchar(225)  
    )
    BEGIN
    
        set _likename = '%s%';  -- 默认值 
        SELECT *  from students where name like _likename;
    
    END;;
    DELIMITER ;

![复制代码][copycode.gif]

执行存储过程

call select_students_by_likename('%s%');

![996472-20170601170250571-368603408.png][]

**使用存储过程进行增删改**

新增

新增学生信息

DROP PROCEDURE IF EXISTS insert_student;

![复制代码][copycode.gif]

DELIMITER ;;
    
    CREATE PROCEDURE `insert_student`(
        _id int,
        _name varchar(225),
        _age int,
        _city varchar(225)
    )
    BEGIN
    
        INSERT INTO students(ID,name,age,city) VALUES(_id,_name,_age,_city);
    
    END;;
    DELIMITER ;

![复制代码][copycode.gif]

执行存储过程

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

执行完后表中就多了一条数据

![996472-20170601171237680-126933889.png][]

修改

根据学生ID，更新学生信息

DROP PROCEDURE IF EXISTS update_student;

![复制代码][copycode.gif]

DELIMITER ;;
    
    CREATE PROCEDURE `update_student`(
        _id int,
        _name varchar(225),
        _age int,
        _city varchar(225)
    )
    BEGIN
    
        UPDATE  students SET name=_name, age=_age, city=_city where ID = _id;
    
    END;;
    DELIMITER ;

![复制代码][copycode.gif]

执行存储过程

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

![996472-20170601171902571-662914221.png][]

删除

根据ID，删除某学生记录

DROP PROCEDURE IF EXISTS delete_student_by_id;

![复制代码][copycode.gif]

DELIMITER ;;
    
    CREATE PROCEDURE `delete_student_by_id`(
        _id int
    )
    BEGIN
    
        DELETE FROM students where ID = _id;
    
    END;;
    DELIMITER ;

![复制代码][copycode.gif]

执行存储过程

call delete_student_by_id(5);

students表中id为5的那条记录就删除啦！

[996472-20170601152410914-1552681450.png]: /images/20220413/fd4f614af4b643b089cc22bfe0d66cfa.png
[copycode.gif]: /images/20220413/18377a2094214951aeae7ef08b9e8cdd.png
[996472-20170601163633821-220886623.png]: /images/20220413/e8ae1e6dc5ba434bb94cfe182abae096.png
[996472-20170601163524633-445270020.png]: /images/20220413/667451e4811748bf9d2fd1e07b0c5b27.png
[996472-20170601164009680-1447129188.png]: /images/20220413/e79b5d97f700464983feabc25a4028b6.png
[996472-20170601170250571-368603408.png]: /images/20220413/8eaf406123ca4043b3e49c3cab5fb751.png
[996472-20170601171237680-126933889.png]: /images/20220413/9abaf5c4ad8f4fb588325423483c7fc9.png
[996472-20170601171902571-662914221.png]: /images/20220413/37077123780f47328fa0fd384e431bdb.png