5.4.5.1 查询从单个表派生出的视图
例5-134 建立信息系学生的视图，并对视图进行查询，查询年龄小于20岁的学生信息。
SQL＞CREATE VIEW infostudent
AS SELECT sno，sname，age
FROM student
WHERE dept′IS′
SQL＞SELECT sno，sname
FROM infostudent
WHERE age＜20；
系统执行此查询时，首先把它转换成等价的对基本表的查询，然后执行此查询，即当查询视图时，系统首先从数据字典中取出该视图的定义，然后把定义中的查询语句和对该视图查询语句结合起来，形成一个修正的查询语句。对以上语句，修正后的查询语句为：
SQL＞SELECT sno，sname
FROM student
WHERE dept′IS′AND age＜20；
表示查询信息系年龄小于20岁的学生的学号和姓名。
由于视图的查询实质上是对基本表的查询，因此基本表的变化可以反映到视图上，视图就如同“窗口”一样，通过视图可以看到基本表的动态变化。
5.4.5.2 查询带虚列的视图（带表达式的视图）
例5-135 将学生的学号以及平均成绩定义为一个视图，并对视图执行查询。
SQL＞CREATE VIEW avggrade（sno，avgg）
AS SELECT sno，avg（grade）
FROM sc
GROUP BY sno
SQL＞SELECT*
FROM avggrade
WHERE avgg＞80；
系统执行此查询时，从数据字典中取出该视图的定义，把定义中的查询语句和对该视图查询语句结合起来，形成一个修正的查询语句。修正后的查询语句如下：
SQL＞SELECT sno，avg（grade）
FROM sc
GROUP BY sno
HAVING avg（grade）＞80
表示查询平均成绩大于等于80分的学生的学号和平均成绩。
5.4.5.3 查询多个表派生出的视图
例5-136 建立信息系选修数据库课程的学生视图，包含学号、姓名和成绩。
SQL＞CR EATE VIEW info-db-student（sno，sname，grade）
AS SELECT s.sno，sname，grade
FROM student s，course c，sc
WHE RE dept′IS′and cname′DB′
and s.snosc.sno and c.cnosc.cno
对info-db-student视图执行下列查询语句。
SQL＞SELECT*
FROM info-db-student
WHERE grade＞70；
系统执行此查询时，从数据字典中取出该视图的定义，把定义中的查询语句和对该视图查询语句结合起来，形成一个修正的查询语句。修正后的查询语句如下：
SQL＞SELECT sno，sname，grade
FROM student s，sc，course c
WHERE dept′IS′and cname′DB′and grade＞70
and s.snosc.sno and sc.cnoc.cno
表示查询IS系的数据库课程成绩大于70分的学生的学号、姓名和成绩。
5.5 SQL的数据操纵
5.5.1 基本表的数据操纵
表建立后，用户需要用DML语句向表中加入数据、修改表中已经存在的数据或删除数据。这些数据操纵命令组成了一个事务，使用事务控制命令保存或删除这些改变。DML是SQL命令的一个子集，用于操纵表中的数据。
5.5.1.1 插入数据
向关系表中插入数据语法如下：
INSERT INTO 表名[（列名1，列名2……）]
{VALUES（值1，值2……）|QUERY语句}
使用INSERT语句向表中加入新行时应注意以下几个问题。
如果用VALUES子句，一次只能插入一行。如果用QUERY语句，表示将其他表的数据往这表中插入，允许一次插入多行。
在INSERT语句中列名是可以选择指定的，如果没有指定列名，则表示这些列按表中或视图中列的顺序和个数。
向表中插入数据之前，表结构必须已经创建。
插入值的数据类型、个数、前后顺序必须与表中列的数据类型、个数、前后顺序匹配。
插入的数据及列名之间用逗号分开。
例5-137 插入一个学生记录到student表中。
SQL＞INSERT INTO student VALUES（′95020′，′张三′，′女′，′CS′，20）
INSERT语句中若没有包括某列，该列值或者为空值（NULL）或者是建表时设定的默认值。也可以把列显式地设为空值NULL（除非该列定义为非空或主键）。
例5-138 插入一条选课记录（′95020′，′C1′）。
SQL＞INSERT INTO sc（sno，cno）VALUES（′95020′，′C1′）；
sc中新插入的记录在grade成绩列上取空值。或者用下列等效的语句。
SQL＞INSERT INTO sc VALUES（′95020′，′C1′，NULL）；
字符型和日期型数据要用单引号引起来，日期型数据可以使用TODATE系统函数对字符串进行转换得到，可使用SYSDATE代表当前系统时钟。
例5-139 在emp表中加入一名雇员′stont′。
SQL＞INSERT INTO emp（empno，ename，hiredate，deptno）
VALUES（7963，′stone′，TODATE（′07-apr-87 08：00′，′dd-mon-yy hh24：mi′），30）；
SQL＞INSERT INTO emp（empno，ename，hiredate，deptno）
VALUES（7963，user，sysdate，30）；
如果在INSERT命令中指出列名，则VALUES子句中值的顺序可以与原来建表时列的顺序不一致，但必须与给出的列名的顺序对应。
例5-140 往student表中插入记录，插入顺序与表中列的顺序不一致。
SQL＞INSERT INTO student（sname，sno）VALUES（′张三′，′95020′）
如果想向有主从关系的从表中插入记录，插入行的外键值如果在主表的主键值中没有出现，将产生一个错误信息。系统自动地检查数据的完整性，对破坏完整性的操作拒绝执行。
假如：例5-137中的插入不成功，即student表中没有学号为′95020′的记录，则例5-138的插入就不能成功。
用带查询的INSERT命令可以从另一个表中选择数据插入到表中。
例5-141 在sc表中，把平均成绩超过80分的男学生的学号和平均成绩存入另一个已知基本表SG（sno，avgg）中（必须先建立SG表）。
SQL＞INSERT INTO sg（sno，avgg）
SELECT sno，avg（grade）
FROM sc
WHER E sno in
（SELECT sno
FROM student
WHERE sex′M′）
GROUP BY sno
HAVING avg（grade）＞80
例5-142 把成绩不及格的学生的学号、姓名、课程号和成绩存入另一个已知基本表中。
NoPass（sno，sname，cno，grade）中。（先建立NoPass表）。
SQL＞INSERT INTO NoPass
SELECT s.sno，sname，cno，grade
FROM student s，sc
WHERE grade＜60 and s.sno sc.sno
例5-143 在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表stud1（sno，sname，sex），先建立stud1表。
SQL＞INSERT INTO stud1（sno，sname，sex）
SELECT sno，sname，sex
FROM student s
WHER E NOT EXISTS
（SELECT*
FROM sc
WHERE grade＜80 AND s.snosc.sno）
5.5.1.2 修改数据
使用UPDATE命令修改表中已经存在的行。修改数据的语法如下：
UPDATE 表名
SET 列名1{表达式1|（子查询1）}[，列名2{表达式2|（子查询2）}……]
[WHERE 条件]；
UPDATE语句一次只能修改一张表中的数据。更新语句只能修改基本表中满足WHERE＜条件＞的行中某些列值，把这些行按SET子句的表达式或子查询的值修改相应字段上的值。如果省略WHERE子句，则表示要修改表中所有的元组。
1.不带子查询的更新
例5-144 把c5课程的任课教师改为wu。该语句只修改一个元组的值。
SQL＞UPDATE course
SET teacher′wu′
WHERE cno′c5′；
例5-145 将所有学生的年龄增加1岁。该语句修改所有元组的值。
SQL＞UPDATE student
SET ageage+1；
2.带子查询的更新
例5-146 把计算机系全体学生的成绩置0。
SQL＞UPDATE sc
SET grade0
WHERE′cs′（SELECT dept
FROM student s
WHERE s.snosc.sno）
3.多个表的更新
UPDATE语句一次只能更新一个表，这会产生破坏参照完整性的问题。
例5-147 用下面的语句把student表中的95001的学号改为95008。
SQL＞UPDATE student
SET sno′95008′
WHERE sno′95001′
在执行这个更新语句之后，数据库已处于不一致的状态，因为在sc表中某些记录（即sno′95001′的那些sc记录）的sno值在student表中不存在，这就违反了关系模型的参照完整性。只有执行了下面的更新语句之后，数据库才重新处于一致状态。
SQL＞UPDATE sc
SET sno′95008′
WHERE sno′95001′
因此，必须保证这两个更新语句要么都执行，要么都不执行。在关系数据库中引入事务（Transaction）的概念来解决这个问题（参见5.5.1.4节）。
5.5.1.3 删除数据
删除数据的语法如下：
DELETE FROM＜表名＞[WHERE＜条件＞]；
DELETE语句的功能是从一个基本表中删除满足条件的元组，DELETE只删表中的数据，表的定义仍然在数据字典中。FROM后的表名只能由一个基本表名。
不带WHERE从句时，表中所有的行都会被删除，如果从一张大表中删除所有的行，可能会占用很长时间，并且需要大量的回退空间。此时可以用TRUNCATE语句删除一张表。
不能删除一行的部分信息，只能通过UPDATE命令将一行的某些列值改为空值。
例5-148 在基本表sc中删除尚无成绩的选课元组。
DELETE FROM sc
WHERE grade IS NULL
例5-149 删除学号为0411的学生记录。
DELETE FROM student
WHERE sno′0411′；
如果想从主表中删除含有主键（被另一张表用作外键）的行，主外键在定义时，如果没有ONDELETE CASCADE约束（即删除含有子记录的主记录），将产生一个错误信息。
例5-150 删除计算机科学系所有学生的选课记录。
DELETE FROM sc
WHERE′CS′（SELETE dept
FROM student
WHERE student.snosc.sno）；
执行删除操作也可能破坏数据的完整性。例如：如例5-149在student表删除了学号为0411的记录，此时在sc表中仍然有学号0411的选课记录，这就违反了关系模型的参照完整性。所以在执行完例5-149的DELETE语句后，必须执行下面的语句，删除0411所有的选课记录。
DELECT FROM sc
WHERE sno′0411′；
只有执行上面两个删除语句之后，数据库才重新处于一致状态。
5.5.1.4 事务控制
几个表之间通过参照完整性可以建立联系，但是由于增、删、改操作只能对一个表操作，这会引起被参照表或参照表中的数据与参照表或被参照表中的数据不一致问题。数据库系统引入了事务概念来解决这个问题，保证数据库的完整性。
事务是用户定义的一个数据库操作序列，几条语句组成一个事务，该事务内的语句要么都做，要么都不做，是一个不可分割的工作单位。例如：从存款账户上撤回资金并存入支票账户，这两步操作需要一起完成，否则两边的资金对不上账，所以需要在一个事务中完成。
当第一条可执行的SQL语句执行时开始一个事务。
当执行下列语句时结束一个事务：COMMIT或ROLLBACK；执行DDL（如CREATE）或DCL命令；错误发生（如死锁）、退出SQL*Plus或系统崩溃时。
在一个事务结束时，下一条可执行的SQL语句又开始一个事务。
事务处理可以分隐式事务处理和显式事务处理两类。
隐式事务处理工作是由系统根据相应的操作自动完成提交或回退工作，不需要用户干预。
显式事务处理工作是由用户发相应的提交或回退命令而结束一个事务。当执行COMMIT命令时，结束当前的事务，使得所有悬挂的数据改变永久保存。当执行ROLLBACK命令时，结束当前的事务，回退所有的修改操作。
例5-151 从student表中删除学生95001的信息时，应该将sc表中95001的选课信息也一起删除（因为有参考完整性约束），此时只能通过一个事务内的两条DELETE 语句来完成。
SQL＞DELETE FROM student WHERE sno′95001′
SQL＞DELETE FROM sc WHERE sno′95001′
SQL＞COMMIT；（或ROLLBACK；）
这两条DELETE语句必须在一个事务内，要么一起提交，即两个DELETE操作都完成；要么一起回退，即两个DELETE操作都失败。
1.提交（COMMIT）
在执行COMMIT命令之后，数据的状态如下：
只有提交之后，对表的插入、删除和修改才会永久性地保存。
提交以前，只有修改数据的会话才能看到数据的改变，而其他会话看到的是修改前的数据。提交后，其他会话才能同样看到修改后的数据。
释放在修改的行上的锁，这些行可以被其他会话操作。
COMMIT命令有显式、隐式和自动提交三种方式。
显式COMMIT方式：在命令行中写明COMMIT指令（如：SQL＞COMMIT）。执行COMMIT命令之后，在此命令之前的所有操作全部提交，事务结束。
隐式COMMIT方式：当事务中的SQL语句为DDL命令或DCL命令，或者正常退出SQL*Plus时，Oracle自动地在该事务后隐式地发出COMMIT语句，提交所有的操作，结束该事务。隐式发出COMMIT的SQL命令有：ALTER、AUDIT、COMMENT、CONNECT、CREATE、DISCONNECT、DROP、EXIT、GRANT、NOAUDIT、QUIT、REVOKE、RENAME等。
自动提交方式：将环境变量AUTOCOMMIT设置为ON。
设置为自动提交后，使用INSERT、UPDATE或DELETE对数据进行的更新将立即提交。可以用SQL*Plus的SET命令来设置自动提交方式。
SQL＞SET AUTOCOMMIT ON
或
SQL＞SET AUTOCOMMIT IMMEDIATE
若取消自动提交方式，可使用下列命令：
SQL＞SET AUTOCOMMIT OFF
OFF是系统的默认值，默认是没有自动提交环境。
2.回退（ROLLBACK）
在执行ROLLBACK命令后，数据的状态如下：
取消该事务所做的一切更新，结束当前的事务。
撤销数据的修改。
重新装入数据以前的状态，恢复到该事务起点的状态。
释放在修改的行上的锁。
ROLLBACK命令也有显式回退和隐式回退两种。
显式回退就是在命令行输入ROLLBACK，撤销所有的改变，结束事务。
隐式回退是在非正常中断SQL*Plus或系统失败时，系统自动回退当前的操作，结束事务。
5.5.2 视图的数据操纵
5.5.2.1 视图更新操作的限制
对视图的查询（SELECT语句）与基本表相同，但是更新操作则受到下列几条规则的限制。
如果视图是从多个基本表使用联结操作导出的，则不允许更新。
如果定义视图的SELECT语句包含GROUP BY、DISTINCT、聚组函数，则不允许更新。
如果视图是从单个基本表使用选择和投影操作导出的，并且定义视图的SELECT语句包括基本表的主键或某个候选键，包含基表中所有含有NOT NULL约束条件的列，不包含聚组函数、不包含GROUP BY 从句、不包含DISTINCT命令、不包含由表达式定义的列（如：SAL*12）、不包含ROWNUM伪列、即视图为行列子集视图，则对视图可以执行插入、更新、删除操作。
如果建立视图时带WITH READ ONLY选项，则不能对该视图进行任何插入、更新和删除操作。
5.5.2.2 允许更新的视图
对视图的更新最终要转换成对基本表的更新，视图DML操作的语法与基本表的DML操作语法一样。（更新包括INSERT，UPDATE和DELETE三类操作。）
例5-152 建立一个只包含了计算机系学生信息的视图CSstudent。
SQL＞CREATE VIEW csstudent
AS SELECT sno，sname，dept，age，sex
FROM student
WHERE dept′CS′
由于该视图只从一个基本表导出，并且包含了该表的主键，所以对该视图的更新操作可以转换成对基本表的更新操作。
1.对视图的修改
例5-153 对csstudent视图的更新，将其中学号为95001的学生的姓名改为WANG PING。
SQL＞UPDATE csstudent
SET sname′WANG PING′
WHERE sno′95001′；
上述更新语句将转换成对基本表student的更新，语句如下：
SQL＞UPDATE student
SET sname′WANG PING′
WHERE dept′CS′AND sno′95001′；
2.对视图的插入
例5-154 向csstudent视图插入一条新记录。
SQL＞INSERT INTO csstudent
VALUES（′95008′，′李四′，′CS′，19，′M′）；
上述语句将转换成对基本表student的插入，语句如下：
SQL＞INSERT INTO student
VALUES（′95008′，′李四′，′CS′，19，′M′）；
3.对视图的删除
例5-155 从CSstudent视图中删除一条记录。
SQL＞DELETE FROM csstudent
WHERE age＞20
上述语句将转换成对基本表student的删除，语句如下：
SQL＞DELETE FROM student
WHERE age＞20 AND dept′CS′
5.5.2.3 不能更新的视图
对复杂视图只能执行查询操作，不能执行DML操作，因为一次只能更新一张基表。有些视图的更新不能唯一地有意义地转换成对基本表的更新。
例5-156 为每个学生选课（grade非空）的门数及平均成绩建立一个视图：
SQL＞CREATE VIEW sgrade（sno，cnum，avgg）
AS SELECT sno，count（cno），avg（grade）
FROM sc
WHERE grade IS NOT NULL
GROUP BY sno；
由于sgrade中的一个元组是由sc表中若干行经过求平均得到的，因此对视图sgrade的更新就无法转换成对sc表的更新，所以视图sgrade是不可更新的。
例5-157 为每个学生选课的成绩和课程名称建立一个视图。
SQL＞CREATE VIEW studentgrade（sno，sname，cno，grade）
AS SELECT s.sno，sname，cno，grade
FROM student s，sc，course c
WHERE S.snosc.sno AND sc.cnoc.cno
由于该视图是从三个基本表联结而得到的，因此对该视图的更新不能转换成对任一个表的更新，所以不能对该视图进行更新。
5.6 SQL数据控制
DBMS提供统一的数据控制功能是数据库系统的特点之一。SQL中数据控制功能包括事务管理功能和数据保护功能，即数据库的恢复、并发控制、数据库的安全性和完整性控制。
本章介绍的数据控制功能是指SQL语言的安全性控制功能，即权限的授予和回收，控制用户执行SQL语句的功能。
SQL数据控制功能是指控制用户对数据的存取权力。某个用户对某类型数据具有何种操作权是由DBA决定的，这是管理问题而不是技术问题。DBMS的功能是保证这些决定的执行，它必须具有下列功能：
把授权和回收权力决定告诉系统，由SQL的GRANT和REVOKE语句来完成。
把授权和回收的结果存入数据字典。
当用户提出操作请求时，根据字典中的授权情况进行检查，以决定是否执行操作请求。
5.6.1 授权
Oracle数据库的权限可分为对象权限、系统权限两类。对象权限是指操作具体某一表、视图等对象所需要的权限；系统权限是指完成某一特定操作（如：建立表）所需要的权限。系统权限的授予和回收在第8章安全管理中介绍。本章介绍的授权工作是针对对象权限的授予和回收。
授予对象权限是通过SQL语言的GRANT语句实现的，一般格式如下：
GRANT＜权限＞[，＜权限＞]……
[ON＜对象名＞]
TO＜用户＞[，＜用户＞]……
[WITH GRANT OPTION]
表示将对指定操作对象的指定操作权限授予指定的用户。
注意：用户可以是多个用户，也可以是PUBLIC，表示给多个用户或所有用户授权；授予属性列的权限时必须明确指出列名，表示将这一列的操作权限授予给用户；指定WITH GRANT OPTION子句，则获得权限的用户还可以将此权限授予给其他用户，否则获得权限的用户只能使用该权限，不能传播此权限；建表权限（CREATE TABLE）属于DBA，DBA将此权限授予普通用户，普通用户可建立基本表；基本表的属主拥有对表的一切操作权限。
对不同的操作对象有不同的操作权限。
对基本表有插入、更新、删除、查询、修改表结构和建立索引六种操作权限，如果是ALL PRIVILEGES表示这六种权限的总和。
例5-158 将student表上的所有操作权（即插入、删除、更新、查询权限）授予所有用户。
SQL＞GRANT SELECT ON student TO PUBLIC；
例5-159 将student表和course表上的所有操作权（即插入、删除、更新、查询权限）授予用户USER1和USER2。
SQL＞GRANT ALL PRIVILEGES ON student，course TO USER1，USER2；
例5-160 将student表上的查询权限和该表的sno列上的更新权限授予用户USER4。
SQL＞GRANT UPDATE（sno），SELECT ON student TO USER4；
例5-161 将sc表上的插入权限授予用户USER5，并允许USER5将该插入权限授予给其他用户。
SQL＞GRANT INSERT ON SN TO USER5 WITH GRANT OPTION；
例5-162 将建立表的权限授予用户USER6。
GRANT CREATE TABLE TO USER6；
例5-163 将基本表student和视图AVGGRADE上的所有操作权授予用户USER5。
GRANT ALL PRIVILEGES ON student，avggrade TO USER5；
注意：一条GRANT语句可以完成同一对象类型的基本表、视图、属性列等不同对象的授权，但不同对象类型的授权必须分开。
例5-163将基本表和视图（对象类型都是TABLE）的授权工作放到一条GRANT语句中实现。但例5-162和例5-163的授权语句不能放到一条GRANT语句中实现，因为对象类型不同。
5.6.2 回收权限
授予用户的权限可以由DBA或授权者用REVOKE语句收回。REVOKE语句的一般格式如下：
REVOKE＜权限＞[，＜权限＞]……
[ON＜对象名＞]
FROM＜用户＞[，＜用户＞]……；
表示从指定的用户处回收对指定对象的指定操作权限。
注意：DBA拥有对数据库中所有对象的所有权限，并可以根据需要将不同的权限授予不同的用户；用户对自己建立的基表和视图拥有全部的操作权限，并可将这些权限授予给别人；所有权限都可用REVOKE语句收回。回收操作有级联关系。
例5-164 将用户USER2 对student表的sno列的更新权限回收。
SQL＞REVOKE UPDATE（sno）ON student FROM USER2；
例5-165 将所有用户对student表的查询权限回收。
SQL＞REVOKE SELECT ON student FROM PUBLIC；
例5-166 将USER5对sc表的插入权限回收。
SQL＞REVOKE INSERT ON sc FROM USER5；
注意：回收操作有级联关系。假如用户USER1（student表的拥有者）将student表的插入权限授予给用户USER2，并允许USER2将该插入权限传播给别人。如果USER2又将此插入权限授予给用户USER3，并允许USER3将该插入权限传播给USER4，则此时系统中用户USER2、USER3、USER4都拥有对student表的插入权限。现在USER1将USER2的插入权限回收，此时USER2、USER3、USER4对student表的插入权限也都被回收。但假如USER3和USER4从其他用户处获得对student表的插入权，则他们仍然具有对student表的插入权限。系统只回收直接或间接从USER1处获得的权限。
例5-167 举例说明回收操作的级联关系。
第一步，USER1给USER2授权：
SQL＞GRANT INSERT ON student TO USER2 WITH GRANT OPTION
第二步，USER2给USER3、USER4授权：
SQL＞GRANT INSERT ON student TO USER3 WITH GRANT OPTION
SQL＞GRANT INSERT ON student TO USER4 WITH GRANT OPTION
第三步，USER1从USER2处回收权限：
SQL＞REVOKE INSERT ON student FROM USER2
此时，USER3、USER4同时被回收了INSERT权限。
5.7 嵌入式SQL
5.7.1 SQL语言的运行环境
1.SQL语言的使用方式
SQL语言有两种使用方法：交互式使用和嵌入式使用。
交互式使用就是在联机终端上直接交互使用SQL语句。嵌入式使用就是将SQL语句嵌入到其他宿主语言编写的程序中，作为宿主语言的子语言，使宿主语言具备访问数据库的能力。这两种使用方法语法结构基本一致，但细节上有差别。特别是SELECT语句，在程序设计的环境下要做必要的扩充。
2.宿主语言与SQL语言的差别
宿主语言与SQL语言有很大差别，主要体现在以下两个方面。
SQL语言不能直接使用指针、数组等数据结构，而是按记录集合进行处理，是非过程性的语言。一般用SQL语言对数据库进行存取操作。
高级语言一般不能直接进行集合的操作，而是按每条记录进行处理，本身不能直接识别SQL语句。一般用宿主语言进行程序流程的控制。
3.嵌入式SQL的处理方式
嵌入式SQL的实现有两种处理方式：一是扩充宿主语言的编译程序，使之能够处理SQL语句；二是采用预编译方式，先用预编译器（预编译器是DBMS的组成部分）对源程序进行扫描，识别出SQL语句，把SQL语句转换成宿主语言的函数调用语句，然后再用宿主语言的编译程序把预编译后的输出文件编译成目标文件，最后联结目标文件，产生可执行文件。
目前多数数据库系统采用预编译方式处理SQL。
5.7.2 嵌入式SQL的使用规定
把SQL嵌入主语言使用时，必须解决两个问题：一是如何区分程序中的SQL语句与宿主语言语句，二是如何实现SQL语句与宿主语言之间的通信问题。
5.7.2.1 如何区分程序中的SQL语句与宿主语言语句
在所有的SQL语句前加前缀标识EXEC SQL，并以某种符号作为结束标志。嵌入的SQL语句的格式如下：
EXEC SQL＜SQL语句＞结束标志
SQL语句的结束标志随着宿主语言的不同而不同。在C和Pascal语言程序中规定结束标志用分号“”：
EXEC SQL＜SQL语句＞；
而在COBOL语言中结束标志为ENDEXEC：
EXEC SQL＜SQL语句＞ENDEXEC
5.7.2.2 SQL语句和宿主语言之间的通信
SQL语句负责操纵数据库，是非过程化语言。宿主语言负责控制程序流程和对SQL查询结果做进一步的处理，是过程化语言。SQL语言与宿主语言之间进行通信实现信息传递的方式一般有三种：通过主变量、通过SQL通信区SQCA和通过游标。
1.通过主变量（或称为共享主变量）
通过主变量可以实现宿主语言向SQL语言提供参数，以及SQL语句查询结果交给宿主语言进一步处理。这些主变量由宿主语言程序定义，再用SQL的DECLARE语句说明，随后SQL语句就可以使用这些主变量。
例5-168 在C语言中可以用下列形式说明主变量。
EXEC SQL BEGIN DECLARE SECTION；
Char nunber[8]，name[10]；
Char sqlstate[6]；
EXEC SQL END DECLARE SECTION；
在SQL语句中引用这些主变量时必须在变量名前加冒号（：）作为前缀，以区别于数据库中的字段名。在宿主语言中使用这些主变量时不用加冒号（：）作前缀。
例5-169 在C语言中查询某一学生的姓名，并输出该学生的学号和姓名：
EXEC SQL SELECT sname
INTO：vname/*SQL语句中引用主变量*/
FROM student
WHERE sno：vnumber；/*SQL语句中引用主变量*/
Printf（″%s，%s″，vnumber，vname）；/*宿主语言中引用主变量*/
2.通过SQL通信区（SQLCA）
通过SQL通信区（SQLCA）可以实现向宿主语言传递SQL语句的执行状态信息。SQL语句执行后，系统要反馈给应用程序若干信息，包括当前的工作环境及语句执行是否成功等状态信息。这些信息送到SQL的通信区SQLCA。应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句。
SQLCA是用EXEC SQL INCLUDE SQLCA语句加以定义的。在SQLCA中有一个状态指示字段SQLCODE。当SQLCODE为预定义的常量SUCCESS时，表示SQL语句执行成功，否则返回一个错误代码（负值）或警告信息（正值），程序员应该在每个SQL语句之后测试SQLCODE的值，以便了解语句执行结果，并采取相应措施。例如：
EXEC SQL INCLUDE SQLCA；/*定义SQL通信区*/
……
if（sqlca.sqlcode＜＞SUCCESS）/*利用SQLCA中状态信息决定何时退出循环*/
3.通过游标（CURSOR）
（1）通过游标显示查询结果
通过游标（CURSOR）可以将SQL语句查询数据库的结果交给宿主语言进一步处理。SQL语言是面向集合的，可以产生或处理一组记录。宿主语言是面向记录的，一次只能处理一个记录。集合处理方式与单记录处理方式之间必须协调，可以用游标来协调这两种不同的处理方式，把集合操作转换成单记录处理方式。
游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标有一个名字。通过游标逐一获取记录，并赋给主变量，交给宿主语言处理。
与游标有关的SQL语句有4个。
①游标定义语句（DECLARE语句）
游标是与某一查询结果集相联系的符号名，游标用SQL的DECLARE语句定义，其中的SELECT语句并不立即执行。DECLARE语句的语法如下：
EXEC SQL DECLARE＜游标名＞CURSOR FOR＜SELECT 语句＞；或[ENDEXEC]
②游标打开语句（OPEN语句）
该语句执行游标定义中的SELECT语句，同时游标处于活动状态。游标是一个指针，此时指向查询结果的第一行。OPEN语句的语法如下：
EXEC SQL OPEN＜游标名＞；或[ENDEXEC]
③游标推进语句（FETCH语句）
执行该语句使游标向前推进一行，并把游标指向的行（称为当前行）中的值取出，放到语句说明中对应的主变量中。FETCH语句常常被用在宿主语言程序的循环程序中，并借助宿主语言的处理语句逐一处理查询结果集中的每一行。FETCH语句的语法如下：
EXEC SQL FETCH＜游标名＞INTO＜变量表＞[＜指示变量＞]；或[ENDEXEC]
变量表由用逗号分开的主变量组成。
④游标关闭语句（CLOSE语句）
该语句用于关闭游标，释放结果集占用的缓冲区和其他资源，使它不再和查询结果相联系。关闭的游标可以再次被打开，与新的查询结果相联系。CLOSE语句的语法如下：
EXEC SQL CLOSE＜游标名＞或[ENDEXEC]
以上这三种通信方式可以同时在一个程序中使用。
例5-170 在student表和sc表中检索某学生（姓名由主变量givesname给出）的学习成绩信息（sno，sname，cno，grade）。
2.通过游标修改或删除当前元组
在游标处于活动状态时，可以用CURRENT形式的UPDATE语句和DELETE语句修改或删除游标所指向的记录。具体步骤如下。
①用DECLARE语句说明游标，其中SELECT语句采用FOR UPDATE OF＜列名＞，表明检索出的数据在指定列上可修改。
②用OPEN 语句打开游标，把所有满足条件的记录取到缓冲区。
③用FETCH语句推进游标指针，并将当前记录从缓冲区送到主变量。
④检查该记录是否是要修改或删除的记录，如果是，就用UPDATE或DELETE语句进行修改或删除。但UPDATE和DELETE语句必须采用WHERE CURRENT OF＜游标名＞，表明要修改或删除的记录是游标所指向的记录。
第③、④步循环处理（依据SQLCA工作区中返回的SQLCODE代码控制循环）。
⑤处理完毕用CLOSE语句关闭游标，释放缓冲区和其他资源。
例5-172 查找sc表，对找到的成绩作如下操作：如果成绩不及格，则删除该记录；如果成绩在60～69分，则将成绩修改为70分，再显示该学生的成绩信息。
#define NOmoretuples！（strcmp（sqlcode，″02000″））
Void sel（）
{EXEC SQL BEGIN DECLARE SECTION
char sno1[5]，cno1[9]，givensno[5]
int g1
char sqlcode[6]
EXEC SQL END DECLARE SECTION
gets（givensno）
EXEC SQL DECLARE scx CURSOR FOR
SELECT sno，cno，grade
FROM sc
FOR UPDATE OF grade
EXEC SQL OPEN scx
while（1）
{EXEC SQL FETCH scx INTO：sno1，cno1，g1
IF（no-more-tuples）break
IF（g1＜60）
EXEC SQL DELETE FROM sc WHERE CURRENT OF scx
ELSE
{IF（g1＜70）
{EXEC SQL UPDATE sc SET grade70 WHERE CURRENT OF scx
g170
}
printf（″%s，%s，%d″，sno1，cno1，g1）
}
}
EXEC SQL CLOSE scx
}
小结
SQL语言是关系数据库的标准语言，各个数据库厂商都支持标准SQL语言，并在此基础上进行了扩充。本章介绍的是标准SQL语言及在Oracle系统中的扩充。
SQL语言可以分为数据定义语言、数据查询语言、数据更新语言和数据控制语言四部分。
SQL的数据定义部分包括基本表、视图、索引的创建、修改和撤销。
SQL的数据查询是本章要求重点掌握的内容，包括单表查询、联结查询、嵌套查询、相关子查询等几种方式，查询语句中可以使用聚组函数进行统计、使用分组子句对查询结果按某一属性的值分组，使用排序子句将输出结果按指定的属性进行排序。在查询时可以使用各种限定条件抽取满足条件的记录进行计算。
SQL的数据更新又包括插入、修改和删除操作。对视图的更新有一定的限制条件。
最后介绍嵌入式SQL的使用规定和使用技术，介绍如何使用游标实现SQL语言和宿主语言之间的数据交换和通信。
本章的内容应该进行上机练习，上机的重点是SQL的数据定义和数据查询、更新操作。
习题
1.简述SQL语言的特点。简述SQL语言的DDL、DML、DCL的功能。
2.什么是基本表？什么是视图？两者的区别和联系是什么？
3.哪类视图可以更新？哪类视图不允许更新？
4.教学数据库有以下三张基本表：
s（sno，sname，age，sex），即（学号、姓名、年龄、性别）
c（cno，cname，teacher），即（课程号、课程名称、教师名）
sc（sno，cno，grade），即（学号、课程号、成绩）
试用SQL查询语句表达下列查询。
（1）检索LIU老师所授课程的课程号和课程名。
（2）检索年龄大于23岁的男学生的学号和姓名。
（3）检索至少选修LIU老师所授课程中一门课程的女学生姓名。
（4）检索WANG同学不学的课程的课程号。
（5）检索至少选修两门课程的学生学号。
（6）检索全部学生都选修的课程的课程号与课程名。
（7）检索选修课程包含LIU老师所授课程的学生学号。
（8）统计有学生选修的课程门数。
（9）求选修c4课程的学生的平均年龄。
（10）求LIU老师所授课程的每门课程的平均成绩。
（11）统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
（12）检索学号比WANG同学大，而年龄比他小的学生姓名。
（13）检索姓名以WANG打头的所有学生的姓名和年龄。
（14）在sc表中检索成绩为空值的学生学号和课程号。
（15）求年龄大于女同学平均年龄的男学生姓名和年龄。
（16）求年龄大于所有女同学年龄的男学生姓名和年龄。
5.用数据定义语言DDL定义第4题中的3张表。
6.用数据操纵语言DML完成下列对第4题的3张表的各种更新操作：
（1）往基本表s中插入一个学生记录（‘s9’，‘WU’，18，‘女’）。
（2）在基本表s中检索每一门课程成绩都大于等于80分的学生学号、姓名、性别，并把检索到的值送往另一个已存在的基本表student（s#，sname，sex）。
（3）在基本表sc中删除尚无成绩的选课记录。
（4）把WANG同学的学习课程和成绩全部删除。
（5）把选修MATHS课不及格的成绩全改为空值。
（6）把低于总平均成绩的女同学的成绩提高5%。
（7）在基本表sc中修改c4课程的成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%（用两个UPDATE语句实现）。
7.在仓库管理数据库中有五个关系模式：
零件PART（P#，PNAME，COLOR，WEIGHT）
项目PROJECT（J#，JNAME，DATE）
供应商SUPPLIER（S#，SNAME，SADDR）
供应P-P（J#，P#，TOTAL）
采购P-S（P#，S#，QUANTITY）
（1）试用SQL DDL语句定义上述5张基本表，并说明主键和外键。
（2）试将PROJECT、P-P、PART三个基本表的自然联结定义为一个视图VIEW1，将PART、P-S、SUPPLIER三个基本表的自然联结定义为一个视图VIEW2。
（3）试在上述两个视图的基础上进行数据查询：
①检索上海的供应商所供应的零件的编号和名字；
②检索项目J4所用零件的供应商编号和名字。
8.对于第4题教学数据库中基本表sc，已建立下列视图：
CREATE VIEW sgrade（s#，cnum，avggrade）
AS SELECT sno，count（*），avg（grade）FROM sc
GROUP BY sno
请判断下列查询和更新是否允许。若允许，写出转换到基本表sc上的相应操作。
（1）SELECT*FROM sgrade
（2）SELECT s#，cnum FROM sgrade WHERE avggrade＞80
（3）SELECT s#，avggrade FROM sgrade
WHERE cnum＞（SELECT cnum FROM sgrade WHERE s#′s4′）
（4）UPDATE sgrade SET cnumcnum+1 WHERE s#′s4′
（5）DELETE FROM sgrade WHERE cnum＞4
9.对于第4题教学数据库的三张基本表，SQL语句完成下列授权操作。
（1）把对s基本表的INSERT权限授予用户张勇，并允许他再将此权限授予其他用户。
（2）把查询sc表和修改grade属性的权限授予给用户吴明。
10.在嵌入式SQL中是如何区分SQL语句和主语言语句的？
11.在嵌入式SQL中是如何协调SQL语言的集合处理方式和主语言的单记录处理方式的？
12.在SQL命令中可以使用的Oracle函数有哪几类？
