ch9_服务器环境下的SQL

1本章讨论的问题（1）在独立使用SQL语言进行数据库操作时，这种使用方法被称为交互式SQL，交互式SQL的缺点是只能进行数据库的操作，不能进行数据处理。当一个程序既要访问数据库又要处理数据时，把SQL语言嵌入到程序设计语言即宿主语言中，将SQL语言访问数据库的功能和宿主语言的数据处理功能结合起来是目前常用的办法，以这种方式使用的SQL被称为嵌入式SQL。2本章讨论的问题（2）本章主要讨论的就是如何把SQL应用到一个完整的编程环境中。由于宿主语言编译程序不能识别SQL语言，如何将嵌有SQL语言的宿主语言程序编译成可执行代码？宿主语言和DBMS之间如何传递数据？数据库的查询结果一般是一些元组的集合，这些元组必须赋值给宿主语言变量才能被宿主语言使用。如何将元组赋给宿主语言的变量？DBMS和宿主语言的数据类型不完全对应，如何在两者之间进行必要而且合理的数据类型的转换？3SQL的三种编程方式嵌入到宿主语言(常规程序设计语言，如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”7SQL与宿主语言之间的阻抗不匹配问题SQL与编程语言的数据类型差别非常大SQL：关系数据模型，不使用指针、结构体、循环和分支常规编程语言：整型、实型、字符型、指针、记录、数组……无法直接表示集合……因此，在SQL和常规编程语言之间不能直接转移数据SQL与常规编程语言二者缺一不可SQL在很大程度上帮助程序员编写数据库操作而很多重要工作(如算法实现)又离不开常规编程语言必须设计一种机制(例如提供接口)，允许程序的开发既可以使用SQL，也可以使用常规编程语言(宿主语言)8SQL/宿主语言接口数据库只能由SQL语句访问，在SQL和宿主语言之间通过宿主语言变量来传递信息，可以用SQL语句读或写宿主语言变量；数据库和宿主语言程序之间实现信息交换的变量称为共享变量SQL中对共享变量的引用要加上冒号作为前缀在宿主语言中这些变量并不需要冒号9共享变量的声明EXECSQLBEGINDECLARESECTION；共享变量的声明EXECSQLENDDECLARESECTION；共享变量的声明要放在DECLARE节中，声明节中的变量声明形式可以是宿主语言要求的任何形式。为使声明的变量有意义，共享变量类型必须是宿主语言和SQL都可以处理的如整型、实型和字符型，或者数组类型声明节10示例9.3EXECSQLBEGINDECLARESECTION；charstudioName[50]，studioAddr[256]；charSQLSTATE[6]；EXECSQLENDDECLARESECTION；11使用共享变量后面是嵌套的SQL语句共享变量在SQL中须加冒号作前缀12特殊变量SQLSTATE在SQL标准中，SQLSTATE这个特殊的变量由系统定义，用于连接宿主语言程序与SQL执行系统。SQLSTATE是五个字符的数组类型。每次调用SQL的库函数，向SQLSTATE变量中存放一个代码，该代码表示调用过程中出现的问题。宿主语言程序能够读出SQLSTATE的值并且基于该值做出判断。13SQLSTATE变量的值SQL标准同时指定了大量的五个字符的代码和它们的意义。例如：’00000’:没有产生任何错误‘02000’:没找到作为SQL查询结果组成部分的元组。这个代码非常重要：它允许在宿主语言程序中创建一个循环并且每执行一次循环检查一个元组，当关系中最后一个元组被检查后中止该循环。‘21000’：表示单元组选择返回了多个元组14关键字EXECSQL在宿主语言中使用SQL语句时，通过SQL语句前面的关键字EXECSQL提示预处理器将有SQL代码进入。系统将预处理这些语句，用宿主语言中合适的函数调用来代替这些语句，并充分利用与SQL相关的函数库。15SQL语句的嵌入任何不返回结果的SQL语句(即非查询语句)都可以用EXECSQL为前缀直接嵌入到宿主语言中包括INSERT、DELETE和UPDATE语句以及那些创建、修改或者删除表和视图等模式元素的语句。然而由于“阻抗不匹配”，select-from-where查询不能直接嵌套到宿主语言。查询产生的结果是元组包，但是大多数宿主语言均不直接支持集合或包数据类型。16查询嵌入宿主语言的机制为了将查询结果与宿主语言程序相连接，嵌套SQL有两种机制可选择：(1)单元组选择语句：只有一个结果元组的查询可将该元组存储到共享变量中，一个变量对应元组的一个分量。(2)游标：当查询结果是多个元组，可以为查询声明一个游标，游标范围覆盖结果关系中的所有元组，每个元组依次被提取到共享变量中，并由宿主语言进行处理。17(1)单元组选择语句单元组选择的形式类似于交互式SQL中的select-from-where语句，只是SELECT子句后紧跟着关键字INTO和一连串的共享变量(以冒号作为前缀)。如果查询结果是个单一元组，那么这个元组的分量将分配给这些变量并成为它们的值。如果结果没有元组或者多于一个元组，那么不会分配给这些共享变量，同时一个相应的错误码被写入到SQLSTATE变量中。18单元组选择示例19(2)游标①游标声明EXECSQLDECLARE游标名称CURSORFOR查询其中查询可以是通常的select-from-where查询或者关系名。游标范围覆盖该查询产生的关系元组。②打开游标(初始化)EXECSQLOPEN游标名称初始化游标的位置，使游标指向其所覆盖的关系中的第一个元组之前。20(2)游标(续1)③fetch子句EXECSQLFETCHFROM游标名称INTO变量列表将游标向前推进一个位置，然后按照游标的当前位置取一个元组，对共享变量进行赋值。每个关系元组的属性对应列表里的一个变量。假如有一个可获取的元组，那么该元组相应的分量将赋值给对应的变量。如果元组已经被遍历过了，那么不会返回任何元组，且SQLSTATE被赋值为‘02000’，表示“没有发现任何元组”。21(2)游标(续2)④关闭游标EXECSQLCLOSE游标名称游标将不再覆盖关系的元组。然而，游标可由另外一条OPEN语句重新初始化，它将重新覆盖这个关系的元组。22游标示例P266例7.4分别计算净资产位数从1-14的出品人个数23游标更新当游标遍历一个基本表的元组时，不仅可以读和处理每个元组的值，也可以修改或者删除当前元组。这里的UPDATE和DELETE语句，要求其WHERE子句只能是WHERECURRENTOF，其后跟着游标的名称。24P267例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后面的选项决定所期望的元组的位置。28FETCH选项NEXT|PRIOR:按顺序提取相对于游标当前位置的下一个|上一个元组。NEXT为默认值。FIRST|LAST:提取第一个|最后一个元组RELATIVEn:n为正|负整数。相对游标当前位置向前(n为正整数)|向后(n为负整数)移动n个元组。RELATIVE1即NEXT，RELATIVE-1即PRIORABSOLUTEn:n为正|负整数。从头部(n为正整数)|尾部(n为负整数)移动n个元组。ABSOLUTE1即FIRST，ABSOLUTE-1即LAST29卷型游标示例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与之相关的动态定义语句简要介绍如下：1.PREPARE语句：将一字符串解释成一组SQL语句，并赋给它一个语句标识符，以后的动态管理语句通过语句标识来引用这组SQL语句。PREPA