爆肝！一看就懂的《SQL 语言知识体系》【建议收藏】（下）

爆肝！一看就懂的《SQL 语言知识体系》【建议收藏】（中）：https://developer.aliyun.com/article/1529602

7.2 更改和删除触发器

使用系统命令 ALTER TRIGGER 更改指定的触发器的定义，语法如下：

ALTER TRIGGER <触发器> [{BEFORE|AFTER}] {[DELETE|INSERT|UPDATE OF [列名清单]]}
ON 表名|视图名
AS 
BEGIN 
 SQL STATEMENTS
END;

使用 DROP TRIGGER <触发器>[,…n]，其中，n 表示可以指定多个触发器的占位符。

8. 嵌入式 SQL 和动态 SQL

8.1 嵌入式 SQL

SQL 提供了将 SQL 语句嵌入某种高级语言中的使用方式，但是如何识别嵌入在高级语言中的 SQL 语句，通常采用预编译的方法。该方法的关键问题是必须区分主语言中嵌入的 SQL语句，以及主语言和 SQL 间的通信问题。采用的方法由 DBMS 的预处理程序对源程序进行扫描，识别出 SQL 语句，把它们转换为主语言调用语句，以使主语言编译程序能识别它，最后由主语言的编译程序将整个源程序编译成目标码。

如何区分主语言语句与 SQL 语句？

需要在所有的 SQL 语句前加前缀 EXEC SQL，而 SQL 的结束标志随主语言的不同而不同。

PL/1 和 C 语言的引用格式为：EXEC SQL <SQL语句>；

COBOL 语言的引用格式为：EXEC SQL <SQL 语句> END-EXEC；

主语言与数据库如何通信？

（1）SQL 通信区 (SQL Communication Area,SQLCA) 向主语言传递 SQL 语句执行的状态信息，使主语言能够根据此信息控制程序流程。

（2）主变量也称共享变量。主语言向 SQL 语句提供参数主要通过主变量，主变量由主语言的程序定义，并用 SQL 的 DECLARE 语句说明，引用变量一定要加上:。

EXEC SQL BEGIN DECLARE SECTION;    /*说明主变量*/
  char Msno[4],Mcno[3],Givensno[5];
  int Mgrade;
  char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;

分析：以上说明了五个共享变量，其中，SQLSTATE 是一个特殊的共享变量，解释 SQL 语句执行状况的作用。当 SQL 语句执行成功时，系统自动给 SQLSTATE 赋值上全零值，否则为非全零（”02000”)。因此，当执行一条SQL语句后，可以根据 SQLSTATE 的值转向不同的分支，以控制程序的流向。引用时，为了与 SQL 属性名相区别，需在主变量前加: 。

示例27. 根据共享变量 givensno 值查询学生关系students 中学生的姓名、年龄和性别。

EXEC SQL SELECT sname,age,sex
  INTO :Msno,Mcno,:givensno
  FROM students
  WHERE sno=:Msno;

示例28. 某学生选修了一门课程信息，将其插入学生选课表SC中，假设学号、课程号、成绩已分别赋给主变量HSno、Hcno和Hgrade。

EXEC SQL INSERT 
  INTO SC(Sno,Cno,Grade)
  VALUES(:Hsno,:Hcno,:Hgrade);

（3）游标。SQL 语言是面向集合的，一条 SQL 语句可产生或处理多条记录。而主语言是面向记录的，一组主变量一次只能放一条记录，所以，引入游标，通过移动游标指针来决定获取哪一条记录。

定义游标：说明性语句，定义中的 SELECT 语句并不立即执行。语法如下：

EXEC SQL DECLARE <游标名> CURSOR FOR 
  <SELECT 语句>

• 打开游标：执行游标定义中的 SELECT 语句，同时游标处于活动状态，游标是一个指针，此时指向查询结果的第一行之前。语法如下：

EXEC SQL OPEN <游标名>

• 推进游标：使用时，游标会推进一行，并把指针指向的行（称为当前行）中的值取出，送到共享变量中。变量表由逗号分开的共享变量组成。该语句经常用于宿主程序的循环结构中，并借助宿主语言的处理语句逐一处理查询结果中的一个元组。语法如下：

EXEC SQL FETCH FROM <游标名> INTO <变量表> 

• 关闭游标：使用时，关闭游标，不再和查询结果相联系。关闭了的游标，可以再次打开，与新的查询结果相联系。在游标处于活动状态时，可以修改和删除游标指向的元组。语法如下：

EXEC SQL CLOSE <游标名>

示例29. 在C语言中嵌入SQL的查询，检索某学生的学习成绩，其学号由共享主变量givensno给出，结果放在主变量Sno,Cno,Grade中。如果成绩不及格，则删除该记录，如果成绩为60~69分，则将成绩修改为70分，并显示学生的成绩信息(除60分以下的)

#DEFINE NO_MORE_TUPLES !(strcmp(SQLSTATE,"02000"))
void sel()
{EXEC SQL BEGIN DECLARE SECTION;  /*说明主变量*/
  char Msno[4],Mcno[3],givensno[5];
  int Mgrade;
  char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE Scx CURSOR FOR  /* 说明游标Scx，将查询结果与Scx建立联系*/
  SELECT Sno,Cno,Grade
  FROM SC
  WHERE Sno=:givensno;
EXEC SQL OPEN Scx;
While(1)                        /* 用循环结构逐条处理结果集中的记录*/
{EXEC SQL FETCH FROM Scx        /* 游标推进一行*/
  INTO :Msno,:Mcno,:Mgrade;     /* 送入主变量，注意每个变量要加分号;*/
 if (NO_MORE_TUPLES) Break;      /* 处理完退出循环*/
 print("%s,%s,%d\n",sno,cno,g);
 };
 if (Mgrade<60)                  /* 成绩<60*/
  EXEC SQL DELETE FROM SC WHERE CURRENT OF Scx;
 Else 
  {if (Mgrade < 70)             /* 成绩<70*/
      EXEC SQL UPDATE SC SET grade = 70 WHERE CURRENT OF Scx;
      MGrade = 70
  }
  Printf("%s,%s,%d",Msno,Mcno,Mgrade); /*显示学生记录*/
EXEC SQL CLOSE Scx;            /* 关闭游标*/
};

8.2 动态 SQL

动态 SQL 允许程序在运行时构造、提交 SQL 查询。使用动态 SQL 语句时，还可以改进技术；当预备语句组合而成的 SQL 语句只需执行一次， 那么预备语句可以在程序运行时由用户输入才组合起来，但并不执行。

动态 SQL 预备语句格式

EXEC SQL PREPARE <动态SQL语句名> FROM <共享变量或字符串>;

动态 SQL 执行语句格式

EXEC SQL EXECUTE <动态SQL语句名>;

示例30. 动态 SQL 示例

char*query = "UPDATE sc SET Grade = Grade*1.1 WHERE Cno = ?";
EXEC SQL PREPARE dynprog FROM :query;
char cno[5] = "C4";
EXEC SQL EXECUTE dynprog USING :cno;

9. SQL-99 所支持的对象关系模型

对象- 关系数据模型扩展关系数据模型的方式是通过提供一个包括复杂数据类型和面向对象的更丰富的类型系统。

9.1 嵌套关系

嵌套关系模型(Nested Relational Model)是关系模型的一个扩展，域可以是原子的也可以赋值为关系。这样元组在一个属性上的取值可以是一个关系，于是关系可以存储在关系中。从而一个复杂对象就可以用嵌套关系的单个元组来表示。如果我们将嵌套关系的一个元组视为一个数据项，在数据项和用户数据库观念上的对象之间就有了一个一一对应的关系。

图书馆示例，每本书存储有书名、作者集合、出版商、关键字集合四种属性。如果这些信息定一个关系，下面的属性将是非原子的：

1）作者。一本书可能有一组作者。

2）关键字。一本书存储了一组关键字，我们希望能够检索出关键字包含该集合中的一个或多个关键字的所有书，这样就将关键字集合域视为非原子的。

3）出版商。与关键字和作者不同，出版社没有一个以集合为值的域。但是，我们可能将出版商视为由名字和分支机构这两个子字段组成的，使得出版商域成为非原子的。

定义一个示例关系 books，该关系是1NF， 如下：

假如需要访问单个作者和单个关键字，可以对 books 关系进行拆解，得到 flat-books 关系1NF如下：

如果 title→→author、title→→keyword、title→pub-name, pub-branch 是多值依赖，我们可以进一步把这个关系拆解成4NF，使得 flat-books 关系的冗余去除。拆解后关系和示例如下：

                  authors(title, author)

                  keywords(title, keyword)

                  books4(title, pub-name, pub-branch)

4NF 设计要求用户她们的查询包含连接操作，因此使得交互复杂化。

面向对象数据库系统支持面向对象数据模型，是一个持久的、可共享的对象库的存储和管理者，而一个对象库是由一个OO模型所定义的对象的集合体。

对象：是由一组数据结构和在这组数据结构上的操作的程序代码封装起来的基本单位。对象之间的界面由 一组消息定义。 一个对象包括属性集合、方法集合和消息集合。

对象标识：是指面向对象数据库中的每个对象都有一个唯一不变的标识。常用的几种标识有值标识、名标识和内标识。

值标识，使用一个值来标识，在关系数据库中通常使用这种形式的标识。例如，一个元组的主码标识了这个元组。

名标识，用用户提供的名称作为标识。这种形式的标识通常用于文件系统中的文件，不管文件的内容是什么，每个文件都被赋予一个名称来唯一标识。

内标识，是建立在数据模型或程序设计语言中内置的一种标识，不需要用户给出标识。面向对象系统中使用这种形式的标识，每个对象在创建时被系统自动赋予一个标识符。

封装：OO模型的一个关键概念就是封装。每一个对象是其状态和行为的封装。封装是对象的外部界面与内部实现之间实行清晰隔离的一种抽象，外部与对象的通信只能通过消息。

类：共享同样属性和方法集的所有对象构成了一个对象类(简称类)。例如，学生是一个类，黎明、张军、樊建喜是学生类中的一个对象。类是“型”，对象是“值”。

9.2 复杂类型

集合类型

集合是集合体类型(collection type)的一个实例，其他的集合体类型包括数组(array)和多重集合(multiset)(即无序的集合体，其中一个元素可以出现多次)。

SQL-99还提供了新字符型数据大对象数据类型(clob)和二进制数据大对象数据类型(blob)。

create table books(
keyword-set setof(varchar(2))
,author-array varchar (20) array [10]
,book-review clob (10KB) 
,image blob (10MB)
,movie blob (2GB)
);

复杂类型值的创建

在SQL-99中构造器函数(constructor function)用来创建结构类型的值。与结构类型同名的函数就是这个结构类型的构造器函数。

示例20. 给Publisher类型声明一个构造器。

create function Publisher (n varchar(20),b varchar(20))
returns Publisher
begin
  set name=n;
  set branch=b;
end

然后我们可以用 Publisher(McGraw-Hill,NewYork) 来创建Publisher 类型的值。

9.3 继承

继承可以在类型的级别上进行，也可以在表级别上进行。

类型继承

如希望在数据库中对那些是学生和教师的人分别存储一些额外的信息。

示例31. 假定人的类型定义如下所示，定义学生和教师类型。由于学生和教师是人，所以可以使用继承。

create type Person(
name varchar(20)
,address varchar(20)
);

create type Student
under Person
(degree varchar(20),
department varchar(20));

create type Teacher
under Person
(salary integer,
department varchar(20))

Student 和Teacher 都继承了Person 的属性，即name和address。Student和Teacher被称为Person 的子类型，Person 是Student 的超类型，同时也是Teacher 的超类型。像属性一样，结构 类型的方法也被它的子类型继承。不过，子类型可以通过在 一个方法声明中使用overriding

method (重载方法)取代原method (方法)的方式重新声明方法，以重定义该方法的作用。

表继承

SQL-99中的子表(subtable)对应的是E-R概念中的特殊化/一般化。子表的类型必须是父表类型的子类型，因此，父表中的每一个属性均出现在子表中

create table people of Person;

create table students of Student under people;

create table teachers of Teacher under people;

当我们声明students 和teachers 作为people的子表时，每一个students 或teachers 中出现的 元组也隐式存在于peopl e中。如果 一个查询用到people 表，它将查找的不仅仅是直接插入到这 个表中的元组，而且还包含插入到它的子表(也就是students 和teachers)中的元组。

9.4 引用类型

面向对象的程序设计语言提供了引用对象的能力，类型的一个属性可以是对一个指定类型的对象的引用。

示例21. 定义一个包括name字段和head字段的Department类型，一个Department类型的表departments。其中，head字段引用到Person类型。方法如下所示

create type Department(
name varchar(20),
head ref(Person) scope people);

create table departments of Department;

这里，引用限制在people 表中的元组。在SQL-99中，对一个指向表的元组的引用范围 (scope) 的限制是强制的，它使引用的行为与外码类似。

9.5 与复杂类型有关的查询

路径表达式

在SQL-99中对引用取内容使用→符号。

# 查找各部门负责人的名字和地址
select head->name,head->address
from departments;

head->name 带有->符号的表达式被称为路径表达式。由于head是一个对people表中元组的引用，上述查询中的name属性就是people表中元组的name属性。引用可以用来隐藏连接操作，如果没有使用引用要找出一个部门负责人的姓名和地址，我们就需要将departments与people关系显式地做一个连接，因此使用引用可以明显地简化查询。

以集合体为值的属性

一个计算集合体值的表达式可以出现在关系名出现的任何地方。

# books关系模式，查询所有的码中包含“database” 字样的书
select title
from books 
where 'database' in (unnest(keyword-set));

unnest(keyword-set)在无嵌套关系的SQL中相当于一个select-from-where的子表达式。

#  一本特定的书具有三个作者,拆出每个作者
select author-array[1],author-array[2],author-array[3]
from books
where title = 'Database System Concepts';
# 包含形式为“ 书名，作者名” ，对应每本书和书的每个作者
select b.title,a.name
from books as b,unnest(b.author-array) as a;

由于books的author-array属性是一个以集合体值的字段，因此可以用在需要有一个关系存在的from子句中。

嵌套与解除嵌套

将一个嵌套关系转换成具有更少(或没有)的关系为值的属性的形式的过程称为解除嵌套(unnesting)。books关系有author-array和keyword-set两个是集合体的属性；同时books关系另外还有title和publisher两个不是集合体的属性。

select title
      ,A as author
      ,publisher.name as pub-name
      ,publisher.branch as pub-branch
      ,K as keyword
from books as B,unnest(B.author-array) as A,unnest(B.keyword-set) as K;

from 子句中的变量B被声明为以books为取值范围，变量A被声明为以书B的author-array 中的作者为取值范围，同时K 被声明为以书B 的keyword-set 中的关键字为取值范围

9.6 函数和过程

SQL 函数和过程

定义一个函数，实现给定一个书名，返回作者数量：

create function author-count(title varchar(20))
returns integer
begin 
  declare a-count integer;
  select count(author) into a-count
  from authors
  where authors.title = title
return a-count;
end;

以上也可以写成一个存储过程：

create procedure author-count-proc(in title varchar(20),out a-count integer)
begin 
  select count(author) into a-count
  from authors
  where authors.title = title
end;
# 应用
declare a-count integer;
call author-count-proc('Database Systems Concepts',a-count);

外部语言程序

SQL-99允许使用一种程序设计语言(如 C或C++)定义函数。这种方式定义的函数会比 SQL中定义的函数效率更高，无法在SQL中执行的计算可以由这些函数执行。

create procedure author-count-proc(in title varchar(20),out a-count integer)
language C
external name '/usr/avi/bin/author-count-proc';

create function author-count(title varchar(20))
returns integer
language C 
external name '/usr/avi/bin/author-count';

过程的构造

1）while 和 repeat 循环语句

一个复合语句有begin … end的形式，在begin和end之间会包含多条SQL语句。语法如下：

declare n integer default 0;
while n<10 do
  set n=n+1;
end while;

repeat
  set n=n-1;
until n=0
end repeat;

2）for 循环语句

declare n integer default 0;
for r as 
  select balance 
  from account 
  where branc-name='Perryridge'
do 
  set n=n+r.balance
end for

程序在 for 循环开始执行的时候隐式地打开一个游标，并且用它每次获得一个行的值存入 for 循环变量(在上面例子中指r)中。语句leave可用来退出循环，而 iterate 表示跳过剩余语句从循环的开始进入下一个元组。

3）if-then-else 语句与 case 语句

条件语句 if-then-else 语法如下：

if r.balance<1000
  then set l=l+r.balance
elseif r.balance<5000
  then set m=m+r.balance
else set h=h+r.balance
end if;

10.总结

通过本文 SQL 语言的基本知识，多加实践掌握数据库数据查询 (Data Query)、数据操纵 (Data Manipulation)、数据定义 (Data Definition)、数据控制 (Data Control)等基本操作，进阶存储过程、触发器、动态 SQL 和嵌入式 SQL，并结合实际项目不断实践，你将能够熟练地处理各种数据库相关的任务，无论是简单的数据查询还是复杂的数据库设计和管理，相信不管遇到任何数据库你都可以得心应手。