第10章 存储过程与存储函数

#第10章 存储过程与存储函数
use stusys ;
#【例10.1】修改MySQL的结束符为“//”。
delimiter //

delimiter ;
#【例10.2】创建一个不带参数的存储过程P_str，输出“Hello MySQL!”。
use stusys;
delimiter //
create procedure P_str()
    begin
        select 'Hello MySQL!' as 课程名称;
        
    end //
delimiter ;
call P_str();

#【例10.3】创建一个带参数的存储过程P_maxGrade，查询指定学号学生的最高分。
delimiter //

 

create procedure P_maxGrade(in v_sno char(10))
    begin
        select max(grade) from score
            where v_sno=sno;
            
    end //
delimiter ;
call P_maxGrade('191001');

 

#【例10.4】创建存储过程P_math，如果”高等数学”课程的平均成绩大于80分，则显示”高等数学成绩良好”，否则显示”高等数学成绩一般”。
delimiter //
create procedure P_math(out v_grade char(20))
    begin
        declare v_avg decimal(4,2);
        select avg(grade) into v_avg
        from student a,course b,score c
        where a.sno=c.sno and b.cno=c.cno and cname='高等数学';
        if  v_avg>80 then
            set v_grade='成绩良好';
        else 
            set v_grade='成绩一般';
        end if;
    end//
delimiter ;
call P_math(@grade);
select @grade;
drop procedure if exists P_math;

#【例10.5】创建存储过程P_title，将教师职称转变为职称类型。
delimiter //
create procedure P_title(in v_tno char(6),out v_type char(10))
    begin
        declare v_str char(12);
        select title into v_str from teacher where tno=v_tno;
        case v_str
            when'教授' then set v_type='高级职称';
            when'副教授' then set v_type='高级职称';
            when'讲师' then set v_type='中级职称';
            when'助教' then set v_type='初级职称';
            else set v_type:='Nothing';
        end case;
    end//
delimiter ;
call P_title('100006',@type);
select @type;

#【例10.6】创建存储过程P_integerSum，计算1~100的整数和。
delimiter //
create procedure P_integerSum( out v_sum1 int)
    begin
        declare v_n int default 1;
        declare v_s int default 0;
        while v_n<=100 do
            set v_s=V_s+v_n;
            set v_n=v_n+1;
        end while;
        set v_sum1=v_s;
    end//
delimiter ;
call P_integerSum(@sum1);
select @sum1;

 

 

 

#【例10.7】创建存储过程P_oddSum，计算1~100的奇数和。
delimiter //
create procedure P_oddSum(out v_sum2 int)
    begin
        declare v_n int default 1;
        declare v_s int default 0;
        repeat
            if mod(v_n,2)<>0 then
                set v_s=v_s+v_n;
        end if;
        set v_n=v_n+1;
        until v_n>100
    end repeat;
    set v_sum2=v_s;
    end //
delimiter ;
call P_oddSum(@sum2);
select @sum2;
drop procedure if exists P_oddSum;

#【例10.8】创建存储过程P_factorial，计算10的阶乘。
delimiter //
create procedure P_factorial(out v_prod int)
    begin
        declare v_n int default 1;
        declare v_p int default 1;
        label:loop
            set v_p:=v_p*v_n;
            set v_n=v_n+1;
            if v_n>10 then
                leave label;
            end if;
        end loop label;
        set v_prod=v_p;
    end //
delimiter ;
call P_factorial(@prod);
select @prod;

#【例10.9】创建一个存储过程，计算student表中行的数目。
use stusys;
select * from student;
delimiter //
create procedure P_tablerows(out v_rows int)
    begin
        declare v_sno char(10);
        declare found boolean default true;
        #(1)定义域或声明游标
        declare cur_student cursor for select sno from student;
        #定义游标句柄，用于控制循环，十游标下移
        declare continue handler for not found set found=false;
        set v_rows=0;
        #(2)打开游标
        open cur_student;
        #(3)获取列表的数据并赋值给变量v_sno
        fetch cur_student into v_sno;
        while found do 
            set v_rows=v_rows+1;
            fetch cur_student into v_sno;
        end while;
        #(4)关闭游标
        close cur_student;
    end//
delimiter ;
call P_tablerows(@rows);
select @rows;

 

 

 

#【例10.10】创建向学生表插入一条记录的过程P_insertStudent，并调用该过程。
select * from student;
delimiter //
    create procedure P_instertStudent()
    begin
        insert into student
            values('191005','王燕','女','1999-04-14',null,null);
        select * from student
            where sno='191005';
    end//
delimiter ;
call P_instertStudent();

#【例10.11】创建修改学生专业和总学分的过程P_updateSpecGrade，并调用该过程。
delimiter //
create procedure P_updateSpecGrade(in v_sno char(6),in v_speciality char(12),in v_tc int)
    begin
        update student set speciality=v_speciality,tc=v_tc where sno=v_sno;
        select * from student where sno='191005';
    end //
delimiter ;
call P_updateSpecGrade('191005','计算机','50');
drop procedure if exists P_updateSpecGrade;

#【例10.12】创建删除学生记录的过程P_deleteStudent，并调用该过程。
delimiter //
create procedure P_deleteStudent(in v_sno char(6),out v_msg char(8))
    begin
        delete from student where sno=v_sno;
        set v_msg=' 删除成功';
    end //
delimiter ;
call P_deleteStudent('191005',@msg);
select @msg;

#【例10.13】删除存储过程P_insertStudent。
drop procedure P_instertStudent;
#【例10.14】创建一个存储函数F_courseName，由课程号查课程名。
delimiter //
create function F_courseName(v_cno char(4))
        returns char(12)
        deterministic
    begin
        return(select cname from course where cno=v_cno);
    end //
delimiter ;
#【例10.15】调用存储函数F_courseName。
select F_courseName('1201');

#【例10.16】删除存储函数F_courseName。
drop function if exists F_courseName;