今天学习了SQL高级语言
触发器、存储过程、视图等
--存贮过程格式
--create procedure name .... --as --begin
--end
---execute name 参数1 参数2
----无参数的存贮过程执行
create procedure proc2
as
begin
select * from 职工 where 工资>2000
end
execute proc2
--有参数的存贮过程
create procedure proc4 @x1 int,@x2 int,@x3 int
as
begin
declare @max int if @x1>@x2
set @max=@x1
else
set @max=@x2
if @x3>@max
set @max=@x3
print '3个数字中的最大值是'+cast(@max as varchar(50)) end
execute proc4 15,18,39
视图
--视图create view v1asselect 仓库号,城市,面积 from 仓库create view v2asselect 姓名,工资 from 职工 where 工资>1800create view v3asselect 仓库.仓库号,城市,面积,创建时间,姓名,性别,工资 from 仓库,职工 where 仓库.仓库号=职工.仓库号alter view v2asselect 仓库.仓库号,城市,面积 from 仓库drop view v3create view testasselect * from 仓库select * from testupdate test set 面积=面积+88 where 仓库号='wh1'delete test where 仓库号='wh1'
触发器
--触发器是一种特殊的存贮过程，他就相当于c#中的事件触发器主要是通过事件触发而被执行的--create trigger 触发器名称 on 表 for insert[update,delete] as-- begin--程序块--endcreate trigger rockyR on 仓库 for updateasbegin insert into 仓库(仓库号,城市,面积,创建时间) values('wh01','郑州',1800,'2014-12-12'),('wh02','北京',1700,'2014-12-13'),('wh03','上海',1600,'2014-12-15')endupdate 仓库 set 面积=面积-10 where 仓库号='wh2'
create trigger student_trigger on class after update as declare @count_student int select @count_student=@@rowcount print '一共修改了'+char(48+@count_student)+'行' return go use db_buiness go update class set tClassName='14网普' where tClassId=10 go exec sp_help student_trigger exec sp_helptext student_trigger
循环语句
declare @cj float,@str varchar(60)set @cj=90set @str=case when @cj>100 or @cj<0 then '您输入的成绩不对，成绩应该在0-100之间' when @cj>=60 and @cj<70 then '及格' when @cj>=70 and @cj<80 then '中等' when @cj>=80 and @cj<90 then '良好' when @cj>=90 and @cj<=100 then '优秀' else '不及格' end print '该学生的成绩评语是'+@str --case [表达式] -- when 条件表达式1 then 结果1 -- when 条件表达式2 then 结果2 -- ........ -- else -- 结果表达式n -- end
--while 条件表达式 -- begin --命令行或程序 -- end declare @x int,@sum int select @x=0,@sum=0 while @x<=100 begin set @sum=@sum+@x set @x=@x+1 end print '1-100之间的和'+cast(@sum as varchar(50))
continue
--continue declare @x int,@sum int select @x=0,@sum=0 while @x<100 begin set @x=@x+1 if @x%2=1 continue set @sum=@sum+@x end print '偶数和'+cast(@sum as varchar(50))
break
--break declare @x int,@sum int select @x=0,@sum=0 while @x<=10 begin set @x=@x+1 set @sum=@sum+@x if @sum>30 break end print '结果'+cast(@sum as varchar(50))
