[电脑基础知识]08第7章-SQL程序基础.ppt

第七章 SQL程序设计,计算机信息工程学院,SQL程序设计,注释 批处理 标识符 数据类型 变量 运算符 流程控制语句 函数 存储过程 触发器 游标 事务,一、注释,通常出现在程序代码中的说明性文字，称为注释。 在SQL Server中，分为： 单行注释 单行注释是使用两个连在一起的减号“-”作为注释；注释语句写在注释符的后面，以最近的回车符作为注释的结束 多行注释 多行注释是使用“/* */”作为注释符；“/*”用于注释文字的开头，“*/”用于注释文字的结尾，中间部分加上注释性文字说明。多行注释不能跨越批处理，整个注释必须包含在一个批处理内。,二、批处理,批处理是指包含一条或多条Transact-SQL语句的语句组，SQL Server服务器将批处理语句编译成一个可执行的单元，也称执行计划。 书写批处理，使用GO语句作为批处理命令的结束标志。 例 use shool go select * from student go select * from se go,建立批处理时，应遵循：,CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER和CREATE VIEW语句不能在批处理中与其他语句组合使用。批处理必须以CREATE语句开始。所有跟在该批处理之后的其他语句将被解释为第一个CREATE语句定义的一部分。 不能在同一个批处理中更改表结构，再引用新添加的列。 如果EXECUTE语句是批处理中的第一句，则不需要EXECUTE关键字。如果EXECUTE不是批处理的第一条语句，则需要EXECUTE。,三、标识符,在SQL Server中，标识符就是指用来定义服务器、数据库、数据库对象和变量等的名称。 对象标识符在定义对象时创建，包含的字符数必须在1128之间，随后就可通过标识符引用相应对象。 标识符可分为常规标识符和分隔标识符。,常规标识符,不需要使用分隔标识符进行分隔的标识符。 第一个字符必须是下列字符之一：Unicode2.0标准所定义的字母，下划线_，符号和数字符号#。 后续字符可以是Unicode2.0所定义的字母、来自基本拉丁字母或其他国家/地区脚本的十进制数字、$、#或下划线_。 不能是T-SQL保留字。 不允许嵌入空格或其它特殊字符,分隔标识符,如果标识符是保留字或者包含空格，则需要使用分隔标识符进行处理。 分隔标识符包含在双引号（“）或方括号（）中。 例如：创建一个名为：my table的表，表中有属性smallint类型的名为order的属性。 create table my table(order smallint) 或 create table “my table“(“order“ smallint),一个对象的全称语法格式,数据库对象的名称被看作是该对象的标识符。 server_name.database_name.owner_name.object_name 一个用户名为sdfi的用户登录到myserver服务器上，在school数据库中创建了一个sc表，则sc表的全称为：myserver.school.sdfi.sc。 在实际使用时，使用全称比较繁琐，因此经常使用简写格式。在简写格式中，没指明的部分则使用默认设置值。,四、数据类型,整数型 逻辑数值型 小数数据类型 货币型 近似数值型 字符型 Unicode字符型 二进制数据类型 日期时间类型 其他数据类型 用户自定义数据类型,整数型,tinyint：0到255。1个字节。 smallint：215（32 768）到2151（32 767）。2个字节，其中1位表示整数值的正负号，其它15位表示整数值的长度和大小。 int：231（2 147 483 648）到2311（2 147 483 647）。4个字节，其中1位表示整数值的正负号，其它31位表示整数值的长度和大小。 bigint：263（9 223 372 036 854 775 808）到2631（9 223 372 036 854 775 807）。8个字节。,小数数据类型,decimal(p，s)：可以提供小数所需要的实际存储空间，可以用2到17个字节来存储从10381到10381之间的数值。其中p表示可供存储的值的总位数（不包括小数点），缺省值为18，最大为38；s表示小数点后的位数，缺省值为0。 numeric：与decimal数据类型相同。可以用于带有identity福安尖子的列（字段）。 注意：在为小数数据类型数据赋值时，应保证所赋数据整数部分的位小于或等于定义的长度，否则会出现溢出错误。,近似数值型,real：可精确到第7位小数，其范围为从3.40E+38到3.40E+38，占用4个字节的存储空间。 float(n)：可精确到第15位小数，其范围为从1.79E+308到1.79E+308，占用8个字节的存储空间。其中，n为153之间的值。,字符型,char(n)：使用固定长度来存储字符，每个字符和符号占一个字节的存储空间。n表示所有字符所占的存储空间，n的取值为1到8000，若不指定n 值，则系统默认值为1。若输入数据的字符数小于n，则系统自动在其后添加空格来填满设定好的空间。若输入的数据过长，将会截掉其超出部分。 varchar(n)：与char类型相似，n的取值为1到8000。Varchar数据类型具有变动长度的特性，存储长度为实际数值长度。 text：专门用于存储数量庞大的变长字符数据。最大长度可达2311（2 147 483 647）个字符。,逻辑 数值型,bit：其值为0或1。如果输入0或1以外的值，将被视为1。 不能对bit类型的列（字段）使用索引。,货币型,货币数据类型用于存储货币值。在使用货币数据类型时，应在数据前加上货币符号，不同货币的符号可参考SQL Server 2000的帮助文档。默认货币符号为“￥”。包括money和smallmoney。 money：有4位小数的DECIMAL值，其取值从263到2631，数据精度为万分之一货币单位。所占存储空间为8个字节。 smallmoney：其取值从214,748.3648到+214,748.3647，所占存储空间为4个字节。,二进制数据类型,二进制数据是一些用十六进制表示的数据，包括：binary、varbinary、image。 binary(n)：固定长度为n个字节二进制数据，最大长度可达8KB。n取值为1到8000。 varbinary(n)：n个字节可变长二进制数据，不能超过8KB。 image：可用于存储字节数超过8KB的可变长度的二进制数据，如Microsoft Word文档、Microsoft Excel图表以及图像数据等，其最大长度为2311个字节。 二进制常量以0x开始，后面跟着位模式的十六进制表示。例如：0x1A表示十六进制的值1A，它等于十进制的数26。,日期时间类型,日期/时间数据类型可以存储日期和时间的组合数据，包括datetime和smalldatetime。 datetime：存储从1753年1月1日零时起到9999年12月31日23时59分59秒之间的所有日期和时间，其精确度可达三百分之一秒，即3.33毫秒。所占存储空间为8个字节。 smalldatetime：存储从1900年1月1日到2079年6月6日，但精度较低，只能精确到分钟。所占存储空间为4个字节。,SQL Server日期时间格式,Unicode字符型,Unicode数据类型需要相当于非Unicode数据类型两倍的存储空间。 nchar(n)：与char 类型相似，n的取值为1到4000。NCHAR类型采用UNICODE标准字符集（CharacterSet）。因此，Unicode数据的存储空间都是：字符数2（字节）。 nvarchar(n)：与varchar类型相似，n的取值为1到4000。 ntext：用于存储可变长度的unicode数据，最多可存储2301个unicode数据。,其他数据类型,cursor：是游标型数据类型，用于创建游标变量或定义存储过程的输出参数。 sql_variant：可以存储除文本、图形数据（text、ntext、image）和timestamp类型数据外的其它任何合法的SQL Server数据。 table：用于存储对表或视图处理后的结果集。这一新类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。 timestamp：提供数据库范围内的惟一值此类型相当于binary(8)或varbinary(8)，但当它所定义的列在更新或插入数据行时，此列的值会被自动更新，一个计数值将自动地添加到此TIMESTAMP数据列中。每个数据库表中只能有一个timestamp数据列。 uniqueidentifier：存储一个16位的二进制数字，此数字称为（GUIDGlobally Unique Identifier，即全球惟一鉴别号），由SQLServer的NEWID函数产生的全球惟一的编码，在全球各地的计算机经由此函数产生的数字不会相同。,用户自定义数据类型,用户定义的数据类型是在系统数据类型的基础上，可使用存储过程sp_addtype所建立的数据类型，当然也可使用企业管理器来实现。 例如，当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空值时，可使用用户定义数据类型。 创建用户定义的数据类型必须提供3个参数： 名称 新数据类型所依据的系统数据类型 为空性 如果用户定义数据类型是在model数据库中创建的，它将作用于所有用户定义的新数据库中。如果数据类型在用户定义的数据库中创建，则该数据类型只作用于此用户定义的数据库。,选中数据库并展开右单击“用户定义的数据类型”,Sp_addtype,sp_addtype typename = type, phystype = system_data_type , nulltype = null_type , ower = owner_name 语法说明： typename= type ：用户定义的数据类型的名称命名遵循标识符规则，并且在每个数据库中必须是唯一的。type 的数据类型为 sysname，无默认值。 phystype= system_data_type ：自定义类型所基于的物理数据类型或 SQL Server 提供的数据类型。 nulltype = null_type：指明处理空值的方式。默认为null。 , ower = owner_name ：指定创建者或所有者，若没指定，则为当前用户。 该存储过程返回0为成功，否则返回1,例题,exec sp_addtype telephone,varchar(24),not null exec sp_addtype fax,varchar(24),null,删除用户定义数据类型sp_droptype,Sp_droptype typename= type 例如： Exec sp_droptype telephone Exec sp_droptype fax,四、变量,Transact-SQL中可以使用两种变量 局部变量（Local Variable） 全局变量（Global Variable）,局部变量,局部变量是用户可自定义的变量，它的作用范围仅在程序内部。 局部变量必须以“”开头，而且必须先用DECLARE命令说明后才可使用。其说明形式如下： declare 变量名 变量类型 ，变量名 变量类型 变量不能是text、ntext或image数据类型 使用SELECT或SET命令来设定变量的值，其语法如下： select 局部变量=变量值,n set 局部变量=变量值,设有一数据库school，库中有表se,declare kch char(4),cj numeric(4,1) -为变量赋值 select kch=1 use school -打开数据库school /*从se表中，查找选了1号课程的平均分并且赋给cj*/ select cj=avg(grade) from se where eleid=kch,使用print输出,语句格式： print 表达式 语法说明： 表达式中可包含常量、变量和函数等。 例如： /*从se表中，查找选了1号课程的平均成绩*/ declare cj int select cj=avg(grade) from se where eleid=1 print cj,全局变量,全局变量是SQL Server系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用全局变量通常存储一些SQL Server的配置设定值和效能统计数据。 注意：全局变量不是由用户的程序定义的，它们是在服务器级定应义的。只能使用预先说明及定义的变局变量。引用全局变量时，必须以“”开头。局部变量的名称不能与全局变量的名称相同、否则会在应用中出错。 常用的全局变量如P148所示。,五、运算符,算术运算符 赋值运算符 位运算符 比较运算符 逻辑运算符 字符串连接运算符 一元运算符,算术运算符,算术运算符用来在两个表达式上执行数学运算，这两个表达式可以是任意两个数字数据类型的表达式。 算术运算符包括+(加)、-(减)、*(乘)、/(除)、%(模)五个。 取模运算返回一个除法的整数余数。例如，15%43。 此外，+(加)和-(减)也用于对datetime和smalldatetime值进行运算。其格式如下： 日期+整数 或 日期-整数,赋值运算符,Transact-SQL有一个赋值运算符，即等号(=)。 例如，下面的代码创建了mycounter变量。然后赋值运算符将mycounter设置成一个由表达式返回的值。 declare MyCounter INT set MyCounter=10+2,位运算符,给出以下T-SQL语句的执行结果,-声明两个变量a和b declare a int,b int -为变量赋值 set a=3 set b=4 -对a和b进行按位与&、按位或|、按位异或 select a&b as ab,a|b as a|b,ab as ab 运行结果为：,对按位运算的两个操作数的要求,注意：按位运算符的两个操作数不能为image数据类型。,比较运算符,逻辑运算符,通配符及其含义,给出以下T-SQL语句的执行结果。,use school /*从student表中查询姓“刘” 并且年龄在18和20之间的学生信息。*/ select * from student where stdname like 刘% and year(getdate()-year(birthday) between 18 and 20,字符串连接运算符,字符串连接运算符为（加号），可将两个或多个字符串合并为一个字符串，还可以连接二进制字符串。 例如：hello+china的结果为：hellochina。,一元运算符,运算符的优先级从高到低排列,括号：( ) +(正)、-(负)、(按位 NOT) 乘、除、求模运算符：*、/、% 加（连接）减运算符：+、- 比较运算符：=,=,!=,!,! 位运算符：,&,| 逻辑运算符：NOT 逻辑运算符：AND 逻辑运算符：ALL、ANY、BETWEEN、IN、LIKE、OR、SOME,七、流程控制语句,BEGINEND语句 IFELSE语句 CASE语句 WHILECONTINUEBREAK语句 GOTO语句 RETURN WAITFOR语句,1、BEGINEND语句,BEGINEND语句能够将多个Transact-SQL语句组合成一个语句块，并将它们视为一个单元处理。 在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGINEND语句。 其语法形式为： BEGIN sql_statement | statement_block END,begin end示例,begin declare a float,b int set a=123.456 set b=100 begin set a=a+10 set b=b-10 end end,使用BEING END的情况,WHILE循环需要包含语句块 CASE函数的元素需要包含语句块 IF或ELSE子句需要包含语句块,2、IFELSE语句,IFELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。 IFBoolean_expresion sql语句|语句块 ELSE sql语句|语句块 SQL Server允许嵌套使用IFELSE语句，而且嵌套层数没有限制。,if else示例,use shool declare xh char(9),kch char(4),cj numeric(3,0) select cj=grade from se where stdid=xh and eleid=kch if cj60 print 及格 Else print 不及格,3、CASE语句,CASE语句可以计算多个条件式，并将其中一个符合条件的结果表达式返回。 CASE语句按照使用形式的不同，可以分为 简单CASE语句将某个表达式与一组简单表达式进行比较以确定结果 搜索CASE语句计算一组布尔表达式以确定结果。,简单CASE语句,CASE input_expression WHEN when_expression THEN result_expression .n ELSE else_result_expression END 当input_expression when_expression 时，则返回result_expression ；否则返回else_result_expression。如果没有ELSE子句，则返回NULL。,简单case举例,use school go select tname as 姓名, case prof when 教授 then 高级职称 when 教授 then 高级职称 when 教授 then 高级职称 when 教授 then 高级职称 end as 职称类型 from teacher go,CASE搜索语句,CASE WHEN Boolean_expression THEN result_expression .n ELSE else_result_expression END 当Boolean_expression 表达式的值为true，则返回then后面的result_expression ，然后跳出case语句；否则继续测试下一个when后面的布尔表达式。如果所有的when后的布尔表达式均为false，则返回else后的表达式，如果没有else子句，则返回NULL。,case语句示例,use school go select stdid,eleid, case when grade60 then 不及格 when grade 70 then 及格 when grade 80 then 中 when grade 90 then 良 else 优 end as 成绩 from se go,P129,一、在student中增加一个奖学金等级level，类型为int。 alter table student add level int 二、更新 update student set level=1？2？3,update student set level= case when (select avg(grade) from se where stdid=student.stdid)=85 and (select min(grade) from se where stdid=student.stdid)=80 and (select grade from se where stdid=student.stdid and eleid in (select eleid from elective where elename=英语)=90) then 1 when (select avg(grade) from se where stdid=student.stdid)=80 and (select min(grade) from se where stdid=student.stdid)=75 and (select grade from se where stdid=student.stdid and eleid in (select eleid from elective where elename=英语)=85) then 2 when (select avg(grade) from se where stdid=student.stdid)=75 and (select min(grade) from se where stdid=student.stdid)=70 and (select grade from se where stdid=student.stdid and eleid in (select eleid from elective where elename=英语)=80) then 3 end,4、WHILECONTINUEBREAK语句,WHILECONTINUEBREAK语句用于设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。其语句格式如下： WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE 其中，CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。BREAK语句则使程序完全跳出循环，结束WHILE语句的执行。,while语句示例：求5的阶乘。,declare s int,i int set i=1 set s=1 while i=5 begin set s=s*i set i=i+1 end print 1*2*3*4*5=+cast(s as char(25),5、GOTO语句,GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行。 GOTO label GOTO语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以“：”结尾。,使用goto语句来求：5的阶乘,declare s int,i int set i=1 set s=1 my_loop: set s=s*i set i=i+1 -如果变量i小于等于5，则跳转到my_loop标号处 if i=5 goto my_loop print 1*2*3*4*5=+cast(s as char(25),6、RETURN语句,RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。 RETURN语句的语法形式为： RETURN integer_expression 其中，参数integer_expression为返回的表达式。 注意：当用于存储过程时，RETURN只能返回整型，不能返回空值，如果返回空值，将生成警告信息并返回0。,7、WAITFOR语句,使用WAITFOR语句，可以在指定的时间或经过了一定时间后，执行暂停的SQL语句、语句块或者存储过程等。其语法格式如下： WAITFOR DELAY 时间间隔 或 WAITFOR TIME 时间值 语法说明： delay：指示SQL Server等待的时间，最长可达24小时。 time：要等待的时间。 例如：WAITFOR DELAY 00:00:05,八、函数,数学函数 字符串函数 转换函数 日期函数 系统函数 用户自定义函数,1、数学函数,ABS ( numeric_expression)：返回给定数字表达式的绝对值。 EXP ( float_expression )：返回所给的float表达式的指数值。 LOG ( float_expression )：返回给定float表达式的自然对数。 SQRT(float_expression)：返回给定表达式的平方根。 CEILING(numeric_expression)：返回大于或等于所给数字表达式的最小整数。 FLOOR( numeric_expression)：返回小于或等于所给数字表达式的最大整数。 ROUND(numeric_expression,length)：将给定的数据四舍五入到给定的长度。 SIGN(numeric_expression)：返回表达式的正(+1)、零(0)或负(-1)号。 PI( )：常量3.14159265358979。 RAND(seed)：返回0到1之间的随机float值,返回ceiling、floor、rand三个函数的值,select ceiling(pi(),floor(pi(),rand(1),rand(2) 运行结果为：,2、字符串函数,LTRIM(char_expr)、 RTRIM(char_expr) LOWER(char_expr)、UPPER(char_expr)。 RIGHT(char_expr,integer_expr)：返回字符串中从右边开始指定个数的字符。 SUBSTRING(expr,start,length)：返回指定表达式中从start位置开始长度位length的部分。 STR(float_expr,length , decimal )：把数值变成字符串返回，length是总长度，decimal是小数点右边的位数。 REPLACE(string_expr1,string_expr2,string_expr3)：用参数3替换参数1中所有出现参数2的子串。 CHARINDEX(expr1,expr2,strat_location)：从expr2表达的字符串的strat_location位置处开始搜索expr1表达的子字符串，找到则返回子字符串的位置，否则返回0。 REPLICATE (character_expression , integer_expression)：把integer_expression个character_expression 连接起来返回。 LEN(str_exp)：返回长度。,使用函数rtrim和ltrim分别删除两个字符串的空格，然后将两个字符串连接形成新的字符串。,declare s1 char(6),s2 char(10) set s1=山东 set s2= 财政学院 select s1+s2 as 字符串简单连接, rtrim(s1)+ltrim(s2) as 去掉空格后的连接 运行结果为：,REPLACE函数的使用,例：把student表中所在系dept中包含cs的全部改成信息系 update student set dept=replace(dept,cs,信息系),字符串函数举例P156,例：如果供应商名称前必须冠以所在省市，并以“-”与后面的供应商名分割，要求分列查询供应商所在省市和供应商名 select replace(left(suppname,charindex(-,suppname),-,), right(suppname,len(suppname)-charindex(-,suppname) from supplier,3、转换函数和ISNULL,CAST（expression AS data_type）：可以将某一个数据类型强制转换为另一种数据类型。 CONVERT（data_type(length),expression,style）：允许用户把表达式从一种数据类型转换为另一种数据类型，并且还在日期的不同显示格式之间进行转换。style参数：提供了各种日期显示格式。 ISNULL(check_expression,replacement_value)：若check_expression为空，则返回replacement_value，否则返回check_expression。这两个参数类型必须一致。,ISNULL函数的使用,select stdid,stdname,isnull(classid,未确定) from student,查询“09”开头的下一个可用的学号，假设学号总长为5位字符，下一个可用编号就是“09”开头的学号中后三位的最大编号1后的编号,select 09+ right(replicate(0,2)+ltrim(str(max(right(stdid,3)+1,3),3) from student where stdid like 09%,4、日期函数,DAY(date)：返回指定日期的天数。 MONTH(date)：返回指定日期的月份值。 YEAR(date)：函数返回指定日期的年份值。 GETDATE()：返回系统当前的日期和时间。 DATEDIFF ( datepart , startdate , enddate ) DATEADD ( datepart , number, date ) DATEPART ( datepart , date ),输出学生的学号、姓名以及年龄,年龄的计算方法为： 如当前日期的“月日”小于出生日期的“月日”，年龄当前年份出生年份1 如当前日期的“月日”大于等于出生日期的“月日”，年龄当前年份出生年份 select stdid,stdname,year(getdate()-year(birthday)- case when left(convert(char(10),birthday,101),5) left(convert(char(10),getdate(),101),5) then 1 else 0 end from student,5、系统函数,COL_NAME：:返回表中指定字段的名称，即列名。 COL_LENGTH：返回指定字段的长度值。 DB_NAME：返回数据库的名称。 ISDATE：检查给定的表达式是否为有效的日期格式。 ISNULL：用指定值替换表达式中的指定空值。 INDEX_COL：返回索引的列名。 OBJECT_NAME：返回数据库对象的名称。 SUSER_SID：返回服务器用户的安全帐户号。 SUSER_NAME：返回服务器用户的登录名。 USER_ID：返回用户的数据库ID号。 USER_NAME：返回用户的数据库用户名。 STATS_DATE：返回最新的索引统计日期。,用COL_NAME函数返回s表中的第二列的字段名,use school go select col_name(object_id(s),2) as s表中的第二列的字段名为： 运行结果为：,6、用户自定义函数,可分为： 标量函数 表值函数,标量函数,1、创建标量函数的语法形式如下： CREATE FUNCTION 函数名（形式参数名称AS 数据类型） RETURNS 返回数据类型 WITH ENCRYPTION|SCHEMABINDING| ENCRYPTION,SCHEMABINDING AS BEGIN 函数内容 RETURN 表达式 END 2、调用标量函数的基本语法： 变量=用户名.函数名（实际参数列表）,创建自定义函数jc()用来计算任意数的阶乘,create function jc(n as int) returns bigint begin declare i int,s bigint set i=1 set s=1 while i=n begin set s=s*i set i=i+1 end return s end go print dbo.jc(5),查看自定义函数信息sp_helptext,exec sp_helptext jc,P130,一、在student中增加一个奖学金等级level，类型为int。 alter table student add level int 二、更新 ? 使用函数，判断奖学金的等级。,创建一函数getlevel,create function getlevel(ag int,ming int,eg int) returns int as begin if(ag=85 and ming=80 and eg=90) return 1 if(ag=80 and ming=75 and eg=85) return 2 if(ag=75 and ming=70 and eg=80) return 3 return 0 end,调用该函数,update student set level= dbo.getlevel(select avg(grade) from se where stdid=student.stdid),(select min(grade) from se where stdid=student.stdid), (select grade from se where stdid=studen.stdid and eleid=(select eleid from elective where elename=英语),P161,假设供应商表supplier中的供应商名称为英文，要求对其格式化：每个单词的第一字母大写，若两个单词之间出现多于一个空格，则去除多余的空格。,create function formatstr(s varchar(100) returns varchar(100) as begin declare c char(1),lc char(1),i int,t varchar(100) set i=1 set lc= set t= while i ) if lc= if (asc(c)=97 and asc(c) or lc ) set t=t+c set i=i+1 set lc=c end return t end,表值函数,表值函数可以返回一个表，即其返回类型是table类型 表值函数分为单语句表值函数和多语句表值函数 如果一个查询比较复杂，通常我们可以把它定义成一个视图，如果查询包含了参数，一般情况下我们仍能使用视图实现，但可能会使视图对应的查询语句变得更为复杂，这个时候就可以考虑使用单语句表值函数。 如果一个查询根本无法用一个SELECT语句完成时，就可以考虑使用多语句表值函数，即用一段程序来获得查询数据,表值函数,创建单语句表值函数的语句为： CREATE FUNCTION 函数名 ( 参数名 参数类型 = 默认值 ,.n ) RETURNS TABLE WITH ENCRYPTION|SCHEMABINDING| ENCRYPTION,SCHEMABINDING AS RETURN 查询语句 该函数将返回执行“查询语句”后得到的结果集，可以使用下列语句获得结果： SELECT * FROM 函数名(参数表),p163查询某个学生选修各门课的名称和成绩,建立单语句表值函数 create function allgrade ( stdid char(6) ) returns table as return (select e.elename,grade from student s left join se on s.stdid=se.stdid left join elective e on se.eleid=e.eleid where s.stdid=stdid) 使用下列语句获得结果： select * from allgrade(该学生学号),P164用表值函数实现先选择后进行外连接的查询,要求：查询所有学生选修“ele002”课程的情况，结果中包括姓名和课程名，对没有选修该课程的学生，课程名为null 定义表值函数： create function s_some_e (eleid char(3) returns table as return (select * from se where eleid=eleid) 使用查询语句： select s.stdname,e.elename from student s left join s_some_e(ele002) b on s.stdid=b.stdid left join elective e on b.eleid=e.eleid,多语句表值函数,创建多语句表值函数的语句为： CREATE FUNCTION 函数名 ( 参数名 参数类型 = 默认值 ,.n ) RETURNS 表变量名 TABLE WITH ENCRYPTION|SCHEMABINDING| ENCRYPTION,SCHEMABINDING AS BEGIN 函数体 END 函数体内可包含一个由多个语句组成的程序，该程序完成获取最终需要的数据，并把它插入或更新到“表变量名”表示的表中，在执行RETURN时，该表数据将被作为函数的结果返回。,P165,例：查询某个班学生选修的各门课的及格和不及格人数，要求查询结果包括：课程号、课程名、及格人数和不及格人数。,create function s (classid char(6) returns pns table (eid char(3),ename varchar(20),pn int,npn int) as begin -插入班级学生选修的课程 insert into pns (eid,ename) select distinct a.eleid,b.elename from se join elective e on se.eleid=e.eleid where stdid in (select stdid from student where classid=classid) -计算及格人数 update pns set pn= ( select count(*) from se where eleid=eid and grade=60 and stdid in (select stdid from student where classid=classid) -计算不及格人数 update pns set npn= ( select count(*) from se where eleid=eid and grade60 and stdid in (select stdid from student where classid=classid) return end 查询班号为“09001”班级中各门选修课的及格和不及格人数的查询语句为： select * from s (09001),7.3 存储过程,存储过程是存储于数据库中的一组T-SQL语句。可将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用execute，即可自动完成命令。,存储过程的优点,存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 当对数据库进行复杂操作时(如对多个表进行update,insert,select,delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 存储过程可以重复使用，可减少数据库开发人员的工作量。 安全性高，可设定只有某些用户才具有对指定存储过程的使用权，允许用户单独执行存储过程，而不给于其访问表格的权限。,存储过程的优点,存储过程被第一次调用后，将驻留在内存中，所以执行效率高， 存储过程运行于数据库服务器端，其数据来源及输出结果通常存储在一台计算机上，与在客户机上实现相同算法相比，可以大大减少了客户机和服务器之间的通信量。,存储过程,系统存储过程 是由SQL Serve系统创建的存储过程，用户可直接使用。 系统存储过程存储在master数据库中，以“sp_”开头命名。系统存储过程主要用于系统管理、用户登录管理、权限设置、数据库对象管理、数据复制等操作。 用户自定义存储过程,常用的系统存储过程,sp_help：报告有关数据库对象（sysobjects 表中列出的任何对象）、用户定义数据类型或系统所提供的数据类型的信息。 sp_addlogin：创建新的 SQL Server 登录，该登录允许用户使用 SQL Server 身份验证连接到 SQL Server 实例。 sp_password：为 Microsoft SQL Server 登录名添加或更改密码。 sp_cursor_list：报告当前为连接打开的服务器游标的属性。 Sp_adduser：向当前数据库中添加新的用户。 sp_addrole：在当前数据库中创建新的数据库角色。 sp_addrolemember：为当前数据库中的数据库角色添加数据库用户、数据库角色、Windows 登录或 Windows 组。,常用的系统存储过程（续）,sp_droplogin：删除 SQL Server 登录名。这样将阻止使用该登录名对 SQL Server 实例进行访问。 sp_dropuser：从当前数据库中删除数据库用户。 sp_droprole：从当前数据库中删除数据库角色。 sp_droprolemember：从当前数据库的 SQL Server 角色中删除安全帐户。 sp_addtype：创建别名数据类型。,存储过程的创建,CREATE PROCEDURE owner. procedure_name ; number parameter data_type VAR