sqlserver.sql

SQLServer宝典

	-- 显示处理结果
	print '最贵的书是: ' + @title + '  ' + '价格是: ' + convert(varchar(12),@price)
	
errNoTitles:
	-- 关闭
	close cur_titles
	
	-- 释放
	deallocate cur_titles
	
	
go




-- 根据作者编号查看其相应的作品年销售量
-- 低于5000,提示: 销售量太低
-- 高于5000,提示: 销售量太高
create procedure pro_sales_avg (@au_id id)
as
if exists(select au_id from authors where au_id = @au_id)
begin
	declare TempSales cursor
	for 
	select title, ytd_sales 
	from titleauthor ta join titles t
		on ta.title_id = t.title_id
	where au_id = @au_id
	
	open TempSales	
	
	declare @t varchar(80)
	declare @y int
	
	fetch TempSales
	into @t, @y

	while @@fetch_status = 0
	begin
		if 5000 > @y
			print @t + ' ' + convert(varchar(5),@y) + ' 销售量太低'
		else
			print @t + ' ' + convert(varchar(5),@y) + ' 销售量太高'
		fetch TempSales
		into @t, @y
	end
	
	close TempSales
	deallocate TempSales
end
else
	print '作者编号无效！'
go

exec pro_sales_avg '213-46-8915'



/*
示例
A. 使用简单游标和语法
打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项，FETCH NEXT 是唯一可用的提取选项。
*/

DECLARE authors_cursor CURSOR
FOR 
SELECT * FROM authors

OPEN authors_cursor

FETCH NEXT FROM authors_cursor

/*
B. 使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。
*/

SET NOCOUNT ON

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
   @message varchar(80), @title varchar(80)

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR 
FOR 
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor 
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT " "
   SELECT @message = "----- Books by Author: " + 
      @au_fname + " " + @au_lname

   PRINT @message

   -- Declare an inner cursor based   
   -- on au_id from the outer cursor.

   DECLARE titles_cursor CURSOR FOR 
   SELECT t.title
   FROM titleauthor ta, titles t
   WHERE ta.title_id = t.title_id AND
   ta.au_id = @au_id   -- Variable value from the outer cursor

   OPEN titles_cursor
   FETCH NEXT FROM titles_cursor INTO @title

   IF @@FETCH_STATUS <> 0 
      PRINT "         <<No Books>>"     

   WHILE @@FETCH_STATUS = 0
   BEGIN
      
      SELECT @message = "         " + @title
      PRINT @message
      FETCH NEXT FROM titles_cursor INTO @title
   
   END

   CLOSE titles_cursor
   DEALLOCATE titles_cursor
   
   -- Get the next author.
   FETCH NEXT FROM authors_cursor 
   INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

-------- Utah Authors report --------
 
----- Books by Author: Anne Ringer
         The Gourmet Microwave
         Is Anger the Enemy?
 
----- Books by Author: Albert Ringer
         Is Anger the Enemy?
         Life Without Fear










(8)触发器




-- 设定数据库的递归触发器
alter database pubs
	set recursive_triggers on
go

-- 创建数据表，并设定主键、外键以及缺省约束
create table emp_mgr
(
Emp char(30) primary key,
Mgr char(30) null foreign key references emp_mgr(Emp),
NoOfReports int default 0
)
go

-- 创建插入触发器
create trigger emp_marins
on emp_mgr
for insert
as
declare @e char(30),@m char(30)
declare cur_mgr cursor for 
	select emp_mgr.emp
	from emp_mgr,inserted
	where emp_mgr.emp = inserted.mgr

open  cur_mgr

fetch next from cur_mgr into @e

while @@fetch_status = 0
begin
	update emp_mgr
	set emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1
	where emp_mgr.emp = @e
	
	fetch next from cur_mgr into @e
end 

close cur_mgr

deallocate cur_mgr

go

-- 查看数据表相关触发器
sp_helptrigger emp_mgr
go


create trigger emp_mgrupd
on emp_mgr
for update
as
if update (mgr)
begin
	update emp_mgr
	set emp_mgr.NoOfReports = emp_mgr.NoofReports + 1
	from inserted
	where emp_mgr.emp = inserted.mgr
	
	update emp_mgr
	set emp_mgr.NoOfReports = emp_mgr.NoOfReports -1
	from deleted
	where emp_mgr.emp = deleted.mgr

end

go


insert emp_mgr(emp,mgr) values ('Harry',null)
insert emp_mgr(emp,mgr) values ('Alice','Harry')
insert emp_mgr(emp,mgr) values ('Paul','Alice')
insert emp_mgr(emp,mgr) values ('Joe','Alice')
insert emp_mgr(emp,mgr) values ('Dave','Joe')
go

select * from emp_mgr
go

update emp_mgr 
set mgr = 'Harry'
where emp = 'Dave'
go

select * from emp_mgr
go



-- “进销存”系统（触发器的经典应用之一）

某“进销存”系统需要记录进货的信息以及出货的信息，并且当用户记录这些信息的同时，库存信息也需要进行相应的调整，
比如：记录进货信息时，如果该货品是新货，在库存表中还不存在任何信息时，则需要添加一条库存信息（Insert），
否则，只需要对相应的库存记录进行更新（Update）；然而，在记录出货信息时，如果该货品在库存表中的库存量小于出货量时，
则需抛出一个用户自定义的“应用错误”（raise_appliction_error），否则，只需要对相应的库存记录进行更新（Update）。
那么，我们如何来作到数据库系统的自动完成。

create table 进货
(
货号	char(1) not null,
数量	int not null,
时间	smalldatetime
)

create table 库存
(
货号	char(1) not null,
数量	int not null
)


create table 出货
(
货号	char(1) not null,
数量	int not null,
时间	smalldatetime
)

drop table 库存
drop table 进货
drop table 出货

select * from 进货
select * from 库存
select * from 出货


create proc pro进货
(
@h char(1),
@s int,
@j smalldatetime
)
as
insert into 进货 values (@h, @s, @j)

if exists(select * from 库存 where 货号 = @h)
	update 库存 set 数量 = 数量 + @s where  货号 = @h
else
	insert into 库存 values (@h, @s)
go

execute pro进货 'A', 120, '2005-12-28'
execute pro进货 'A', 180, '2005-12-29'


create proc pro出货
(
@h char(1),
@s int,
@j smalldatetime
)
as
if exists(select * from 库存 where 货号 = @h)
begin
	if exists(select 数量 from 库存 where 货号 = @h and 数量 >= @s)
	begin
		update 库存 set 数量 = 数量 - @s where  货号 = @h
		insert into 出货 values (@h, @s, @j)
	end
	else
		print 'No,you need too more!'
end
else
	print 'No, no you need Dongxi!' 
go

execute pro出货 'D', 120, '2005-12-28'
execute pro出货 'A', 680, '2005-12-29'
execute pro出货 'A', 80, '2005-12-29'

create proc pro进货
(
@h char(1),
@s int,
@j smalldatetime
)
as
insert into 进货 values (@h, @s, @j)
go

create trigger tri进货
on 进货
after insert
as
declare @h char(1)
declare @s int
select @h = 货号, @s = 数量 from inserted

if exists(select * from 库存 where 货号 = @h)
	update 库存 set 数量 = 数量 + @s where 货号 = @h
else
	insert into 库存 values (@h, @s)
go




create proc pro销售
(
@h char(1),
@s int,
@j smalldatetime
)
as
if exists(select * from 库存 where 货号 = @h)
	insert into 销售 values (@h, @s, @j)
go


create trigger tri销售
on 销售
after insert
as
if (select 数量 from inserted) > (select 数量 from 库存 where 货号 = (select 货号 from inserted))
begin
	print 'Nononono!'
	rollback 
end
else
	update 库存 set 数量 = 数量 - (select 数量 from inserted) where 货号 in (select 货号 from inserted)	
go






-- 部门管理（触发器的递归实现的经典应用之一）
use pubs
go

drop table departments
go

-- 定义数据表
create table Departments
(
dep_id		int		not null	identity(1,1)	primary key,
dep_name	varchar(18)	not null	unique,
dep_parent	int		not null,
dep_description	varchar(42)	null
)
go

-- 添加基本数据
insert into departments
(dep_name, dep_parent, dep_description)
values
('上海', 0, '省级代理')
go

insert into departments
(dep_name, dep_parent, dep_description)
values
('北京', 0, '省级代理')
go

insert into departments
(dep_name, dep_parent, dep_description)
values
('湖南', 0, '省级代理')
go

insert into departments
(dep_name, dep_parent, dep_description)
values
('长沙', 3, '市级代理')
go

insert into departments
(dep_name, dep_parent, dep_description)
values
('株洲', 3, '市级代理')
go

insert into departments
(dep_name, dep_parent,dep_description)
values
('湘潭', 3, '市级代理')
go

insert into departments
(dep_name, dep_parent, dep_description)
values
('石峰', 5, '区级代理')
go

insert into departments
(dep_name, dep_parent, dep_description)
values
('天元', 5, '区级代理')
go

insert into departments
(dep_name, dep_parent,dep_description)
values
('芦淞', 5, '区级代理')
go

select * from departments
go


-- 定义添加部门的存储过程
create procedure proc_InsertDepartment
@dep_parent_id	int,
@dep_name	varchar(20),
@dep_id		int output
as
insert into departments (dep_name, dep_parent,dep_description) values (@dep_name, @dep_parent_id, '代理')
select @dep_id =  @@IDENTITY
go

-- 定义修改指定部门的名称的存储过程
create procedure proc_UpdateDepartment
@dep_id		int,
@dep_name	varchar(20)
as
update departments set dep_name = @dep_name where dep_id = @dep_id
go

-- 定义删除指定部门以及相应子部门的存储过程
create procedure proc_DeleteDepartment
@dep_id		int
as
delete from departments where dep_id = @dep_id
go

-- 定义删除触发器(递归)
create trigger tri_del_dep
on departments
for delete
as
-- 获取被删除部门的编号
-- 检查是否存在子部门
if exists(select * from departments where dep_parent in (select dep_id from deleted ))
	begin
		-- 如果返回值为真，删除相应子部门，激活子部门的删除触发器，进行递归调用
		delete from departments where dep_parent in (select dep_id from deleted )
	end
go

-- 启动直接递归触发器
exec sp_dboption 'pubs','recursive triggers','true'

-- 启动间接递归触发器
exec sp_configure 'nested triggers','1' 



(9)链接服务
-- 在SQL Server上创建一个链接服务：与其他数据库建立连接，将其数据表或是视图作为本地信息源访问
-- 比如：将Access数据库Northwind作为一个链接服务源
EXEC sp_addlinkedserver 
   @server = 'SEATTLE Mktg', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'


SELECT *
into #au4
FROM OPENQUERY([SEATTLE Mktg], 'SELECT * FROM 产品') 
GO

select * 
from #au4