SQL Server(二)

create database DemoTest
Go
use DemoTest
/*第一部分临时表和游标*/

/*声明局部表*/
declare @t_A table
(
ID int IDENTITY (1, 1) primary key,
userID varchar(20),
passWord varchar(20),
loginTime dateTime
)

insert @t_A values('PMQ','123',getDate())
insert @t_A values('PMQ','123',dateadd(d,-1,getDate()))
insert @t_A values('PMQ','123',dateadd(yyyy,-1,getDate()))
insert @t_A values('PMQ','123',dateadd(m,-1,getDate()))
insert @t_A values('PMQ','123',dateadd(d,-2,getDate()))
insert @t_A values('PMQ','123',dateadd(d,-2,getDate()))
insert @t_A values('PMQ','123',dateadd(d,-1,getDate()))

/*声明局部表*/
declare @t_B table
(
ID int IDENTITY (1, 1) primary key,
userID varchar(20),
passWord varchar(20),
loginTime dateTime
)

/*游标执行一个结果集操作*/
declare @userID varchar(20)
declare @passWord varchar(20)
declare @loginTime datetime
declare var_cursor cursor for
select userID,passWord,loginTime from @t_A
open var_cursor
fetch next from var_cursor into @userID,@passWord,@loginTime
while(@@fetch_status=0)
begin
insert @t_B values(@userID,@passWord,@loginTime)
fetch next from var_cursor into @userID,@passWord,@loginTime
end
close var_cursor
deallocate var_cursor

select * from @t_A
select * from @t_B

/*创建表*/
create table ValueTable
(
ID int IDENTITY (1, 1) primary key,
userID varchar(20),
passWord varchar(20),
loginTime dateTime
)

/*
insert ValueTable values('PMQ','456',getDate())
insert ValueTable values('LCC','456',dateadd(d,-1,getDate()))
insert ValueTable values('FJT','123',dateadd(yyyy,-1,getDate()))
insert ValueTable values('CYJ','123',dateadd(m,-1,getDate()))
insert ValueTable values('YHT','1234',dateadd(d,-2,getDate()))
insert ValueTable values('YXS','1235',dateadd(d,-2,getDate()))
insert ValueTable values('WCF','1237',dateadd(d,-1,getDate()))
*/

 

 

/*第二部分存储过程*/

/*创建输出游标的存储过程*/
CREATE PROCEDURE dbo.currency_cursor
    @currency_cursor CURSOR VARYING OUTPUT
AS
    SET @currency_cursor = CURSOR
    FORWARD_ONLY STATIC FOR
     select * from ValueTable;
    OPEN @currency_cursor;
GO

/*声明一个游标，输出游标赋给局部游标*/
DECLARE @MyCursor CURSOR;
EXEC dbo.currency_cursor @currency_cursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

select * from ValueTable

/*创建带输入输出参数的存储过程*/
create procedure dbo.proViewTable
(
@MaxDateTime datetime output,
@passWord   varchar(20)
)
as
set  @MaxDateTime=(select isnull(max(loginTime),getdate()) from valueTable where passWord=@passWord)
Go

 

/*调用带输出参数的存储过程，并且查询输出参数*/
declare @CreateDate datetime
exec dbo.proViewTable @CreateDate outPUT,'123'
select @CreateDate

 

/*
 多输出参数的存储过程
1.注意存储过程的参数不需要declare声明
2.包括参数’()‘可要可不要
*/
create procedure dbo.prosetValue
(
   @ID int,
   @userID varchar(20) output,
   @passWord varchar(20) output,
   @loginTime dateTime output
)
as
select @loginTime=loginTime,@userID=userID,@passWord=passWord from valuetable whereID=@ID
GO

/*调用存储过程，查询局部变量*/
declare @userID varchar(20)
declare @passWord varchar(20)
declare @loginTime dateTime
exec dbo.prosetValue 2,@userID output,@passWord output,@loginTime output
select @userID,@passWord,@loginTime

/*创建历史表*/
create table HistoryTable
(
ID int IDENTITY (1, 1) primary key,
userID varchar(20),
passWord varchar(20),
loginTime dateTime
)

/*
valuetable 的所有数据插入到HistoryTable中
这一句在有时很有用
*/
insert into HistoryTable select UserID,PassWord,loginTime from valuetable
select * from HistoryTable
delete from HistoryTable

 

 

/*第三部分触发器
  本trigger的作用是当删除valueTable的一条记录时,其会在HistoryTable储存这条记录

  create trigger [触发器名称]
  on [要建触发器的表名] [状态]如after delete ,for update等
as
 sql语句集合
GO

  触发的三张临时表.inserted,deleted,updated
  注意没有selected表
  触发器的几种状态  FOR | AFTER | INSTEAD OF
  update,insert,delete
*/

create trigger tig_valueTableDelete
on valuetable after delete
as
declare @userID varchar(20)
declare @passWord varchar(20)
declare @loginTime datetime
select @userID=userID,@passWord=passWord,@loginTime=logintime from deleted
if @userID<>null and @passWord<>null and @loginTime<>null
insert HistoryTable values(@userID,@passWord,@loginTime)
GO

/*测试触发器*/
select * from valueTable
delete from valuetable where ID=1
select * from historytable

/*
  第四部分View视图
  视图返回的是一个table
  视图类似一虚拟的Table
*/
create view v_valueTable
as
select password as V_PASSWORD, max(loginTime) as V_LOGINTIME from valuetable group by password
GO

select * from dbo.v_valueTable
/*使用别名*/
create view v_valueTable1
([用户密码],[登陆时间])
as
select password as V_PASSWORD, max(loginTime) as V_LOGINTIME from valuetable group by password
GO

select * from dbo.v_valueTable1

/*
  第五部分Function自定义函数

*/

/*
返回Table的自定义函数
这种自定义函数使用起来非常方便灵活,
在一定的情况下它比View更加好使
*/
create function f_valueTable
(
 @password varchar(20)
)
returns @v_table table
(
userID varchar(20),
password varchar(20),
loginTime dateTime
)
as
begin
 insert into @v_table select userID,password,loginTime from valueTable where password=@password
 return
end
select * from dbo.f_valueTable('456')

create function f_NumberValue
(
@number1  int,
@number2  int,
@number3 int
)
returns int
as
begin
set @number3=@number1*@number2
return @number3
end
select dbo.f_NumberValue(10,5,11)