数据库题目整理及详解（四）

请根据所学存储过程相关知识完成如下如下表示的功能:

(1) 创建数据库及 student 表,并至少插入 3 条数据;
(2) 创建并调用名为 sp 的存储过程,查询学生的所有信息;
(3)创建并调用名为 sp1 的存储过程,向表中插入一条学生信息;
(4) 创建并调用名为 sp2 的存储过程,查询学生表中的最大班级号;
(5)创建并调用名为 sp3 的存储过程,修改学生学号,如果学号为 1,修改为
10;否则,修改学号为 20,并查看信息表中的最大学号值;
(6) 创建并调用名为 sp4 的存储过程,使得学生的班级号既做输入参数又做输出参数;
(7) 创建并调用名为 sp5 的存储过程,根据输入的班级号,判断班级名称;
(8) 创建并调用名为 sp6的存储过程,根据输入的班级号,更新班级名称;

题目解答

(1) 创建数据库及 student 表, 并至少插入 3 条数据;

create database procedure_test;     ## 创建数据库
use procedure_test;     ## 切换至该数据库下

drop table if exists student;       ## 好习惯
create table student (      ## 建表
id int(11) primary key, 
name char(10)
not null, 
classno int(11), 
birth datetime);

desc student;   ## 看一下表结构

## 插入数据
insert into student values(1, ‘xiaohua‘, 01, ‘1992-02-01 10:20:31‘);
insert into student values(2, ‘xiaohua1‘, 02, ‘1993-05-11 20:34:35‘);
insert into student values(3, ‘xiaohua2‘, 03, ‘1989-011-11 11:11:11‘);

## 查询一下啊
select * from student;

(2) 创建并调用名为 sp 的存储过程,查询学生的所有信息;

drop procedure if exists sp;    ## 好习惯
create procedure sp() select * from student;  ## 建立存储过程
call sp();      ## 调用一哈
show procedure status \G;       ## 参看一哈

(3)创建并调用名为 sp1 的存储过程,向表中插入一条学生信息;

delimiter //    ## 自制分隔符，防止“；”带来的意外

## 接下来就和上边一样一样啦， 就不写了啊
create procedure sp1(in id int, in name char(10), in
classno int(11), in birth datetime ) comment ‘insert into a value to student‘
begin
    set @id = id;
    set @name = name;
    set @classno = classno;set @birth = birth;
    insert into student values(id, name, classno, birth);
end//

call sp1(4, ‘xiaohua4‘, 4, ‘2010-10-05 13:14:27‘)//
select * from student//
show procedure status \G

(4) 创建并调用名为 sp2 的存储过程,查询学生表中的最大班级号;

create procedure sp2(out p int)
deterministic
begin
    select max(classno) into p from student;
end//

call sp2(@pv)//
select @pv//
show procedure status \G

(5) 创建并调用名为 sp3 的存储过程,修改学生学号,如果学号为 1,修改为
10;否则,修改学号为 20,并查看信息表中的最大学号值;

create procedure sp3(in p1 int, out p2 int)
begin
    if p1 = 1 then
        set @v = 10;
    else
        set @v = 20;
    end if;
    select max(id) into p2 from student;
end//

call sp3(1, @ret)//
select @ret;
show procedure status \G

(6) 创建并调用名为 sp4 的存储过程,使得学生的班级号既做输入参数又做输
出参数;

drop procedure if exists sp4//
create procedure sp4(inout p4 int)
begin
    if p4 = 3 then
        set @value = 13;
    else
        set @value = 14;
    end if;
    select @value;
end//

call sp4(@result)//
set @result = 4//
call sp4(@result)//

(7) 创建并调用名为 sp5 的存储过程,根据输入的班级号,判断班级名称;

drop procedure if exists sp5//

## 眼尖的童鞋可能会看到，这里加了一步操作， 为了弥补第一题所犯的错，表结构忘记建立该属性了
alter table student add classname char(11)//  

create procedure sp5(in classno int, out classname char(11))
begin
    if classno = 1 then
        set classname = ‘firstclass‘;
    end if;

    if classno = 2 then
        set classname = ‘secondclass‘;
    end if;

    if classno = 3 then
        set classname = ‘thirdclass‘;
    end if;

    if classno = 4 then
        set classname = ‘fourthclass‘;
    end if;

    select * from student;
end//

call sp5(2, @ret)//
select @ret;

(8) 创建并调用名为 sp5 的存储过程,根据输入的班级号,更新班级名称;

## 错误总是无独有偶， 显然！这步是弥补上步未完成的任务
create procedure sp6(in classno int)
begin
    if classno = 1 then
        update student set classname = ‘firstclass‘ where id=1;
    end if;

    if classno = 2 thenupdate student set classname =     ‘secondclass‘ where id=2;
    end if;

    if classno = 3 then
        update student set classname = ‘thirdclass‘ where id=3;
    end if;

    if classno = 4 then
        update student set classname = ‘fourthclass‘ where id=4;
    end if;

    select * from student;
end//

call sp6(1)//
call sp6(2)//
call sp6(3)//
call sp6(4)//

参考资料

[1]. http://www.cnblogs.com/zhuawang/p/4185302.html
[2]. http://blog.csdn.net/woshixuye/article/details/8348180
[3]. http://www.2cto.com/database/201408/327315.html

数据库题目整理及详解（四）

标签：