基本控制语句:
代码
-- 1、if
declare @i int
set @i=3
if @i=2
select 'x'
else
select 'y'
-- 2、while
declare @i int
set @i=5
while @i>0
begin
if @i=2
break
select @i
set @i=@i-1
end
-- 3、case when
declare @i int
set @i=4
select
case
when @i=3 then '三'
when @i=4 then '四'
end
declare @i int
set @i=3
select
case when @i=3 then '三' else 'x' end,
case when @i=4 then '四' else 'y' end
常用内置过程
代码
--建表
CREATE TABLE [dbo].[person]( --编号,姓名,性别,生日,年龄,分派次数,是否党员,备注
[cCode] [nvarchar](3) NOT NULL check(len(cCode)=3) primary key,
[cName] [nvarchar](2) NOT NULL,
[cGender] [nvarchar](1) NOT NULL check([cGender]='男' or [cGender]='女'),
[dBirthday] [datetime] NULL check([dBirthday]>'1960-01-01' and [dBirthday] <'1990-01-01'),
[age] as datediff(yyyy,[dBirthday],getdate()),
[iDispatchCount] [int] NULL,
[bParty] [bit] NULL,
[cMemo] [nvarchar](100) NULL
)
insert into person(cCode,cName,cGender,dBirthday,iDispatchCount)
select '001','小a','男','1980-01-01',3 union
select '002','小b','女','1973-03-17',0 union
select '003','小c','女','1970-10-04',10 union
select '004','小d','男','1985-12-12',0 union
select '005','小e','女','1981-02-23',5 union
select '006','小f','男','1982-03-04',7
--1、存储过程
--无返回值
create procedure pro1 @code nvarchar(10)
as
select * from person where cCode=@code
exec pro1 '003'
--有返回值
CREATE PROCEDURE myProc
@outparm int OUTPUT,
@inparm int
AS
begin
SELECT @outparm = COUNT (*) FROM person WHERE iDispatchCount > @inparm
IF (@outparm > 0)
RETURN 10000
ELSE
RETURN -1
end
declare @iOutput int
declare @iReturn int
exec @iReturn=myProc @iOutput output,3
select @iOutput
select @iReturn
--2、函数
create function myAdd(@i as int,@j as int) returns int
as
begin
return @i+@j
end
select dbo.myAdd(2,4)
create function Fun1()
returns table
as
return (select * from person)
select * from dbo.Fun1()
--3、触发器
create trigger myTri1
on person
for update
as
begin
declare @i nvarchar(10)
declare @j int
select @i=iDispatchCount from deleted
select @j=iDispatchCount from inserted
print '字段iDispatchCount从' + str(@i) + '变成' + str(@j)
end
update person set iDispatchCount=8 where cCode='001'
--4、游标
一些应用:
批量生成测试数据
DECLARE @i INT
SET @i=1
WHILE @i<=3000
BEGIN
PRINT @i
INSERT INTO POOrder(code, date) VALUES(@i, Dateadd(day,@i%1000,getdate()));
SET @i=@i+1
END