第10章 可编程对象(2)

--10.3 流程控制元素
--10.3.1 if...else...
if YEAR(CURRENT_TIMESTAMP) <> YEAR(DATEADD(day, 1, CURRENT_TIMESTAMP))
print 'today is the last day of the year.'
else 
print 'today is not the last day of the year.'
go

if YEAR(CURRENT_TIMESTAMP) <> YEAR(DATEADD(DAY, 1, CURRENT_TIMESTAMP))
print 'today is the last day of the year.'
else
if MONTH(CURRENT_TIMESTAMP)<> MONTH(DATEADD(DAY, 1, CURRENT_TIMESTAMP))
print 'today is the last day of the month.'
else
print 'today is not the last day of the year and the month.'

--如果需要在if或else部分运行多条语句,则可以使用语句块.语句块的边界是用一对begin和end关键字标识的
if DAY(CURRENT_TIMESTAMP) =1
begin
	print 'today is the first day of the month.';
	print 'starting a full database backup.';
	backup database mydb
	to disk = 'C:\Temp' with init;
	print 'Finished full database backup.';
end
else
begin 
	print 'today is not the first day of the month';
	print 'starting a differential database backup.';
	backup database mydb
	to disk = 'C:\Temp';
	print 'Finished differential database backup.';
end

--10.3.2 while
declare @i as int = 1;
while @i<=10
begin
	print @i;
	set @i+=1;
end;
go

--如果想在循环体内部的某一处退出当前循环,继续执行循环体之后的语句,则可以使用break命令.
declare @j as int = 0;
while @j<10
begin
	print @j;
	set @j+=1;
	if @j>6
		break;
end
go

--如果想在循环体内的某处忽略当前循环的剩余处理,继续进行下一次循环,则可以使用continue命令.
declare @j as int = 0;
while @j<10
begin
	set @j+=1;
	if @j=6
		continue;
	print @j;
end
go

--10.3.3 if&while
set nocount on;
use tempdb;
if OBJECT_ID('dbo.nums', 'u') is not null
	drop table dbo.nums;
create table dbo.nums(
P int not null constraint PK_Nums primary key identity,
V int not null unique);
go

declare @i as int = 1;
while @i<=1000
begin
	insert into dbo.nums(V)
	values(@i);
	set @i+=1;
end
go

select * from dbo.nums;