sql server 常用

 

--事务
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ   
begin transaction
  --insert delete update select
  if @@error<>0   
    begin
      rollback transaction
    end
commit transaction

--变量
declare @name varchar(20) --声明   
select @name='zhangsan' --赋值

--存储过程
Create proc sp_demo @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int output
with encryption --加密
as
insert table1 (column1,column2,column3)
Values(@param1,@param2,@param3)
select @param4=sum(money) from bankMoney where userID='Zhangsan'
go

declare @total_price int
exec insert_bank '004','Zhangsan','男',@total_price output
print '总余额为'+convert(varchar,@total_price)
go
 
--视图，视图也是表，一般是多个表的交集
CREATE VIEW PartitionedView 
AS
SELECT * 
FROM MyDatabase.dbo.PartitionTable1
UNION ALL
SELECT *
FROM Server2.MyDatabase.dbo.PartitionTable2
UNION ALL
SELECT *
FROM Server3.MyDatabase.dbo.PartitionTable3

--触发器
Create Trigger tg_event On event
for Insert 
As
begin
  insert event_temp (id,project_id,taji_id,[time],event_type,event_miaoshu,drive_id) 
  select id,project_id,taji_id,[time],event_type,event_miaoshu,drive_id
  from inserted
end