ch9_服务器环境下的SQL.ppt

1 本章讨论的问题 1 在独立使用SQL语言进行数据库操作时 这种使用方法被称为交互式SQL 交互式SQL的缺点是只能进行数据库的操作 不能进行数据处理 当一个程序既要访问数据库又要处理数据时 把SQL语言嵌入到程序设计语言即宿主语言中 将SQL语言访问数据库的功能和宿主语言的数据处理功能结合起来是目前常用的办法 以这种方式使用的SQL被称为嵌入式SQL 2 本章讨论的问题 2 本章主要讨论的就是如何把SQL应用到一个完整的编程环境中 由于宿主语言编译程序不能识别SQL语言 如何将嵌有SQL语言的宿主语言程序编译成可执行代码 宿主语言和DBMS之间如何传递数据 数据库的查询结果一般是一些元组的集合 这些元组必须赋值给宿主语言变量才能被宿主语言使用 如何将元组赋给宿主语言的变量 DBMS和宿主语言的数据类型不完全对应 如何在两者之间进行必要而且合理的数据类型的转换 3 SQL的三种编程方式 嵌入到宿主语言 常规程序设计语言 如C 中关键问题 SQL与环境变量 宿主语言之间如何交换数据存储过程将SQL和被称为持久性存储模块的一般通用程序结合起来 这些模块是以数据库模式形式存储的代码段 由用户以命令的形式执行调用级界面以常规语言编程 用函数库来访问数据库 例如为了从C程序中调用 可以使用称为SQL CLI的SQL标准库 Java的JDBC 或PHP等 4 主要内容 9 1编程环境下的SQL9 2模式中的存储过程9 3SQL环境9 4使用调用层接口9 5SQL中的安全机制和用户认证 5 包含SQL语句的典型编程系统 将包含宿主语言 嵌套SQL语句的程序提交给预处理器 预处理器将嵌套SQL语句转化为调用函数 此函数把SQL语句当作字符串参数 并且执行这个SQL语句经过预处理的宿主语言程序随后以通常的方式编译 6 DBMS销售商提供了必要的函数库 这样实现SQL的函数被执行 并且整个程序像一个整体一样运作 还有另一种方式 程序员用宿主语言编写程序 在涉及到数据库访问时直接使用这些函数调用 这就是SQL编程的第3种方式 调用级界面 CLI 7 SQL与宿主语言之间的阻抗不匹配问题 SQL与编程语言的数据类型差别非常大SQL 关系数据模型 不使用指针 结构体 循环和分支常规编程语言 整型 实型 字符型 指针 记录 数组 无法直接表示集合 因此 在SQL和常规编程语言之间不能直接转移数据SQL与常规编程语言二者缺一不可SQL在很大程度上帮助程序员编写数据库操作而很多重要工作 如算法实现 又离不开常规编程语言必须设计一种机制 例如提供接口 允许程序的开发既可以使用SQL 也可以使用常规编程语言 宿主语言 8 SQL 宿主语言接口 数据库只能由SQL语句访问 在SQL和宿主语言之间通过宿主语言变量来传递信息 可以用SQL语句读或写宿主语言变量 数据库和宿主语言程序之间实现信息交换的变量称为共享变量SQL中对共享变量的引用要加上冒号作为前缀在宿主语言中这些变量并不需要冒号 9 共享变量的声明 EXECSQLBEGINDECLARESECTION 共享变量的声明EXECSQLENDDECLARESECTION 共享变量的声明要放在DECLARE节中 声明节中的变量声明形式可以是宿主语言要求的任何形式 为使声明的变量有意义 共享变量类型必须是宿主语言和SQL都可以处理的如整型 实型和字符型 或者数组类型 声明节 10 示例9 3 EXECSQLBEGINDECLARESECTION charstudioName 50 studioAddr 256 charSQLSTATE 6 EXECSQLENDDECLARESECTION 11 使用共享变量 12 特殊变量SQLSTATE 在SQL标准中 SQLSTATE这个特殊的变量由系统定义 用于连接宿主语言程序与SQL执行系统 SQLSTATE是五个字符的数组类型 每次调用SQL的库函数 向SQLSTATE变量中存放一个代码 该代码表示调用过程中出现的问题 宿主语言程序能够读出SQLSTATE的值并且基于该值做出判断 13 SQLSTATE变量的值 SQL标准同时指定了大量的五个字符的代码和它们的意义 例如 00000 没有产生任何错误 02000 没找到作为SQL查询结果组成部分的元组 这个代码非常重要 它允许在宿主语言程序中创建一个循环并且每执行一次循环检查一个元组 当关系中最后一个元组被检查后中止该循环 21000 表示单元组选择返回了多个元组 14 关键字EXECSQL 在宿主语言中使用SQL语句时 通过SQL语句前面的关键字EXECSQL提示预处理器将有SQL代码进入 系统将预处理这些语句 用宿主语言中合适的函数调用来代替这些语句 并充分利用与SQL相关的函数库 15 SQL语句的嵌入 任何不返回结果的SQL语句 即非查询语句 都可以用EXECSQL为前缀直接嵌入到宿主语言中包括INSERT DELETE和UPDATE语句以及那些创建 修改或者删除表和视图等模式元素的语句 然而由于 阻抗不匹配 select from where查询不能直接嵌套到宿主语言 查询产生的结果是元组包 但是大多数宿主语言均不直接支持集合或包数据类型 16 查询嵌入宿主语言的机制 为了将查询结果与宿主语言程序相连接 嵌套SQL有两种机制可选择 1 单元组选择语句 只有一个结果元组的查询可将该元组存储到共享变量中 一个变量对应元组的一个分量 2 游标 当查询结果是多个元组 可以为查询声明一个游标 游标范围覆盖结果关系中的所有元组 每个元组依次被提取到共享变量中 并由宿主语言进行处理 17 1 单元组选择语句 单元组选择的形式类似于交互式SQL中的select from where语句 只是SELECT子句后紧跟着关键字INTO和一连串的共享变量 以冒号作为前缀 如果查询结果是个单一元组 那么这个元组的分量将分配给这些变量并成为它们的值 如果结果没有元组或者多于一个元组 那么不会分配给这些共享变量 同时一个相应的错误码被写入到SQLSTATE变量中 18 单元组选择示例 19 2 游标 游标声明EXECSQLDECLARECURSORFOR其中查询可以是通常的select from where查询或者关系名 游标范围覆盖该查询产生的关系元组 打开游标 初始化 EXECSQLOPEN初始化游标的位置 使游标指向其所覆盖的关系中的第一个元组之前 20 2 游标 续1 fetch子句EXECSQLFETCHFROMINTO将游标向前推进一个位置 然后按照游标的当前位置取一个元组 对共享变量进行赋值 每个关系元组的属性对应列表里的一个变量 假如有一个可获取的元组 那么该元组相应的分量将赋值给对应的变量 如果元组已经被遍历过了 那么不会返回任何元组 且SQLSTATE被赋值为 02000 表示 没有发现任何元组 21 2 游标 续2 关闭游标EXECSQLCLOSE游标将不再覆盖关系的元组 然而 游标可由另外一条OPEN语句重新初始化 它将重新覆盖这个关系的元组 22 游标示例 P266例7 4分别计算净资产位数从1 14的出品人个数 23 游标更新 当游标遍历一个基本表的元组时 不仅可以读和处理每个元组的值 也可以修改或者删除当前元组 这里的UPDATE和DELETE语句 要求其WHERE子句只能是WHERECURRENTOF 其后跟着游标的名称 24 P267例7 5更新行政长官的资产值 若净资产值少于1000则删除该元组 否则将其净资产值加倍 MovieExecs name address cert netWorth 游标更新示例 25 防止并发更新 不希望游标读取的元组被并发的变化所影响对游标所读取关系进行修改的语句 在游标读取元组前就已彻底完成或在游标读取元组后再运行为了保证这一点 对于并发变化可以将游标声明为对并发修改不敏感 insensitive 例如 EXECSQLDECLAREexecCursorINSENSITIVECURSORFORSELECTnetWorthFROMMovieExec 保证在execCursor打开和关闭之间 对关系MovieExec所作的变化不会影响游标提取到的元组集合 26 将游标声明为READONLY 若已知关系R上的一个游标不会改变R 则该游标可与R的其他不敏感游标同时运行 将游标声明为FORREADONLY 那么数据库系统可以保证基本关系R不会因为读取游标而修改了关系R EXECSQLDECLAREexecCursorCURSORFORSELECTnetWorthFROMMovieExecFORREADONLY 这样 任何试图通过游标execCursor所做出的关系修改都会产生错误 27 卷型游标SCROLL 游标同时提供了遍历关系中元组的顺序选择缺省选择是从关系顶端开始 依次提取元组直至末尾 最常用 也可以通过定义卷型游标按别的顺序提取元组 为了可以按其他顺序提取元组 有2个步骤 声明游标时 将关键字SCROLL置于保留字CURSOR之前 告诉SQL系统 游标的使用方式不只是按照元组顺序向前移动 FETCH语句中 关键字FETCH后面的选项决定所期望的元组的位置 28 FETCH选项 NEXT PRIOR 按顺序提取相对于游标当前位置的下一个 上一个元组 NEXT为默认值 FIRST LAST 提取第一个 最后一个元组RELATIVEn n为正 负整数 相对游标当前位置向前 n为正整数 向后 n为负整数 移动n个元组 RELATIVE1即NEXT RELATIVE 1即PRIORABSOLUTEn n为正 负整数 从头部 n为正整数 尾部 n为负整数 移动n个元组 ABSOLUTE1即FIRST ABSOLUTE 1即LAST 29 卷型游标示例 EXECSQLDECLAREexecCursorSCROLLCURSORFORMovieExec EXECSQLOPENexecCursor EXECSQLFETCHLASTFROMexecCursorINTO execName execAddr certNo worth While 1 EXECSQLFETCHPRIORFROMexecCursorINTO execName execAddr certNo worth 30 动态SQL 嵌入式SQL有两种主要的形式 静态的SQL和动态的SQL 静态的SQL语句是在编写程序时要定义所有的SQL语句 如INSERT SELECT等普通的SQL语句 但很多情况下 SQL语句或SQL所带的参数在编译时并不知道 应用必须在运行时才能生成SQL语句 这种在运行时才能生成的SQL语句叫动态SQL语句 对于动态SQL SQL语句可以被应用程序在运行时构造 31 如果在预编译时下列信息不能确认 就必须使用动态SQL技术 1 SQL语句正文2 主变量个数3 主变量的数据类型4 SQL语句中引用的数据对象 例如 列 基本表 视图等 动态SQL方法允许在程序运行过程中临时 组装 SQL语句 主要有三种形式 1 语句可变 允许用户在程序运行时临时输入完整的SQL语句 2 条件可变 对于查询语句 SELECT子句是确认的 即语句的输出是确定的 其他子句 如Where子句 Having短语 有一定的可变性 3 数据库对象 查询条件均可变 对于查询语句 Select子句的列名 From子句中的表名或视图名 Where子句和Having短语中的条件均可由用户临时构造 即语句的输入和输出可能都是不确定的 32 与之相关的动态定义语句简要介绍如下 PREPARE语句 将一字符串解释成一组SQL语句 并赋给它一个语句标识符 以后的动态管理语句通过语句标识来引用这组SQL语句 PREPARE语句的一般形式为 PREPARE语句标识名from SQL语句 或PREPARE语句标识名from 宿主变量 EXECUTE语句 运行与其语句标识符对应的事先准备好的语句 对非select语句 用EXECUTE来运行 EXECUTE语句的一般形式为 EXECUTE被预备的语句标识名 using宿主变量列 3 EXECUTEIMMEDIATE语句 立即执行语句 它接收一字符串 将其解释成SQL语句并立即执行它 然后释放准备SQL语句所占用的资源 33 4 DECLARE语句 为已准备好的select语句说明游标 5 FREE语句 释放已准备好的语句或已打开的游标所占用的资源6 DESCRIBE语句 确定已准备好的语句是否为select语句 如果是 则得到单一查找行的存储要求 否则只返回语句类型 34 动态SQL的特性为 语句的说明与执行分为两步 第一步 PREPARE语句用于分析SQL语句 并且在必要时建立一个程序计划 第二步 用EXECUTE或定义游标来执行被预备好的SQL语句 这样一旦一条SQL语句经过预备处理后 就可重复执行它多次 加入仅执行一次的SQL语句那就不必做预备处理 35 动态SQL执行的两个步骤 1 读入字符串并转换成可执行的SQL语句EXECSQLPREPAREVFROMV是SQL变量 是其值为字符串的任意一条宿主语言表达式 宿主语言程序指导SQL系统接受刚读到的字符串 经语法分析后将其转化为可执行SQL语句 用V表示2 执行该SQL语句EXECSQLEXECUTEV执行V所代表的SQL语句 36 上述两个步骤也可以合二为一EXECSQLEXECUTEIMMEDIATE如果一条语句被编译一次 然后执行很多次时 就会看到合并这两步是不利的 使用EXECUTEIMMEDIATE 每次语句执行时都要付出准备该语句的代价 而不是只付出一次 37 动态SQL示例 38 本节习题机械三P233清华版P2719 3 1 a d 7 1 1 a d f f 使所有 旧 PC的价格调低 100 要确保在你的程序运行期间插入的任何 新 PC的价格不会调低 7 1 3请使用滚动游标找出如下关系中满足条件的所有PC PC model speed ram hd cd price 条件是 至少有两种速度相同而价格更贵的PC 提示 读取先按速度再按价格排序的PC的元组 对于每个读出的元组 往前跳两个元组看看速度是否没变 39 主要内容 9 1编程环境下的SQL9 2模式中的存储过程9 3SQL环境9 4使用调用层接口9 5SQL中的安全机制和用户认证 40 持久性存储模块 PersistentStoredModules SQL PSM 简写为PSM 第二种结合SQL与通用程序设计语言的编程方法 允许用简单通用的语言编写过程并且将它们存储在数据库中 作为模式的一部分 称为PSM 这些过程可以在数据库中完成不能用SQL单独完成的处理 每个商用性的DBMS均向用户提供了自身的PSM扩展 本书中介绍的是SQL PSM标准 描述了这些扩展功能的主要思想 41 PSM模块 PSM中定义了模块 modules 该模块是如下内容的集合 函数和过程定义临时关系声明其他可选声明 42 1 创建PSM函数和过程 PSM过程的声明创建过程的保留字CREATEPROCEDUREPSM过程的参数是模式前缀 参数名 参数类型三元组模式前缀为IN OUT INOUT 这三个关键字分别表明参数是仅输入的 仅输出的 既可输入又可输出的 缺省前缀是IN 可省略 CREATEPROCEDURE 可选的局部变量声明定义过程的可执行的代码体 43 创建PSM函数和过程 PSM函数的声明函数定义与过程定义的不同之处使用保留字FUNCTION 必须指定返回值的类型RETURN 函数的参数只可以是IN模式 换句话说 PSM阻止了函数中的副作用 从函数中得到信息的唯一方式是通过函数的返回值 在过程定义中常指出IN模式 但函数参数中不指明 CREATEFUNCTION RETURNS 44 存储过程示例 存储过程中SQL语句的限制 只允许查询进行单元组选择语句和基于游标的访问 PSM过程将新旧两个地址作为其参数 并且用新地址替换MovieStar中每一个旧地址 变量类型与MovieStar模式定义中address的类型相匹配 参数名可作常量 PSM过程和函数中的参数或别的局部变量不要求加冒号 45 2 PSM中的简单语句格式 调用语句返回语句局部变量声明赋值语句语句组语句标号 46 1 过程调用的语句 CALL 调用语句在不同的地方使用不同的形式 宿主语言中的调用形式 EXECSQLCALLFoo x 3 作为另一个PSM函数或过程的语句作为发送给基本SQL界面的SQL命令例如 把语句CALLFoo 1 3 发送给该界面 并分别用1和3作为赋值过程的两个参数 然后执行存储过程Foo在PSM中调用函数应使用函数名和匹配的参数作为表达式的一部分 47 2 返回语句 RETURN 该语句只能出现在函数中 它计算表达式的值 并将函数的返回值设置为该计算结果 和普通编程语言不同的是 PSM的返回语句不结束这个函数 甚至 它将继续控制后面的语句 而且在函数完成之前返回值都可能会改变 48 3 局部变量声明 DECLARE 用给定的类型声明给定名称的变量 这个变量是局部的 在函数或者过程运行后 DBMS不再保存其值 函数或过程体中的局部变量声明必须在可执行语句之前 49 4 赋值语句 SET 除了引导保留字SET外 PSM中的赋值和别的语言完全相似 计算等号右边表达式的值 将其赋值给左边的变量 表达式可以是NULL 甚至可以是查询 只要该查询是返回一个单值 50 5 语句组 语句组以分号结束 并置于保留字BEGIN和END之间 这种构造被当作单个语句 可以出现在任何单个语句可以出现的地方 特别是 由于过程或函数体相当于单个语句 所以在过程和函数体中可插入任何语句序列 只要它们被置于BEGIN和END之间 51 6 语句标号 语句标号 用名字 标号名 和冒号作为前缀来标识语句 52 3 分支语句 IF语句 其形式与C和其他类似语言的不同是 用保留字ENDIF结束嵌套在IF语句中的ELSE子句以单词ELSEIF开始 可选 53 课本示例 编写一个关于年份y和电影公司s的函数 它返回一个布尔值 其值为TRUE当且仅当电影公司s在第y年至少制作了一部喜剧电影 或其该年没有制作任何电影 54 4 PSM中的select from where查询方式 1 子查询可用于条件语句中 或者一般而言 在SQL中任何地方使用子查询都是合法的 55 4 PSM中的select from where查询方式 2 返回单一值的查询可用在赋值语句的右边 3 PSM中单元组选择语句是合法语句 INTO子句将变量赋值为单个返回元组的分量 这些变量可以是局部变量或PSM过程的参数 SETpresNetWorth SELECTnetWorthFROMStudio MovieExecWHEREpresC cert ANDStudio name studioName 56 4 PSM中的select from where查询方式 4 声明和使用游标 下面几点是不同的 语句中不出现EXECSQL局部变量不使用冒号前缀使用游标的例子将被延迟到下一节学习了PSM循环语句之后 57 5 PSM中的LOOP循环 LOOPENDLOOP 若对LOOP语句设置循环标识 可使用下面的语句中断循环 LEAVE 循环中可用游标读取元组 当没有更多元组时 就希望离开这个循环 对于表示没有找到元组的SQLSTATE值 02000 可以定义一个条件名 DECLARENot FoundCONDITIONFORSQLSTATE 02000 更一般地 可以用如下语句声明表示任何希望与SQLSTATE值相对应的标识作为条件 DECLARECONDITIONFORSQLSTATE PSM循环示例 本例的PSM过程将电影公司名称s作为输入参数 并且用输出参数mean和variance给出电影公司s拥有的所有电影长度的平均值和方差 均值的定义 方差的定义 方差计算公式 59 6 PSM中的FOR循环 PSM中的FOR循环结构 唯一重要的目的是游标的迭代 60 FOR循环示例 61 PSM中的其他循环 PSM中也有while和repeat循环 其含义与C相同 也就是说 可以创建如下形式的循环WHILEDOENDWHILE 或者这种形式的循环 REPEATUNTILENDREPEAT 62 7 PSM中的异常处理 SQL系统通过在SQLSTATE变量中设置非零数字序列来表明错误条件 例如 02000 表示 没有找到元组 21000 表示单元组选择返回了多个元组PSM可以声明称为异常处理的代码 即在语句或语句组执行过程中 当错误代码列表中的任何一个出现在SQLSTATE中时 就调用异常处理 63 异常处理的组成 每一个异常处理都和一个由BEGIN END描述的代码块有关 处理过程出现在代码块中 并且仅仅针对代码块中的语句 异常处理的组成一组异常条件 当这些条件成立时调用异常处理当异常发生时 与该异常相关联的执行代码指明处理器完成处理后的转移去处 64 异常处理声明的形式 DECLAREHANDLERFOR转移方式有3种选择 CONTINUE 表示执行异常处理声明中的语句之后 继续执行产生异常的语句之后的语句 EXIT 表示执行异常处理语句后 控制离开声明异常处理的BEGIN END块 下一步执行该代码块之后的语句 UNDO 与EXIT差不多 区别在于到目前为止 已执行的该块语句对数据库或局部变量产生的变化都被撤消由逗号分隔的条件列表 可以是被声明的条件 也可以是SQLSTATE和5位字符串的表达式 65 异常示例 编写一个PSM函数 以电影片名作为参数 返回电影的年份 如果该片名的电影不存在或是不止一个的话 则返回NULL 66 8 使用PSM函数和过程 在嵌套的SQL程序 PSM代码本身或提供给基本界面的普通SQL命令中都可调用PSM函数和过程用保留字CALL作为前缀来调用过程函数作为表达式的一部分出现 67 使用PSM函数和过程的示例 假定模式中包括了具有GetYear函数的模块 想象面对基本界面 准备输入DenzelWashington是RemembertheTitans中的影星这个事实 但却忘记了电影年份 只要这个名称的电影只有一部 并且它在关系Movies中 那么就不必通过预先查询去找出该年份 而且 可以将下面的语句插入到基本SQL界面中 68 P241习题9 4 1在电影数据库上用PSM过程或函数完成下列任务 Movies title year length genre studioName producerC StarsIn movieTitle movieYear starName MovieStar name address gender birthdate MovieExecs name address cert netWorth Studio name address presC a 给定电影公司的名称 计算其制片经理的净产值 c 给定电影公司名称 将该电影公司的两部最长的电影片名作为参数输出 如果没有这样的电影则参数中的一个或两个被赋值为NULL 例如 如果电影公司只有一部电影 则没有 第二长的 电影 d 给定一个影星的名字 找出由他出演的时间超过120分钟的最早的电影 如果没有这样的电影 则返回年份0 69 主要内容 9 1编程环境下的SQL9 2模式中的存储过程9 3SQL环境9 4使用调用层接口9 5SQL中的安全机制和用户认证 70 什么是SQL环境 SQL环境是SQL中数据所在的框架 该框架下可以存储数据 并对数据进行SQL操作 可以看做安装并运行在某些系统上的DBMS 数据库的所有元素 包括表 视图 触发器等 都是在SQL环境中定义的 这些元素组成了层次性结构 模式目录簇 71 1 模式 指 数据库模式 而非 关系模式 是组织的基本单元 模式是表 视图 断言 触发器等数据库元素的集合 模式声明的形式 CREATESCHEMA模式声明示例7 19 72 1 模式 续 CREATESCHEMA提供了在SQL语句中创建表 视图以及授予对象权限的方法 不同模式的模式元素可以重名 可用 模式名 模式元素名 来引用某个模式元素 没有必要一次就声明完所有的模式 可以使用合适的CREATE DROP或ALTER语句来修改或增加模式使用SETSCHEMA语句改变 当前 的模式 例如 SETSCHEMAMovieSchema 将使例7 19描述的模式作为当前模式 73 2 目录 模式的集合 模式的创建和修改都在目录中 每个目录有一个或多个模式 目录中的模式名必须唯一 每个目录包含一个叫IMFORMATION SCHEMA的特殊模式 这个模式包含了该目录中所有模式的信息 设置当前目录的语句 SETCATALOG 对模式的操作 新建 修改模式等 都是针对当前目录 74 数据库模式元素的全名 例如 目录MovieCatalog中的模式MovieSchema的表Movies的引用如下 MovieCatalog MovieSchema Movies如果目录是缺省的或是当前的目录 则可省去目录名如果模式也是缺省的或当前的模式 那么模式部分也可以省去 这样只留下元素自己的名称 然而 当需要访问当前模式或目录以外的元素时 就不得不使用完全名 75 3 簇 簇是目录的集合 每个用户有一个关联的簇 是用户可访问的所有目录的集合 簇是被提交的查询的最大范围 故在一定程度上 簇是特定用户所看到的 数据库 76 SQL环境中的客户和服务器 依照SQL标准 SQL环境有两种特殊的进程 SQL服务器 支持对数据库元素的操作 充当数据库服务器的角色 SQL客户程序 允许用户连接到服务器上 对数据库进行操作 例如 服务器运行在一个大的存储数据库的主机上 而客户程序运行在其他主机上 也许是远离服务器的个人工作站 也许是和服务器运行在同一台主机上 SQL的客户程序和服务器通常与连接 会话和模块这三个概念紧密地联系在一起 77 连接 连接是指将客户端和服务器连接起来 如果在SQL客户端主机上运行包含SQL的程序 则可通过CONNECT语句打开客户端和服务器间的连接 CONNECTTOASAUTHORIZATION依赖于安装 可用名称DEFAULT将用户连接到任何被作为 缺省服务器 安装的SQL服务器 AUTHORIZATION授权子句后跟随着用户名和密码 提供给服务器进行验证 可在以后用于引用连接 原因是SQL允许用户打开好几个连接 但任何时候只有一个连接有效 78 引用连接 任何当前有效的连接进入休眠状态后 只有用SETCONNECTION语句显式地调用才能将其激活 SETCONNECTIONconn1 使用DISCONNECT语句断开连接时conn1被中止 不是休眠 不能再被激活 DISCONNECTconn1 注 如果连接创建后再也不被引用 CONNECTTO子句中的AS和连接名可以省略 79 会话 当连接激活时 所执行的SQL操作将在客户端和服务器之间形成请求 响应式的交互 我们称这样的操作序列为一个会话 Session 会话和创建它的连接处于同一状态 例如 当连接处于休眠状态时 它的会话也处于休眠状态SETCONNECTION语句可激活连接 同时激活相应会话 每个会话有一个当前目录和该目录中的一个当前模式由语句SETSCHEMA和SETCATALOG进行设置每个会话都有一个授权用户 80 模块 SQL操作代码 SQL2标准建议SQL实现至少为用户提供如下三种类型的应用程序 称为模块 之一 通用SQL接口 这种类型就是交互式SQL 在这种方式下用户可以输入由SQL服务器执行的SQL语句 每个查询或其他操作都是独立的模块 嵌入式SQL 在这种方式下 SQL语句出现在宿主语言程序中 并有EXECSQL引导 通过预处理程序把嵌入式SQL语言转换为适于SQL系统的函数调用 编译好的宿主语言程序执行到相应的函数语句时将调用相应的函数 实际模块 含有存储函数或过程集合的模块 这些函数 过程部分是宿主语言代码 部分是SQL语句 它们之间可通过参数或共享变量进行通讯 81 SQL客户端 服务器交互 模块的执行被称为SQL代理 模块是代码 SQL代理是代码的执行 模块和SQL代理作为一个整体 通过访问SQL客户端建立与数据库的连接 82 三层体系结构图 83 三层体系结构 1 Web服务器 可以解析HTTP协议 当Web服务器接收到一个HTTP请求 会返回一个HTTP响应 例如送回一个HTML页面 2 应用服务器 通过各种协议 可以包括HTTP 提供访问商业逻辑的途径 供客户端应用程序使用 3 数据库服务器 运行DBMS并执行应用服务器请求的查询和更新 84 三层体系结构的解释 如果你访问的网页只有象HTML这种页面的 用WEB服务器就够了但是如果是JSP 也就是含JAVA代码的HTML 则就需要JAVA应用服务器了 因为只有JAVA应用服务器才能解析JSP里的JAVA代码 并将解析结果以HTML的格式返回 85 主要内容 9 1编程环境下的SQL9 2模式中的存储过程9 3SQL环境9 4使用调用层接口9 5SQL中的安全机制和用户认证 86 授权ID 本节首先学习SQL允许对数据库元素进行哪些授权 然后学习用户如何获得授权 即授权ID 最后如何授权 SQL假定存在授权ID 这些ID基本上都是用户名SQL有一个特殊的授权ID 称作PUBLIC 它包含了所有用户 授权ID可以被授予权限 87 1 权限 SQL中定义了九种类型的权限 SELECT INSERT DELETE UPDATE 应用于关系的查询 插入 删除 更新的权力 REFERENCE 在完整性约束下引用关系的权力 约束可以是第7章介绍的断言 基于属性或元组的检查 或引用完整性约束等 USAGE 主要应用在关系和断言之外的其他模式元素上 给出了在声明中使用域等其他模式元素的权利TRIGGER 定义这个关系上的触发器的权力EXECUTE 执行如PSM过程 函数之类代码的权力UNDER 创建给定类型的子类型的权力 88 权限示例 考虑下列插入语句所需的权限拥有关系Studio的INSERT或INSERT name 权限拥有关系Movie的SELECT或SELECT studioName 权限拥有关系Studio的SELECT或SELECT name 权限 其他属性分量上只能接受缺省值或NULL 89 2 创建权限 取得权限有两个方面需要明确创建权限 权限最初是如何创建的授权 权限如何从一个用户传递到另一个用户 90 SQL元素 如模式或模块 都有一个属主 属主拥有其所属事物的所有权限 创建权限的关键在于如何取得属主身份 在SQL中建立属主身份有三种情况 模式创建时 该模式及其所有模式元素的所有权都属于其创建者 该用户拥有模式元素所有可能权限会话被CONNECT语句初始化时 可用AUTHORIZATION子句指定用户 模块 SQL操作代码 创建时 可通过AUTHORIZATION子句选择其属主 例如 CONNECTTOStarfleet sql serverASconn1AUTHORIZATIONkirk 用户kirk创建了一个连接到名为Starfleet sql server的SQL服务器的链路conn1 在SQL的实现中还将验证用户名是否有效 例如通过询问密码 也可将密码包含在AUTHORIZATION子句中 但这种方式密码可见 模块创建语句中的子句AUTHORIZATIONpicard 使得用户picard成为该模块的属主 模块也可以不指定属主 这种情况下模块被公开执行 执行模块中的任何操作所必需的权限必须从别处取得 例如 在模块执行过程中连接和会话与用户的关联 2 创建权限 91 3 检查权限的过程 每个模块 模式和会话有一个相关用户 即授权ID 任何SQL操作有两部分 操作执行涉及的数据库元素产生操作的代理对代理有效的权限来自当前授权ID 这个ID可以是 模块授权ID 如果代理正在执行的模块有一个授权ID会话授权ID只要当前授权ID拥有执行操作所涉及的数据库元素所必需的权限 就可以执行这个SQL操作 92 示例 关系表Movies和Studio是用户Janeway创建和拥有的模式MovieSchema的一部分 用户Janeway拥有这些表和模式MovieSchema的任何其他元素的所有权限 该插入示例可以有如下几种执行方式 93 插入示例执行方式1 用户Janeway创建了一个包含AUTHORIZATIONJaneway子句的模块 这个插入作为该模块的一部分来执行 则Janeway是模块授权ID 如果有模块授权ID 该ID总是变成当前授权ID 于是Janeway成为当前授权ID 则该模块及其SQL插入语句拥有与用户Janeway相同的权限 包括表Movies和Studio的所有权限 94 插入示例执行方式2 插入是模块的一部分 但是该模块没有属主 用户Janeway在CONNECT语句中用AUTHORIZATIONJaneway子句打开一个连接 会话授权ID 于是Janeway再次成为当前授权ID 该插入拥有了所需的所有权限 95 插入示例执行方式3 用户Janeway将表Movies和Studio所有权限授权给用户archer 或是代表 所有用户 的特殊用户PUBLIC假定插入语句存在于带有AUTHORIZATIONarcher子句的模块中 模块授权ID是archer 因此当前授权ID是archer 而且archer拥有所需的权限 故插入再次被允许 96 插入示例执行方式4 假定用户Janeway已经将所需的权限给了用户archer 同时又假定插入语句存在于没有属主的模块中 模块在一个会话中执行 该会话的授权ID由AUTHORIZATIONarcher子句设置 会话授权ID是archer 这样当前授权ID是archer且这个ID拥有所需的权限 97 四条准则 拥有数据的用户 其ID就是当前授权ID 那么所需权限总是可用的 执行方法 1 和 2 如果用户的ID是当前授权ID 而且数据的属主已经把所需要的权限授予了该用户或授予给用户PUBLIC 那么这些权限也是可用的 执行方法 3 和 4 执行数据的拥有者所拥有的模块或者执行对数据已有授权的某个用户所拥有的模块 就使得所需要的权限是可用的 执行方法 1 和 3 在会话 它的授权ID是具有所需要权限的用户 期间执行公用模块是合法执行该操作的另一种方法 执行方法 2 和 4 98 4 授权 SQL提供了GRANT语句 以允许一个用户将权限授权给另一个用户 第一个用户仍保留所授予的权限 授权选项WITHGRANTOPTION如果用户A将带有授权选项的某个权限授权给用户B 则用户B可以把该权限再次授权给别的用户 带受权选项或不带 如果用户A授予用户B某个权限时不带授权选项 则用户B无法再将该权限授予别的用户 只有A能够把该权限授予别的用户 99 授权语句的语法格式 GRANTONTO WITHGRANTOPTION 典型的是一个关系 基本表或视图 如果是其他类型的元素 元素名之前应有作前缀 如ASSERTION 是一个或多个权限 如INSERT name 或SELECT 关键字ALLPRIVILEGES可根据需要在此出现 表示授权者对正在讨论的数据库元素可合法授予的所有权限 100 授权语句的执行 为了合法地执行这条授权语句 执行它的用户必须拥有被授予的权限 而且这些权限还必须带有授权选项 但授权者可以拥有比授出的权限更通用的权限 带有授权选项 例如 表Studio的INSERT name 权限被授出 同时授权者拥有表Studio的更通用的带有授权选项的权限INSERT 101 授权示例 用户Janeway是MovieSchema模式的属主 该模式包括Movies title year length genre studioName producerC Studio name address presC 1 Janeway将表Studio的INSERT和SELECT权限以及表Movies的SELECT权限授予用户kirk和picard 而且包括了这些权限的授权选项 GRANTSELECT INSERTONStudioTOkirk picardWITHGRANTOPTION GRANTSELECTONMoviesTOkirk picardWITHGRANTOPTION 102 授权示例 续 2 Picard授予用户sisko相同的权限 但没有授权选项 GRANTSELECT INSERTONStudioTOSisko GRANTSELECTONMoviesTOSisko 3 Kirk也授予Sisko表Studio的SELECT和INSERT name 权限以及Movies的SELECT权限 GRANTSELECT INSERT name ONStudioTOSisko GRANTSELECTONMoviesTOSisko 103 5 授权图 用授权图表示授权 SQL系统维护授权图的表示 并跟踪权限