MYSQL——存储过程和存储函数练习题

目录

一、创建一个存储过程

1.创建要求

2.代码展示

​编辑 二、创建一个执行动态SQL的存储过程

三、分别创建一个功能相同的存储过程和存储函数并比较不同点

1.存储过程

2.存储函数

​编辑 3.存储函数与存储过程有如下区别

---

 

一、创建一个存储过程

1.创建要求

包括参数、变量、变量赋值、条件判断、UPDATE语句、SELECT返回结果集。

2.代码展示

create table zuoye3_1(
    id int primary key auto_increment,
    name varchar(60),
    age int,
    job varchar(20)
) charset = 'utf8mb4';

insert into zuoye3_1(id, name, age, job) values (null, '小万', 18, '班长'),
(null, '小向', 19, '学委'),
(null, '小谢', 20, '心理委员'),
(null, '小钟', 20, '安全委员'); 

delimiter // 
create procedure proc()
begin
select * from zuoye3_1;
end //
delimiter ;

 二、创建一个执行动态SQL的存储过程

 delimiter //
create procedure zuoye3_2(in choose int)
begin
    declare d_id int default 0;
    declare d_name varchar(20) default '';
    declare d_age int default 0;
    declare d_job varchar(60) default '';
    declare num int default 0;
    declare d_cur cursor for select * from zuoye3_1;
    if choose = 1 then
        select * from zuoye3_1;
        update zuoye3_1 set name = '小羊' where id = 4;
        select * from zuoye3_1;
    elseif choose = 2 then
        select * from zuoye3_1;
        insert into zuoye3_1 values (null, '小白', 22, '宣传委员');
    elseif choose = 3 then
        create table demo2(
            id int primary key auto_increment,
            name varchar(60),
            age int,
            job varchar(20)
        ) charset = 'utf8mb4';
        open d_cur;
        repeat
            fetch d_cur into d_id, d_name, d_age, d_job;
            insert into demo2 values (d_id, d_name, d_age, d_job);
            set num = num + 1;
            select num;
        until num = (select count(*) from zuoye3_1) end repeat ;
        close d_cur;
    else
        select '参数超出';
    end if ;
end //
delimiter ;

三、分别创建一个功能相同的存储过程和存储函数并比较不同点

1.存储过程

delimiter //
create procedure level(in i int, out levelr varchar(10))
begin
    case
        when i > 85 then set levelr = '优秀' ;
        when i > 75 then set levelr = '良好' ;
        when i > 60 then set levelr = '及格' ;
        else set levelr = '不及格';
    end case ;
end //
delimiter ; 

select @lever; 

2.存储函数

delimiter //
create function level_f(i int)
    returns varchar(10)
begin
    declare levelr varchar(10);
    case
        when i > 85 then set levelr = '优秀' ;
        when i > 75 then set levelr = '良好' ;
        when i > 60 then set levelr = '及格' ;
        else set levelr = '不及格';
    end case ;
    return levelr;
end //

 

delimiter ;
select level_f(72);

 3.存储函数与存储过程有如下区别

　　（1）存储函数的限制比较多,例如不能用临时表,只能用表变量,而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。

　　（2）返回值不同。存储函数必须有返回值,且仅返回一个结果值；存储过程可以没有返回值,但是能返回结果集(out,inout)。

　　（3）调用时的不同。存储函数嵌入在SQL中使用,可以在select 存储函数名(变量值)；存储过程通过call语句调用 call 存储过程名。

　　（4）参数的不同。存储函数的参数类型类似于IN参数，没有类似于OUT和INOUT的参数。存储过程的参数类型有三种，IN、out和INOUT：

　　　　a. in：数据只是从外部传入内部使用(值传递),可以是数值也可以是变量

　　　　b. out：只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量

　　　　c. inout：外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量。