-
|
第十三课时 游标
一.对一个数据的多条记录进行处理(得到的是一个结果集/数据集)
格式: select * from tablename<表名> [where 子句]
select * from table4
select * from table4 where 年龄>20
二.定义.打开.关闭.../游标的格式.
1.定义游标格式:declare 游标名 scroll cursor for [select 语句]
2.打开游标格式 pen 游标名
3.提取数据格式:fetch next from 游标名
4.关闭游标格式:close 游标名
5.释放游标格式: deallocate 游标名
例(1).(游标针对以上的五步法)
declare cur scroll cursor for select * from table4
open cur
fetch next from cur
close cur
--以上的运行结果为<只取一条记录> 1) 200310 a 20 NULL 江西师大 NULL
例(2).取两条记录
declare cur scroll cursor for select * from table4
open cur
fetch next from cur
fetch next from cur
close cur
deallocate cur
--执行结果如下:
(1)200310 a 20 NULL 江西师大 NULL
(2)200311 b 19 df 江西师大 1997-08-07 00:00:00.000
将取以上两条记录.
例(3).引用全局变量将表中数据全部取出
declare cur scroll cursor for select * from table4
open cur
fetch next from cur
while @@fetch_status=0
begin
fetch next from cur
end
close cur
deallocate cur
执行结果为记录全部找出来,而且是一条记录一个表
**@@fetch_status返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
返回值 描述(以下三种值)
A. 0 FETCH 语句成功。
B. -1 FETCH 语句失败或此行不在结果集中。
C. -2 被提取的行不存在。
三.定义变量.(以后开发数据库不可能对整个记录进行操作,只能对某个字段进行操作.)
例(1).
declare
@xh varchar(11),
@xm varchar(8),
@nl int,
@jl varchar(100),
@xx varchar(20),
@sj datetime
declare cur scroll cursor for select * from table4
open cur
fetch next from cur into @xh,@xm,@nl,@jl,@xx,@sj
while @@fetch_status=0
begin
print @xm
print @xh
print @nl
print @jl
print @xx
print @sj
print '##########'
fetch next from cur into @xm,@xh,@nl,@jl,@xx,@sj
end
close cur
deallocate cur
以上程序运行显示结果如下:
/*a
200310
20
江西师大
##########
200311
b
19
df
江西师大
08 7 1997 12:00AM
##########
200312
c
20
江西师大
##########
200313
d
25
江西师大
09 8 1998 12:00AM
##########
200314
e
25
江西师大
01 1 2002 12:00AM
##########
200315
f
25
江西师大
01 1 2000 12:00AM
##########
200416j
pent
44
rere
江西师大
01 1 2000 12:00AM
##########*/
四.带上条件的游标分别将值放入变量中.
例(1).
declare
@xh varchar(11),
@xm varchar(8),
@nl int,
@jl varchar(100) ,
@xx varchar(20),
@sj datetime
declare cur scroll cursor for select * from table4 where 年龄>20
open cur
fetch next from cur into @xh,@xm,@nl,@jl,@xx,@sj
while @@fetch_status=0
begin
print @xh
print @xm
print @nl
print @jl
print @xx
print @sj
print '###$######'
fetch next from cur into @xh,@xm,@nl,@jl,@xx,@sj
end
close cur
deallocate cur
运行以上程序执行结果如下:
/*200313
d
25
江西师大
09 8 1998 12:00AM
###$######
200314
e
25
江西师大
01 1 2002 12:00AM
###$######
200315
f
25
江西师大
01 1 2000 12:00AM
###$######
200416j
pent
44
rere
江西师大
01 1 2000 12:00AM
###$######*/
五.还可以对列进行操作.
例(1).
declare
@xh varchar(11),
@xm varchar(8),
@nl int,
@jl varchar(100),
@xx varchar(20),
@sj datetime
declare cur scroll cursor for select 学号,姓名,年龄,入学时间 from table4
open cur
fetch next from cur into @xh,@xm,@nl,@sj
while @@fetch_status=0
begin
print @xh
print @xm
print @nl
print @sj
print '########'
fetch next from cur into @xh,@xm,@nl,@sj
end
close cur
deallocate cur
运行以上程序执行结果如下:
/*200310
a
20
########
200311
b
19
08 7 1997 12:00AM
########
200312
c
20
########
200313
d
25
09 8 1998 12:00AM
########
200314
e
25
01 1 2002 12:00AM
########
200315
f
25
01 1 2000 12:00AM
########
200416j
pent
44
01 1 2000 12:00AM
########*/
六.对列和行同时进行操作
例(1).
declare
@xh varchar(11),
@xm varchar(8),
@nl int,
@jl varchar(100),
@xx varchar(20),
@sj datetime
declare cur scroll cursor for select 年龄,姓名,入学时间
from table4 where 年龄>20
open cur
fetch next from cur into @nl,@xm,@sj
while @@fetch_status=0
begin
print @nl
print @xm
print @sj
print '#########'
fetch next from cur into @nl,@xm,@sj
end
close cur
deallocate cur
运行以上程序执行结果如下:
/*25
d
09 8 1998 12:00AM
#########
25
e
01 1 2002 12:00AM
#########
25
f
01 1 2000 12:00AM
#########
44
pent
01 1 2000 12:00AM
#########*/
七.向表中添加记录.
例(1).
declare
@xh varchar(11),
@xm varchar(8),
@nl int,
@jl varchar(100),
@xx varchar(20),
@sj datetime
declare cur scroll cursor for select 学号,姓名,年龄,入学时间 from table5
open cur
fetch next from cur into @xh,@xm,@nl,@sj
begin tran
while @@fetch_status=0
begin
insert into table4 (xh,xm,nl,sj) values (@xh,@xm,@nl,@sj)
fetch next from cur into @xh,@xm,@nl,@sj
end
if @@error<>0
rollback
else
commit tran
close cur
deallocate cur
程序运行结果:则会将表table5中的所有记录添加到表table4中.
八.根据某个条件去更新另外一个表中的内容.
例(1)
declare
@xh varchar(11),
@xm varchar(8),
@nl int,
@jl varchar(100),
@xx varchar(20),
@sj datetime,
@i int
declare cur scroll cursor for select 学号 from
table4 where 年龄>30
open cur
fetch next from cur into @xh
set @i=1
begin tran
while @@fetch_status=0
begin
update table5 set nl=nl+@i*10 where xh=@xh
fetch next from cur into @xh
set @i=@i+1
end
if @@error<>0
rollback tran
else
commit tran
close cur
deallocate cur
/*要注意向表中更新的字段名需与变量名一一对应,否则会有以下报错信息.
要注意以下信息由于在SELECT 后的字段名与在FETCH...INTO后的字段名不是一一对应的关系)
服务器: 消息 16924，级别 16，状态 1，行 14
Cursorfetch: 在 INTO 列表中声明的变量数目必须与所选择的列数目匹配。*/
以上程序运动结果为:根据table4中'年龄'字段大于40的字号记录,而去更新table5表中的所对应'nl'并使其更新后的逐个递增10.
九.(删除操作)根据一定的条件由一个表中的操作另外一个表中的记录
declare
@xh varchar(11),
@xm varchar(8),
@nl int,
@jl varchar(100),
@xx varchar(20),
@sj datetime,
@i int
declare cur scroll cursor for select 学号 from table4
open cur
fetch next from cur into @xh
set @i=1
begin tran
while @@fetch_status=0
begin
delete from table5 where nl>30
fetch next from cur into @xh
end
if @@error<>0
rollback tran
else
commit tran
close cur
deallocate cur
以上程序的运行结果:根据table4中查询'学号'字段的记录.然后去删除table5表中的与其对应并且'nl'大于30记录值
====================================================================================================================================================================================================================
第十四课时 存储过程
一.概述
1.一般基于网络开发,考虑到网络的性能,所以在数据库用存储过程来提高网络的性能(较之没用存储过程的系统的网络速度将提升100倍的速率),所以基于网络的数据库开发一般要使用存储过程.
可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。
2.利用存储过程
(1)建立存储过程
(2)存储过程的作用:添加,更新,删除,查询,统计.
(3)调用存储过程
二.创建存储过程
在'企业管理器'点击'新建存储过程'即可.
格式:CREATE PROCEDURE [OWNER].[PROCEDURE NAME]
<输入与输出的函数>
AS
<临时存储数据的变量>
begin
<开始写代码>
end
三.向表中查询的存储过程
例(1).创建一个查询的存储过程
CREATE PROCEDURE select1
AS
begin
select * from table1
end
GO
针对select1的过程调用:
方法一:select1
方法二:exec select1
方法三:execute select1
执行以上程序的结果:查询显示table1中所有字段的内容.
例(2).创建一个带参数的查询的存储过程
CREATE PROCEDURE select2
@nl int
AS
begin
select * from table1 where nl>@nl
end
GO
针对select2(带一个参数的存储过程)的过程调用:
方法一:select2 20
方法二:exec select2 20
方法三:execute select2 20
执行以上程序的结果:查询显示table1中'nl'字段大于20所有记录值.
例(3).创建两个输入参数的查询的存储过程
CREATE PROCEDURE select3
@nl int,
@sj datetime
AS
begin
select * from table1 where nl>@nl and sj>@sj
end
GO
针对select3(带两个参数的存储过程)的过程调用:
方法一:select3 20 , '2000-1-1'
方法二:exec select3 20,'2000-1-1'
方法二:execute select3 20,'2000-1-1'
执行以上程序的结果:查询显示table1中'nl'字段大于20和'sj'字段大于'2000-1-1'所有记录值.
四.向表中添加数据的存储过程
例(1):向表中添加数据时不具有显示结果的特性.
CREATE PROCEDURE ins
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
AS
begin
begin tran
insert into table1(xm,nl,xs,sj) values (@xm,@nl,@xs,@sj)
if @@error<>0
rollback tran
commit tran
end
GO
针对ins,(向表中添加数据的存储过程)的过程调用:
ins 'peng',15,111,'1999-9-9'
执行以上程序的结果:向表table1中添加一条'xm'字段为'peng','nl'字段为15,'xs'字段为111 'sj'字段为'1999-9-9'的记录.添加记录不会立即显示添加的结果.
例(2):向表中添加数据时具有显示结果的特性.
CREATE PROCEDURE ins1
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
AS
begin
begin tran
insert into tablea1 (xm,nl,xs,sj) values (@xm,@nl,@xs,@sj)
if @@error<>0
rollback tran
commit tran
select * from table1 /*加入此语句会显示添加记录的结果*/
end
GO
针对ins1,(向表中添加数据的存储过程)的过程调用:
ins 'peng',15,111,'1999-9-9'
执行以上程序的结果:向表table1中添加一条'xm'字段为'peng','nl'字段为15,'xs'字段为111 'sj'字段为'1999-9-9'的记录.添加记录会立即显示添加的结果.
五.向表中更新数据的存储过程
例(1):向表中更新数据时不具有显示结果的特性.
CREATE PROCEDURE up
@nl int,
@xm varchar(8)
AS
begin
begin tran
update table1 set nl=@nl where xm=@xm
if @@error<>0
rollback tran
commit tran
end
GO
针对up(向表中更新数据的存储过程)的过程调用:
up 50,'peng'
执行以上程序的结果:向表table1中根据'xm'字段为'peng'的条件记录去更新其'nl'字段值为50.更新记录不会立即显示更新的结果.
例(2):向表中更新数据时具有显示结果的特性.
CREATE PROCEDURE up1
@nl int,
@xm varchar(8)
AS
begin
begin tran
update tablea1 set nl=@nl where xm=@xm
if @@error<>0
rollback tran
commit tran
select * from tablea1 /*加入此语句会显示添加记录的结果*/
end
GO
针对up(向表中更新数据的存储过程)的过程调用:
up 50,'peng'
执行以上程序的结果:向表table1中根据'xm'字段为'peng'的条件记录去更新其'nl'字段值为50.更新记录会立即显示更新的结果.
六.向表中删除数据的存储过程
例(1):向表中删除数据时不具有显示结果的特性.
CREATE PROCEDURE del
@xm varchar(8)
AS
begin
begin tran
delete from table1 where xm=@xm
if @@error<>0
rollback tran
commit tran
end
GO
针对del(向表中删除数据的存储过程)的过程调用:
del 'peng'
执行以上程序的结果:将删除表table1中'xm'字段为'peng'的所有记录.删除记录不会立即显示删除的结果.
例(1):向表中删除数据时具有显示结果的特性.
CREATE PROCEDURE del1
@xm varchar(8)
AS
begin
begin tran
delete from tablea1 where xm=@xm
if @@error<>0
rollback tran
commit tran
select * from tablea1 /*加入此语句会显示添加记录的结果*/
end
GO
针对del1(向表中删除数据的存储过程)的过程调用:
del1 'peng'
执行以上程序的结果:将删除表table1中'xm'字段为'peng'的所有记录.删除记录会立即显示删除的结果.
七.建立有输入与输出参数的存储过程
例(1):
CREATE PROCEDURE out1
@sumnl int output
AS
begin
set @sumnl=(select sum(nl) from tablea1)
end
GO
针对out1(输出参数的存储过程)的过程调用
(1)以下为错误的执行调用:
declare
@sss int
begin
out1 @sss output
end
/*--报错信息如下:
服务器: 消息 170，级别 15，状态 1，行 57
第 57 行: 'out1' 附近有语法错误。*/
(2)不显示结果的调用
declare
@sss int
begin
exec out @sss output
end
以下为正确的调用:
declare
@sss int
begin
exec out @sss output
print @sss
end
第二种正确的调用(会显示结果)
declare
@sss int
begin
execute out @sss output
print @sss
end
八.带两个数据输出参数的存储过程
CREATE PROCEDURE out2
@sumnl int output,
@sumxs decimal(7,2) output
AS
begin
set @sumnl=(select sum(nl) from tablea1)
select @sumxs=(select sum(xs) from tablea1)
end
GO
out2带输入和输出的参数的过程调用
declare
@sss int
begin
execute out2 @sss output,9
print @sss as
end
九.带输入与输出参数的存储过程
CREATE PROCEDURE out3
@count int output,
@nl int
AS
begin
set @count=(select count(*) from table1 where nl>@nl)
end
GO
out3的调用过程:
declare
@sss int
begin
execute out3 @sss output ,9
print @sss
end
十.输出某字段名的存储过程.
CREATE PROCEDURE out4
@nl int output,
@xm varchar(8)
AS
declare
@ii int
begin
set @ii=(select count(*) from tablea1 where xm=@xm)
if @ii=1
set @nl=(select nl from tablea1 where xm=@xm)
else
set @nl=0
end
GO
输出某个字段的过程调用out4(针对多个数值对就同个变量的情况)
declare
@sss int
begin
execute out4 @sss output,'peng'
print @sss
end
达到的效果是:如在表中'姓名'只有一个单独的,则显示该'姓名'所对就的年龄.
如在表中'姓名'有两个或两个以上相同的,则显示结果为'0'
因为不能同时给一个变量赋给两个值.
--输出某个字段的过程调用aa.(根据某个条件去输出特定的字段)
declare
@sss int
begin
execute aa @sss output,'peng'
print @sss
end
和以上相比:如在表中'姓名'只有一个单独的,而且只有一个唯一值,那么将对就显示出'年
龄'字段记录.
但当有两个或以上的值时,则会有以下的报错提示:
"服务器: 消息 512，级别 16，状态 1，过程 aa，行 7
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
====================================================================================================================================================================================================================
第十五课时 触发器
一.概述
1.触发器适用于表与表之间的操作.是一种特殊的存储过程.一般适于当一个表中的数据发生了改变(添加,删除,更新)时,造成其它表中的数据有何影响.
所有用触发器可以完成的操作(逻辑规则),还可以用存储过程来写.但不是所有的存储过程都能通过触发器来完成的.
触发器还是对表中数据进行操作,包括以下:
(1).添加的触发器
(2).删除的触发器
(3).更新的触发器
2.当向表中加入一条记录时,则会激活'添加触发器',并产生一个'临时表'<inserted>,临时表的结构和原表结构相同.分为以下两步:
A.将记录加入到实际存在的表中
B.触发一个动作,产生一个临时表<inserted>并且加入第二条记录时,自动将原来的记录替换原有在临时表中的记录.
3.当向表中删除一条记录时,则会激活'删除触发器',并主生一个'临时表'<deleted>,这个临时表的记录存储刚才被删除的记录.在临时表中的结构与当前表的结构相同.
4.当向表中更新一条记录时,则先会激活'删除触发器',并主生一个'临时表'<deleted>,这个临时表的记录存储刚才被删除的记录.然后再激活'添加触发器',并主生一个'临时表'<inserted>,这个临时将添加进入新的记录.
二.创建触发器(要建立在一个表的基础上)
例:在'table1'表击右键-----'所有任务'-----'管理触发器'----在'触发器属性'对话框-----'新建'----可在下面键入代码-----即可完成.
三.向表中添加记录,同时激发触发器,而使另外的表发生的一些变化.
1.向TABLE1加入记录时,在TABLE111也会生成同样的记录.
CREATE TRIGGER in1 ON [dbo].[tablea1]
FOR INSERT
AS
declare
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
begin
set @xm=(select xm from inserted)
set @nl=(select nl from inserted)
set @xs=(select xs from inserted)
set @sj=(select sj from inserted)
begin tran
insert into table111 (xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
if @@error<>0
rollback tran
commit tran
end
2.向TABLEA1加入记录的同时,会向TABLE111加入一条同样的记录.
并能向TABLE4更新'年龄'字段的值.
CREATE TRIGGER in2 ON [dbo].[tablea1]
FOR INSERT
AS
declare
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
begin
set @xm=(select xm from inserted)
set @nl=(select nl from inserted)
set @xs=(select xs from inserted)
set @sj=(select sj from inserted)
begin tran
insert into table111(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
update table4 set 年龄=@nl where 姓名=@xm
if @@error<>0
rollback tran
commit tran
end
3.--向TABLEA1表中加入记录时:
会向TABLE111自动加入同样的记录.
会向TABLE4根据"姓名"与TABLEA1的XM相同,而去更改表TABLE4的'年龄'字段的值为TABLEA1的NL值.
同时还会TABLE18中根据XM相同的条件(与TABLEA1比较而言)去删除相应的记录.
CREATE TRIGGER dem ON [dbo].[tablea1]
FOR insert
AS
declare
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
begin
set @xm=(select xm from inserted)
set @nl=(select nl from inserted)
set @xs=(select xs from inserted)
set @sj=(select sj from inserted)
begin tran
--insert into table111(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
--update table4 set 年龄=@nl where 姓名=@xm
delete from table18 where xm=@xm
if @@error<>0
rollback tran
commit tran
end
四.删除.
1.向TABLEA1删除记录时,会自动向TABLE111生成一条刚才被删的记录.
CREATE TRIGGER dell ON [dbo].[tablea1]
FOR DELETE
AS
declare
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
begin
set @xm=(select xm from deleted)
set @nl=(select nl from deleted)
set @xs=(select xs from deleted)
set @sj=(select sj from deleted)
begin tran
insert into table111(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
if @@error<>0
rollback tran
commit tran
end
2.向TABLEA1中删除记录时,会向TABLE111自动加入一条刚才被删除的记录.
另外,还会向TABLE18中根据XM与TABLEA1中的XM相同的条件去删除
TABLE18中这条对应的记录.
CREATE TRIGGER dell1 ON [dbo].[tablea1]
FOR DELETE
AS
declare
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
begin
set @xm=(select xm from deleted)
set @nl=(select nl from deleted)
set @xs=(select xs from deleted)
set @sj=(select sj from deleted)
begin tran
insert into table111(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
delete from table18 where xm=@xm
if @@error<>0
rollback tran
commit tran
end
3.向TABLEA1中删除记录时,会向TABLE111自动加入一条刚才被删除的记录.
另外,还会向TABLE18中根据XM与TABLEA1中的XM相同的条件去删除
TABLE18中这条对应的记录
还会向TABLE4中'姓名'字段与TABLEA1中'XM'相同的条件去更改相对应的
'年龄'字段记录.
CREATE TRIGGER dell2 ON [dbo].[tablea1]
FOR DELETE
AS
declare
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
begin
set @xm=(select xm from deleted)
set @nl=(select nl from deleted)
set @xs=(select xs from deleted)
set @sj=(select sj from deleted)
begin tran
insert into table111(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
delete from table18 where xm=@xm
update table4 set 年龄=@nl where 姓名=@xm
if @@error<>0
rollback tran
commit tran
end
五.更新.
当向TABLEA1中更新数据时,会自动向TABLE111加入两条记录(TABLEA1原有 的老记录和更新后的新数据).
CREATE TRIGGER upda ON [dbo].[tablea1]
FOR UPDATE
AS
declare
@oldxm varchar(8),
@oldnl int,
@oldxs decimal(7,2),
@oldsj datetime,
@newxm varchar(8),
@newnl int,
@newxs decimal(7,2),
@newsj datetime
begin
set @oldxm=(select xm from deleted)
set @oldnl=(select nl from deleted)
set @oldxs=(select xs from deleted)
set @oldsj=(select sj from deleted)
set @newxm=(select xm from inserted)
set @newnl=(select nl from inserted)
set @newxs=(select xs from inserted)
set @newsj=(select sj from inserted)
begin tran
insert into table111(xm1,nl1,xs1,sj1) values (@oldxm,@oldnl,@oldxs,@oldsj)
insert into table111(xm1,nl1,xs1,sj1) values (@newxm,@newnl,@newxs,@newsj)
if @@error<>0
rollback tran
commit tran
end
以下的操作会造成报错:!!!!!!!!!
--delete from tablea1 where nl>20
--update tablea1 set xs=1000 where nl>20
服务器: 消息 512，级别 16，状态 1，过程 upda，行 14
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
语句已终止。
解决方法:用到游标.
以下为作业:
练习1利用存储过程达到触发器的效果)
向表TABLE50加入一条记录时,则会在表TABLE51自动生成一条相同的记录.
还会将表TABLE52中的'姓名'与刚才在TABLE50加进的记录的'XM'相同的记录删除.
CREATE PROCEDURE mygod
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
AS
begin
begin tran
insert into table50(xm,nl,xs,sj) values (@xm,@nl,@xs,@sj)
insert into table51(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
delete from table52 where 姓名=@xm
if @@error<>0
rollback tran
commit tran
end
GO
二.练习2:(利用存储过程达到触发器的效果)
向表TABLE50更新一条记录时,则会在表TABLE51删与之'姓名'相同的记录.
还会将表TABLE52中的'姓名'与刚才在TABLE50加进的记录的'XM'相同的记录做同样的更新.
CREATE PROCEDURE mygod1
@nl int,
@xm varchar(8)
--@xs decimal,
-- @sj datetime
AS
begin
begin tran
update table50 set nl=@nl where xm=@xm
delete from table51 where xm1=@xm
update table52 set 年龄=@nl where 姓名=@xm
if @@error<>0
rollback tran
commit tran
end
GO
练习3:通过存储过程来实现,向TABLE50加入记录时,会向TABEL51生成同样的一份记录,会根据条件向TABEL53
删除符合条件的记录.
CREATE PROCEDURE mygod3
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
AS
begin
begin tran
insert into table50(xm,nl,xs,sj) values (@xm,@nl,@xs,@sj)
insert into table51(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
delete from table53 where xm2=@xm
if @@error<>0
rollback tran
commit tran
end
GO
练习4:通过存储过程来实现,向TABLE50加入记录时,会向TABEL51生成同样的一份记录,会根据条件向TABEL53
删除符合条件的记录.还会去更新TABLE52中相对应的字段记录.
CREATE PROCEDURE mygod3
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
AS
begin
begin tran
insert into table50(xm,nl,xs,sj) values (@xm,@nl,@xs,@sj)
insert into table51(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
update table52 set 年龄=@nl,薪水=@xs,时间=@sj where 姓名=@xm
delete from table53 where xm2=@xm
if @@error<>0
rollback tran
commit tran
end
GO
练习5:通过存储过程来实现,向TABLE50更新记录时,也会根据条件向TABEL53删除符合条件的记录.
CREATE PROCEDURE mygod4
@xm varchar(8),
@nl int
AS
begin
begin tran
update table50 set nl=@nl where xm=@xm
delete from table53 where xm2=@xm
if @@error<>0
rollback tran
commit tran
end
GO
练习6:通过存储过程来实现,向TABLE50更新记录时,也会根据条件向TABEL53删除符合条件的记录.并能向TABLE51加入所有TABLE50中刚才更新的记录,还能向TABLE52根据符合'姓名'与TABLE50的'XM'相同的记录而去更新TABLE52中的'年龄'字段的记录.
CREATE PROCEDURE mygod4
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
AS
begin
begin tran
update table50 set nl=@nl,xs=@xs,sj=@sj where xm=@xm
(或:update table50 set nl=@nl where xm=@xm)
insert into table51(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
update table52 set 薪水=@xs where 姓名=@xm
delete from table53 where xm2=@xm
if @@error<>0
rollback tran
commit tran
end
GO
*注意事项:
在执行'存储过程'时一定要例:
mygod4 'peng',25,1800,'2001-1-1'
的方式去例出所有的字段的记录.
或在定义'过程'时将update table50 将所有字段都声明.
否则会报错如下:
"服务器: 消息 201，级别 16，状态 3，过程 mygod4，行 0
过程 'mygod4' 需要参数 '@xs'，但未提供该参数。"
====================================================================================================================================================================================================================
第十六课时 存储过程,触发器,游标三者之间的结合
一.建立DELL触发器(带游标的).作用:可以删除多条记录.
以下的效果:
向表TABLEA1中删除记录时,会自动在TABLE111中生成刚才被删 除的记录.
CREATE TRIGGER dell ON [dbo].[tablea1]
FOR DELETE
AS
declare
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetime
begin
declare ccc scroll cursor for select * from deleted
open ccc
fetch next from ccc into @xm,@nl,@xs,@sj
begin tran
while @@fetch_status=0
begin
insert into table111(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
fetch next from ccc into @xm,@nl,@xs,@sj
end
if @@error<>0
rollback tran
commit tran
close ccc
deallocate ccc
end
二.建立一个UPDA的触发器(带游标).*可以针单条,也可以针对多条记录.
以下的执行效果:
向TABLEA1更新数据时,会自动向TABLE111加入老数据和新数据全部添加进来.
CREATE TRIGGER upda ON [dbo].[tablea1]
FOR UPDATE
AS
declare
@oldxm varchar(8),
@oldnl int,
@oldxs decimal(7,2),
@oldsj datetime,
@newxm varchar(8),
@newnl int,
@newxs decimal(7,2),
@newsj datetime
begin
begin tran
declare cur scroll cursor for select * from deleted
open cur
fetch next from cur into @oldxm,@oldnl,@oldxs,@oldsj
while @@fetch_status=0
begin
insert into table111(xm1,nl1,xs1,sj1) values (@oldxm,@oldnl,@oldxs,@oldsj)
fetch next from cur into @oldxm,@oldnl,@oldxs,@oldsj
end
close cur
deallocate cur
declare cur scroll cursor for select * from inserted
open cur
fetch next from cur into @newxm,@newnl,@newxs,@newsj
while @@fetch_status=0
begin
insert into table111(xm1,nl1,xs1,sj1) values (@newxm,@newnl,@newxs,@newsj)
fetch next from cur into @newxm,@newnl,@newxs,@newsj
end
close cur
deallocate cur
if @@error<>0
rollback tran
commit tran
end
三.写一个带有参数的存储过程,(将游标写在存储过程中)
可以将一个表中一条记录或多条记录加入到一个表中
例(1):
CREATE PROCEDURE storedproccu
AS
declare
@xm varchar(8),
@nl int,
@xs decimal(7,2),
@sj datetimeA
begin
declare cur scroll cursor for select * from tablea1
open cur
fetch next from cur into @xm,@nl,@xs,@sj
while @@fetch_status=0
begin
insert into table111(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
fetch next from cur into @xm,@nl,@xs,@sj
end
close cur
deallocate cur
end
GO
--执行在'查询分析器' 中运行以下
storedproccur (不要带条件)
结果为:将TABLEA1中的全部记录加入到TABLE111中
例(2):创建带有条件和存储过程和游标
CREATE PROCEDURE storedproccur1
@nnll int
AS
declare
@xm varchar(8),
@nl int,
@xs decimal,
@sj datetime
begin
declare cur scroll cursor for select * from tablea1 where nl >@nnll
open cur
fetch next from cur into @xm,@nl,@xs,@sj
while @@fetch_status=0
begin
insert into table111(xm1,nl1,xs1,sj1) values (@xm,@nl,@xs,@sj)
fetch next from cur into @xm,@nl,@xs,@sj
end
close cur
deallocate cur
end
GO
以上在"查询分析器"中执行以下:
storedproccur1 10
执行结果为: 将TABLEA1中的"nl"字段大于10的记录加入到TABLE111中
==========================================================================================================================================================================================
第十七课时 自定义函数
一.函数的概述
函数有一个返回值,而'过程'没有返回值
例:cast (变量 as 数据类型)
以上cast为系统函数,不需要我们定义的函数.
用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行.
用户定义函数用 ALTER FUNCTION 修改，用 DROP FUNCTION 除去.
二.函数的建立.
1.求和函数(输入一个整数,返回一个整数,递增求和)
create function qh
(@n int)
returns int /*表示返回值的类型*/
as
begin
declare /*定义变量一定要放在BEGIN的后面*/
@sn int,
@i int
set @sn=0
set @i=1
while @i<=@n /*此处可加入( ),但也可不加入*/
begin
set @sn=@sn+@i
set @i=@i+1
end
return @sn /*返回值.是RETURN而不是RETURNS*/
end
在'查询分析器'中调用:
print dbo.qh(100)
执行过程:从1递增加到100,结果为:5050
2.没有任何附带条件的求和函数
create function addd
(@first1 decimal(7,2),@second1 decimal(7,2))
returns decimal(8,2)
as
begin
return @first1+@second1
end
在'查询分析器'中调用:
print dbo.addd(88888.22,11111.22)
执行结果:99999.44<为两数之和>
3.创建将两个字符串连在一起的函数
create function union1
(@firstname varchar(8),@secondname varchar(8))
returns varchar(20)
as
begin
return @firstname+' '+@secondname
end
在'查询分析器'中调用:
declare
@sss varchar(20)
begin
set @sss=dbo.union1('peng','song')
print @sss
end
与上面的结果等效:
print dbo.union1('peng','song')
以上程序的执行结果为:peng song
4.表示时间的求和函数(一个datetime与一个int相加返回datetime的类型)
create function shijian
(@date1 datetime,@aaa int)
returns datetime
as
begin
declare
@sss datetime
set @sss=@date1+@aaa
return @sss
end
在'查询分析器'中调用:
print dbo.shijian('2001-1-1',100)
运行结果:04 11 2001 12:00AM
5.两个时间差的函数(输入时间,返回整数)
create function shjiancha
(@date1 datetime,@date2 datetime)
returns int
as
begin
return(cast (@date2 as int) - cast (@date1 as int))
end
在'查询分析器'中调用:
print dbo.shijiancha ('2003-11-11','2006-11-11')
运行结果:1095
三.通过函数返回结果集(返回一个表中的所有数记录值)
格式:create function reset
() /*或中间带参数,但( )不可少*/
as
return (select * from 表名) /*注意不可使用begin...end*/
调用格式:select * from dbo.reset()
(1)带有条件的参数
create function reset1
(@bh varchar(2))
returns table
as
return (select * from teacher where tbh>tbh)
在'查询分析器'中调用:
select * from dbo.reset1('11')
运行结果:将表中tbh大于11的查询结果显示出来.
(2)不带参数特写字符串函数
create function sss
()
returns varchar(10)
as
begin
return 'hello'
end
在'查询分析器'中调用:
print dbo.sss()
显示结果为:hello
====================================================================================================================================================================================================================
第十八课时系统函数
一.日期函数
查看函数:'查询分析器'----'工具'-----'对象浏览器'----'显示和隐藏'-----先将'对象浏览器'调出.----然后在'左框'的'对象浏览器'----可以查看自己想要的函数.
(1)DATEADD:在向指定日期加上一段时间的基础上，返回新的 datetime 值
在'查询分析器'中调用:
select dateadd(hh,10,getdate())
运行结果为:(当前时间加10小时)2003-11-26 00:17:04.990
select dateadd(mm,10,getdate())
运行结果为:(当前时间加10月)2004-09-25 14:18:44.547
日期部分 缩写
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
(2)DATEDIFF返回跨两个指定日期的日期和时间边界数。
在'查询分析器'中调用:
select datediff(yy,'1999-1-1','2003-1-1')
运行结果为:4
select datediff(mm,'1999-1-1','2003-1-1')
运行结果为:48
select datename(yy,getdate())
运行结果为:2003
(3)DATENAME返回代表指定日期的指定日期部分的字符串。
在'查询分析器'中调用:
select datename(yy,getdate())
运行结果为:2003
(4)DATEPART返回代表指定日期的指定日期部分的整数。
在'查询分析器'中调用:
select datepart(yy,getdate())
运行结果为:2003
(5)YEAR返回表示指定日期中的年份的整数。
在'查询分析器'中调用:
select year(getdate())
运行结果为:2003
(6)MONTH返回代表指定日期月份的整数。
在'查询分析器'中调用:
select month(getdate())
运行结果为:11
(7)DAY返回代表指定日期的天的日期部分的整数。
在'查询分析器'中调用:
select day(getdate())
运行结果为:25
二.数学函数
(1)ABS返回给定数字表达式的绝对值。
在'查询分析器'中调用:
select abs(-122)
运行结果为:122
(2)CEILING返回大于或等于所给数字表达式的最小整数。
在'查询分析器'中调用:
select ceiling(5.4)
运行结果为:6
(3)FLOOR返回小于或等于所给数字表达式的最大整数。
在'查询分析器'中调用:
select floor(4.3)
运行结果为:4
(4)PI返回 PI 的常量值。
在'查询分析器'中调用:
select pi()
运行结果为:3.1415926535897931
(5)POWER返回给定表达式乘指定次方的值。
在'查询分析器'中调用:
select power(4,3)
运行结果为:64
(6)RAND返回 0 到1 之间的随机float 值。
格式:RAND ( [ seed ] )
seed:是给出种子值或起始值的整型表达式（tinyint、smallint 或 int）。
返回类型:float
注释
在单个查询中反复调用 RAND() 将产生相同的值。
在'查询分析器'中调用:
select rand()
运行结果为:3.6197752281442702E-2
0.21873208638419581...等等
select cast(rand()*100 as int)
运行结果为:2
6...等等
(7)SQUARE返回给定表达式的平方。
在'查询分析器'中调用:
select square(5)
运行结果为:25.0
(8)SQRY返回给定表达式的平方根。
在'查询分析器'中调用:
select sqrt(81)
运行结果为:9.0
三.字符串函数
(1)ASCII将 int ASCII 代码转换为字符的字符串函数。
语法:CHAR ( integer_expression )
参数integer_expression:介于 0 和 255 之间的整数。如果整数表达式不在此范围内，将返回 NULL 值。
在'查询分析器'中调用:
select ascii('a')
运行结果为:97
(2)CHARCHAR将 int ASCII 代码转换为字符的字符串函数。
语法:CHAR ( integer_expression )
参数integer_expression:介于 0 和 255 之间的整数。如果整数表达式不在此范围内，将返回 NULL 值。
在'查询分析器'中调用:
select char(97)
运行结果为:a
(3)LEN返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格.
在'查询分析器'中调用:
select len('abcdefghi')
运行结果为:9
declare
@aa varchar(10)
set @AA='abcdef'
select len(@aa)
运行结果为:6
(4)LEFT返回从字符串左边开始指定个数的字符。
在'查询分析器'中调用:
select left(txm,2) from teacher
运行结果为: tk
tk
xi
xi
fa
只返回两个左边的字符
(5)RIGHT返回字符串中从右边开始指定个数的字符。
在'查询分析器'中调用:
select right ('abcdefg',3)
运行结果为:efg
(6)SUBSTRING返回字符、binary、text 或 image 表达式的一部分。
在'查询分析器'中调用:
select substring ('abcdefghi',2,5)
运行结果为:bcdef
(7)PATINDEX返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。
在'查询分析器'中调用:
select patindex('%2%','sub2003_2910')
运行结果为:4
(8)LTRIM删除起始空格后返回字符表达式。
在'查询分析器'中调用:
select ltrim(' abc')
运行结果为:abc
(9)RTRIM截断所有尾随空格后返回一个字符串。
在'查询分析器'中调用:
select rtrim('abc ')
运行结果为:abc
(10)SPACE返回由重复的空格组成的字符串。
在'查询分析器'中调用:
select 'aa'+space(6)+'bb'
运行结果为:aa bb
(11)LOWER将大写字符数据转换为小写字符数据后返回字符表达式。
在'查询分析器'中调用:
select lower('ABCDE')
运行结果为:abcde
(12)UPPER返回将小写字符数据转换为大写的字符表达式。
在'查询分析器'中调用:
select upper('abcdef')
运行结果为:ABCDEF
(13)REPLACE用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。
在'查询分析器'中调用:
select replace ('abcdefg','bc','66')
运行结果为:a66defg
(14)REPLICATE以指定的次数重复字符表达式。
在'查询分析器'中调用:
select replicate('abc',3)
运行结果为:abcabcabc
四.系统函数
(1)CONVERT将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。
在'查询分析器'中调用:
select convert (int,'123')
运行结果为:123 (将字符型的'123'转换为int型的123)
(2)HOST_NAME返回工作站名称。
在'查询分析器'中调用:
select host_name()
运行结果为:TIAN
(3)USER_NAME返回给定标识号的用户数据库用户名。
在'查询分析器'中调用:
select user_name()
运行结果为:dbo
(4)CAST将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能
(5)CASE计算条件列表并返回多个可能结果表达式之一。
CASE 具有两种格式：
A.简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。
B.CASE 搜索函数计算一组布尔表达式以确定结果。
两种格式都支持可选的 ELSE 参数。
==========================================================================================================================================================================================
第十九课时 数据库的备份与恢复
第一方法:
1.备份数据库
数据库的移植(规则,视图,游标,存储过程.)
选定将要备份的数据------右键选'所有任务'-----'备份数据库'-----然后弹出一个'备份'对话----点'添加'生成一个文件名-----弹出'备份设备位置'对话框----在'文件名'写一个'固定的备份'文件名----点'确定'即可完成(在选'文件'时可以选择'合适的路径')
2.还原数据库
选定将要备份的数据------右键选'所有任务'-----'还原数据库'-----然后弹出一个'还原'对
话框-----在'还原为数据库'名自己输入一个名称------然后点'添加'-----在'选择目的'选择在'原来备份过的文件'----然后点'确定'-----在'还原数据库'对话框的'选项'选项上-------
选定'现有数据库上强制还原'-----点'还原'即可恢复
*移至物理文件名的路径一定要存在.
第二方法:(利用文件拷贝功能来恢复数据)
1.备份数据库
将原来在创建数据库时的备份文件(或源文件)及日志文件拷贝到一定的位置.
2.还原数据库
在'企业管理器'----'数据库'----新建一个数据库------(新建与刚才备份时一样的系统数据文件和日志文件名一致才行)-----然后将'原备份的文件'-----复制到'刚才新建的空数据中'-----覆盖粘贴即可恢复
第三方法:
1.备份数据库
(1) 使用sp_addumpdevice存储过程
例如，下面创建一个逻辑名为test_backup的备份设备：
USE test
EXEC sp_addumpdevice 'disk', 'test_backup', 'D:\test_backup.bak'
(2) 使用BACKUP语句
例如，使用下面的SQL语句可以完成上面相同的功能：
BACKUP DATABASE bookdb TO Book_Backup
2. 恢复用户数据库
(1) 使用RESTORE语句例如，下面的SQL语句用于恢复bookdb数据库：
RESTORE DATABASE bookdb FROM Book_Backup
数据库的安全性
一.标识符限制
(1)在'企业管理器'-----'安全性'-----'登录'-----击右键-----'新建用户'----然后输入任意名即可
用'新用户名'进入系统后只能看到'系统数据库',由于没具备相应的访问权限.
(2)设置数据库访问权限
双击刚才新建的'新用户'-----'数据库的访问'-----可在'指定此登录可以访问的数据库'-----然后可以选定'自己将要访问的数据名'----以后该'新用户'就具有'选定的数据库'
的访问权限.
(3)表,视图,存储过程的访问权限
[ 本帖最后由 电脑小虾 于 2007-2-7 15:35 编辑 ] |
|
