SQL高级应用

 

SQL 高级应用

1 、嵌入式SQL 的使用

SQL是非过程的、面向集合的数据操纵语言，它大部分语句的使用都是独立的，与上下文条件无关的。在事务处理中，常常需有流程控制，即需要程序根据不同的条件执行不同的任务，如果单单使用SQL语言，很难实现这类应用。另一方面，高级语言在涉及数据库操作时，不能高效地进行数据的存取。所以，嵌入式SQL的使用，结合了高级语言的过程性和SQL语言的数据操纵能力，可提高数据库应用程序的效率。

DBMS有两种方法处理嵌入式 SQL语言：预编译和扩充编译程序法。

预编译是由DBMS的预编译器对源程序进行扫描，识别出其中的SQL语句，把它们转换为宿主语言调用语句，使宿主语言编译器能够识别，最后由编译器将整个源程序编译为目标码。

扩充编译程序法是修改和扩充宿主语言的编译程序，使其能够直接处理SQL语句。

目前使用较多的是预编译方法，嵌入式SQL语句的处理过程其中关键的一步，是将嵌有SQL的宿主语言源代码通过预编译器（Precomplier）变成纯宿主语言源代码。RDBMS除了提供SQL语言接口外，一般都提供一批用宿主语言编写的SQL函数，供应用程序调用DBMS的各种功能。如建立与DBMS的连接及连接的环境、传送SQL语句、执行SQL语句、返回执行结果和状态等等。这些函数组成SQL函数库。预编译器将SQL语句编译成宿主语言对SQL函数的调用，从而把嵌有SQL的宿主语言源代码变成纯宿主语言源代码，在编译连接后执行。

1.1 、嵌入式SQL 的一般形式

在交互式和嵌入式两种不同的使用方式下，SQL语言的语法结构基本相同。

各个DBMS在实现嵌入式SQL时，对不同的宿主语言，所用的方法基本上是相同的。

预编译器不能够检查宿主语言的语法合法性，它所能做的是查找表示“嵌入式SQL从这里开始”以及“嵌入式SQL在这里结束”的信号。所以嵌入式SQL一般都具有一个前缀和一个结束符。

下面以SQL嵌入C语言为例，说明实现嵌入式SQL的一般方法。

在 C 语言中嵌入的 SQL 语句以 EXEC SQL 开始，以分号“；”结尾：

EXEC SQL <SQL语句>；

EXEC SQL的大小写没有关系。在 EXEC SQL和分号之间只能包含有效的 SQL语句，不能有任何宿主语言语句。

嵌入式SQL语句按照功能的不同，可分为可执行语句和说明性语句。宿主语言程序中，任何允许说明性编程语句出现的地方，都可以出现说明性SQL语句；任何可出现可执行编程语句的地方，都允许出现可执行SQL语句。

1.2 、嵌入式SQL 与宿主语言之间的信息传递

在DBMS和宿主语言程序之间的数据传递，是通过宿主语言程序变量，简称主变量来实现的。

（1 ）主变量

当SQL语句引用主变量时，变量前应加冒号“：”，以区别于数据库对象名（如：列名、表名、视图名等），因此主变量可与数据库变量同名。在宿主语言中引用主变量时，不须加冒号。

通过主变量，宿主语言可向SQL语句提供参数，如指定向数据库中插入（或修改）的数据；另一方面，SQL语句应能对主变量赋值或设置状态信息，返回给应用程序，使应用程序得到SQL语句的结果和状态。

在嵌入式程序中，所有的主变量，除系统定义的外，都必须预先加以说明，说明放在两个嵌入式SQL语句之间：

    EXEC SQLBEGIN DECLARE SECTION；

        ……

    EXEC SQLEND DECLARE SECTION；

中间的内容称为说明段。说明段中变量说明的格式必须符合宿主语言的要求，而且变量的数据类型应该是宿主语言和SQL都能处理的类型

（2 ）SQL 通信区

在主变量中，有一个系统定义的主变量，叫SQLCA，SQLCA是一全局变量，供应用程序和DBMS通信之用。SQLCA变量不需加以说明，只需在嵌入的可执行SQL语句前加INCLUDE语句就能使用。其格式为：

EXEC SQLINCLUDE SQLCA；

SQLCA.SQLCODE是SQLCA的一个分量，属于整数类型，是供DBMS向应用程序报告SQL语句的执行情况。每执行一条SQL语句，返回一个SQLCODE代码。因此在应用程序中，每执行一条SQL语句后，都应测试SQLCODE的值，用来了解该SQL语句的执行情况，并执行相应的操作。

不同的系统，SQLCODE代码值的含义可能不完全相同。一般约定：

SQLCODE=0，表示语句执行成功，无异常情况；

SQLCODE为负整数，表示SQL语句执行失败，具体负值表示错误的类别；

SQLCODE为正整数，表示SQL语句已执行，但出现了例外情况。如SQLCODE=100，表示语句已执行，但无数据可取（如：DB中无满足条件的数据）。

带 INTO 子句的 SELECT 语法是嵌入式 SQL 特有的。

EXEC SQL SELECT COUNT（*）INTO：emp_num

    FROM emplolee WHERE eno=：eno;

1.3 、游标

SQL语言和宿主语言的不同数据处理方式，是通过游标（Cursor）来协调的。游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，由宿主语言作进一步的处理。

游标的操作包括四个步骤：

（1 ）说明游标

用DECLARE语句为一条SELECT语句定义游标，其格式为：

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

其中的SELECT语句，既可以是简单查询，也可以是连接查询或嵌套查询，它的结果集是一个新的关系。当游标向前“推进”（fetch）时，可以依次指向该新关系的每条记录。

说明游标仅仅是一条说明性语句，此时DBMS并不执行SELECT指定的查询操作。

（2 ）打开游标

使用OPEN语句，将打开指定游标。其一般格式为：

    EXEC SQL OPEN <游标名>；

打开游标，将执行相应的SELECT语句，把满足查询条件的所有记录，从表中取到缓冲区中。此时游标被激活，指针指向结果集中的第一条记录。

（3 ）推进游标指针并取当前记录

使用FETCH 语句，将缓冲区中的当前记录取出送至主变量供宿主语言进一步处理。同时，把游标指针向前推进一条记录。其语句的一般格式为：

    EXEC SQL FETCH <游标名> INTO <主变量名列表>；主变量名列表由逗号分开，并与SELECT语句中的目标列表达式必须一一对应。推进游标的目的是为了取出缓冲区中的下一条记录。因此FETCH 语句通常用在一个循环结构语句中，逐条取出结果集中的所有记录进行处理。如果记录已被取完，SQLCA.SQLCODE返回值为100。

（4）关闭游标

用CLOSE语句关闭游标，释放结果集占用的缓冲区及其他资源。但是，被关闭的游标可以用OPEN语句重新初始化，与新的查询结果相联系。关闭游标的一般格式为：

    EXEC SQLCLOSE <游标名>；

1.4 、通过游标的更新和删除

用UPDATE语句或DELETE语句修改或删除游标指向的记录时，要用

    WHERE CURRENT OF <游标名>

在定义游标时，如果是为使用游标的UPDATE语句说明游标，在SELECT语句中要用

    FOR UPDATE OF <列名>

指明检索出的数据在指定列是可修改的。但是为使用游标的DELETE语句定义游标时，则不必使用该子句。

2 、事务

事务是RDBMS提供的一种特殊手段。通过这一手段，应用程序将一系列的数据库操作组合在一起，作为一个整体执行，以保证数据库处于一致（正确）状态。

2.1 、事务的概念

所谓事务，就是用户定义的一个数据库操作序列，是一个不可分割的工作单位。不论有无故障，数据库系统必须保证事务的正确执行——或者执行整个事务或者属于该事务的操作一个也不执行。一个事务，可以是一个SQL语句、一组SQL语句或整个程序。一般地，一个程序中包含有多个事务。

事务的开始和结束可以由用户显式控制。如果没有显式地定义事务，则由具体的DBMS按缺省规定自动划分事务。

在SQL语言中，处理事务的语句有以下三条：

    BEGIN TRANSACTION   

COMMIT    

ROLLBACK

2.2 、事务的特性

为了保证数据完整性，要求数据库系统维护以下事务性质：

1）原子性（Automicity）事务在逻辑上是数据库的最基本工作单位，一个事务中包含的操作，要么全部执行并正常结束；要么什么都不做，好像此事务从未发生过一样。

2）一致性（Consistency）事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。如果在事务的执行过程中，由于硬件或者软件的故障，而使事务的执行中断，就会造成数据库的不一致性。因此事务的原子性是一致性的保证。

3）隔离性（Isolation）数据库系统中多个事务可以被同时执行（并发执行），但必须保证一个事务的执行不能被其他事务干扰。即一个事务内部的操作及其使用的数据对其他事务是隔离的。这样，每个事务都感觉不到系统中有其他事务在并发地执行。

4）持久性（Durability）一个事务一旦提交（即正常完成），它对数据库中数据的改变是持久的，即使数据库因故障而受到破坏，DBMS也应能正确地恢复之。事务的以上四个特性也称为ACID特性。保证事务的ACID特性是数据库系统的重要任务之一。

3 、SQL 中的存储过程

目前，大部分的RDBMS都提供了存储过程和触发器，用来解决应用中的一些较复杂的业务规则。

在C/S和B/S结构的软件开发中，SQL语言是前台应用程序和后台数据库服务器之间的主要编程接口。使用SQL程序时，可用两种方法存储和执行程序。一是可以在前台存储程序，并创建向后台服务器发送命令并处理返回结果的应用程序；二是将程序存储在数据库中保存为存储过程，在前台创建执行存储过程并处理结果的应用程序。

数据库中的存储过程与其他编程语言中的过程类似，存储过程可以：

· 接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理；

· 包含执行数据库操作（包括调用其他过程）的编程语句；

· 向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）。

存储过程是由一组 SQL 语句组成，预先编译后存储在数据库中，可供前台应用程序多次调用。使用存储过程既能方便软件开发，又减少了解释执行SQL语句时句法分析和查询优化的时间，提高了效率。在C/S结构下，应用程序（客户端）只需向服务器发出一个调用存储过程的请求，服务器上就可执行一批SQL命令，中间结果不用返回客户端，大大降低了网络流量和服务器的开销。

3.1 、存储过程的定义

存储过程的定义主要包括两部分：一是过程名及参数的说明；二是过程体的说明。创建存储过程的语句形式如下：

    CREATE PROCEDURE <存储过程名> 

          ［<参数列表>］    

AS         

    <过程体>；

其中，<参数列表>由一个或多个参数说明组成，每个参数说明包括参数名和参数的数据类型，当然，存储过程也可以没有任何参数；<过程体>是实现存储过程功能的一组SQL语句。

3.2 、存储过程的执行

存储过程一旦创建，就作为数据库的一个对象保存在数据库中。用户可以在 RDBMS中执行，但更多的时候是在前台应用程序中调用。

4 、SQL 中的触发器

触发器不仅能实现完整性规则，而且能保证一些较复杂业务规则的实施。所谓触发器就是一类由数据库操作事件（插入、删除、修改）驱动的特殊过程，一旦由某个用户定义，任何用户对该触发器指定的数据进行增、删或改操作时，系统将自动激活相应的触发动作，在数据库服务器上进行集中的完整性控制

4.1 、触发器的组成和类型

触发器的定义包括两个方面：

· 指明触发器的触发事件；

· 定义触发器执行的动作。

触发事件包括表中行的插入、删除和修改，即执行INSERT、DELETE、UPDATE语句。在定义触发器时，必须要指定一个触发器条件，也可以同时指定多种。在修改操作（UP-DATE）中，还可以指定，特定的属性或属性组的修改为触发条件。

事件的触发还有两个相关的时间：AFTER和BEFORE 。AFTER 触发器是在事件发生之后触发，BEFORE触发器是在事件发生之前触发。

触发动作实际上是一系列SQL语句，也被称为触发体，可以有两种方式：

（1）对被事件影响的每一行（FOR EACH ROW）——每一元组执行触发过程，称为行级触发器；

（2）对整个事件只执行一次触发过程（FOR EACH STATEMENT），称为语句级触发器。该方式是触发器的默认方式。

4.2 、创建触发器

（1 ）创建触发器的语句一般格式为

    CREATE TRIGGER <触发器名> ［｛BEFORE AFTER ｝］

           ｛［DELETE!INSERT!UPDATE OF［列名清单］］｝ 

          ON 表名

         ［REFERENCING <临时视图名>］ 

         ［WHEN <触发条件>］       

 <触发动作>      

 ［FOR EACH ｛ROW  STATEMENT｝］

（2 ）说明

BEFORE：指示DBMS在执行触发语句之前激发触发器。

AFTER：指示DBMS在执行触发语句之后激发触发器。

DELETE：指明是DELETE触发器，每当一个DELETE语句从表中删除一行时激发触发器。

INSERT：指明是INSERT触发器，每当一个INSERT语句向表中插入一行时激发触发器。

UPDATE：指明是UPDATE触发器，每当UPDATE语句修改由OF子句指定的列值时，激发触发器。如果忽略OF子句，每当UPDATE语句修改表的任何列值时，DBMS都将激发触发器。

REFERENCING <临时视图名>：指定临时视图的别名。在触发器运行过程中，系统会生成两个临时视图（或者称为虚拟表），分别存放被更新值（旧值）和更新后的值（新值）。对于行级触发器，默认临时视图名分别是OLD 和NEW；对于语句级触发器，默认临时视图名分别是OLD-TABLE和NEW-TABLE。一旦触发器运行结束，临时视图就不存在。

WHEN <触发条件>：指定触发器的触发条件。当满足触发条件时，DBMS才激发触发器。触发条件中必须包含临时视图名，不包含查询