SQL编程

自定义函数

执行时机:调用执行

定义函数语法:

create  function  函数名(参数列表)returns 类型
begin
   return  null;
end;

函数的主体内容:

也就是begin到end中的逻辑代码，可以使用变量，也能写逻辑控制语句

变量的使用

变量分为全局变量和局部变量

全局变量：依赖于一次有效的数据库连接

使用步骤：1.赋初值，使用 set @变量名=数据值; @符号必须存在，这是全局变量的标识符，如果不为全局变量赋初值，默认值为null

2.使用，直接通过@变量名即可访问,并且在函数、存储过程、触发器中均可访问

局部变量:只能在存储过程、函数或触发器的begin~end之间使用

使用步骤：1.声明，声明格式：

declare 变量名 数据类型;

2.赋值：set 变量名=值;

3.使用：通过变量名访问

注意：所有局部变量的声明必须集中放置在所属结构的最前方！

逻辑控制语句

逻辑控制语句分为条件结构和循环结构

条件结构语句

关于条件结构语句这里只列举两个：

嵌套条件结构:

示例代码：

if(条件表达式)
then
     if(条件表达式)
    then
        条件成立时的操作
     else
        不成立时的操作
     end if;
else
    不成立时的操作
end if;

多重条件结构

示例代码：

if(条件1)
then 
​
elseif(条件2)
then
​
elseif(条件3)
then
​
else
​
end if;

循环结构语句

在mysql有多种循环结构，这里只介绍while循环(因为其他不会)

定义while循环语法:

while(循环条件表达式)
do
​
end while;

示例代码:

drop function  if exists fun_add;
create function fun_add(n int)returns int
begin
    declare i  int;
    declare sum int;
    set i=1;
    set sum=0;
    while(i<=n)
    do
        set sum=sum+i;#累加操作
        set i=i+1;#循环变量自增
    end while;
    return sum;
end;

存储过程

执行时机:调用执行

优点：1.执行速度更快

2.允许模块化程序设计(一个存储过程可以包含多条SQL指令，完成一段连续的逻辑操作)

3.提高系统安全性

4.减少网络流通量

创建存储过程语法:

create  procedure  存储过程名称(参数列表)
begin
    逻辑代码主体
end;

示例代码:

create procedure pro_addEmp(eId int, eName varchar(20), ePosition varchar(20),
                                                  eHrId int, eSalary float, eBonus float, dId int)
begin
    #查找
    declare c int;
    set c=(select count(empId) from employee where empId=eId);
    if(c=0)
    then
        insert into employee(empId,empName,empPosition,empHrId,empSalary,empBonus,deptId) values(eId,eName,ePosition,eHrId,eSalary,eBonus,dId);
    else
        signal sqlstate 'TX001' set message_Text='工号已经存在';
    end if;
end;

与函数的区别

1.存储过程的主体代码可以包含查询语句 返回结果集

2.参数：输入参数：in修饰，可以省略，默认为输入参数

输出参数(也就是函数中的返回值)：out修饰

示例代码：

drop procedure if exists pro_sum;
create procedure pro_sum(in n int,out s int,out c int)
begin
    declare i int;
    set i=1;
    set s=0;
    set c=1;
    while(i<=n)
    do
        set s=s+i;
        set c=c*i;
        set i=i+1;
    end while;
end;
call pro_sum(10,@mysum,@myc);

3.存储过程没有返回值

4.调用存储过程 需要使用 call 存储过程名(参数)进行调用

触发器

执行时机:预设执行时机

创建触发器语法:

create trigger tri_emp_add
    before/after   insert/update/delete on 表名
    for each row
    begin
        触发后的操作代码段
    end;

示例代码:

create trigger tri_emp_add
    before  insert on employee
    for each row
    begin
    if(new.empHrId is not null and not exists(select * from employee where empId=new.empHrId))
        then
        #不能新增 找不到对应的上级
        #阻止当前正在进行的新增操作
        signal sqlstate 'TX001' set message_text ='上级工号设置有误';
    end if;
end;

上述代码在新增数据之前对上级工号进行判断是否不为空并且没有此上级工号时，会激活此触发器，抛出一个错误阻止插入操作

手动制造一个错误，用于终止正在执行的操作：

signal sqlstate 'TX001' set message_text ='消息文本';

注意：每张表的每一个触发时机只能绑定一个触发器,新增操作只能用new表，删除操作只能用old表，修改操作既可以用new表也可以用old表

触发器的级联操作

级联删除部门和对应部门员工的触发器:

create trigger tri_dept_remove
    before  delete on department
    for each row
    begin
        #先删除对应部门的员工
        delete from employee where deptId=old.deptId;
end;

级联删除操作一定要谨慎使用 ! ! !

级联更新触发器

create trigger tri_sellInfo_add
    before  insert on sellinfo
    for each row
    begin
        #验证本次添加的销售记录中销售的商品数量是否 超过 对应商品的库存数量
        declare wCount int;
        set wCount=(select wareCount from inventinfo where id=new.inventId);
        if(new.sellCount>wCount)
            then
            signal sqlstate 'TX001' set message_text ='商品库存不足';
            else
            #数量充足 把库存数量减少
            update inventinfo set wareCount=wareCount-new.sellCount
            where id=new.inventId;
        end if;
end;

上述代码在对销售表进行新增操作的时候会激活此构造器，首先对库存数量进行判断，库存不足会抛出错误，阻止此次新增，如果库存充足，会将库存数量减少，再对销售表进行新增

例题：

#创建学生表和成绩表
create table t_Student
(
    stuName    varchar(20) not null,
    stuNo      varchar(100) primary key,
    stuSex     varchar(10) check ( stuSex = '男' or stuSex = '女'),
    stuAge     int         not null,
    stuSeat    int unique  not null,
    stuAddress varchar(100) default '地址不详'
);
​
create table t_Score
(
    examNo      varchar(20)  not null comment '科目号',
    stuNo       varchar(100) not null comment '学号',
    writtenExam int          not null comment '笔试成绩',
    labExam     int          not null comment '机试成绩',
    foreign key (stuNo) references t_Student (stuNo)
);

关于数据:笔者直接用图形化工具添加的(因为懒)，所以没有插入语句，随便填一下就好啦(记得先填主表t_Student的数据！)

题目及其解决过程:

#循环提分过程，要求比较平均机试成绩和笔试成绩，对较低者进行提分，每次提一分，直到有人的成绩达到97分为止。计算通过率
create procedure pro_addScore()
begin
    declare avgWrite double;
    declare avgLab double;
    set avgWrite = (select avg(writtenExam) from t_score); #平均笔试成绩
    set avgLab = (select avg(labExam) from t_score); #平均机试成绩
    if (avgLab > avgWrite)
    then
        #对笔试成绩提分
        #循环
        while(not exists(select writtenExam from t_score where writtenExam = 97))
            do
                #每次提1分
                update t_score
                set writtenExam=writtenExam + 1;
            end while;
    else
        #对机试成绩提分
        #循环
        while(not exists(select labExam from t_score where labExam = 97))
            do
                #每次提1分
                update t_score
                set labExam=t_Score.labExam + 1;
            end while;
    end if;
end;
​
#调用存储过程
call pro_addScore();
​
#查询
select stuName                                             姓名,
       stu.stuNo                                           学号,
       ifnull(writtenExam, '缺考')                         笔试成绩,
       ifnull(labExam, '缺考')                             机试成绩,
       (if(writtenExam >= 60 and labExam >=60, '是', '否')) 是否通过
from t_Student stu
         left join t_Score ts on stu.stuNo = ts.stuNo;
​
#计算通过率
select count(stu.stuNo) 总人数,
       count(case when writtenExam>=60 and labExam >=60 then stu.stuNo end ) 通过人数,
       concat(format(count(case when writtenExam>=60 and labExam >=60 then stu.stuNo end )/count(stu.stuNo)*100,0),'%') 通过率
from t_score ts
right join t_Student stu on stu.stuNo = ts.stuNo;