第八部分在实际工作中应用SQL知识

04-13Ctrl+D 收藏本站

关灯小中大繁直达底部

第22章高级SQL主题

第23章 SQL扩展到企业、互联网和内部网

第24章标准SQL的扩展

第22章高级SQL主题

本章的重点包括：

什么是光标

使用存储过程

什么是触发器

动态SQL基础

使用SQL生成SQL

直接SQL与嵌入SQL

调用级接口

前面的章节介绍了SQL的一些基本操作，比如从数据库查询数据、创建数据库结构、操作数据库里的数据，现在我们来介绍一些高级SQL主题，内容包括光标、存储过程、触发器、动态SQL、直接SQL与嵌入SQL、SQL生成SQL。很多SQL实现都支持这些高级特性，增强了SQL的功能。

注意：某些主题不属于ANSI SQL

某些主题并不都属于ANSI SQL，所以其实际语句和规则要取决于具体实现。本章会介绍一些主要厂商的语法以供比较。

22.1 光标

通常，数据库操作被认为是以数据集为基础的操作。这就意味着，大部分ANSI SQL命令是作用于一组数据的。但是，光标则被用于通过以记录为单位的操作，来获得数据库中数据的子集。因此，程序可以依次对光标里的每一行进行求值。光标一般用于过程化程序里嵌入的SQL语句。有些光标是由数据库服务程序自动隐含创建的，有些是由SQL程序员定义的。每个SQL实现里对光标用法的定义是不同的。

下面介绍本书中一直在应用的 3个流行SQL实现的范例：MySQL、SQL Server和Oracle。

MySQL里对光标的声明语法如下所示：

SQL Server里对光标的声明语法如下所示：

Oracle的语法如下：

下面的光标包含了表EMPLOYEE_TBL全部记录的子集：

根据ANSI标准，在光标被创建之后，可以使用如下操作对其进行访问。

OPEN：打开定义的光标。

FETCH：从光标获取记录，赋予程序变量。

CLOSE：在对光标的操作完成之后，关闭光标。

22.1.1 打开光标

要使用光标，必须首先打开光标。当光标被打开时，指定光标的SELECT语句被执行，查询的结果被保存在内存里的特定区域。

在MySQL和Microsoft SQL Server中打开一个光标的语法如下：

在Oracle里的语法如下：

下面的范例会打开光标EMP_CURSOR：

22.1.2 从光标获取数据

在光标打开之后，我们可以使用FETCH语句获取光标的内容（查询的结果）。

在SQL Server里，FETCH语句的语法如下所示：

在Oracle里的语法如下：

MySQL里的语法如下：

下面的FETCH语句把光标EMP_CURSOR里的内容获取到变量EMP_RECORD：

FETCH EMP_CURSOR INTO EMP_RECORD

在从光标获得数据时，需要注意可能会到达光标末尾。不同的实现使用不同的方法来解决这个问题，从而避免用户在关闭光标的时候产生错误。下面是一些伪代码实例，显示了MySQL、Microsoft SQL Server和Oracle如何处理这种情况，帮助读者理解光标的处理过程。

MySQL中的语法如下：

Microsoft SQL Server中的语法如下：

Oracle中的语法如下：

22.1.3 关闭光标

光标可以打开，当然就可以关闭。在光标关闭之后，程序就不能再使用它了。关闭光标是相当简单的。

下面是SQL Server里关闭和释放光标的语法：

在Oracle里，当光标被关闭之后，不必使用DEALLOCATE语句就可以释放资源和姓名。其语法如下：

MySQL的光标也是这样，不必使用DEALLOCATE语句。其语法如下：

注意：高级特性在不同实现间的差别很大

从前面的范例可以看出，不同实现之间的差别很大，特别是高级特性和 SQL扩展（详情请见第24章）。关于光标使用的详细情况请参见具体实现的文档。

22.2 存储过程和函数

注意：释放光标所占据的资源

关闭光标并不一定意味着会释放它所占据的内存空间。在某些实现里，光标占用的内存必须使用DELLOCATE语句才能解除分配。当光标被解除分配时，相关联的内存被释放，而光标的名称可以被再次使用。而在某些实现里，当光标被关闭时，内存会被隐含地解除分配。当光标占据的内容被释放之后，它们可以用于其他操作，比如打开另一个光标。

存储过程是一组相关联的SQL语句，通常被称为函数和子程序，能够让程序员更轻松和灵活地编程。这是因为存储过程与一系列单个SQL语句相比更容易执行。存储过程可以嵌套在另一个存储过程里，也就是说存储过程可以调用其他存储过程，后者又可以调用另外的存储过程，依此类推。

利用存储过程可以实现过程化编程。基本的SQL DDL（数据定义语言）、DML（数据管理语言）和 DQL（数据查询语言）语句（CREATE TABLE、INSERT、UPDATE、SELECT等）只是告诉数据库需要做什么，而不是如何去做。而通过对存储过程进行编程，我们就可以告诉数据库发动机如何处理数据。

存储过程是保存在数据库里的一组SQL语句或函数，它们被编译，随时可以被数据库用户使用。存储函数与存储过程是一样的，但函数可以返回一个值。

函数由过程调用。当函数被过程调用时也可以传递参数，函数会进行所需要的计算，并且把一个值返回给调用它的过程。

当存储过程被创建之后，组成它的各种子程序和函数都保存在数据库里。这些存储过程经过了预编译，可以随时由用户调用。

下面是MySQL创建存储过程的语法：

下面是SQL Server创建存储过程的语法：

Oracle的语法如下所示：

下面是一个很简单的存储过程，它在表PRODUCTS_TBL里插入一行新记录：

SQL Server里执行存储过程的语法如下：

下面是Oracle的语法：

下面是MySQL的语法：

注意：基本SQL命令往往是相同的

可以看出，不同 SQL 实现里对过程进行编程的语法有很大的差别。在不同的SQL实现里，基本的SQL命令应该是相同的，但编程概念（变量、条件语句、光标、循环）可能会有很大不同。

现在执行前面创建的过程：

与单个SQL语句相比，存储过程具有一些明显的优点，包括：

存储过程的语句已经保存在数据库里了；

存储过程的语句已经被解析过，以可执行格式存在；

存储过程支持模块化编程；

存储过程可以调用其他存储过程和函数；

存储过程可以被其他类型的程序调用；

存储过程通常具有更好的响应时间；

存储过程提高了整体易用性。

22.3 触发器

触发器是数据库里编译了的SQL过程，基于数据库里发生的其他行为来执行操作。它是存储过程的一种，会在特定 DML 行为作用于表格时被执行。它可以在 INSERT、DELECT或UPDATE语句之前或之后执行，可以在这些语句之前检查数据完整性，可以回退事务，可以修改一个表里的数据，可以从另一个数据库的表里读取数据。

在大多数情况下，触发器都是很不错的函数，但它们会导致更多的I/O开销。如果使用存储过程或程序能够在较少开销下完成同样的工作，就应该尽量不使用触发器。

22.3.1 CREATE TRIGGER语句

这个语句用于创建触发器。

ANSI标准语法是：

MySQL里使用触发器的语法是：

SQL Server里创建触发器的语法是：

Oracle的基本语法是：

下面是使用Oracle语法编写的一个触发器范例：

前面的范例创建了一个名为EMP_PAY_TRIG的触发器，每当表EMPLOYEE_PAY_TBL里的记录被更新时，它就会在表EMPLOYEE_PAY_HISTORY里插入一条记录。

注意：触发器的内容不能修改

触发器的内容是不能修改的。想要修改触发器，我们就只能替换它或重新创建它。有些实现允许使用CREATE TRIGGER语句替换已经存在的同名触发器。

22.3.2 DROP TRIGGER语句

这个语句可以删除触发器，其语法如下：

22.3.3 FOR EACH ROW语句

MySQL里的触发器还可以调整触发条件。FOR EACH ROW语法可以让过程在SQL语句影响每条记录时都触发，或是一条语句只触发一次。其语法如下所示：

区别在于触发器执行的次数。如果创建了一个普通触发器，执行了一条会影响100行记录的SQL语句时，触发器只会执行一次。如果创建触发器时使用了FOR EACH ROW语法，并且再次执行同样的SQL语句，触发器就会执行100次，也就是SQL语句影响的每条记录都会触发它。

22.4 动态SQL

动态SQL允许程序员或终端用户在运行时创建SQL语句的具体代码，并且把语句传递给数据库。数据库然后就把数据返回到绑定的程序变量里。

为了更好地理解动态SQL，先要来复习一个静态SQL。本书前面介绍的全部都是静态SQL。静态SQL是事先编写好的，不准备进行改变的。虽然静态SQL语句可以保存到文件里以备以后使用，也可以作为存储过程保存在数据库里，但其灵活性还是不能与动态 SQL相比。

使用静态SQL语句的一个问题是，虽然我们可以为终端用户提供大量的语句，但依然可能出现不能满足所有用户需要的情况。动态SQL通常被用于专门的查询工具，允许用户随时创建SQL语句，从而满足特定情况下的特定查询需求。在语句根据用户需要被生成之后，它们被送给数据库，数据库检查语法正确性及所需的权限，对语句进行编译。

使用调用级接口可以创建动态SQL，下一小节将介绍调用级接口。

注意：动态SQL的性能不一定好

虽然动态SQL为终端用户提供了更好的灵活性，但其性能不能与存储过程相比，因为后者已经被SQL优化器进行了解析。

22.5 调用级接口

调用级接口（CLI）用于把 SQL代码嵌入到主机程序，比如ANSI C。程序员应该很熟悉调用级接口的概念，它是把SQL嵌入到不同的过程序编程语言的方法之一。在使用调用级接口时，我们只需要根据主机编程语言的规则把SQL语句的文本保存到一个变量里，然后利用这个变量就可以在主机程序里执行SQL语句。

EXEC SQL是一个常见的主机编程语言命令，可以在程序里调用SQL语句。

下面是支持CLI的常见编程语言：

ANSI C；

C#；

VB.NET；

JAVA；

Pascal；

Fortran。

注意：调用级接口的语法因平台而异

使用调用级接口的具体语法请参考所用主机编程语言的文档。调用级编程语言与平台有关。所以，Oracle与SQL Server的调用级接口互不兼容。

22.6 使用SQL生成SQL

使用SQL生成SQL是节省SQL语句编写时间的一个好方法。假设数据库里已经有了100个用户，我们创建一个新角色ENABLE，要授予给这100个用户。这时不必手工创建100个GRANT语句，下面的SQL语句会生成所需的每一条语句：

这个范例使用了Oracle的系统目录视图（包含着关于用户的信息）。

注意包围GRANT ENABLE TO的单引号，它表示所包围的内容（包括空格在内）要直义使用。还记得吗，我们可以像从表里选择字段一样选择直义值。USERNAME 是系统目录表 SYS.DBA_USERS 里的字段，双管道符号（||）用于连接字段，它把分号连接到用户名之后，从而形成完整的语句。

这个SQL语句的结果是这样的：

这些结果应该保存到文件里，再发送给数据库。然后数据库执行文件里的每条SQL语句，这样我们就不必输入很多的命令，从而节省了时间与精力。GRANT ENALBE TO USERNAME语句会对数据库里的每个用户重复执行。

在需要编写会重复多次的SQL语句时，我们应该发挥自己的想象力，让SQL为我们完成工作。

22.7 直接SQL与嵌入SQL

直接SQL是指从某种形式的交互终端上执行的SQL语句，它的执行结果会直接返回到终端。本书的大部分内容是关于直接SQL的。直接SQL也被称为交互调用或直接调用。

嵌入SQL是在其他程序里使用的SQL代码，这些程序包括Pascal、Fortran、COBOL和C。前面已经介绍过，SQL 代码是通过调用级接口嵌入到主机编程语言里的。在主机编程语言里，嵌入 SQL语句通常以EXEC SQL开始，以分号结束。当然也有使用其他结束符的，比如END-EXEC和右圆括号。

下面是在主机程序（比如ANSI C）里嵌入SQL的范例：

22.8 窗口表格函数

窗口表格函数可以对表格的一个窗口进行操作，并且基于这个窗口返回一个值。这样就可以计算连续总和、分级和移动平均值等。窗口表格函数的语法如下所示：

几乎所有汇总函数都可以作为窗口表格函数，另外还有5个新的窗口表格函数：

RANK OVER；

DENSE_RANK OVER；

PERCENT_RANK OVER；

CUME_DIST OVER；

ROW_NUMBER OVER。

一般来说，计算个人在一个收入年度里的评分级别是比较困难的，而窗口表格函数可以让这种工作容易一些，比如下面这个Microsoft SQL Server范例：

不是全部RDBMS实现都支持窗口表格函数，所以在使用这种函数之前请查看具体实现的文档。

22.9 使用XML

2003版的ANSI标准里有一个与XML相关功能的部分，从那之后，很多数据库实现都努力至少支持其中的部分功能。举例来说，ANSI标准里有一部分是以XML格式输出查询的结果，SQL Server就通过语句FOR XML提供了这个功能，范例如下：

XML 功能集里另一个重要特性是能够从 XML 文档或片断里获取信息，MySQL 通过EXTRACTVALUE函数提供了这个功能，它有两个参数，第一个是XML片断，第二个是定位器，用于返回与字符串匹配标记的第一个值。其语法如下所示：

下面的范例使用这个函数从节点a里提取值：

关于XML功能的详细情况请参见具体实现的文档。某些实现，例如SQL Server和Oracle，拥有特定的XML数据类型。例如，Oracle的XMLTYPE类型拥有特定的API来处理与XML有关的大部分功能，例如查找和提取数据。Microsoft SQL Server的XML类型允许使用模板来确保输入到列的XML数据的完整性。

22.10 小结

本章介绍了一些高级SQL概念，虽然并没有深入讨论，但可以让我们对这些概念有一个基本的了解。首先是光标，它可以把查询的结果传递到内存里的某个位置。当程序里声明了一个光标之后，在访问之前要打开它，然后就可以把光标的内容获取到一个变量里，用于程序进行处理。光标的内容会保存在内存里，直到光标被关闭且内存被重新分配。

接着介绍了存储过程和触发器。存储过程就是保存在数据库里的SQL语句，这些语句（以及其他命令）在数据库里是经过编译的，可以被用户随时执行。存储过程通常比单个SQL语句具有更好的性能。

另外还介绍了动态SQL、用SQL生成SQL、直接SQL与嵌入SQL的不同。动态SQL是用户在运行期间创建的SQL代码，这是与静态SQL的最大区别。

最后，我们还讨论了窗口表格函数和XML，这些是相对比较新的特性，可能不是所有数据库都支持，但还是值得了解一下。这里介绍的一些高级主题可以用于解释第23章中的企业级SQL应用。

22.11 问与答

问：存储过程能够调用另一个存储过程吗？

答：是的，被调用的存储过程被称为嵌套的。

问：如何执行一个光标？

答：只需要使用OPEN CURSOR语句，就会把光标的结果发送到特定存储区域。

22.12 实践

下面的内容包含一些测试问题和实战练习。这些测试问题的目的在于检验对学习内容的理解程度。实战练习有助于把学习的内容应用于实践，并且巩固对知识的掌握。在继续学习之前请先完成测试与练习，答案请见附录C。

22.12.1 测验

1．触发器能够被修改吗？

2．当光标被关闭之后，我们能够重用它的名称吗？

3．当光标被打开之后，使用什么命令获取它的结果？

4．触发器能够在INSERT、DELECT或UPDATE语句之前或之后执行吗？

5．在MySQL里使用什么语句从XML片断里获取信息？

6．为什么Oracle和MySQL不支持针对光标的DEALLOCATE语法？

7．为什么光标不是基于数据集的操作？

22.12.2 练习

1．参考下面的MySQL命令，编写SQL语句，来返回数据库中所有表的描述信息：

2．编写一个 SELECT 语句来生成 SQL 代码，统计每个表里的记录数量。（提示：类似于练习1。）

3．编写一组SQL命令来创建一个光标，返回所有用户及其销售数据。确保在用户所使用的实现中，正确关闭光标并回收资源。

第23章 SQL扩展到企业、互联网和内部网

本章的重点包括：

SQL与企业

前台和后台程序

访问远程数据库

SQL与互联网

SQL与内部网

前一章介绍了一些高级SQL概念，它们基于本书前面章节所介绍的内容，并且开始展示SQL的一些实际应用。本章着重于把SQL扩展到企业背后的概念，其中包括SQL应用程序和让企业全部成员都能够使用数据来完成日常工作。

23.1 SQL与企业

很多商业公司都为其他企业、顾客和销售商提供数据，比如一个企业可能会向顾客提供关于产品的详细信息，从而希望实现更好的销售。企业雇员的需求也在考虑之列，比如提供关于雇员的特定信息，包括考勤登记、休假计划、培训计划、公司政策等。在数据库被创建之后，顾客和雇员应该可以通过SQL或某种互联网语言访问企业的数据。

23.1.1 后台程序

任何应用的核心都是后台程序，它们对于数据库终端用户是透明的，但却是发生一切事情的幕后场所。后台程序包括实际的数据库服务程序、数据源、把程序连接到Web或局域网上远程数据库的中间软件。

确定所要使用的数据库实现通常是移植任何程序的第一步，包括通过局域网（LAN）到企业、到企业自己的内部网，或是到互联网。移植描述了在一个环境里实现一个应用供用户使用的过程。数据库服务程序应该由数据库管理员（DBA）创建，他理解公司的需求与程序的要求。

应用的中间件包括Web服务程序、能够把Web服务程序连接到数据库服务程序的工具。其主要目的是让Web上的程序能够与公司的数据库进行通信。

23.1.2 前台程序

前台程序是应用的组成部分，终端用户通过它进行交互。前台程序可以是现成的商业软件，或是使用第三方工具自己开发的程序。商业软件包括一些使用Web浏览器来展示内容的应用软件。在Web环境下，类似FireFox和IE这样的浏览器经常被用来访问数据库程序。这样，用户不必安装特定软件也可以访问数据库。

注意：应用具有很多不同的层

前台程序简化了终端用户对数据库的操作。底层的数据库、代码和数据库里发生的事件对于用户来说是透明的。前台程序使得用户不必对系统本身非常了解，从而减少了他们的猜测与疑惑。新技术使得程序更加智能化，让用户能够专注于真正与实际工作有关的部分，从而提高了整体的生产力。

目前可以使用的工具是用户友好的、面向对象的，具有图标、向导，支持鼠标的播放操作。用于把程序移植到Web的流行工具包括Borland公司的C++ Builder、IntraBuilder和微软的Visual Studio。其他一些用于在局域网上开发公司级程序的工具还有Powersoft的PowerBuilder、Oracle公司的Oracle Designer和Oracle Forms、微软的Visual Studio、Borland的Delphi。

图23.1展示了数据库应用里的前台程序和后台程序。后台程序位于数据库所在的主机服务器上。后台用户包括开发人员、程序员、DBA、系统管理员和系统分析员。前台程序位于客户计算机，通常就是每个用户的个人电脑。前台用户是前台程序的大量使用人员，包括数据输入员、会计等。终端用户能够通过网络连接（LAN或广域网）访问后台数据库，这是由一些通过网络为前台和后台程序提供连接的中间件（比如ODBC驱动程序）实现的。

图23.1 数据库应用

23.2 访问远程数据库

有时要访问的数据库是个本地数据库，也就是直接连接的。但在很多情况下，我们都会访问某种形式的远程数据库。远程数据库是非本地的，或是说位于非直接连接的服务器上，这时我们必须使用网络和网络协议与数据库进行交互。

访问远程数据库的方式有多种。从广义角度来说，我们是利用中间产品（ODBC和JDBC就是标准的中间件，在后续章节进行介绍）通过网络或互联网连接访问远程数据库的。图23.2展示了访问远程数据库的3种情形。

图23.2 访问远程数据库

图23.2展示了从本地数据库服务器、本地前台程序和本地主机服务器访问远程服务器的情形。本地数据库服务器和本地主机服务器经常是同一台机器，因为数据库一般位于本地主机服务器上。但是，我们通常在没有本地数据库连接的情况下从本地服务器连接到远程数据库。对于终端用户来说，前台程序是访问远程数据库的最典型方式。所有的方法都必须把对数据库的请求通过网络进行路由。

23.2.1 ODBC

开放式数据库连接（ODBC）可以通过一个库驱动程序连接到远程数据库。前台程序利用ODBC驱动与后台数据库进行交互。在连接到远程数据库时，可能还需要一个网络驱动。程序调用ODBC函数，驱动管理程序加载ODBC驱动。ODBC驱动处理这个调用，提交SQL请求，从数据库返回结果。

作为 ODBC 的一个组成部分，所有关系数据库管理系统（RDBMS）厂商都提供了数据库的应用编程接口（API）。

23.2.2 JDBC

JDBC是Java数据库连接，它类似ODBC，通过一个Java库驱动连接到远程数据库。前台的Java程序使用JDBC驱动与后台的数据库进行交互。

23.2.3 OLE DB

OLE DB是微软公司使用组件对象模型（Component Object Model）编写的一组接口，用于代替 ODBC。OLE DB实现力图拓展ODBC功能，不仅可以连接各种数据库实现， 也可以连接非数据库存储的数据，例如电子表格等。

除了 ODBC 驱动之外，很多厂商也提供了自己的产品，可以把用户连接到远程数据库。这些厂商产品都是专门用于特定 SQL 实现的，一般不能移植到其他类型的数据库服务程序。

23.2.4 厂商连接产品

除了驱动和 API 之外，很多厂商也提供了自己的产品，可以把用户连接到远程数据库。这些厂商产品都是专门用于特定 SQL 实现的，一般不能移植到其他类型的数据库服务程序。

Oracle公司有一个名为Oracle Fusion Middleware的中间件产品，既可以连接Oracle数据库，也可以连接其他应用软件。

Microsoft也有几款产品与其数据库配合使用，例如Microsoft SharePoint Server和 SQL Server Reporting Services。

23.2.5 通过Web接口访问远程数据库

通过Web接口访问远程数据库十分类似于通过局域网进行访问，主要区别在于用户的全部请求都经过Web服务程序进行了路由（如图23.3所示）。

从图23.3 中可以看出，一个终端用户通过一个 Web 接口访问数据库，首先是调用一个Web浏览器，它用于连接到一个特定的URL（由Web服务程序的位置决定）。Web服务程序验证用户的访问，把用户请求（可能是一个查询）发送给远程数据库（也可能对用户的身份进行验证）。数据库服务程序然后把结果返回给Web服务程序，后者把结果显示在用户的Web浏览器上。使用防火墙可以控制对特定服务器的非授权访问。

警告：注意互联网信息安全问题

注意在Web上提供的信息。永远要确保在全部恰当的级别都采取了应有的预防措施，其中包括Web服务器、主机服务器、远程数据库。涉及个人隐私的数据，比如个人的社会保险号码，永远都不应该公开在Web上。

图23.3 远程数据库的Web接口

防火墙是一种安全机制，防止来自和针对服务器的非授权连接。在一台服务器上可以启动一个或多个防火墙来监视对数据库或服务器的访问。

另外，一些数据库实现允许我们根据IP地址限制对数据库的访问，这就提供了另一层保护，因为我们可以把对数据库的访问限制到充当应用层的Web服务器。

23.3 SQL与互联网

SQL可以嵌入到或用于像C#和JAVA这样的编程语言，还可以嵌入到互联网编程语言，比如Java和ASP.NET。源自于HTML的文本可以被转换为SQL，从Web前端远程数据库发送请求。在数据库完成查询操作之后，输出结果被转换回HTML，显示在用户的Web浏览器上。下面的小节将讨论SQL在互联网上的应用。

23.3.1 让数据可以被全世界的顾客使用

随着互联网的出现，数据对全世界的顾客和厂商都开放了。一般来说，用户利用前台工具以只读方式访问数据。

为顾客提供的数据包括一般的顾客信息、产品信息、发票信息、当前订单、延期交货单和其他相关信息。但其中不应该包括隐私信息，比如公司策略和雇员信息。

在互联网上拥有自己的主页已经成为公司竞争中不可缺少的组成部分，Web页面可以仅用很小的代价就向浏览者展示公司的全面情况，包括它的服务、产品和其他信息。

23.3.2 向雇员和授权顾客提供数据

数据库可以通过互联网或公司的内部网向雇员或顾客提供访问。互联网是一个非常有价值的通信资源，可以用于向雇员提供公司政策、福利、培训等信息。但是，在通过互联网提供数据时一定要非常小心，公司机密和个人信息不应该能够通过Web访问。另外，在线提供的数据应该只是数据库的一个子集或子集的副本。主要的实用数据库应该全力保护。

警告：互联网的安全性还不够好

与互联网的安全相比，数据库安全更可靠一些，因为后者可以根据所包含的数据进行精细的调整。虽然在通过互联网访问数据时也可以使用一些安全措施，但通常是有限的，而且不像数据库权限那样容易修改。我们应该总是尽量使用数据库服务器具有的安全特性。

23.4 SQL与内部网

IBM 最初创建 SQL 是要实现主机上的数据库与使用客户机的用户之间的通信。用户通过 LAN 连接到主机，SQL 被选作数据库与用户之间通信的标准语言。内部网基本上就是一个小型互联网，主要区别是内部网是针对单个公司的应用，而互联网是对公共福斯开放的。内部网上的用户（客户端）接口与客户/服务器环境里的是一样的。SQL经过Web服务器和语言（比如HTML）的路由转发到数据库。内部网主要用于公司内部应用、文档、表单、Web页面和电子邮件。

通过互联网进行的SQL请求必须特别注意性能问题。在这种情况下，不仅需要从数据库获取数据，还需要把数据显示在用户的浏览器上。这通常涉及把数据转换为某种形式的HTML兼容代码。另外，Web连接一般都比内部网连接的速度慢，因此数据来回传递的速度也慢。

连接入Web的数据库实现必须重视安全性。这需要考虑很多问题，来确保数据处于安全保护之下。首先，如果数据暴露于公共网络，必须确保这些数据不会被非法访问。通常，数据会被转换成明文形式，以便用户阅读。可以考虑使用SSL作为部分安全措施，来保护网络交流。SSL使用证书来加密服务端和客户端之间传递的消息，这种加密可以被用HTTPS开头的网站所识别。

另一个需要考虑的问题是非法的数据输入。用户或应用程序可能会向错误的字段输入了错误的数据类型，也可能会遇到更严重的SQL注入攻击，黑客可能通过这种方式向数据库注入并执行自己的SQL代码。

预防上述问题的最好方法就是，严格约束应用软件账户对数据库的访问。可以在需要访问数据库的时候，使用存储过程和函数，这样就可以对进出系统的数据有所控制。同时，还可以使用户执行任何符合DBA要求的数据操作，以确保数据的一致性。

23.5 小结

本章介绍了在互联网上应用SQL和数据库程序背后的概念，这些概念对于公司在当今这个时代保持竞争力是非常重要的。事实已经证明，为了不被时代抛弃，在互联网上占据一席之地是很有好处的——甚至是必须的。为此，公司必须开发程序，甚至是从客户/服务器系统上把程序移植到互联网上的Web服务器。在提供任何类型及任何数量的公司数据时，最需要考虑的问题就是安全，并且应该始终严格坚持安全准则。

本章还讨论了通过局域网和互联网访问远程数据库。任何访问远程数据库的方式都需要使用网络和协议适配器来转换对数据库的请求。在此，我们概要介绍了基于局域网、公司内部网和互联网的SQL应用。在完成后面的测验和练习之后，我们就要进入最后一章了。

23.6 问与答

问：为什么说，了解数据是否通过互联网的公共网络被访问，这一点很重要？

答：在客户端和Web应用之间传递的数据往往是明文形式。这就意味着，任何人都可以拦截消息并看到其中的内容，例如社会保险号或银行账号。在可能的情况下，最好对数据进行加密。

问：针对Web应用的后台数据库与针对客户/服务器系统的后台数据库有什么不同吗？

答：针对 Web 应用的后台数据库本身不必与针对客户/服务器系统的有什么不同，但基于Web 的程序需要满足其他一些要求。举例来说，需要使用Web 服务程序访问数据库。在使用Web程序时，用户通常不是直接连接到数据库的。

23.7 实践

23.7.1 测验

1．一台服务器上的数据库能够被另一台服务器访问吗？

2．公司可以使用什么方式向自己的雇员发布信息？

3．提供数据库连接的产品被称为什么？

4．SQL能够嵌入到互联网编程语言里吗？

5．如何通过Web程序访问远程数据库？

23.7.2 练习

1．连接到互联网，查看一些公司的主页。如果你自己的公司有主页，可以把它与竞争对手的主页进行一下比较，回答以下这些问题。

页面上有动态的内容吗？

什么样的页面或者页面上的什么区域，可能包含来自后端数据库的数据？

Web页面上有什么安全机制吗？在访问保存在数据库里的数据时需要登录吗？

现在，大部分浏览器允许用户查看返回页面的源代码。使用你的网页浏览器查看源代码。其中是否存在一些代码，可以告诉你后端使用的数据库是什么？

如果在源代码中发现了一些信息，例如服务器名称或者数据库用户名，你认为这属于安全漏洞吗？

2．访问下面的站点，浏览其中的内容、最新的技术和公司在Web上使用的数据（来自于数据库的数据）。

www.amazon.com

www.informit.com

www.epinions.com

www.mysql.com

www.oracle.com

www.ebay.com

www.google.com

第24章标准SQL的扩展

本章的重点包括：

各种实现

不同实现之间的区别

遵循ANSI SQL

交互SQL语句

使用变量

使用参数

本章介绍对ANSI标准SQL的扩展。虽然大多数SQL实现遵循了这个标准，但有很多厂商会通过各种形式的改进对标准SQL进行扩展。

24.1 各种实现

多家厂商发布了多种SQL实现，在此不可能列出全部的关系型数据库厂商，只能讨论一些主流实现，其中包括MySQL、Microsoft SQL Server和Oracle。其他一些比较流行的厂商还有Sybase、IBM、Informix、Progress、PostgreSQL等。

24.1.1 不同实现之间的区别

虽然这里讨论的各种实现都是关系型数据库产品，但彼此之间还是有所区别的。这些区别源自于产品设计和数据库发动机处理数据的方式，但本书着重介绍SQL方面的区别。所有的实现都根据ANSI的要求使用SQL作为与数据库通信的语言，但很多实现都对SQL进行了某种形式的扩展。

注意：厂商有意扩展SQL标准

不同厂商会出于性能及易用性的考虑对ANSI SQL进行增强，努力提供其他厂商没有的优势，从而吸引顾客。

在了解了SQL之后，根据不同实现的区别对SQL进行调整应该没有什么问题。换句话说，如果我们可以在Sybase实现里编写SQL，就可以在Oracle里编写SQL。另外，了解不同厂商的SQL还可以增加我们的就业机会。

下面比较几个主流厂商与ANSI标准的SELECT语句。

首先是ANSI标准：

下面是Microsoft SQL Server的语法：

Oracle的语法：

从这些语法的比较可以看出，它们基本上是相同的。它们都具有 SELECT、FORM、WHERE、GROUP BY、HAVING、UNION和ORDER BY子句，这些子句在工作概念上是一样的，但有些具有额外的选项，这些选项就被称为扩展。

24.1.2 遵循ANSI SQL

厂商们的确努力遵循ANSI SQL，但都没有做到百分之百符合ANSI SQL标准。有些厂商添加了命令或函数，而且其中很多新命令或函数被吸收到ANSI SQL里。对于厂商来说，遵循标准有很多好处，最明显的是使用其产品易于学习，而且其使用的代码也易于移植到其他实现。当数据库从一个实现迁移到另一个实现时，可移植性是一个非常重要的考虑因素。

对于被认为遵循ANSI的数据库来说，它只需要对应于ANSI标准的一个功能子集。ANSI标准是由多家数据库厂商共同制定的。因此，虽然大多数SQL实现彼此之间有很大差别，但它们都被认为是遵循ANSI标准的。所以，把代码限制到严格遵循ANSI标准的语句能够提高可移植性，但数据库性能可能不会达到最优。总之，我们要在可移植性与性能之间权衡。权衡的结果通常是放弃可移植性，从而充分利用用户所用平台的性能。

24.1.3 SQL的扩展

实际上，全部主流厂商都对SQL有所扩展。对于特定实现来说，SQL扩展都是不同的，而且一般不便于移植。然而，流行的标准扩展已经得到了ANSI的关注，将来可能会成为新标准。

Oracle的PL/SQL、Sybase和Microsoft SQL Server使用的Transact-SQL是标准SQL扩展的两个范例，后面的范例里将更详细地介绍它们。

24.2 扩展范例

PL/SQL和Transact-SQL都被认为是第4代编程语言，是过程化语言，但SQL是非过程化语言。我们还会简要地讨论一下MySQL。

非过程语言SQL包括如下语句：

INSERT；

UPDATE；

DELETE；

SELECT；

COMMIT；

ROLLBACK。

SQL扩展是一种过程语言，包括标准SQL里全部语句、命令和函数，另外还包括：

变量声明；

光标声明；

条件语句；

循环；

错误处理；

变量累加；

日期转换；

通配符；

触发器；

存储过程。

这些语句可以让程序员在过程化语言里更好地控制数据处理方式。

24.2.1 Transact-SQL

Transact-SQL是Microsoft SQL Server使用的一种过程语言，表示我们告诉数据库如何、在何处获取和操作数据。SQL 是非过程的，由数据库决定如何、在何处选择和操作数据。Transact-SQL 的几个突出优点包括声明本地和全局变量、光标、错误处理、触发器、存储过程、循环、通配符、日期转换和汇总报告。

Transact-SQL语句的一个范例如下：

这是个很简单的Transact-SQL语句，它表示如果表PRODUCTS_TBL里的平均价格大于50，就显示“LOWER ALL COSTS BY 10 PERCENT”，否则就显示“COSTS ARE REASONABLE”。

其中使用了 IF...ELSE 语句计算条件的值，而 PRINT 命令也是个新命令。这些只是Transact-SQL强大功能的九牛一毛。

注意：SQL不是过程语言

标准 SQL 从根本上来说是非过程语言，表示我们把语句提交给数据库服务程序，后者决定如何以最优方式执行语句。过程语言允许程序员请求要获取或操作的数据，告诉数据库服务程序如何准确地执行请求。

24.2.2 PL/SQL

PL/SQL是Oracle对SQL的扩展，也是一种过程语言，由代码的逻辑块构成。一个逻辑块包含三个部分，其中两个是可选的。第一部分是DECLARE部分，是可选的。它包含变量、光标和常数。第二个部分是 PROCEDURE，是必需的，包含条件命令和 SQL 语句，是逻辑块的执行部分。第三部分是 EXCEPTION，是可选的，定义了程序如何处理错误和自定义异常。PL/SQL 的突出优点包括使用了变量、常数、光标、属性、循环、处理异常、向程序员显示输出、事务控制、存储过程、触发器和软件包。

PL/SQL语句的范例如下所示：

这个范例里使用了三个部分里的两个：DECLARE和PROCEDURE。首先，用一个查询定义了一个名为EMP_CURSOR的光标；然后声明了一个变量EMP_REC，与光标里每个字段的数据类型（%ROWTYPE）相同。PROCEDURE部分（在BEGIN之后）的第一步是打开光标，然后使用 LOOP命令遍历光标里每条记录，结束于 END LOOP语句。光标里的全部记录都会更新到表EMPLOYEE_TBL。如果雇员的中间名是NULL，更新操作会把中间名设置为“X”。更新被提交到数据库，最后光标被关闭。

24.2.3 MySQL

MySQL是个多用户、多线程SQL数据库客户/服务器实现，它包含一个后台服务程序、一个终端监控客户程序、几个客户程序和库。MySQL的主要目标是速度、强健性和易用性，它最初的设计目的是对大型数据库提供更快速的访问。

MySQL被认为是一种比较符合ANSI标准的数据库实现。从最开始，MySQL就是一个半开源的开发环境，以便严格遵守ANSI标准。从5.0版开始，MySQL推出了开源的社区版和闭源的企业版。2009年，MySQL随同SUN公司一起被Oracle公司收购。

目前，MySQL还不像Oracle或Microsoft SQL Server那样有大的改动，但根据其近期的表现来看，情况很快就会有变化了。用户可以查看所用版本 MySQL 的说明书，以便了解哪些扩展可能会被开发。

24.3 交互SQL语句

交互SQL语句会在完全执行之前询问用户变量、参数或某种形式的数据。假设我们有一个SQL语句是交互的，用于在数据库里创建用户。它会提示我们输入一些信息，比如用户ID、用户名、电话号码等。它可以创建一个或多个用户，而且只需执行一次。否则，我们就需要用CREATE USER语句分别创建每个用户。当然，这个SQL语句还能提示设置权限。并不是全部厂商都具有交互式SQL语句，详细情况请参见具体实现的文档。

交互式SQL语句的另一个优点是可以使用参数。参数是SQL里的变量，位于程序之内。我们可以在运行时向SQL语句传递参数，让用户能够以更灵活的方式执行语句。很多主流实现支持使用这些参数，下面的小节将展示在Oracle和SQL Server里传递参数的范例。

Oracle里可以把参数传递给静态SQL语句，比如：

前面这个SQL语句会提示输入EMP_ID，然后返回EMP_ID和对应的LAST_NAME、FIRST_NAME。下面的语句提示我们输入城市和州，返回居住在指定城市和州里的雇员的全部数据。

在Microsoft SQL Server里，我们可以把参数传递给存储过程：

下面就执行这个存储过程并传递参数：

24.4 小结

本章介绍了一些厂商对标准SQL的扩展以及它们遵循ANSI标准的情况。在学习了SQL之后，我们可以轻松地把这些知识（和代码）应用到SQL的其他实现。SQL在不同厂商之间是可以移植的，大多数SQL代码只需要很小的修改就可以在大多数SQL实现中使用。

最后一部分内容展示了三种实现使用的两个扩展。Microsoft SQL Server和Sybase使用了Transact-SQL，而Oracle使用的是PL/SQL。从范例中可以看出这两者之间的相似之处。它们都遵循ANSI标准，在此基础上进行增强，提供更好的功能和效率。另外还介绍了MySQL，其设计目的是提高大型数据库查询的速度。本章的目标是让用户了解到存在着很多 SQL 扩展，而遵循ANSI SQL标准也是一件非常重要的事情。

如果可以掌握本书的内容并使用它（创建自己的代码、进行测试、增长知识），我们就走上了掌握SQL的阳光大道。公司都要使用数据，没有数据库就很难正常运行。关系型数据库遍布四方，而SQL是与关系型数据库进行通信和管理的标准语言，所以学习SQL是个非常好的选择。祝你好运！

24.5 问与答

问：为什么SQL有差异？

答：不同的SQL实现使用不同方式存储数据，各个厂商都努力超越其他竞争对手，不断出现的新概念，这些原因导致了SQL有差异。

问：在学习了基本SQL之后，我们是不是就可以在不同实现上使用SQL了？

答：是的，但是要记住不同实现之间存在的差异与变化，但大多数实现的SQL基本构架是一样的。

24.6 实践

24.6.1 测验

1．SQL是过程语言还是非过程语言？

2．除了声明光标之外，光标的3个基本操作是什么？

3．过程或非过程：数据库发动机在处理什么语句时会决定对SQL语句进行估值和执行？

24.6.2 练习

研究一下不同厂商的SQL差异。访问如下站点，研究常见的SQL实现：