branch

T-SQL supports IF, IF-ELSE, IF-ELSE IF a variety of ways

IF condition in which judgment IF, IF NOT, IF EXISTS three forms

DECLARE @num int  
　　set @num = 5  
IF @num = 0      
　　PRINT '000000000000'  
ELSE IF @num = 1      
　　PRINT '11111111'  
ELSE      
　　PRINT '222222222'  
go

CASE WHEN usage

CASE input_expression

WHEN when_expression THEN result_expression

[, ... n ]

[ELSE else_expression]

END

cycle

WHILE < Conditional Expressions > ;

BEGIN

< code block > 

[BREAK]

[ CONTINUE ]

END

delay execution

WAITFOR {TIME | DELAY} '00: 00:00 '

where TIME is the time specified, DELAY is the delay time

dynamic SQL statements

in T-SQL can be spliced to execute SQL, and then call the system stored procedure execution, Dynamic SQL type must be of type NVARCHAR, if it is the word of string constants: N'SQL '

system stored procedure call is: SP_EXECUTESQL

DECLARE @STR NVARCHAR(100)  
    SET @STR = 'PRINT 11111'  
execute sp_executesql @STR  
GO

addition, there EXECUTE command can also execute dynamic SQL statements

DECLARE @STR NVARCHAR(100)  
    SET @STR = 'sp_help headgmp_systemuser'  
execute(@STR)  
GO 
 
execute('sp_help headgmp_systemuser')

execute commands and sp_executesql stored procedure A major difference is that, sp_executesql supports parameter substitution, The execute command is not supported, only to pass a string

--sp_executesql参数替换  
declare @sqlString nvarchar(100)  
declare @paramDefine nvarchar(50)  
declare @paramValue nvarchar(50)  
set @paramDefine = N'@userIdValue varchar(10)'    --声明替换参数类型  
set @paramValue = N'qiuwei'                        --实际参数值  
set @sqlString = N'select * from headgmp_systemuser where userid = @userIdValue';    --动态SQL字符串  
execute sp_executesql @sqlString,@paramDefine,@userIdValue=@paramValue                --调用方式  
go

cursor

cursor using steps:

DECLARE - declare

OPEN - to open and fill Data

FETCH - read a single line of data

_______ - perform specific operations

CLOSE - close the cursor stops, You can use the OPEN repopulate an open

DEALLOCATED - release resources

cursor declaration

DECLARE cursor_name CURSOR 
[LOCAL | GLOBAL]    --局部游标（存在创建的批处理、存储过程或触发器中）或全局游标 
[FORWARD_ONLY | SRCOLL]    --前者只能从第一行滚动到最后一行，且只能用FETCH NEXT提取内容,而SCROLL可以前后滚动 
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]    --STATIC：创建游标使用数据的临时副本；KEYSET：游标打开时，游标中行的资格和顺序已固定；DYNAMIC：滚动游标时对行进行的修改更新到表中；FAST_FORWARD：启动性能优化 
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --READ_ONLY:禁止更新数据表；SCROLL_LOCKS：确保通过游标的更新、删除可以成功；OPTIMISTIC：如果行读入游标后被更新，则对该行的更新、删除不成功 
[TYPE_WARNING] --游标从请求的类型银饰转换成另一种类型，给client发出warning msg 
FOR select_statement 
[FOR UPDATE [OF column_name [,....n]]] --指定可被更新的列

cursor variable declaration

declare @curVal cursor 
set @curVal = cursor for  
select name from headgmp_systemuser

cursors open

OPEN cursor_name

cursor to read

FETCH 
[ 
    [NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}] 
    FROM 
] 
{ 
    {[GLOBAL] cursor_name} | @cursor_var_name 
} 
[ 
    INTO @var_name [,...n] 
]

cursor Close

CLOSE cursor_name

cursor release

DEALLOCATE cursor_name

--游标练习：判断名字长度，用数字标注 
--声明游标 
DECLARE name_cursor CURSOR 
LOCAL 
FORWARD_ONLY 
KEYSET 
READ_ONLY 
FOR select top 20 name from headgmp_systemuser 
--声明游标结束 
DECLARE @tName varchar(20) --声明临时变量 
OPEN name_cursor --打开游标 
FETCH NEXT FROM name_cursor INTO @tName 
WHILE @@FETCH_STATUS = 0 
    BEGIN 
        PRINT @tName 
        FETCH NEXT FROM name_cursor INTO @tName 
        UPDATE headgmp_systemuser set nameLen = len(@tName) where name = @tName 
    END 
 
CLOSE name_cursor --关闭游标，可再次进行OPEN 
 
DEALLOCATE name_cursor --释放

stored procedure

basic syntax of the stored procedure

create {procedure | proc} [schame_name.] proc_name [; number]

[

{@ parameter dataType [varying] [= default] [out [put]]}

] [, ... n]

.......

no reference procedure

--无参procedure  
create proc demo01_none_arguments  
as  
select * from headgmp_systemuser go  
--exec demo01_none_arguments

- with input parameters

create proc demo02_with_arguments      
    @deptId char(32),      
    @name varchar(10)  
as      
    select * from headgmp_systemuser  
    where deptid = @deptId and namelike '%'+@name+'%'  
go  
--exec demo02_with_arguments '4028817b3bcb585b013bd0b3b56d074e','' 
--exec demo02_with_arguments @name='李',@deptId='4028817b3bcb585b013bd0b3b56d074e'

- with output parameters

create proc demo03_with_output      
    @deptId char(32),    --部门ID      
    @userCount int out    --部门下用户数量  
as      
    select @userCount=COUNT(id) from headgmp_systemuser where deptid = @deptId  
go 
/*  
 
    declare @count int  
    set @count = 1  
    exec demo03_with_output '4028817b3bcb585b013bd0b3b56d074e',@count out  
    print @count 
 
*/

function

function return value divided according to scalar and table-valued functions

create function getRecordCount () returns int as begin declare @ count int select @ count = count ( * ) from headgmp_systemuser return @ count end go / * declare @ c int set @ c = dbo.getRecordCount () print @ c * /

Java lovers

2013年7月30日星期二

T branch cycle procedure

branch

cycle

delay execution

dynamic SQL statements

stored procedure

没有评论:

发表评论