Transact-SQL (T-SQL)
来源:互联网 发布:电信光猫端口1和端口2 编辑:程序博客网 时间:2024/04/29 04:42
http://en.wikipedia.org/wiki/Transact-SQL
Transact-SQL
Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension toSQL. SQL, often expanded to Structured Query Language, is astandardized computer language that was originally developed by IBM for querying, altering and defining relational databases, usingdeclarative statements. T-SQL expands on the SQL standard to includeprocedural programming,local variables, various support functions for string processing, date processing, mathematics, etc. and changes to theDELETE and UPDATE statements. These additional features make Transact-SQL Turing complete.
Transact-SQL is central to using Microsoft SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.
Contents
[hide]- 1Flow control
- 2Changes to DELETE and UPDATE statements
- 3BULK INSERT
- 4TRY CATCH
- 5See also
- 6External links
[edit]Flow control
Keywords for flow control in Transact-SQL include BEGIN
and END
, BREAK
, CONTINUE
, GOTO
, IF
andELSE
, RETURN
, WAITFOR
, and WHILE
.
IF
and ELSE
allow conditional execution. This batch statement will print "It is the weekend" if the current date is a weekend day, or "It is a weekday" if the current date is a weekday.
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 PRINT 'It is the weekend.'ELSE PRINT 'It is a weekday.'
BEGIN
and END
mark a block of statements. If more than one statement is to be controlled by the conditional in the example above, we can use BEGIN and END like this:
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1BEGIN PRINT 'It is the weekend.' PRINT 'Get some rest on the weekend!'ENDELSEBEGIN PRINT 'It is a weekday.' PRINT 'Get to work on a weekday!'END
WAITFOR
will wait for a given amount of time, or until a particular time of day. The statement can be used for delays or to block execution until the set time.
RETURN
is used to immediately return from a stored procedure or function.
BREAK
ends the enclosing WHILE
loop, while CONTINUE
causes the next iteration of the loop to execute. An example of aWHILE
loop is given below.
DECLARE @i INTSET @i = 0 WHILE @i < 5BEGIN PRINT 'Hello world.' SET @i = @i + 1END
[edit]Changes to DELETE and UPDATE statements
In Transact-SQL, both the DELETE and UPDATE statements allow a FROM clause to be added, which allows joins to be included.
This example deletes all users
who have been flagged with the 'Idle' flag.
DELETE users FROM users as u INNER JOIN user_flags as f ON u.id=f.id WHERE f.name = 'Idle'
[edit]BULK INSERT
BULK INSERT is a Transact-SQL statement that implements a bulk data-loading process, inserting multiple rows into a table, reading data from an external sequential file. Use of BULK INSERT results in better performance than processes that issue individual INSERT statements for each row to be added. Additional details are available on Microsoft's MSDN page.
[edit]TRY CATCH
Beginning with SQL Server 2005, Microsoft introduced additional TRY CATCH logic to support exception type behaviour. This behaviour enables developers to simplify their code and leave out @@ERROR checking after each SQL execution statement.
-- begin transactionBEGIN TRAN BEGIN TRY -- execute each statement INSERT INTO MYTABLE(NAME) VALUES ('ABC') INSERT INTO MYTABLE(NAME) VALUES ('123') -- commit the transaction COMMIT TRANEND TRYBEGIN CATCH -- rollback the transaction because of error ROLLBACK TRANEND CATCH
[edit]See also
- Adaptive Server Enterprise (Sybase)
- PL/SQL (Oracle)
- PL/pgSQL (PostgreSQL)
[edit]External links
- Sybase Transact-SQL User's Guide
- Transact-SQL Reference for SQL Server 2000 (MSDN)
- Transact-SQL Reference for SQL Server 2005 (MSDN)
- Transact-SQL Reference for SQL Server 2008 (MSDN)
- Transact-SQL (T-SQL)
- Sql server T-SQL (Transact-SQL)学习
- 【Transact-SQL】T-SQL中的Like
- sql server 2005 T-SQL 参考 -(负)(Transact-SQL)
- sql server 2005 T-SQL 参考 -(减)(Transact-SQL)
- sql server 2005 T-SQL 参考 *(乘)(Transact-SQL)
- sql server 2005 T-SQL 参考 /(除)(Transact-SQL)
- sql server 2005 T-SQL &(位与)(Transact-SQL)
- sql server 2005 T-SQL |(位或)(Transact-SQL)
- sql server 2005 T-SQL ~(位非)(Transact-SQL)
- sql server 2005 T-SQL =(等于)(Transact-SQL)
- sql server 2005 T-SQL >(大于)(Transact-SQL)
- sql server 2005 T-SQL --(注释)(Transact-SQL)
- sql server 2005 T-SQL /*...*/(注释)(Transact-SQL)
- sql server 2005 T-SQL $PARTITION (Transact-SQL)
- sql server 2005 T-SQL @@CONNECTIONS (Transact-SQL)
- sql server 2005 T-SQL @@CPU_BUSY (Transact-SQL)
- sql server 2005 T-SQL @@CURSOR_ROWS (Transact-SQL)
- 抒一下情——早上在站点等公交上班有感
- JS 的魅力
- VS2008 解决方案的目录结构设置和管理
- 开发技巧
- linux 内存分配函数
- Transact-SQL (T-SQL)
- 字符组合问题的分治算法
- 你的个人数据是如何被收集并出售的
- WPF 之InsertBefore 与 InsertAfter用
- 【有奖征文】情人节,和书一起走过的日子
- 使用代码分离构建自定义组件
- 新的小工具——KillFiles文件粉碎器
- ms2008 program configuration is incorrect
- 算法与数据结构-数论之辗转相除法