《补充内容Transact-SQL语言.ppt》由会员分享，可在线阅读，更多相关《补充内容Transact-SQL语言.ppt（72页珍藏版）》请在三一办公上搜索。
1、Transact-SQL语言,1.T-SQL语言简介2.T-SQL编程3.T-SQL提供的函数4.T-SQL游标5.T-SQL存储过程6.触发器7.函数,T-SQL，即事务SQL（Transact-SQL），是MS SQL Server和Sybase对标准SQL的扩展版本，它不仅与ANSI SQL标准兼容，还在存储过程等许多方面作了扩充和增强。本节主要介绍如下几个问题：,一.T-SQL语言简介,T-SQL的存储过程：由SQL语句组成，分系统存储过程和用户自定义存储过程。,由三部分组成，即：SQL语句、函数和存储过程。,T-SQL的子语言：即DDL、DML和DCL三个子语言。,T-SQL的函数：
2、系统函数、聚集函数、数学函数、字符串函数、数据类型转换函数和日期函数等。,1.T-SQL组成,2.T-SQL的数据类型,SQL Server有两类数据类型：系统数据类型和用户自定义类型。,T-SQL系统数据类型(1/2),T-SQL系统数据类型(2/2),用户自定义类型,利用SQL Server的系统数据类型，可定义用户自定义类型。,具体方法 利用系统存储过程sp_addtype、sp_droptype和sp_help，分别创建、删除和查看用户定义类型。,sp_addtype,命令语法：sp_addtype 类型名,系统数据类型名,属性,关于数据的说明：属性有3种选择。,。NULL：允许列为空
3、值,。NOT NULL：不允许列为空值,。IDENTITY：指定列为标识列，每张表只能有一个标识列。IDENTITY(seed,increment),用户自定义类型（续）,sp_addtype,EXEC sp_addtype notes,text,NULLEXEC sp_addtype test,char(2),NOT NULL,sp_droptype,命令语法：sp_addtype 类型名,sp_help,命令语法：sp_help 类型名,(1)概念,由多条T-SQL语句组成，类似于DOS中的批命令，通过一个“批”将多条SQL语句用一个GO命令提交给服务器，由服务器按一个“事务”来执行该批。
4、,(2)两种类型,交互批：在交互使用SQL命令的环境中，用GO作为一个批的结束，并提交系统执行。交互方式下，一次只能提交一个批。,文件批：将多个批放在一个文件中，提交给系统执行，其中的每个批均以GO结束。大多数的SQL语句可放在一个批中。,SQL Server中的批,用“变量名”表示，一般由用户定义和使用。定义语法如下：,2.注释(Comment)方式,(1)多行时，用/*.*/,(2)单行时，用-,3.变量,DECLARE 变量名 类型,n,(1)局部变量,说明,(1)变量一旦定义，系统自动赋NULL值。,(2)对局部变量的赋值用SELECT语句。,示例：4-19,DECLARE myqty
5、 int,msg varchar(40),(1)局部变量（续）,两种赋值方式,直接赋值方式：SELECT/SET 变量名=值 例如：select myqty=60,间接赋值方式：从表中取值赋给变量，但应保证类型一致。若从表中返回的是多个值，则取最后一个值赋给变量。例如：SELECT s=price FROM titles WHERE title_id=bu395,查看变量值,SELECT 变量名,表示及概念：变量名，由系统定义，用户可使用之。,作用：用于指明系统运行过程中的运行状态，用户只能引用，不能修改和定义。,(2)全局变量,常用的全局变量：,error：返回最后一个语句产生的错误码。,r
6、owcount：返回语句执行后受影响的行数。,version：SQL SERVER版本号。,trancount：事务计数。,FETCH_STATUS：返回被 FETCH 语句执行的最后游标的状态。,4.流程控制语句,IF(条件关系式)ELSE IF,注意：如果关系式含有SELECT语句，则该SELECT语句须用圆括号括起，其嵌套级别最多为150。,用途及用法,条件分支，同一般高级语言中的条件分支语句用法。,举例：,IF(SELECT AVG(price)FROM titles)=20 UPDATE titles SET price=price*1.3ELSE PRINT 平均价格未知!,(2)
7、BEGIN.END,用途及用法,用于界定由多条SQL语句组成的语句块。类似于C/C+中的“”或PASCAL语言中的“BEGINEND”的用法。,举例：,DECLARE avg_price moneySELECT avg_price=AVG(price)FROM titlesIF avg_price20 BEGIN UPDATE titles SET price=price*1.5 PRINT 价格提高50%ENDELSE BEGIN UPDATE titles SET price=price*1.3 PRINT 价格提高50%END,(3)WHILE.BREAK/CONTINUE,4.流程控制
8、语句,用途及用法,举例：,循环控制语句。同C/C+中的“WHILE BREAK/CONTINUE”。,WHILE(SELECT AVG(price)FROM titles)30 BREAK ENDSELECT title_id,price FROM titles,4.流程控制语句,(4)GOTO.：跳到用户定义的标号处；建议尽量少用。,(5)RETURN：无条件退出；同C/C+中RETURN的用法。,(6)IF NOT EXISTS：判断是否有数据存在。语法如下：IF NOT EXISTS(SELECT 语句)语句块,示例：IF NOT EXISTS使用示例。,IF NOT EXISTS（SE
9、LECT*FROM titles WHERE title_id=010101）BEGIN PRINT 没有010101的任何记录 RETURN END,5.信息显示,(7)WAITFOR：延迟某段时间,示例：WAITFOR DELAY 00:30:00/*延迟30分钟*/,语法：WAITFOR DELAY 日期时间格式的时间值|TIME 时间|,(1)Print.,(2)Print msg 作用：显示局部变量的值,DECLARE PrintMessage NVARCHAR(50);SET PrintMessage=NThis message was printed on+RTRIM(CAST(
10、GETDATE()AS NVARCHAR(30)+N.;PRINT PrintMessage;,4.流程控制语句,6.RAISERROR,用途：系统有很多系统信息及其代码（代码值在20000以下），用户也可用RAISERROR，自己定义错误信息及其代码（其代码值应在20000以上）。,语法：RAISERROR 错误代码 错误信息|局部变量,参数表,举例：RAISEERROR使用示例。DECLARE table_name varchar(30)SELECT table_name=TITLE RAISERROR 99999 表%1！不存在。，table_name,说明：,错误代码将放入全局变量er
11、ror中，且须大于20000,局部变量的类型须为char或varchar，错误信息长度小于255,三.T-SQL提供的函数,1.字符串操作,(1)连接操作“+”,T-SQL提供的函数包括：字符串函数、日期函数、数据类型转换函数、数学函数、聚集函数和系统函数等。这些函数在DML的各语句中被广泛使用。,用途：使用“+”连接字符串。结果字符串最大不超过255 个字符。,示例：China+，+Chengdu=China,Chengdu,(2)字符串函数,Substring、right、upper、char、space,2.类型转换,注：“”表示本身类型的转换；“i”表示隐式转换；“e”表示显式转换,S
12、QL Server中的类型转换分两种，即：显式（Explict）和隐式（Implict）。显式用convert函数转换，隐式由系统自动进行。下表是SQL Server的类型转换。,(2)显式转换,作用：将表达式按指定风格转换成指定的类型，其中的style只用于日期类型到字符串的转换，用于指定日期时间的输出格式。其他类型，则省略style参数。,语法：convert(类型符，表达式，style),(1)隐式转换,2.类型转换,显式转换需使用convert函数。,示例：,SELECT convert(char(10),pubdate,105)FROM titles WHERE title_id=P
13、C1035 其中，pubdate为日期型，返回值为字符型30-06-1986。,3.有关日期的几个函数,(1)getdate(),(2)datename()与datepart(),datename(日期元素,日期表达式)：以字符串形式返回日期元素指定的日期的名字。,用途：将服务器OS的时间送客户机。,示例：SELECT getdate(),datepart(日期元素,日期表达式)：以数字形式返回日期元素指定的日期的名字。,日期元素及其指定返回的日期部分为：,示例：,Select datename(mm,pubdate)from titiles where title_id=Bu1032返回Ju
14、ne，如用datepart则返回 6。,yy：返回日期表达式中的年year或年数。,qq：返回日期表达式表示的季quarter或季数。,mm：返回日期表达式中的月month或月数。,dw：返回日期表达式表示的星期几day of week。,dy：返回日期表达式表示的一年中的第几天day of year,dd：返回日期表达式中的天或天数day。,wk：返回日期表达式表示的一年中的第几个星期或星期数。,hh：返回日期表达式中的小时或小时数。,3.有关日期的几个函数,4.数学函数,(3)dateadd()与datediff(),dateadd(日期元素，数值，日期表达式)：将数值转换成日期元素指定的
15、部分加到日期表达式上返回。,datediff(日期元素，较早日期表达式，较晚日期表达式)：两个日期相减后，按日期元素指定部分转化后返回。,5.聚集函数,作用：返回统计值，该类函数只能放在SELECT中，不能出现在WHERE子句中。,6.isnull函数,语法：isnull(列名，值),功能：当列值为空（NULL）时，用指定的数值代替之。,用途：在聚集函数中，一般均会将空值的列排除在外。若想将空值的列包含进来参加运算，即可用此函数。该函数不会替代表中的NULL值。,举例：例SELECT AVG(isnull(price,0)FROM titles,1.游标(CURSOR)及其作用,SQL Ser
16、ver在内存开辟一段缓冲区，SELECT查询的结果集合按行放入该缓冲区，并且提供了存取该缓冲区数据的“行指针”。用户利用该指针即可存取和处理各行数据。为高级编程语言提供了按行处理查询结果集合的途径。,2.游标生命周期,包括：定义、打开游标（Open Cursor）、存取（Fetch）数据、关闭游标(Close cursor)和释放（Deallocate）游标缓冲区。类似于程序设计语言中的“文件”。对应关系为：,游标定义文件的创建,打开游标打开文件,存取游标存取文件,关闭游标关闭文件,释放游标删除文件,游标指针文件指针,Declare 游标名 CURSORFOR Select语句FOR READ
17、 ONLY|UPDATE OF 列名表,3.定义游标,语法,两类游标：只读（READ ONLY）游标和更新(UPDATE)游标。,说明,READ ONLY：不能通过游标对表进行修改。,UPDATE：可以通过游标对表进行修改。,游标的定义须作为单独的事务批提交。,4.打开游标,：OPEN 游标名,用法：Fetch一次，指针下移一行。一次只能存取一行。注意：作用：将当前行各字段的内容存入局部变量列表中，并且指针 下移一行。使用前应事先定义好局部变量。影响两个全局变量，即：,5.FETCH,语法,语法,作用：执行查询并将查询结果集放入内存缓冲区，游标指针指向结果第一行。,：FETCH 游标名 INT
18、O 局部变量列表,6.Close和Deallocate,：CLOSE 游标名,CLOSE语法,用法：游标关闭后，不能再使用该游标中的数据，须再次打开，才能存取数据。,：Deallocate Cursor 游标名,Deallocate语法,用法：游标释放后，不能再打开，须重新定义并打开后才能使用。,7.用游标对数据操作,删除操作：Delete 表名 Where Current of 游标名 作用：删除表中与游标位置对应的行，要求表有惟一索引。说明：删除后指针不动，下面的行自动上移。,修改操作：Update 表名 Set 列名=值,Where Current of 游标名 作用：修改表中与游标位置
19、对应的行，要求表有惟一索引。,功能要求：须是可更新游标。可实现对表中数据的删除与修改。,8.游标使用示例,-用流控制语言统计tblSelCourse表中的成绩为A、B、C、D各个层次的学生数。假如规定：优-90，100 良-80，89 中-60，80 及格-0，60declare a int,b int,c int,d intSelect a=0,b=0,c=0,d=0declare score decimal(4,2)DECLARE SC_csr CURSOR FOR SELECT score FROM SCOPEN SC_csr FETCH SC_csr into scoreWHILE F
20、ETCH_STATUS=0BEGIN if score=60 and score=80 and score=90 set a=a+1 FETCH SC_csr into scoreENDselect a 优,b 良,c 中,d 及格CLOSE SC_csr DEALLOCATE SC_csr,1.存储过程概念,存储过程是存储于RDBMS中的一段由SQL语句组成的程序。在SQL Server中，它是一种数据库对象，建立时由RDBMS编译和优化，其执行代码存储于数据库中。用户可以通过调用存储过程来操纵数据库。,类型：分系统存储过程与用户自定义存储过程两类。,系统存储过程：由SQL Server系统
21、提供，用户或数据库管理员可使用它管理和维护数据库中的各种数据信息和对象。系统存储过程以“SP_”开头。,用户自定义存储过程：由用户自己定义，本节主要介绍这种。,好处：常用于实现经常使用的数据操作，除执行速度快外，具有：,实现较复杂的完整性约束，如动态完整性约束；可在程序中被反复使用，有助于程序的模块化；有助于提供安全性；减少网络流量；,可在命令行或批中调用。在批中调用，若非第一条语句，应加 exec,1.创建存贮过程,Create Proc 过程名 As SQL语句Return,2.执行,3.查看,sp_helptext 过程名,4.改名,sp_rename 旧过程名，新过程名,5.删除,Dr
22、op Proc 过程名,(1)创建,6.带传递参数的存贮过程,Create Procedure 过程名(输入参数变量 类型=DEFAULT,.输出参数变量 类型 OUTPUT)WITH RECOMPILE As SQL 语句Return 存储过程执行状态值,说明：输出参数需要用关键字OUTPUT标明；,WITH RECOMPILE规定每次执行是否需要重编译；,return返回存储过程执行的状态值，但不同于输出参数。,(2)传递参数和调用执行,exec 状态接收变量=过程名 输入参数1，输入参数2，接收输出的局部变量 OUTPUT,6.带传递参数的存贮过程,说明：调用前，应定义一个接收输出的局部
23、变量；,调用时，参数的传递按存储过程中定义的顺序进行，但可使用“变量=参数值”的格式打乱。,在接收输出的变量后加关键字OUTPUT。,“状态接收变量”用于接收存储过程执行的状态，一般应先定义为整型。,8.对存贮过程的限制,(1)某些命令不能放在过程中,Create View/Default/Rule/Trigger/Proc等。,(2)不能在同一过程中刚删除一个DB对象，又创建同名的对象。,(3)嵌套级数=16。,USE AdventureWorks;GOIF OBJECT_ID(HumanResources.usp_GetEmployees,P)IS NOT NULL DROP PROCED
24、URE HumanResources.usp_GetEmployees;GOCREATE PROCEDURE HumanResources.usp_GetEmployees lastname varchar(40),firstname varchar(20)AS SELECT LastName,FirstName,JobTitle,Department FROM HumanResources.vEmployeeDepartment WHERE FirstName=firstname AND LastName=lastname;GO,(1)存储过程创建,8.存贮过程示例,存储过程调用,EXECU
25、TE HumanResources.usp_GetEmployees Ackerman,Pilar;-OrEXEC HumanResources.usp_GetEmployees lastname=Ackerman,firstname=Pilar;GO-OrEXECUTE HumanResources.usp_GetEmployees firstname=Pilar,lastname=Ackerman;GO-Or,if this procedure is the first statement within a batch:HumanResources.usp_GetEmployees Ack
26、erman,Pilar;,10.1 触发器的基本概念和优点,概念:触发器是一种特殊的过程，不带参数，不被用户和程序调用，只能由用户对DB中表的操作（即：插入、删除和修改3种操作）触发。也就是说，它是由操作激发的过程。因此，可利用触发器来维护表间的数据一致性。特征：与表紧密联系，离开了表它将不复存在（这点与约束十分类似）。触发器只能在表上建立，且一张表最多建立3个触发器操作时被触发而执行，不能由用户直接调用。,触发器执行过程中的两个表（1/2）,触发器是在表上建立的，我们将触发器所依附的表称为触发器表。当修改表的数据而引发了触发器时，触发器将执行一系列T-SQL命令，在执行这些动作之前系统首先自
27、动建立两个表，Deleted表与Inserted表。这两个表临时驻留在内存当中，其结构与触发器表完全相同，一旦触发器执行完毕，两个表也随之消失。可以使用Deleted表与Inserted表测试某些数据修改的效果以及设置触发器操作的条件，但不能直接修改这两个表中的数据。,触发器执行过程中的两个表（2/2）,Inserted表用于存放执行INSERT或UPDATE操作时向触发器表（被插入数据的表）中插入的数据行，即新的数据行被同时插入到两个表触发器表和Inserted表中。Deleted表存放由于执行DELETE或UPDATE操作时从触发器表中删除的数据行。UPDATE操作相当于先执行DELETE
28、操作，然后再执行INSERT操作，因此要用到Deleted与Inserted两个表。,使用触发器的优点,强制比check约束更复杂的数据完整性使用自定义的数据错误实现数据库中多张表的级联修改比较数据库修改前后的数据状态维护非规范化数据,触发器类型,INSTEAD OF触发器：在触发后将执行一组新的T-SQL语句用以代替引起触发器执行的语句（原有语句没有执行，仅执行新语句）。该类触发器既可以在表上定义，也可以在视图上定义。AFTER触发器：在引起触发器执行的语句执行完毕后，再执行触发器中的语句。这类只能在表上定义。,10.2 触发器的创建,使用Mangement Studio创建使用T-SQL语
29、句创建触发器 CREATE TRIGGER trigger_name ON table|view FOR|AFTER|INSTEAD OF INSERT，DELETE，UPDATE WITH ENCRYPTION AS IF UPDATE(column)AND|OR UPDATE(column).n sql_statement.n,CREATE TRIGGER参数说明（1/3）,trigger_name：是触发器的名称，必须符合标识符规则，并且必须在数据库中惟一。table|view：是在其上建立触发器的表或视图，亦称为触发器表或触发器视图。WITH ENCRYPTION：用于加密 sysco
30、mments 表中包含 CREATE TRIGGER 语句文本的条目，以防止将触发器作为 SQL Server 复制的一部分发布。,CREATE TRIGGER参数说明（2/3）,FOR：用以指定触发器的类型，该关键字可以省略。后面为AFTER时表示触发器为AFTER类型，后面为INSTEAD OF时表示触发器为INSTEAD OF类型。如果仅指定 FOR 关键字，则默认为AFTER 触发器。DELETE,INSERT,UPDATE：指定在表或视图上执行哪些数据修改语句时将激活触发器。必须至少指定一个选项，如果指定的选项多于一个，需用逗号分隔。,CREATE TRIGGER参数说明（3/3）,
31、IF UPDATE(column)AND|ORUPDATE(column)：判定某一列或几列是否被修改。如果希望仅当某一列（或几列）的数据发生变化才导致触发器动作，而其他列的数据变化不会引发触发器执行，则可以采用本选项。此时可以称之为列级触发器。,INSERT触发器,当在触发器表上进行插入操作时，该触发器自动执行例CREATE TRIGGER my_trigger1 ON authorsFOR INSERT AS raiserror(unauthorized,10,1)说明：由于创建时，仅指定了 FOR 关键字，因此 AFTER 是默认设置。这样只有在触发SQL语句中指定的所有操作都已成功执行
32、后才激发。对于本例，可以用insert语句向authors中插入元组，只是插入完成后会出现错误信息。,INSTEAD OF选项,例CREATE TRIGGER my_trigger2 ON authorsINSTEAD OF DELETEASraiserror(You have no right to delete the record,10,1)说明：由于使用instead of选项，在表authors上的删除操作将不能成功。,UPDATE触发器,在带有UPDATE触发器的表上执行UPDATE语句时被触发。带有IF UPDATE选项，当特定列被更新时触发触发器。例1CREATE TRIGGE
33、R member_trigger ON authorsFOR UPDATE ASIF UPDATE(au_lname)BEGIN raiserror(unauthorized,10,1)rollback transactionEND,对au_lname字段进行更新UPDATE authorsSET au_lname=WangtiWHERE au_lname=White该更新将失败对au_fname字段进行更新UPDATE authorsSET au_fname=WangtiWHERE au_fname=Johnson对未保护字段的更新可以进行,DELETE触发器,由DELETE操作出发例CRE
34、ATE TRIGGER delete_triggerON salesFOR DELETEASBEGIN raiserror(unauthorized,10,1)rollback transactionEND,查看触发器信息,使用系统存储过程：sp_help：显示触发器对象的类型sp_helptext：显示触发器的定义文本sp_helptrigger：显示表中触发器的信息sp_depends：显示表中触发器的依赖关系使用系统表：SELECT*FROM Sysobjects WHERE type=TR使用Management Studio,使用触发器,使用触发器强制数据完整性与CHECK约束相比，
35、使用触发器可以实现更加复杂的业务逻辑，能够实现前者无法完成的功能。在以下场合考虑使用触发器：一个表中的一列参照另一个表中的一列，但这两列的类型不一致；一个表中的一列参照多个表中的多列；应用程序需要使用自定义信息和较为复杂的错误处理,使用带有提醒电子邮件的触发器,USE pubs IF EXISTS(SELECT name FROM sysobjects WHERE name=reminder AND type=TR)DROP TRIGGER reminder GO CREATE TRIGGER reminder ON titles FOR INSERT,UPDATE,DELETE AS EXE
36、C master.xp_sendmail MaryM,Dont forget to print a report for the distributors.GO,使用触发器强制业务规则,用触发器可以实现复杂的业务规则例1CREATE TRIGGER delete_customersON CustomersFOR DELETEASIF EXISTS(SELECT COUNT(*)FROM Orders INNER JOIN Deleted ON Orders.CustomerID=Deleted.CustomerID)BEGINraiserror(You can not delete the c
37、ustomer with the order record the transaction will be canciled,10,1)ROLLBACK TRANSACTIONEND,利用触发器实现参照完整性,publishers表的主键是pub_id，它也出现在titles表中，即titles表中的pub_id是外键，也就是说，publishers为主表，而titles则为从表。要维护两表间的完整性，可对titles用外键限制或参照限制来维护它们间的完整性，或对publishers和titles用触发器来维护它们间的完整性。,由删除操作激发的主表“删除触发器”,CREATE TRIGGER
38、pub_delON publishers FOR DELETEASIF rowcount=0RETURNDELETE titlesFROM titles t,deleted dWHERE t.pub_id=d.pub_idRETURN,由修改操作激发的主表“修改触发器”,CREATE TRIGGER pub_updateON publishers FOR UPDATE ASdeclare num_rows int/*定义局部变量*/select num_rows=rowcount/*局部变量赋值*/IF num_rows=0 RETURNIF update(pub_id)/*检查pub_id的
39、值是否修改，返回逻辑值*/beginif num_rows 1begin raiserror 33333 不支持主键的多行更新！rollback transaction returnendUPDATE titles/*级联修改titles表*/SET pub_id=i.pub_idFROM titles t,deleted d,inserted iWHERE t.pub_id=d.pub_id endRETURN,修改和删除触发器,修改触发器语法：ALTER TRIGGER trigger_name ON table|view FOR|AFTER|INSTEAD OF INSERT，DELET
40、E，UPDATE WITH ENCRYPTION AS IF UPDATE(column)AND|OR UPDATE(column).n sql_statement.n,由插入和修改操作激发的从表“插入修改触发器”,CREATE TRIGGER title_iuON titles FOR INSERT,UPDATE ASdeclare num_rows intselect num_rows=rowcountIF num_rows=0 RETURN IF(select count(*)from publishers p,inserted I where p.pub_id=i.pub_id)!=n
41、um_rows begin raiserror 33334 试图插入/更新非法pub_id到titles表中！rollback transaction return endRETURN,删除触发器：DROP TRIGGER trigger_name禁止或启用触发器：ALTER TABLE talbe_nameENABLE|DISABLE TRIGGERALL|trigger_name,增删改3种操作对主从表间数据完整性的影响,学生表（从表）,班级表（主表）,用户自定义函数的基本概念,概念：在SQL SERVER中将一个或多个T-SQL语句组成一个可以有返回值的例程。根据返回值的不同分为三类：标
42、量函数内嵌表值函数多语句表值函数,创建用户自定义函数,创建语法:CREATE FUNCTION function_name(parameter_name scalar_parameter_datatype=default,n)RETURN scalar_parameter_datatypeWITH,AS BEGINSql_statmentRETURN scalar_expressionEND,创建自定义函数示例,CREATE FUNCTION my_function1(moneyinput money)returns nvarchar(5)BEGIN DECLARE returnstring
43、nvarchar(5)IF moneyinput10000 SET returnstring=非热销商品 ELSE SET returnstring=热销商品 RETURN returnstringEND,查看用户自定义函数,使用系统存储过程：sp_helpsp_helptext使用信息表或系统信息架构视图：使用系统表sysobjects和syscomments使用信息架构视图：ROUTINES、PARAMETERS和ROUTINE_COLUMNS使用企业管理器,用户自定义函数类型,标量函数：返回单个数据值内嵌表值函数：返回由选择的结果构成的记录集-表能替代视图的功能，且可带参数多语句表值函数
44、：返回由选择的结果构成的记录集-表Returns子句指定的table带有列及其数据类型,创建标量函数语法,CREATE FUNCTION owner_name.function_name(parameter_name AS scalar_parameter_data_type=default,.n)RETURNS scalar_return_data_type WITH,.n AS BEGINfunction_bodyRETURN scalar_expressionEND,创建标量函数示例,CREATE FUNCTION my_function2(date datetime,separator
45、 nvarchar(2)=:)RETURNS nvarchar(20)BEGINDECLARE returnstring nvarchar(20)SET returnstring=今天是+CONVERT(nvarchar(5),datepart(year,date)+年+separator+CONVERT(nvarchar(5),datepart(month,date)+月+separator+CONVERT(nvarchar(5),datepart(day,date)+日RETURN returnstringEND,创建内嵌表值函数语法,CREATE FUNCTION owner_name.
46、function_name(parameter_name AS scalar_parameter_data_type=default,.n)RETURNS TABLE WITH,.n AS RETURN(select-stmt),创建内嵌表值函数示例,CREATE FUNCTION my_function3(RegionParameter nvarchar(25)RETURNS tableAS RETURN(SELECT city,companyname,contactname,customers as relationshipfrom customerswhere city=RegionPa
47、rameterUNIONSELECT city,companyname,contactname,suppliersFROM supplierswhere city=RegionParameter),多语句表值函数,返回的是由选择的结果构成的记录集；Returns子句指定的TABLE带有列及其数据类型；多语句表值函数允许使用以下语句：赋值语句控制语句DECLARE语句SELECT语句游标操作INSERT、UPDATE、DELETE语句EXECUTE语句,创建多语句表值函数语法,CREATE FUNCTION owner_name.function_name(parameter_name AS s
48、calar_parameter_data_type=default,.n)RETURNS return_variable TABLE WITH,.n AS BEGINfunction_bodyRETURNEND:=ENCRYPTION|SCHEMABINDING:=(column_definition|table_constraint,.n),创建多语句表值函数示例,CREATE FUNCTION my_function4(RegionParameter nvarchar(25),SelectParameter char(9)RETURNS my_table TABLE(City nvarchar(15)NULL,CompanyName nvarchar(40)NOT NULL,ContactName nvarchar(30)NULL)AS BEGININSERT my_table SELECT City,CompanyName,ContactName FROM Customer and Suppliers by CityWHERE City=RegionParameter AND RelationShip=SelectParameterRETURN END,
