第10章 可编程对象(1)

--第10章 可编程对象
--10.1 变量
--变量用于临时保存数据值，以供在声明它们的同一批处理语句中引用。
declare @i as int;
set @i = 10;
set @i += @i;
select @i as i;

declare @j as int = 10;

use TSQLFundamentals2008;
declare @empname as nvarchar(10);
set @empname = (select firstname + N' ' + lastname
from HR.Employees
where empid = 3);

select @empname as empname;

declare @firstname as nvarchar(10), @lastname as nvarchar(10);
select
@firstname = firstname,
@lastname = lastname
from HR.Employees
where empid = 3;

select @firstname as firstname, @lastname as lastname;
--10.2 批处理
--事务是工作的原子工作单元,而一个批处理可以包含多个事务,一个事务也可以在多个批处理中的某些部分提交.
--如果批处理中存在语法错误,整个批处理就不会提交到SQL Server执行.

--变量是属于定义它们的批处理的局部变量,如果试图引用在其他批处理中定义的变量,SQL Server引擎将会报告
--引用的变量还没有定义.

--Create default, create function, create proceduce, create rule, create schema, create trigger, create view
--不能在同一批处理中和其他语句同时编译。
if OBJECT_ID('Sales.MyView','v') is not null drop view sales.MyView;
go  ---must go here!!!
create view Sales.MyView
as
select YEAR(orderdate) as orderyear
from  Sales.Orders
group by YEAR(orderdate);
go

--Go可以支持一个正整数参数,表示Go之前的批处理将执行指定的次数.当需要重复执行批处理时,就可以使用这个新选项.
if OBJECT_ID('dbo.mytable', 'u') is not null  drop table dbo.mytable;
go
create table dbo.mytable
(i int primary key identity,
j int not null
);
go
insert into dbo.mytable(j)
values(0);
go
declare @i as int = (select top 1 j from dbo.mytable order by j desc);
set @i += 1;
insert into dbo.mytable(j)
values(@i);
go 100  --Execute 100 times here!!!
select * from dbo.mytable;