数据库系统原理-第九章 服务器环境下的SQL

1、第第9章章 服务器环境下的服务器环境下的SQL 9.1 三层体系结构9.2 SQL环境9.3 SQL/宿主语言接口9.4 存储过程9.5 使用调用层接口Page 19.1 三层体系结构 具有三种不同而又相互联系的功能： Web服务器 指那些连接客户端和数据库系统的进程 应用服务器 系统要做的所有操作。 数据库服务器 这些进程运行DBMS并执行应用服务器请求的查询和更新。Page 2Web服务器 Web服务器进程管理与用户交互，当用户发起连接时，可通过打开URL，Web服务器则响应用户请求。之后用户便成为Web服务器进程的一个客户端。Page 3应用层 应用层的工作是将数据作为响应从数据库发回给

2、那些从Web服务器获得的请求。每个Web服务器的进程可以调用一个或多个应用层的进程来处理请求。 被应用层执行的操作通常称为数据库运行结构的交易逻辑。即应用层是根据推理潜在客户的请求响应和实现这些推理的策略来设计。Page 4数据库层 数据库层具有多个进程，它们能被分配到多台机器，也可以在一台机器上共同工作。数据库层负责执行来自应用层请求的查询，另外提供一些数据缓冲。 由于与数据库连接的时间不能忽略，通常保持一定数量的连接处于开放状态，同时允许应用进程共享这些连接。为避免应用进程之间的一些异常交互，每一个应用进程必须返回它获得的连接状态。Page 569.2 SQL环境 SQL环境是SQL中数据

3、所在的框架，为数据操作提供支持，即一个运行环境(如DBMS运行环境)。 数据库的所有元素，包括表、视图、触发器等，都是在SQL环境中定义的。这些元素组成了层次层次性结构性结构： 模式模式 目录目录 簇簇71. 模式 模式是组织的基本单元。 模式是表、视图、断言、触发器等模式元素的集合。 模式声明的形式模式声明的形式CREATE SCHEMA 模式声明如图模式声明如图9-381. 模式(续) CREATE SCHEMA 提供了在SQL语句中创建表、视图以及授予对象权限的方法，并区别重名，即属于不同模式的模式元素可以重名，可用“模式名.模式元素名”来引用某个模式元素。 没有必要一次就声明完所有的模

4、式。可以使用合适的CREATE、DROP或ALTER语句来修改或增加模式 使用SET SCHEMA语句改变“当前”的模式。 例如： SET SCHEMA MovieSchama ; 将使图9-3描述的模式作为当前模式92. 目录 模式的集合。每个目录有一个或多个模式，目录中的模式名必须唯一，每个目录包含一个叫INFORMATION_SCHEMA的特殊模式，这个模式包含了该目录中所有模式的信息。 没有CREATE CATALOG语句，只规定了设设置当前目录的语句：置当前目录的语句： SET CATALOG ； 对模式的操作(新建/修改模式等)都是针对当前目录10模式元素的全名 . . 例如：例如

5、：目录MovieCatalog中的模式MovieSchema的表Movies的引用如下：MovieCatalog . MovieSchema . Movies 如果目录是缺省的或是当前的目录，则可省去目录名 如果模式也是缺省的或当前的模式，那么模式部分也可以省去，这样只留下元素自己的名称。 然而，当需要访问当前模式或目录以外的元素时，就不得不使用完全名。113. 簇 簇是目录的集合。每个用户有一个关联的簇：用户可访问的所有目录的集合。簇是被提交的查询的最大范围，故在一定程度上，簇是特定用户所看到的“数据库”。12SQL环境中的客户端和服务器 依照SQL标准，SQL环境有两种特殊的进程： SQL

6、 服务器：服务器：支持对数据库元素的操作，充当数据库服务器的角色。 SQL客户端：客户端：允许用户连接到服务器上，对数据库进行操作 例如： 服务器运行在一个大的存储数据库的主机上，而客户端运行在其他主机上，也许是远离服务器的个人工作站，也许是和服务器运行在同一台主机上。13连接 如果在SQL客户端主机上运行包含SQL 的程序，则可通过下面的SQL语句打开客户端和服务器间的连接： CONNECT TO AS AUTHORIZATION 依赖于安装。可用名称DEFAULT将用户连接到任何被作为“缺省服务器”安装的SQL服务器。如果仅是在SQL客户端主机上执行SQL语句，也可为自己建立一个缺省连接

7、可在以后用于引用连接。原因是SQL允许用户打开好几个连接，但任何时候只有一个连接有效 AUTHORIZATION授权子句授权子句后跟随着用户名和密码。14引用连接 将将conn1切换为有效连接：切换为有效连接：SET CONNECTION conn1; 任何当前有效的连接进入休眠状态后，只有用下任何当前有效的连接进入休眠状态后，只有用下列语句显式地调用才能将其激活：列语句显式地调用才能将其激活：SET CONNECTION conn1; 断开连接时断开连接时conn1被中止，不是休眠，也不能被激被中止，不是休眠，也不能被激活活:DISCONNECT conn1; 注：如果连接创建后再也不被引用

8、，CONNECT TO子句中的AS和连接名可以省略15会话 连接有效时，执行的连接有效时，执行的SQL操作形成了一个会话操作形成了一个会话 会话和创建它的连接具有相同的生命周期会话和创建它的连接具有相同的生命周期。例如: 当连接处于休眠状态时，它的会话也处于休眠态 SET CONNECTION语句可激活连接，同时激活相应会话。连接和会话是客户和服务器间链路的两个方面16会话 每个会话有一个当前目录和该目录中的一个当前每个会话有一个当前目录和该目录中的一个当前模式模式 由语句SET SCHEMA和SET CATALOG进行设置 每个会话都有一个授权用户17模块 模块是对应用程序而言的，SQL标准

9、提出三种模块： 普通普通SQL界面：界面：用户可以键入SQL服务器执行的SQL语句。这种模式下，每个查询或其他语句本身是一个模块。 嵌套嵌套SQL：预处理器将这个嵌套的SQL语句转变为SQL系统的对应函数或过程调用。编译后的宿主语言程序(包括这些函数调用)是一个模块。 真模块：真模块：含有存储函数或过程集合的模块。这些函数|过程部分是宿主语言代码，部分是SQL语句，它们之间可通过参数或共享变量进行通讯。 仅要求SQL的实现中至少提供一种类型给用户。189.3 SQL/宿主语言接口 用宿主语言编程，且用到了一些并非宿主语言的嵌套SQL语句,将整个程序提交给预处理器。 预处理器将嵌套SQL语句转化

10、为调用函数，此函数把SQL语句当作字符串参数，并且执行这个SQL语句 经过预处理的宿主语言程序随后以通常的方式编译19包含SQL语句的典型编程系统 DBMS销售商提供了必要的函数定义库。这样实现SQL的函数被执行，并且整个程序像一个整体一样运作。 还有另一种方式：程序员直接用宿主语言编写程序，只是在必要时使用这些函数调用。这就是SQL编程的第3种方式“调用级界面/CLI”20阻抗不匹配问题 连接SQL语句和常规编程语言的基本问题就是阻抗不匹配：即SQL数据模式与编程语言模式差别非常大。 SQL：关系数据模型，不使用指针、结构体、循环和转移 常规编程语言：整型、实型、算术性、字符型、指针、记录、

11、数组无法直接表示集合 因此，在SQL和常规编程语言之间不能直接转移数据21SQL与常规编程语言二者缺一不可 SQL与常规编程语言二者缺一不可 SQL在很大程度上帮助程序员编写数据库操作 而很多重要工作(如算法实现)又离不开常规编程语言 必须设计一种机制(例如提供接口)，允许程序的开发既可以使用SQL，也可以使用常规编程语言(宿主语言)22SQL/宿主语言接口 数据库只能由SQL语句访问 在数据库和宿主语言程序之间的信息交换是通过共享变量共享变量实现的。这种变量允许出现在宿主语言和SQL语句中。 SQL中共享变量前面要加上冒号作为前缀 在宿主语言中这些变量并不需要冒号23关键字EXEC SQL

12、在宿主语言中使用SQL语句时，通过SQL语句前面的关键字EXEC SQL提示预处理器将有SQL代码进入。 系统将预处理这些语句，用宿主语言中合适的函数调用来代替这些语句，并充分利用与SQL相关的函数库。24特殊变量SQLSTATE 在SQL标准中，SQLSTATE这个特殊变量用于连接宿主语言程序与SQL执行系统。 SQLSTATE是五个字符的数组类型。 每次调用SQL的库函数，向SQLSTATE变量中存放一个代码，该代码表示调用过程中出现的问题。25SQLSTATE变量的值 SQL标准同时指定了大量的五个字符的代码和它们的意义。例如： 00000(五个零五个零): 没有产生任何错误 02000

13、:没找到作为SQL查询结果组成部分的元组。 这个代码非常重要：它允许在宿主语言程序中创建一个循环并且每执行一次循环检查一个元组，当关系中最后一个元组被检查后中止该循环。26DECLARE节 EXEC SQL BEGIN DECLARE SECTION； 共享变量的声明共享变量的声明 EXEC SQL END DECLARE SECTION； 声明节中的变量声明形式可以是宿主语言要求的任何形式。 为使声明的变量有意义，共享变量类型必须是宿主语言和SQL都可以处理的 如整型、实型和字符型，或者数组类型27示例9.3EXEC SQL BEGIN DECLARE SECTION； char studi

14、oName50，studioAddr256； char SQLSTATE6；EXEC SQL END DECLARE SECTION；28使用共享变量后面是嵌套的后面是嵌套的SQL语句语句共享变量在共享变量在SQL中须加冒中须加冒号作前缀号作前缀29SQL语句的嵌入 任何不返回结果的SQL语句(即非查询语句)都可以用EXEC SQL为前缀直接嵌入到宿主语言中 包括INSERT、DELETE和UPDATE语句以及那些创建、修改或者删除表和视图等模式元素的语句。 然而由于“阻抗不匹配”，select-from-where查询不能直接嵌套到宿主语言。 查询产生的结果是元组包，但是大多数宿主语言均不直

15、接支持集合或包数据类型。30查询嵌入宿主语言的机制 为了将查询结果与宿主语言程序相连接，嵌套SQL有两种机制可选择： (1) 单元组选择语句单元组选择语句：只有一个结果元组的查询可将该元组存储到共享变量中，一个变量对应元组的一个分量。 (2) 游标：游标：如果为查询声明一个游标，那么产生多于一个元组的查询就可以执行了。游标范围覆盖了结果关系中的所有元组，每个元组依次被提取到共享变量中，并由宿主语言进行处理。31(1) 单元组选择语句 单元组选择的形式类似于普通的select-from-where语句，只是SELECT子句后紧跟着关键字INTO和一连串的共享变量(以冒号作为前缀)。 如果查询结果

16、是个单一元组，那么这个元组的分量将分配给这些变量并成为它们的值。 如果结果没有元组或者多于一个元组，那么不会分配给这些共享变量，同时一个相应的错误码被写入到SQLSTATE变量中。32单元组选择示例Page 3233(2) 游标 游标声明游标声明 EXEC SQL DECLARECURSOR FOR 其中查询可以是通常的select-from-where查询或者关系名。游标范围覆盖该查询产生的关系元组。 打开游标打开游标 (初始化初始化) EXEC SQL OPEN 这个语句初始化游标的位置，使游标指向其覆盖的那个关系中的第一个元组，并从那里开始检索。34(2) 游标(续1) 一次或者多次使用

17、一次或者多次使用fetch子句子句 EXEC SQL FETCH FROMINTO 目的是得到游标覆盖的那个关系中的下一个元组。 每个关系元组的属性对应列表里的一个变量。假如有一个可获取的元组，那么该元组相应的分量将赋值给对应的变量。如果元组已经被遍历过了，那么不会返回任何元组，且SQLSTATE被赋值为02000，表示“没有发现任何元组”。35(2) 游标(续2) 关闭游标关闭游标 EXEC SQL CLOSE 这条语句关闭游标，游标将不再覆盖关系的元组。 然而，游标可由另外一条OPEN语句重新初始化，它将重新覆盖这个关系的元组。36游标示例 分别计算净资产位数从1-14的出品人个数Page

18、 3637游标更新 当游标遍历一个基本表的元组时，不仅可以读和处理每个元组的值，也可以修改或者删除当前元组。 这里的UPDATE和DELETE语句，要求其WHERE子句只能是WHERE CURRENT OF，其后跟着游标的名称。38游标更新示例39防止并发更新 不希望游标读取的元组被并发的变化所影响，而强调统计是针对某个时刻已存在的关系。 对游标所读取关系进行修改的语句，在游标读取元组前就已彻底完成或在游标读取元组后再运行 为了保证这一点，对于并发变化可以将游标声明为对并发修改不敏感(insensitive)。例如： EXEC SQL DECLARE execCursor INSENSITIV

19、E CURSOR FOR SELECT netWorth FROM MovieExec ; 保证在execCursor打开和关闭之间，对关系MovieExec所作的变化不会影响游标提取到的元组集合。40将游标声明为READ ONLY 若确信关系R上的游标不会改变R，则该游标可与R的不敏感游标同时运行。 将游标声明为FOR READ ONLY，那么数据库系统可以保证基本关系R不会因为读取游标而修改了关系R。 EXEC SQL DECLARE execCursor CURSOR FOR SELECT netWorth FROM MovieExec FOR READ ONLY; 这样，任何试图通过游

20、标execCursor所做出的关系修改都会产生错误。41动态SQL 目前为止，嵌套在宿主语言中的SQL模型都是在宿主语言程序中特定的特定的SQL查询和命令。 嵌套SQL的另一种形式是自身可以被宿主语言处理的语句。这种语句编译时不可知编译时不可知，因此，不能被SQL预处理器和宿主语言编译器处理。 例如：例如：程序提示用户输入SQL查询，然后读这个查询并执行。 查询被读到后，将立即进行语法分析，并且由SQL系统寻找适合执行该查询的方式。42动态SQL的执行 1. EXEC SQL PREPARE V FROM V是SQL变量，是其值为字符串的任意一条宿主语言表达式，该字符串被当作SQL语句。 宿主

21、语言程序指导SQL系统接受刚读到的字符串，经语法分析后将其转化为可执行SQL语句, 用V表达 2. EXEC SQL EXECUTE V 执行V所代表的SQL语句。43动态SQL的执行(2) 上述两个步骤也可以合二为一 EXEC SQL EXECUTE IMMEDIATE +字符串表达式字符串表达式 如果一条语句被编译一次，然后执行很多次时，就会看到合并这两步是不利的。 使用EXECUTE IMMEDIATE ，每次语句执行时都要付出准备该语句的代价，而不是只付出一次。44动态SQL示例既然查询只执行一次，那么既然查询只执行一次，那么6)7)可替代为：可替代为：EXEC SQL EXECUTE

22、 IMMEDIATE:query459.4 存储过程 Persistent Stored Modules: SQL/PSM(简写为简写为PSM) 允许用简单通用的语言编写过程并且将它们存储在数据库中，作为模式的一部分。这些过程可以在数据库中完成不能用SQL单独完成的处理。 每个商用性DBMS均向用户提供了自身的PSM扩展。本书描述的是SQL/PSM标准，表明这些功能的主要思想，帮助大家理解任何与特定系统相关的该类语言。46PSM模块 PSM中定义了模块(modules)，该模块是如下内容的集合： 函数和过程定义 临时关系声明 其他可选声明471. 创建PSM函数和过程 过程声明过程声明 PSM

23、过程的参数是模式前缀模式前缀-参数名参数名-参数类型参数类型三元组 模式前缀模式前缀为IN|OUT|INOUT。这三个关键字分别表明参数是仅输入的仅输入的|仅输出的仅输出的|既可输入又可输出既可输入又可输出的的。缺省前缀是IN，可省略。CREATE PROCEDURE () 可选的局部变量声明可选的局部变量声明 ； 定义过程的可执行的代码体定义过程的可执行的代码体48创建PSM函数和过程 函数声明函数声明 函数定义与过程定义的不同之处 使用保留字使用保留字FUNCTION； 必须指定返回值的类型；必须指定返回值的类型； 函数的参数只可以是函数的参数只可以是IN模式。模式。PSM阻止了函数中的副

24、作 用，从函数中得到信息的唯一方式是通过函数的返回值。 在过程定义中常指出IN模式，但函数参数中不指明。CREATE FUNCTION () RETURNS ；49存储过程示例 存储过程中SQL语句的限制：只允许查询进行单元组选择语句和基于游标的访问。 PSM过程将新旧两个地址作为其参数，并且用新地址替换MovieStar中每一个旧地址。变量类型与变量类型与MovieStar模式定模式定义中义中address的类的类型相匹配型相匹配参数名可作常量参数名可作常量PSM过程和函数中过程和函数中的参数或别的局部的参数或别的局部变量不要求加冒号变量不要求加冒号502. PSM中的简单语句格式 调用语句

25、 返回语句 局部变量声明 赋值语句 语句组 语句标号51(1)过程调用的语句 CALL() 这种调用语句在不同的地方使用不同的形式： 在宿主语言中的调用形式在宿主语言中的调用形式 例如: EXEC SQL CALL Foo( :x, 3)； 作为另一个作为另一个PSM函数或过程的语句。函数或过程的语句。 作为发送给基本作为发送给基本SQL界面的界面的SQL命令。命令。 例如：把语句CALL Foo(1,3)；发送给该界面，并分别用 1和3作为赋值过程的两个参数，然后执行存储过程Foo 注意：这里不允许调用函数。在PSM中调用函数应使用函数名和匹配的参数作为表达式的一部分。52(2)返回语句 R

26、ETURN； 该语句只能出现在函数中。它计算表达式的值，并将函数的返回值设置为该计算结果。 和普通编程语言不同的是： PSM的返回语句不结束这个函数。 甚至，它将继续控制后面的语句，而且在函数完成之前返回值都可能会改变。53(3)局部变量声明 DECLARE； 用给定的类型声明给定名称的变量。 这个变量是局部的，在函数或者过程运行后，DBMS不再保存其值。 函数或过程体中的声明必须在可执行语句之前54(4)赋值语句 SET=; 除了引导保留字SET外，PSM中的赋值和别的语言完全相似。计算等号右边表达式的值，将其赋值给左边的变量。 表达式可以是NULL，甚至可以是查询，只要该查询是返回一个单值

27、。55(5)语句组 语句组以分号结束，并置于保留字BEGIN和END之间。 这种构造被当作单个语句，可以出现在任何单个语句可以出现的地方。 特别是，由于过程或函数体相当于单个语句，所以在过程和函数体中可插入任何语句序列，只要它们被置于BEGIN和END之间。56(6)语句标号 语句标号：用名字(标号名)和冒号作为前缀来标识语句。573. 分支语句(IF语句) 其形式与C和其他类似语言的不同是： 用保留字END IF结束 嵌套在IF语句中的ELSE子句以单词ELSEIF开始可选可选任何布尔类型的表达式任何布尔类型的表达式语句列表由以分号结束语句列表由以分号结束的语句构成，但不必置的语句构成，但不

28、必置于于BEGINEND之间之间Page 5758课本示例P236例9.12 编写一个关于年份y和电影公司s的函数，它返回一个布尔值，其值为TRUE当且仅当电影公司s在第y年至少制作了一部喜剧电影，或其该年没有制作任何电影594. PSM中select-from-where查询方式 (1)子查询可用于条件语句中，或者一般而言，在SQL中任何地方使用子查询都是合法的。Page 59604. PSM中select-from-where查询方式 (2)返回单一值的查询可用在赋值语句的右边。 (3)PSM中单元组选择语句是合法语句。INTO子句将变量赋值为单个返回元组的分量。这些变量可以是局部变量或P

29、SM过程的参数。 SET presNetWorth=(SELECT netWorth FROM Studio,MovieExec WHERE presC#=cert# AND S=studioName);614. PSM中select-from-where查询方式 (4)声明和使用游标。下面几点是不同的： 语句中不出现EXEC SQL 局部变量不使用冒号前缀 使用游标的例子将被延迟到下一节学习了PSM循环语句之后625. PSM中的LOOP循环 LOOP END LOOP； 若对LOOP语句设置循环标识，可使用下面的语句中断循环： LEAVE; 循环中可用游标读取元组，当没

30、有更多元组时，就希望离开这个循环。对于表示没有找到元组的SQLSTATE值( 02000)，可以定义一个条件名： DECLARE Not_Found CONDITION FOR SQLSTATE 02000； 更一般地，可以用如下语句声明表示任何希望与SQLSTATE值相对应的标识作为条件： DECLARE CONDITION FOR SQLSTATE ;63PSM循环示例 本例的PSM过程将电影公司名称s作为输入参数，并且用输出参数mean和variance给出电影公司s拥有的所有电影长度的平均值和方差。Page 63646.PSM中的FOR循环 PSM中的FOR循环结构，唯一重要的目的是游

31、标的迭代。65FOR循环示例Page 6566PSM中的其他循环 PSM中也有while和repeat循环，其含义与C相同。也就是说，可以创建如下形式的循环 WHILEDO END WHILE； 或者这种形式的循环： REPEAT UNTIL END REPEAT;677. PSM的异常处理 SQL系统通过在长为5个字符的字符串SQLSTATE变量中设置非零数字序列来表明错误条件。例如： 02000表示“没有找到元组” 21000表示单元组选择返回了多个元组 PSM可以声明称为异常处理异常处理的代码。即在语句或语句组执行过程中，当错误代码列表中的任何一个出现在SQLSTATE中时，就调用异常处

32、理。68异常处理的组成 每一个异常处理都和一个由BEGINEND描述的代码块有关。处理过程出现在代码块中，并且仅仅针对代码块中的语句。 异常处理的组成异常处理的组成 一组异常条件：当这些条件成立时调用异常处理 当异常发生时，与该异常相关联的执行代码 指明处理器完成处理后的转移去处。69异常处理声明的形式 DECLARE HANDLER FOR 有3种选择： CONTINUE：表示执行异常处理声明中的语句之后，继续执行产生异常的语句之后的那条语句。 EXIT：表示执行异常处理语句后，控制离开声明异常处理的BEGINEND块，下一步执行该代码块之后的语句 UNDO：与EXIT差不多，区别在于到目前为止，已执行的该块语句对数据库或局部变量产生的变化都被撤消 由逗号分隔的条件的列表，可以是被声明的条件，也可以是SQLSTATE和5位字符串的表达式。70异常示例 编写一个PSM函数，以电影片名作为参数，返回电影的年份。如果该片名的电影不存在或是不止一个的 话，则返回NULL。如果有多条语句，则放在如果有多条语句，则放在BEGINEND之间之间718. 使用PSM函数和过程 在嵌套的SQL程序、PSM代码或提供给基本界面的普通S