第六章 SQL编程技术(数据库原理与应用).ppt

1、,第六章 SQL编程技术,教学内容 熟悉Transact-SQL的语法元素 掌握SQL的控制流语句与用户自定义函数的用法 理解游标、存储过程和触发器的概念与应用 了解嵌入式SQL的用法 教学重点 游标、存储过程和触发器 教学难点 触发器的工作原理,SQL与高级语言,SQL 高度非过程化 面向集合的操作方式 高级语言 从面向过程到面向对象 灵活的数据处理方式 SQL以同一种语法结构提供了交互式和嵌入式两种使用方式,Transact-SQL概述,与SQL Server通讯的所有应用程序都通过向服务器发送Transact-SQL语句来进行通讯，而与应用程序的用户界面无关 语法元素 标识符、数据类型、

2、函数、表达式、运算符、注释、保留关键字,标识符,作用 服务器、数据库和数据库对象（例如表、视图、列、索引、触发器、过程、约束、规则等）都有标识符 对象标识符是在定义对象时创建的，标识符随后用于引用该对象 分类 常规标识符：符合标识符格式规则 分隔标识符：包含在“”或内的标识符 符合标识符格式规则的标识符可以分隔，也可以不分隔 对不符合标识符规则的标识符必须进行分隔,标识符,标识符规则 取决于数据库的兼容级别(可用系统存储过程sp_dbcmptlevel来设置)，当兼容级别为80时，规则为： 第一个字符必须是下列字符之一： Unicode 标准 2.0 所定义的字母（ a-z 和 A-Z，以及来

3、自其它语言的字母字符） _、或者# 后续字符可以是： Unicode 标准 2.0 所定义的字母 来自基本拉丁字母或其它国家/地区脚本的十进制数字 $、_、或者# 标识符不能是Transact-SQL的保留字 不允许嵌入空格或其它特殊字符,标识符,常规标识符举例 SELECT * FROM my_Table WHERE ID=124 分隔标识符举例 SELECT * FROM my_Table WHERE ID=124 SELECT * FROM My Table WHERE Last Name=OBrien,SQL-92 规则： 双引号只能用于分隔标识符，不能用于分隔字符串 单引号必须用来包

4、含字符串，不能用于分隔标识符 如果字符串包含单引号，则需要在单引号前再增加一个单引号,标识符,一个对象的完整名称包括四个标识符 服务器名称、数据库名称、所有者名称和对象名称 格式 server.database.owner_name.object_name Server默认为本地服务器 Database默认为当前数据库 Owner_name默认为与当前连接的登录ID相关联的指定数据库中的用户名称,数据类型,作用 包含数据的对象（如表和视图中的列、存储过程中的参数、变量、函数、存储过程）都具有一个相关的数据类型，此数据类型定义对象所能包含的数据种类 基本类型的层次结构 数字、字符和二进制字符串、

5、table、cursor、sql_variant、timestamp、uniqueidentifier 用户定义的数据类型 可以使用系统存储过程sp_addtype创建,数据类型,数字 精确数字 整数：bigint、int、smallint、tinyint Bit decimal和numeric money和smallmoney 近似数字 float Real datetime和smalldatetime,数据类型,字符和二进制字符串 字符串 char、varchar和text Unicode字符串 nchar和nvarchar Ntext 二进制字符串 binary和varbinary im

6、age,常量,常量是表示特定数据值的符号，常量的格式取决于它所表示的值的数据类型 字符串：OBrien Unicode字符串：NMichl 二进制字符串常量：0 x12Ef bit常量：由数字0或1表示 datetime常量：April 15, 1998 、04/15/98 、14:30:24 、04:24 PM integer常量：1894 decimal常量：1894.1204 float和real常量：101.5E5 money常量：$542023.14 uniqueidentifier常量： 0 xff19966f868b11d0b42d00c04fc964ff、 6F9619FF-8

7、B86-D011-B42D-00C04FC964FF,变量,分类 局部变量：用户定义的变量，名称以开头 全局变量：由系统提供并赋值的变量（用户不能定义或修改），名称以开头 声明 DECLARE变量名称数据类型 如：DECLARE pub_id char(4), hire_date datetime,变量,赋值 SET变量名称表达式 当初始化多个变量时，为每个局部变量使用一个单独的 SET 语句 如SET pub_id = 0877 SET hire_date = 1/01/93 SELECT变量名称表达式 可同时初始化多个变量 如SELECT pub_id = 0877, hire_date

8、= 1/01/93 通过查询赋值 SET rows = (SELECT COUNT(*) FROM Customers) SELECTName=姓名,Sex=性别,Age=年龄 FROM 学生 WHERE 学号=200490111,函数,作用 函数是由一个或多个Transact-SQL语句组成的子程序，可用于封装代码以便重新使用 分类 内置函数 聚集函数、系统函数、字符串函数、文本和图像函数、数学函数、日期和时间函数等 用户自定义函数 标量函数：返回单个数据值 内嵌表值函数：没有函数主体，返回单个 SELECT 语句的结果集（表） 多语句表值函数：在BEGIN.END块中定义的函数主体包含TR

9、ANSACT-SQL语句，这些语句可生成行并将行插入将返回的表中,用户自定义函数,标量函数举例 CREATE FUNCTION CubicVolume (CubeLength decimal(4,1), CubeWidth decimal(4,1), CubeHeight decimal(4,1) ) RETURNS decimal(12,3) AS BEGIN RETURN( CubeLength * CubeWidth * CubeHeight ) END,用户自定义函数,CREATE TABLE Bricks (BrickPartNmbr int PRIMARY KEY, BrickCo

10、lor nchar(20), BrickHeight decimal(4,1), BrickLength decimal(4,1), BrickWidth decimal(4,1), BrickVolume AS ( dbo.CubicVolume(BrickHeight, BrickLength, BrickWidth) ) ),用户自定义函数,DECLARE MyDecimalVar decimal(12,3) EXECUTE MyDecimalVar = dbo.fn_CubicVolume CubeLength = 12.3, CubeHeight = 4.5, CubeWidth =

11、 4.5 EXEC MyDecimalVar = dbo.fn_CubicVolume 12.3, 4.5, 4.5,用户自定义函数,内嵌表值函数举例 CREATE FUNCTION fn_CustomerNamesInRegion ( RegionParameter nvarchar(30) ) RETURNS table AS RETURN ( SELECT CustomerID, CompanyName FROM Northwind.dbo.Customers WHERE Region = RegionParameter ) GO SELECT * FROM fn_CustomerNam

12、esInRegion(NWA) GO,用户自定义函数,多语句表值函数举例 CREATE FUNCTION LargeOrderShippers ( FreightParm money ) RETURNS OrderShipperTab TABLE (ShipperID int, ShipperName nvarchar(80), OrderID int, ShippedDate datetime, Freight money ) AS,用户自定义函数,BEGIN INSERT OrderShipperTab SELECT S.ShipperID, S.CompanyName, O.OrderI

13、D, O.ShippedDate, O.Freight FROM Shippers AS S INNER JOIN Orders AS O ON S.ShipperID = O.ShipVia WHERE O.Freight FreightParm RETURN END 调用函数 SELECT * FROM LargeOrderShippers( $500 ),表达式与运算符,表达式是标识符、值和运算符的组合 运算符使您得以执行算术、比较、连接或赋值操作 运算符分类 算术运算符：+-*/% 赋值运算符：= 按位运算符：,嵌入式SQL,如何在C程序与SQL语句之间传递数据 在C程序中定义宿主变量 EXEC SQL BEGIN DECLARE SECTION; 定义C语言的变量 EXEC SQL END DECLARE SECTION; 在SQ