6.Transact SQL
6.1数据类型Data Types
6.2脚本语法sytanx
6.3脚本游标Cursor
6.4存储过程Procedure
6.5存储函数Function
6.6触发器Trigger
6.7事务Transaction
6.8其它other
/**********************************************************/
6.1数据类型Data Types
1. bigint
2. bit
3. bitwise operators
4. Char
5. collate
6. Create Type
7. Data type
8. Date Type
9. datetime
10. decimal
11. Float
12. FULLTEXT
13. integer
14. Large Text
15. money
16. nchar
17. nVarChar
18. OPENROWSET
19. READTEXT
20. smalldatetime
21. Smallint
22. SQL_VARIANT
23. text
24. TEXTPTR
25. timestamp
26. VARBINARY
27. VARCHAR
28. WRITETEXT
29. Unicode
-----------------------------------------------------------
6.2脚本语法syntax
6.2.0局部/全局变量定义
局部变量 (以@开头)
格式:declare @变量名 数据类型
代码:declare @x int
全局变量 (必须以@@开头)
格式:declare @@变量名 类型
代码:select @@id = '10010001'
6.2.1块语句
格式:
begin
...
end
-----------------------------------------------------------
6.2.2赋值语句set/select
set @id = '10010001'
select @id = '10010001'
6.2.3条件语句(if/case)
6.2.3.1 if语句
declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
print 'x > y' --打印字符串'x > y'
else if @y > @z
print 'y > z'
else print 'z > y'
6.2.3.2 CASE语句
--CASE
use pangu
update employee
set e_wage =
case
when job_level = '1' then e_wage*1.08
when job_level = '2' then e_wage*1.07
when job_level = '3' then e_wage*1.06
else
e_wage*1.05
end
6.2.4循环语句(while)
--WHILE
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
print @x --打印变量x 的值
while @y < 3
begin
select @c = 100*@x + @y
print @c --打印变量c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end
6.2.5定时执行(waitfor)
--WAITFOR
--例 等待1 小时2 分零3 秒后才执行SELECT 语句
waitfor delay '01:02:03'
select * from employee
--例 等到晚上11 点零8 分后才执行SELECT 语句
waitfor time '23:08:00'
select * from employee
-----------------------------------------------------------
6.3脚本游标Cursor
//游标应用顺序
1.DECLARE --为查询设定游标
2.OPEN --检索查询结果打开一个游标
3.FETCH --检索一行查询结果
4.CLOSE / DEALLOCATE--关闭游标或者重新分配游标
语法:
DECLARE <游标名称> CURSOR FOR(select sql)
OPEN <游标名称>
while @@fetch_status = 0
begin
FETCH NEXT FROM <游标名称> INTO <变量名清单>
{其它代码处理}
end
CLOSE <游标名称>
代码1:
/*带游标的存储过程*/
create procedure p_fill_remark_tb_hr_gz
as
declare @id1 int
declare @name1 varchar(30)
declare @bm1 varchar(20)
begin
declare cursor1 cursor for select id,name,bm from tb_hr_gz
open cursor1
fetch next from cursor1 into @id1,@name1,@bm1
while @@fetch_status <> 0
begin
update tb_hr_gz set remark=@name1+'-'+@bm1 where id=@id1
fetch next from cursor1 into @id1,@name1,@bm1
end
close cursor1
end
//测试带游标的存储过程
EXEC dbo.p_fill_remark_tb_hr_gz
-----------------------------------------------------------
6.4存储过程Procedure
//存储过程建立
语法:
create procedure <存储过程名>(
[输入参数列表],[返回参数列表 output]
)
as
[局部变量定义]
begin
{语句体}
end
代码:
create procedure p_update_name_tb_hr_gz(@id int,@newname varchar(30))
as
begin
if (exists(select * from tb_hr_gz where id=@id))
begin
update tb_hr_gz set name=@newname where id=@id
end
end
//测试
EXEC dbo.p_update_name_tb_hr_gz '112','chenglei'
//存储过程删除
语法:
drop procedure <存储过程名>
代码:
drop procedure p_update_name_tb_hr_gz
-----------------------------------------------------------
6.5存储函数Function
//存储函数建立
语法:
CREATE FUNCTION <函数名>(参数变量列表)
[返回值RETURNS 数据类型] [WITH ENCRYPTION]
AS
BEGIN
{函数代码体....}
END
代码:
//函数f_amt_to_eng()功能:数字金额转换为英文字母金额
CREATE FUNCTION f_amt_to_eng(@num numeric(15,2))
RETURNS varchar(400) WITH ENCRYPTION
AS
BEGIN
DECLARE @i int,@hundreds int,@tenth int,@one int
DECLARE @thousand int,@million int,@billion int
DECLARE @numbers varchar(400),@s varchar(15),@result varchar(400)
SET @numbers='one two three four five '
+'six seven eight nine ten '
+'eleven twelve thirteen fourteen fifteen '
+'sixteen seventeen eighteen nineteen '
+'twenty thirty forty fifty '
+'sixty seventy eighty ninety '
SET @s=RIGHT('000000000000000'+CAST(@num AS varchar(15)),15)
SET @billion=CAST(SUBSTRING(@s,1,3) AS int)--将12位整数分成4段:十亿、百万、千、百十个
SET @million=CAST(SUBSTRING(@s,4,3) AS int)
SET @thousand=CAST(SUBSTRING(@s,7,3) AS int)
SET @result=''
SET @i=0
WHILE @i<=3
BEGIN
SET @hundreds=CAST(SUBSTRING(@s,@i*3+1,1) AS int)--百位0-9
SET @tenth=CAST(SUBSTRING(@s,@i*3+2,1) AS int)
SET @one=(CASE @tenth WHEN 1 THEN 10 ELSE 0 END)+CAST(SUBSTRING(@s,@i*3+3,1) AS int)--个位0-19
SET @tenth=(CASE WHEN @tenth<=1 THEN 0 ELSE @tenth END)--十位0、2-9
IF (@i=1 and @billion>0 and (@million>0 or @thousand>0 or @hundreds>0)) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 or @hundreds>0)) or
(@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds>0))
SET @result=@result+', '--百位不是0则每段之间加连接符,
IF (@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds=0 and (@tenth>0 or @one>0)))
SET @result=@result+' and '--百位是0则加连接符AND
IF @hundreds>0
SET @result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+' hundred'
IF @tenth>=2 and @tenth<=9
BEGIN
IF @hundreds>0
SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@tenth*10+171,10))
END
IF @one>=1 and @one<=19
BEGIN
IF @tenth>0
SET @result=@result+'-'
ELSE
IF @hundreds>0
SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@one*10-9,10))
END
IF @i=0 and @billion>0
SET @result=@result+' billion'
IF @i=1 and @million>0
SET @result=@result+' million'
IF @i=2 and @thousand>0
SET @result=@result+' thousand'
SET @i=@i+1
END
IF SUBSTRING(@s,14,2)<>'00'
BEGIN
SET @result=@result+' AND '
IF SUBSTRING(@s,14,1)='0'
SET @result=@result+'zero'
ELSE
SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,1) AS int)*10-9,10))
IF SUBSTRING(@s,15,1)<>'0'
SET @result=@result+' '+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,15,1) AS int)*10-9,10))
END
RETURN(@result)
END
-----------------------------------------------------------
CREATE FUNCTION f_amt_to_chn (@num numeric(14,2))
RETURNS varchar(100) WITH ENCRYPTION
AS
BEGIN
DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int
SET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14)
SET @c_data=''
SET @i=1
WHILE @i<=14
BEGIN
SET @n_str=SUBSTRING(@n_data,@i,1)
IF @n_str<>' '
BEGIN
IF not ((SUBSTRING(@n_data,@i,2)='00') or ((@n_str='0') and ((@i=4) or (@i=8) or (@i=12) or (@i=14))))
SET @c_data=@c_data+SUBSTRING('零壹贰叁肆伍陆柒捌玖',CAST(@n_str AS int)+1,1)
IF not ((@n_str='0') and (@i<>4) and (@i<>8) and (@i<>12))
SET @c_data=@c_data+SUBSTRING('仟佰拾亿仟佰拾万仟佰拾元角分',@i,1)
IF SUBSTRING(@c_data,LEN(@c_data)-1,2)='亿万'
SET @c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1)
END
SET @i=@i+1
END
IF @num<0
SET @c_data='负'+@c_data
IF @num=0
SET @c_data='零元'
IF @n_str='0'
SET @c_data=@c_data+'整'
RETURN(@c_data)
END
//测试函数
select name, gz,dbo.f_amt_to_chn(gz) as 中文金额,dbo.f_amt_to_eng(gz) as 英文金额 from tb_hr_gz
//删除函数
语法:
drop function <函数名称>
代码:
drop function f_num_to_eng
-----------------------------------------------------------
6.6触发器Trigger
22. 1. Trigger( 14 ) 22. 10. Trigger order( 2 )
22. 2. Alter Trigger( 4 ) 22. 11. Drop trigger( 2 )
22. 3. Trigger for after( 4 ) 22. 12. COLUMNS_UPDATED( 1 )
22. 4. Trigger for Delete( 4 ) 22. 13. Update function( 3 )
22. 5. Trigger for insert( 1 ) 22. 14. Deleted table( 2 )
22. 6. Trigger for update( 4 ) 22. 15. Inserted table( 5 )
22. 7. Trigger on database( 2 ) 22. 16. RECURSIVE_TRIGGERS( 1 )
22. 8. Trigger on server( 1 ) 22. 17. Utility trigger( 4 )
22. 9. Trigger on view( 3 )
//触发器建立
语法:
create trigger <触发器名称> on <表名>
[for insert | update | delete]
as
[定义变量]
begin
{代码块...}
end
代码0:
create trigger tg_tb_hr_bm on tb_hr_bm
for insert,update,delete
as
declare @bm_d varchar(20)
declare @bm_i varchar(20)
begin
set @bm_d=(select bm from deleted)
set @bm_i=(select bm from inserted)
if exists(select * from tb_hr_gz ,deleted where(tb_hr_gz.bm =deleted.bm ))
begin
update tb_hr_gz set bm='' where bm =@bm_d
end
if update(bm)
begin
update tb_hr_gz set bm=@bm_i where bm =@bm_i
end
end
//删除触发器
语法:
drop trigger <触发器名称>
代码:
drop trigger tg_w_house_center
-----------------------------------------------------------
6.7事务Transaction
事务(COMMIT/ROLLBACK)
SET TRANSACTION --定义当前事务数据访问特征
COMMIT --提交当前事务
ROLLBACK --取消当前事务
联系客服
