📄 50622.htm
字号:
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>USE AdventureWorks;<BR>GO</P><P>/* Create a user-defined table type */<BR>CREATE TYPE LocationTableType AS TABLE <BR>( LocationName VARCHAR(50)<BR>, CostRate INT );<BR>GO</P></PRE></TD></TR></TBODY></TABLE></P>
<P>对于表类型，有如下约束：</P>
<P>(1)用户自定义表类型不能作为表的列或者结构化用户自定义类型的域。<BR>(2)基于用户自定义表类型的别名类型。<BR>(3)不允许 NOT FOR REPLICATION 选项。<BR>(4)CHECK 约束需要一个计算列。<BR>(5)在计算列上的主键必须包含 NOT NULL 和 PERSISTED 约束。<BR>(6)不能在用户自定义表类型上创建非簇索引。除非索引是创建 PRIMARY KEY 或 UNIQUE 约束的返回值。<BR>(7)不能指定 DEFAULT 值。<BR>(8)一旦用户自定义表类型被创建，则它就无法更改。<BR>(9)如果没有定义用户自定义表类型上的计算列，则用户自定义函数无法调用。</P>
<P><STRONG>6. 表值参数</STRONG></P>
<P>数据库引擎现在支持一种新的参数类型来引用用户自定义表类型(参考 5)。表值参数可以发送更多的 SQL Server 数据。</P>
<P>下面的示例展示了如何使用表值参数。</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>USE AdventureWorks;<BR>GO</P><P>/* Create a table type. */<BR>CREATE TYPE LocationTableType AS TABLE <BR>( LocationName VARCHAR(50)<BR>, CostRate INT );<BR>GO</P><P>/* Create a procedure to receive data for the table-valued parameter. */<BR>CREATE PROCEDURE usp_InsertProductionLocation<BR> @TVP LocationTableType READONLY<BR> AS <BR> SET NOCOUNT ON<BR> INSERT INTO [AdventureWorks].[Production].[Location]<BR> ([Name]<BR> ,[CostRate]<BR> ,[Availability]<BR> ,[ModifiedDate])<BR> SELECT *, 0, GETDATE()<BR> FROM @TVP;<BR> GO</P><P>/* Declare a variable that references the type. */<BR>DECLARE @LocationTVP <BR>AS LocationTableType;</P><P>/* Add data to the table variable. */<BR>INSERT INTO @LocationTVP (LocationName, CostRate)<BR> SELECT [Name], 0.00<BR> FROM <BR> [AdventureWorks].[Person].[StateProvince];</P><P>/* Pass the table variable data to a stored procedure. */<BR>EXEC usp_InsertProductionLocation @LocationTVP;<BR>GO</P></PRE></TD></TR></TBODY></TABLE></P>
<P><STRONG>7. MERGE 语句</STRONG></P>
<P>这个新增的 Transaction SQL 语句在一个基于源数据连接结果集的目标表上执行 INSERT、UPDATE 和 DELETE 操作。该语法允许您将一个数据源连接到目标表或视图上。然后在连接后的结果集上执行多种操作。</P>
<P>#p#</P>
<P>MERGE 的语法为：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>[ WITH <common_table_expression> [,...n] ]<BR>MERGE <BR>[ TOP ( expression ) [ PERCENT ] ] <BR>[ INTO ] target_table [ [ AS ] table_alias ]<BR> [ WITH ( <merge_hint> ) ]<BR>USING <table_source><BR>ON <search_condition><BR>[ WHEN MATCHED [ AND <search_condition> ]<BR> THEN <merge_matched> ]<BR>[ WHEN [TARGET] NOT MATCHED [ AND <search_condition> ]<BR> THEN <merge_not_matched> ]<BR>[ WHEN SOURCE NOT MATCHED [ AND <search_condition> ]<BR> THEN <merge_ matched> ]<BR><output_clause><BR>[ OPTION ( <query_hint> [ ,...n ] ) ] <BR>;<BR><merge_hint>::=<BR> { [ <table_hint_limited> [ ,...n ] ]<BR> [ [ , ] INDEX ( index_val [ ,...n ] ) ] }</P><P><table_source> ::= <BR>{<BR> table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] <BR> [ WITH ( table_hint [ [ , ]...n ] ) ] <BR> | rowset_function [ [ AS ] table_alias ] <BR> [ ( bulk_column_alias [ ,...n ] ) ] <BR> | user_defined_function [ [ AS ] table_alias ]<BR> | OPENXML <openxml_clause> <BR> | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] <BR> | <joined_table> <BR> | <pivoted_table> <BR> | <unpivoted_table> <BR>}</P><P><merge_matched>::=<BR> { UPDATE SET <set_clause> | DELETE }</P><P><set_clause>::=<BR> { column_name = { expression | DEFAULT | NULL }<BR> | { udt_column_name. { { property_name = expression <BR> | field_name = expression } <BR> | method_name ( argument [ ,...n ] ) } }<BR> | column_name { .WRITE ( expression , @Offset , @Length ) }<BR> | @variable = expression <BR> } [ ,...n ] </P><P><merge_not_matched>::=<BR> INSERT [ ( <column_list> ) ] <BR> { VALUES ( <values_list> )<BR> | DEFAULT VALUES }</P><P><output_clause>::=<BR>{<BR> [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }<BR> [ (column_list) ] ]<BR> [ OUTPUT <dml_select_list> ]<BR>}<BR><dml_select_list>::=<BR>{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]<BR> [ ,...n ]</P><P><column_name> ::=<BR> { DELETED | INSERTED | from_table_name } . { * | column_name }<BR> | $ACTION</P></PRE></TD></TR></TBODY></TABLE></P>
<P>示例：在一条 SQL 语句中使用 WHERE 在一张表上执行 UPDATE 和 DELETE 操作</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>USE AdventureWorks;<BR>GO<BR>MERGE Production.ProductInventory AS pi<BR>USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail sod<BR> JOIN Sales.SalesOrderHeader soh<BR> ON sod.SalesOrderID = soh.SalesOrderID<BR> AND soh.OrderDate = GETDATE()<BR> GROUP BY ProductID) AS src (ProductID, OrderQty)<BR>ON (pi.ProductID = src.ProductID)<BR>WHEN MATCHED AND pi.Quantity - src.OrderQty <> 0 <BR> THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty<BR>WHEN MATCHED AND pi.Quantity - src.OrderQty = 0 <BR> THEN DELETE;</P></PRE></TD></TR></TBODY></TABLE></P>
<P>这个示例是一个非常典型的销售定货库存问题。这个示例很简单，表达的意思就是：如果某一个产品产生了销售定单数据，则将其对应的产品库存除去该销售定单所产生的数量，如果当前库存数量与该销售定单数量相同，则从库存表中删除该产品的库存纪录。</P>
<P>我们看到，利用 MERGE 语句可以将复杂的 SQL 语句简化。它比起 IF、CASE 等更加灵活和强大。</P>
<P><STRONG>结论</STRONG></P>
<P>Microsoft SQL Server 2008 对事务性 SQL 语言做了一些增强，提高了查询效率。使得 SQL Server 成为大中型企业数据库的首先产品。SQL Server 2008 将伴随 Visual Studio 2008 一起发布，开发人员提前了解这些信息有助于在 SQL Server 的新版本发布后快速建立基于该版本的企业级应用程序。</P>
<P><FONT size=4>【相关文章】</FONT></P>
<UL type=disc>
<LI><A href="http://database.51cto.com/art/200707/50357.htm" target=_blank><U><FONT color=blue>SQL Server 2008 -“Katmai”功能简介</FONT></U></A></LI></UL>
<UL type=disc>
<LI><A href="http://database.51cto.com/art/200706/49904.htm" target=_blank><U><FONT color=blue>微软新一代数据库SQL Server 2008明年初上市</FONT></U></A></LI></UL>
<UL type=disc>
<LI><A href="http://database.51cto.com/art/200706/48898.htm" target=_blank><U><FONT color=blue>Katmai改名SQL Server 2008</FONT></U></A></LI></UL>
<UL type=disc>
<LI><A href="http://www.51cto.com/art/200511/12389.htm" target=_blank><U><FONT color=blue>SQL Server 2005中的T-SQL</FONT></U></A></LI></UL>
<UL type=disc>
<LI><A href="http://www.51cto.com/art/200510/8649.htm" target=_blank><U><FONT color=blue>T-SQL实用例句</FONT></U></A></LI></UL>
<DIV align=right>【责任编辑：<A class=ln href="mailto:sunsj@51cto.com">火凤凰</A> TEL：（010）68476606-8007】</DIV></td> </tr> <tr> <td class="d_font4"> </td> </tr> </table>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -