------------------------------------------------------------带有默认值的参数
create proc getScore
@score int =60 ------------默认值 as
select * from score where score>@score go
------------------------------------------------------------执行带默认值参数的存储过程
exec getScore @score =80 ---------自己传入默认值 exec getScore ---------------------参数会使用默认值
----------------------------------------------------------练习存储过程 create proc getStusex
@sex char(2) , ----参数一
@snumb int=1 ---------带默认值的参数一般放在参数的最后 as
select sname,sex,birthday,address from INFO where snumb=@snumb and sex=@sex go
---------------------------------------------执行存储过程
--------------------------------------------建议按照参数名及顺序传参 exec getStusex 1 exec getStusex 1,2
exec getStusex 2,1 --错误的,顺序不对 exec getStusex @sex=0
exec getStusex @snumb=2,@sex=0
exec getStusex , @sex=0 ----错误的,中间有个逗号
-------------------------------------------------------插入海量数据的存储过程
create table dump (
id int identity(1,1), num int ,
name varchar(50) )
----------------------------------------循环插入数据的过程 create proc AddData @num int as
declare @i int --------声明局部变量 set @i =1
while(@i<=@num) begin
insert into dump(num,name ) values(@i ,NEWID ()) set @i =@i+1 end go
exec AddData @num=100000 select * from dump
-----------------------------SQL Server数据库 -存储过程返回值 ----------------------1、output类型参数可以返回值 create proc GetAvg
@Savg decimal(18,2) output --@Savg numeric(6,2) output as
select @Savg= AVG (score) from score go
-------------执行 输出参数，必须声明变量接收传出的参数值 declare @avg decimal(18,2) exec GetAvg @Savg=@avg output
---exec GetAvg @avg output ---简写方式 print @avg
--------------练习：加法，两个加数是in 类型参数，求和，把和返回， --------------和是output类型参数，调用过程输出和 create proc GetSum @numb1 int , @numb2 int , @sum int output as
select @sum= @numb1+@numb2 go
-----------执行存储过程 declare @sums int
exec GetSum @numb1=1,@numb2=1, @sum=@sums output --参数=值 print '和是:'+cast( @sums as varchar)
----------------------2、return语句返回值（SQL Server特殊特点） ---案列：求平均分，，并返回 create proc GetAvger as
declare @avg numeric(6,2)
select @avg =AVG (score ) from score return @avg -------可以通过rertun返回值 go
drop proc GetAvger -----执行存储过程
declare @avge numeric(6,2) exec @avge=GetAvger print @avge
----------return另外的作用,结束程序 create proc text as
print 'aaaaa' return
print 'bbbbb' go
exec text
------------------------------------------------查看系统存储过程 exec sp_help score -------查看对象信息 exec sp_helptext -----------查看原代码 exec sp_rename -----------改名
exec sp_stored_procedures -------查看存储过程
------------查年龄在40以上,返回名字,lastname.firstname||lastname.firstname create proc GetEmployee @age int as
select FirstName+'.'+LastName as 姓名 from Employees where DATEDIFF(YEAR ,BirthDate,getdate()) >@age go
drop proc GetEmployee
exec GetEmployee @age=40
-------------编写过程,计算班级的考试通过率(考试及格人数/ 总人数) create proc GetTexts as
declare @num int declare @count int
declare @ClassText numeric(6,2)
select @num =COUNT(snumb) from score where score>60-------考试通过人数 select @count=COUNT (snumb) from info ------------------总人数 set @ClassText =@num / @count
declare @p int --------------------声明该变量时为了解决00.00%的小数点后位数的问题
set @p =@ClassText*100
print '通过率为：'+cast(@p as varchar)+'%' go
----------执行存储过程 exec GetTexts
--------------------------------触发器的语法 create trigger trigger_name on table_name [with encryption]
for [delete,insert,update] as
T_SQL语句 go
create database bank
create table banks (
customername varchar(20),----账户名 cardid varchar(10),----账户
currentmoney money check(currentmoney>=1) )
create table transinfo (
transdate datetime,----交易时间 cardid varchar(10), ----交易账号 transtype varchar(10),----交易类型 transmonry money )
drop table transinfo
----账户表插入数据
insert into banks values('张三','1001',2000) insert into banks values('李二','1002',200) go
---------------------------------------------------------------------------------------insert触发器
-------------------------------------------------------------------------------------------触发器案例
---当向交易信息表（transinfo）中插入一条交易信息时，自动更新对应账户的余额 ---分析：插入触发器，transinfo表，触发器出发后要update账户表的余额 create trigger trigger_transinfo on transinfo ----触发器创建的表
for insert ---------插入触发器类型 as
------------------------------触发器的内容 ----------------------------1、提取有用数据 declare @cardid varchar(10) ----卡号
declare @transtype varchar(10) ---交易类型 declare @transmoney money ----交易金额
-----------------------------变量赋值---inserted表提供数据
select @cardid =cardid,@transtype=transtype,@transmoney=transmonry from inserted
----------------------------------2、修改余额 ----------------------------------判断交易类型 if(@transtype='存入')
update banks set currentmoney=currentmoney+@transmoney where cardid=@cardid
else if(@transtype='支取')
update banks set currentmoney=currentmoney-@transmoney where cardid=@cardid
print '交易后，账户余额为：'
select * from banks where cardid=@cardid go
------------------------------------测试出发器的功能
insert into transinfo values(GETDATE (),'1001','存入',10000) insert into transinfo values(GETDATE (),'1002','支取',100) insert into transinfo values(GETDATE (),'1002','存入',1000)
select * from transinfo select * from banks
------------------------------------------------------------------------------------------delete触发器
------------------------当删除交易信息表时,要求自动备份被删除的数据到表backup table
--------------------分析:交易信息表,出发类型为delete,触发的功能:把删除的数据添加到backup table中(已存在)
--------------------------表要先创建后添加数据