Ch11.T-SQL T-SQLCh12.Ch13.

T-SQL (Transact-SQL Programming)

Chapter 11 Outline11-1 SQL 11-2 11-3 11-4 T-SQL 11-5 11-6

11-1 SQL T-SQL-- /* */

11-2 BatchSQL "GO" ScriptSQL ServerSQLSQL ()SQL ()()

11-2 Go

11-2 SQLSQL

11-2

SQLSQLSQL

11-2 create defaultcreate view create rule create procedure create triggerALTER Stored procedure"EXEC"

11-3 T-SQLSQLVariablesT-SQLLocal VariablesGlobal Variables@ @@

11-3 T-SQLDECLAREdeclare @Variable_name datatype [@Variable_name datatype]. @Variable_namedatatype initial valueNull value

11-3

@a

11-3 setselectSETSET @local_variable = expression expressionT-SQL SELECT(Assignment Select)(Retrieval Select)SELECT @variable_name = expression(SETSELECT)

11-3 (Retrieval Select)expressionSQL

11-3 SQL Server @@ @@CONNECTIONS SQL Server @@ERROR T-SQL

11-4 T-SQL SQL Server 2005T-SQLSQL-92SQLT-SQL T-SQL IFELSE BEGINEND PRINT WHILE CASE GOTO RETURN WAITFOR RAISERROR TRY CATCH

11-4 T-SQLIFELSE IF Boolean_expression {sql_statementstatement_block}END {sql_statementstatement_block}

Boolean_expressionTRUEFALSE {sql_statementstatement_block}Transact-SQL

11-4 T-SQL11-4.1 >50'>50 ' 50) --() print '>50' else print '

11-4 T-SQLBEGINENDBEGINENDBEGIN{ sql_statement statement_block}END {sql_statementstatement_block}Transact-SQL

11-4 T-SQL11-4.211-4.150 enddeclare @a int --set @a=77--if (@a>50) --() print '>50' else begin print '

11-4 T-SQLPRINTPrint

PRINT 'any ASCII text' @local_variable@@FUNCTIONstring_expr

any ASCII textlocal_variable @@FUNCTION string_expr

11-4 T-SQL11-4.3PRINT SQLgetdate()charvarchar

11-4 T-SQLWHILET-SQL WHILE Boolean_expression {sql_statementstatement_block} [BREAK] {sql_statementstatement_block} [CONTINUE]BREAKWHILE CONTINUEWHILECONTINUE

11-4 T-SQL11-4.51+2+3+.n>1000ndeclare @target int, @n int ,@sum intselect @target=1000,@n=1,@sum=0while(1=1) --(1=1)TRUEbegin set @sum=@sum+@n if(@sum>@target) break --WHILE set @n=@n+1 continue print ''endprint 'n='+cast(@n as char(5)) continuewhile

11-4 T-SQLCASE CASE1.CASE2.CASE

11-4 T-SQL( aCASECASE input_expression WHEN when_expression THEN result_expression [n] [ELSE else_result_expression] END

11-4 T-SQLbCASECASE WHEN Boolean_expression THEN result_expression [n] [ELSE else_result_expression END

11-4 T-SQL 1.CASE input_expression TRUEresult_expressioninput_expression = when_expressionTRUEELSESQL Serverelse_result_expressionELSENULL2.CASE WHENBoolean_expressionTRUEresult_expressionBoolean_expressionTRUEELSESQL Serverelse_result_expressionELSENULL

11-4 T-SQLGOTOGOTO label GOTO label

labelGOTO GOTO

11-4 T-SQL11-4.7GTOT1+2+3+.n>1000n declare @target int, @n int ,@sum int select @target=1000,@n=0,@sum=0 sumhere: set @n=@n+1 set @sum=@sum+@n if(@sum>@target) --finish goto finish else goto sumhere --sumhere finish: print 'n='+cast(@n as char(5))

11-4 T-SQLRETURN RETURNRETURNRETURN[integer_expression] integer_expression RETURNnullEXECUTE @return_status procedure_name

11-4 T-SQLWAITFORWAITFOR WAITFOR{DELAY'time'TIME'time'}

DELAYSQL Servertime TIMESQL Server

11-4 T-SQL11-4.8Begin --CASE 1 waitfor DELAY '00:00:10' --10 exec sp_helpdbendgo--CASE 2begin waitfor time '13:00'--13:00 exec sp_helpdbendgo

11-5

11-5 SQL@@ERROR

11-5 @@ERRORSQLSQL TRY...CATCH T-SQL TRYT-SQLCATCHSQL

11-5 TRYBEGIN TRYEND TRY BEGIN TRY sql_statement_1 END TRY BEGIN CATCH sql_statement_2 END CATCH sql_statement1sql_statement_2

11-5 11-5.2 TRY CATCH declare @dividend int,@divisor int,@quotient int set @dividend=15-- set @divisor =0-- begin try set @quotient= @dividend/@divisor -- print ''+cast(@quotient as char(10)) end try begin catch print ''+ERROR_MESSAGE() end catch

11-6 SQL ServerBatchSQLGO Script DECLARE@ SELECT

11-6 SQL@@SELECT T-SQLBEGINENDPRINTIFELSEWHILECASEGOTORETURNWAITFORRAISERRORTRY CATCH

Go @aIFELSE(VBC++C#) T-SQL WHILEBREAK continuewhilecontinuecontinue continueelsecaseGOTOGOTObreakGTOT @@ERROR0 ---------