首页 > 代码库 > 2016年11月20日--存储过程、视图
2016年11月20日--存储过程、视图
存储过程：
存储过程（stored procedure）有时也称为sproc。存储过程存储于数据库中而不是在单独的文件中，有输入参数、输出参数以及返回值等。
在数据库中，创建存储过程和创建其他对象的过程一样，除了它使用的AS关键字外。
create proc 存储过程名 --创建存储过程 或 alter proc 存储过程名 -- 修改存储过程
参数1 参数类型，参数2 参数类型……参数n 参数类型
as
begin--- ={
查、插、删、改
end--- =}
1 --存储过程 2 create proc firstproc--创建一个存储过程 3 as --存储过程关键字 4 select * from student--存储过程的语句 5 go 6 --执行存储过程的语句（两个都可以） 7 exec firstproc 8 execute firstproc 9 10 --存储过程可以有返回值 11 --定义一个变量去接收 12 declare @fanhui int 13 exec @fanhui = firstproc--需要执行之后才会有返回值，用变量接收 14 select @fanhui as 返回值 --查看返回值 15 16 --修改存储过程的语句 17 alter proc firstproc 18 as 19 select * from score 20 go 21 22 --利用存储过程查找三个表内的信息 23 create proc secondproc 24 as 25 select * from student 26 select * from teacher 27 select * from score 28 go 29 30 --执行 31 exec secondproc 32 33 34 35 --利用存储过程查找语文教师张晓华所教课程的学生的分数, 36 --过80的算优秀,优秀人数超过3个人即为【教师评测达标】 37 --若不到三个人,【不达标】 38 create proc thirdproc 39 as 40 declare @code int 41 select @code=code from teacher where name =‘张晓华‘ 42 declare @count int 43 select @count= COUNT(*) from score where code 44 in(select code from student where yujiao = @code) 45 and yufen>80 46 if @count >3 47 print ‘教师评测达标‘ 48 else 49 print ‘教师评测不达标‘ 50 go 51 52 exec thirdproc 53 54 55 --带一个参数的存储过程 56 create proc fourthproc 57 @one char(10) --as前面写上参数的名称，类型 58 as 59 print @one--存储过程中就可以使用上面的变量 60 go 61 --执行 62 exec fourthproc ‘你好啊‘ --执行时需要将此参数传给存储过程 63 64 --带两个参数的存储过程 65 create proc fifthproc 66 @two varchar(50), --两个参数或者多个参数时中间用，隔开 67 @three varchar(50) 68 as 69 print @two + @three 70 go 71 --执行 72 exec fifthproc ‘你好啊！‘,‘你在干嘛？‘--两个参数用逗号隔开 73 74 75 --查询学号为我们输入的号码的学生的数学成绩 76 create proc sixproc 77 @one int 78 as 79 select shufen from score where code=@one 80 go 81 --执行 82 exec sixproc 8 83 84 85 --练习:存储过程 86 --查看所输入编号的学生是否能够结业，两门以上及格即可结业 87 --三门都及格，【优秀】 88 --两门及格，【结业】 89 --一门及格，【不结业】 90 --三门都不及格，【请重修】 91 create proc eighthproc 92 @code int 93 as 94 declare @yu decimal(18,2),@shu decimal(18,2),@ying decimal(18,2) 95 select @yu=yufen from score where code=@code --分别查询语数英的分数 96 select @shu=shufen from score where code=@code 97 select @ying=yingfen from score where code=@code 98 declare @count int--定义标记变量 99 set @count=0 --标记变量在下面需要先使用再赋值,所以先给他为0100 if @yu>=60 --判断语数英是否及格101 set @count=@count+1--及格的时候count+1102 if @shu>=60103 set @count=@count+1104 if @ying>=60105 set @count=@count+1 106 107 if @count=3--判断count的值:判断几门课及格108 print ‘优秀‘109 else if @count=2110 print ‘结业‘111 else if @count=1112 print ‘不结业‘113 else 114 print ‘请重修‘ 115 go116 exec eighthproc 6117 118 119 120 --不带参数带返回值的存储过程121 create proc elevenproc122 as123 return 5124 go125 --执行126 --需要一个变量来接收这个返回值127 declare @fan int128 exec @fan= elevenproc129 print @fan130 131 132 --带参数,带返回值的存储过程133 create proc twelveproc134 @one int,135 @two int136 as137 declare @sum int138 set @sum = @one +@two139 return @sum140 go141 --执行142 declare @fanhuizonghe int143 exec @fanhuizonghe = twelveproc 2,4144 print @fanhuizonghe145 146 147 148 --输入一个学生的学号,想要经过存储过程之后得到在这个学生的总分149 create proc oneproc150 @code int 151 as152 declare @yu int153 select @yu=yufen from score where code=@code154 declare @shu int155 select @shu=shufen from score where code=@code156 declare @ying int157 select @ying=yingfen from score where code=@code158 declare @sum int159 select @sum=@yu+@shu+@ying160 return @sum161 go162 declare @fan int163 exec @fan = oneproc 5164 print @fan165 166 167 168 --在创建存储过程时，我们可以设置它有一个默认值。169 create proc twoproc170 @sum int =10 --设置默认值171 as172 set @sum =@sum +10173 return @sum174 go175 --执行176 declare @sumfan int177 exec @sumfan= twoproc --可以不写默认值的参数,或者写上default 缺省,默认178 print @sumfan179 180 181 --存储过程练习:输入一个数,求1~n的和182 create proc threeproc183 @shu int184 as185 declare @i int,@sum int186 set @i=0187 set @sum=0188 while @i<=@shu189 begin190 set @sum=@sum+@i191 set @i=@i+1192 end193 return @sum194 go195 declare @he int196 exec @he=threeproc 10197 print @he198 199 200 --存储过程练习:输入一个数求这个1!+2!+...+n!的阶乘201 create proc cproc202 @n int203 as204 declare @i int,@jie int,@sum int205 set @i=1206 set @jie=1207 set @sum=0208 while @i<=@n209 begin210 set @jie*=@i211 set @sum+=@jie 212 set @i=@i+1213 end 214 return @sum 215 go216 217 declare @jiefan int218 exec @jiefan=cproc 3219 print @jiefan
视图：
1.视图的概述 视图其实就是一条查询sql语句，用于显示一个或多个表或其他视图中的相关数据。视图将一个查询的结果作为一个表来使用，因此视图可以被看作是存储的查询或一个虚拟表。视图来源于表，所有对视图数据的修改最终都会被反映到视图的基表中，这些修改必须服从基表的完整性约束，并同样会触发定义在基表上的触发器。(Oracle支持在视图上显式的定义触发器和定义一些逻辑约束)
2.视图的存储 与表不同，视图不会要求分配存储空间，视图中也不会包含实际的数据。视图只是定义了一个查询，视图中的数据是从基表中获取，这些数据在视图被引用时动态的生成。由于视图基于数据库中的其他对象，因此一个视图只需要占用数据字典中保存其定义的空间，而无需额外的存储空间。
3.视图的作用
用户可以通过视图以不同形式来显示基表中的数据，视图的强大之处在于它能够根据不同用户的需要来对基表中的数据进行整理。视图常见的用途如下：
（1）通过视图可以设定允许用户访问的列和数据行，从而为表提供了额外的安全控制
（2）隐藏数据复杂性：视图中可以使用连接(join)，用多个表中相关的列构成一个新的数据集。此视图就对用户隐藏了数据来源于多个表的事实。
（3）简化用户的SQL 语句：用户使用视图就可从多个表中查询信息，而无需了解这些表是如何连接的。
（4）以不同的角度来显示基表中的数据：:视图的列名可以被任意改变，而不会影响此视图的基表
（5）使应用程序不会受基表定义改变的影响：:在一个视图的定义中查询了一个包含4 个数据列的基表中的3 列。当基表中添加了新的列后，由于视图的定义并没有被影响，因此使用此视图的应用程序也不会被影响。
（6）保存复杂查询：:一个查询可能会对表数据进行复杂的计算。用户将这个查询保存为视图之后，每次进行类似计算只需查询此视图即可。
（7）逻辑数据独立性：:视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。
示例1：将下列两个语句写成一个语句
示例2：利用语句来建立视图，将示例1中的语句写进视图中
示例3：利用鼠标来建立视图的过程
在视图上右击选择新建视图，
在弹出的页面选择要建立关系的表的名称，
选择需要的列的名称，并保存取名。
保存完毕后就可以利用语句直接调用视图。视图就是一个虚拟的新建表。
2016年11月20日--存储过程、视图