[数据库之五] 高级 SQL

2021-05-28 本文已影响0人小胡_鸭

1、使用程序设计语言访问数据库

标准：JDBC（Java）、ODBC（C、C++、C#、VB）

2、函数和过程

（1）函数的声明和调用

// 声明一个函数，根据传入的学院名称得到该学院中的教师数
create function dept_count(dept_name varchar(20))
    returns integer
    begin
    declare d_count integer;
        select count(*) into d_count
        from instructor
        where instructor.dept_name = dept_name
    return d_count;
    end
    
// 使用函数，返回教师数大于 12 的所有系的名称和预算
select dept_name, budget
from department
where dept_count(dept_name) > 12;


// 声明一个表函数，即返回关系作为结果的函数，获得某个系的所有老师
create function instructor_of (dept_name varchar(20))
    returns table (
        ID varchar(5),
        name varchar(20),
        dept_name varchar(20),
        salary numeric(8, 2))
return table
    (select ID, name, dept_name, salary
     from instructor
     where instructor.dept_name = instructor_of.dept_name);
     
// 使用表函数，返回金融系的所有老师
select * 
from table(instructor_of('Finance'));

（2）存储过程的声明和调用

// 声明一个存储过程，获得某个系的老师人数
create procedure dept_count_proc(in dept_name varchar(20), out d_count integer)
    begin
        select count(*) into d_count
        from instructor
        where instructor.dept_name = dept_count_proc.dept_name
    end
    
// 使用存储过程，获得物理系的老师人数
declare d_count integer;
call dept_count_proc('Physics', d_count);

（3）函数和存储过程的区别

① 传参的差异：函数只能返回一个参数（值或表对象），存储过程可以返回。

② 调用方式的差异：函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面；存储过程一般是作为一个独立的部分来执行（EXEC执行）。

③ 功能实现上：存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

从本质上，函数和存储过程的执行没有什么区别。

3、触发器

当数据库被修改时，有时需要有一种机制来实现特定事件发生的情况下，去执行某些动作，这就是触发器的作用。

（1）对触发器的需求

触发器常用来实现未被 SQL 约束机制指定的某些完整性约束。

比如大学数据库中，takes 表示学生的选课情况，如果某门课已经结课并出成绩，就应该更新 student 关系中选课的学生所对应元组，把该课的学分加入这个学生的总学分中。

【学生】student ( id, name, dept_name, tot_cred )
【学生课表】takes ( id, course_id, sec_id, semester, year, grade )

（2）触发器的定义和使用

【课程段】section ( course_id, sec_id, semester, year, building, room_number, time_slot_id )

【上课时段】time_slot ( time_slot_id, day, start_time, end_time )

【学生】student ( id, name, dept_name, tot_cred )

【学生课表】takes ( id, course_id, sec_id, semester, year, grade )
【课程】course ( course_id, title, dept_name, credits )

新增时执行触发器

当新增一个课程段时，需要触发器检查新插入 section 关系的元组中的 time_slot_id 是否合法，不合法则回滚取消插入。

create trigger timeslot_check1 after insert on section
    referencing new row as nrow
    for each row
    when (nrow.time_slot_id not in (
            select time_slot_id
            from time_slot))    /* time_slot 中不存在该 time_slot_id */
    begin
        rollback
    end;

删除时执行触发器

删除一个上课时段时，需要触发器检查当前有没有课程段使用了该时段，是则不允许删除。

create trigger timeslot_check2 after delete on time_slot
    referencing old row as orow
    for each row
    when (orow.time_slot_id not in (
            select time_slot_id
            from time_slot)     /* 在 time_slot 中刚刚被删除的 time_slot_id */
    and orow.time_slot_id in (
            select time_slot_id
            from section))      /* 在 section 中仍含有该 time_slot_id 的引用 */
    begin
        rollback
    end;

更新时执行触发器

当关系 takes 中的元组的属性 grade 被更新时，需要用触发器来维护 student 里元组的 tot_cred 属性，使其保持实时更新。

使用 after update of takes on grade 来标示当更新关系 takes 的属性 grade 时执行触发器。

create trigger credits_earned after update on takes on (grade)
    referencing new row as nrow
    referencing old row as orow
    for each row
    when nrow.grade <> 'F' and nrow.grade is not null
        and (orow.grade = 'F' or orow.grade is null)
    begin atomic
        update student
        set tot_cred = tot_cred +
            (select credits
             from course
             where course.course_id = nrow.course_id)
        where student.id = nrow.id;
    end;

上述的三个例子触发器都是在 SQL 执行之后触发，实际上触发器也可以在事件之前触发，作为避免非法更新、插入或删除的额外约束。

事件之前执行触发器

学生选课插入的分值为空白表明分数发生缺失，定义触发器用 null 值代替。

create trigger setnull before update of takes
    referencing new row as nrow
    for each row
    when (nrow.grade = '')
    begin atomic
        set nrow.grade = null;
    end;

触发器创建时默认有效，可使用以下语法设置为无效：

alter trigger trigger_name disable;
(一些数据库的语法) disable trigger trigger_name;

删除触发器使用以下语法：

drop trigger trigger_name;