oz

越努力,越幸运!!

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

今天给大家总结一下sql server中的存储过程和函数。本人是小白,里面内容比较初级,大神不喜勿喷 自行飘过就是。。

首先给大家简单列出sql server中的流控制语句,后面会用到的^_^

sql server常用控制语句

1.begin..end语句:该语句用来定义一串由顺序执行的SQL语句构成的块。                            

begin
      statement block
   end

 2.if....else语句:该语句用来定义有条件执行的某些语句。

                             
 if boolen_expression
     statement
 [else [if boolean_expression] statement]

 

示例:查询学号为9704学生的成绩状况                             
if ( select min (mark) from student where sno= '9704') > 90
             print ' 学生成绩全部优秀 '
else
if ( select min (mark) from student where sno= '9704') >60
                   print ' 学生成绩全部及格 '
else
            print ' 学生成绩全部及格 '
View Code

 3.while、break和continue语句:写过程序的同学相比对这个并不陌生,直接上代码

示例:学号为9705学生的平均成绩如果小于75,则将该学生的每门成绩以5%的比例提高,当平均成绩大于等于75或者所有课程都及格时,终止操作。
 
while(select avg( mark) from student) < 75
  begin 
             update student
        set mark= mark*1.05
        if(select min( mark) from student) >=60
           break
  end
View Code

 4.declare语句:用来定义一个局部变量,可用select语句为该变量赋初值。这个变量必须用@开头,跟着一个标识符

 declare @variable_name datatype
                [,@variable_name datatype]....

 示例:查询学号为9704学生的成绩状况

declare @verygood int
         select @verygood = min( mark)  /*使用select 赋值*/
         from student
         where sno= '9704'
         if @verygood > 60
         print ' 成绩合格'
View Code

5.return语句:用来无条件地退出一个查询或一个过程。

return [int_expression]

6.waitfor语句:用来定义某天中的一个时刻,执行一个语句块。 语法如下

waitfor {delay 'time'| time 'time'}   注:delay后的time表示等待多长时间执行sql语句;time后的time说明在time时间点执行sql语句
示例:在下午15:41执行存储过程update_all_stats 
begin
  waitfor time '15:41'
   execute update_all_stats
end
View Code

使用表结构

文章中用到了三张表,分别为student(学生信息表)、teacher(教师信息表)、course(课程信息表) ,表结构请看下方
 
存储过程
      初印象:存储过程是由SQL语句和控制语句构成的语句串(语句集合)。它不仅可以带有输入参数还可以带有输出参数,存储过程能够通过接收参数,向调用者返回结果集,结果集的格式由调用者确定。返回状态值给调用者,指明调用成功还是失败,包括针对数据库的操作语句,并且可以在一个存储过程中调用另一存储过程。 另外存储过程好处不必多说,比如支持sql重用、执行速度快、减少网络流量、安全可靠。
   在sql server中存储过程分为两类:系统提供存储过程和用户自定义存储过程。 系统存储过程主要存储在master数据库中,并以sp_为前缀,主要是从系统表中获取信息;用户存储过程由用户创建并完成特定功能,这里我主要写的是用户存储过程
 

1. 创建存储过程

sql server中使用create procedure语句创建、编译存储过程。
语法如下:
create procedure procedure_name [;version number]
[{ @parameter data_type}
       [varying] [=default value][output]
] [,...n]
[with
        {recompile|encryption|recompile,encryption}]
  [for replication]
 as sql_statement [...n]

解释:

  • procedure_name是存储过程的名称,最多可有128个字符;
  • version number为版本号
  • @parameter是存储过程参数名。可以声明一个或多个参数,当调用该存储过程时,除非定义了参数缺省值,用户必须给出所有的参数值。一个存储过程至多有1024个参数,参数可以用作存储过程中的变量名,但参数不能用作列名、表名或其他数据库对象的名称
  • data_type是参数的数据类型。在存储过程中所有的数据类型都可被用作参数,但是如果参数为游标(cursor)数据类型,则该参数必须被指定为varing和output。
  • varying指定由output参数支持的结果集,仅应用于游标参数。
  • default value是指参数的缺省值。
  • output表明该参数是一个返回参数。用output参数可以向调用者返回信息,该参数在存储过程中可以变化,但是Text类型参数不能用作output参数。
  • recompile指明sql server并不保存该存储过程的执行计划,该存储过程每执行一次都要重新编译。默认每次调用时都使用同一计划
  • encryption为syscomments表中存储过程条目加密,防止用户查看编译后的语句。
  • for replication选项指明了该存储过程只能在复制过程中执行
  • sql_statement是包含在存储过程中的任何数量和类型的sql语句
 示例:创建存储过程usp_select_teacher,查询特定系的教师的信息,判定教师的年龄结构,并将该系教师的平均年龄和最大年龄传递给用户。          
create procedure usp_select_teacher @depart char( 10),@avg_age int output,@max_age int output
as
select * from teacher
where dname= @depart
select @max_age= max(age )
from teacher
where dname= @depart
select @avg_age= avg(age )
from teacher
where dname= @depart
if @avg_age <= 30
select ' 年龄结构'= '年龄结构偏年轻', '平均年龄' =@avg_age
if @avg_age > 30 and @avg_age <=40
select ' 年龄结构'= '年龄结构合理', '平均年龄' =@avg_age
if @avg_age > 40
select ' 年龄结构'= '年龄结构偏大', '平均年龄' =@avg_age
View Code

2.调用存储过程

sql server使用execute命令调用存储过程
示例:调用存储过程usp_select_teacher,并查看其输出参数的值。
declare @avgage int ,@maxage int
execute usp_select_teacher ' 计算机', @avgage output, @maxage output

我的运行结果如下:

 
SQL函数
初印象:当要执行一段语句,产生单一值或表时,可使用自定义函数而不是存储过程。与存储过程不同,自定义函数只能返回单一值或表。此外,函数不能执行insert、update和delete操作。在SQL server中,根据函数返回值形式的不同,将用户自定义函数分为3种类型。
  • 标量型函数(Scalar Function):返回一个确定类型的标量值。
  • 内联表值型函数(Inline Table-valued Function):以表的形式返回一个返回值,即返回的是一个表,相当于一个参数化的视图
  • 多声明表值型函数(Multi-statement Table-Valued Function):可以看做标量型和内联表值型函数的结合体。返回值是一个表,但它和标量型函数一样,有一个begin-end语句括起来的函数体,该函数体包含插入语句,向返回表中插入数据。
 
下面给出各种类型的示例代码

1.标量型函数

示例:创建工龄工资计算函数year_wage,共有3个输入参数:工作时间(work_date),当前时间(now_date),工龄工资标准(per_wage).
create function year_wage(@work_date datetime, @now_date datetime,@per_wage decimal)
returns decimal
as
begin
return (( year(@now_date )-year( @work_date)) * @per_wage)
end
View Code

调用函数语句:

select dbo. year_wage ('2014-10-01' ,getdate(), 12) as ' 工龄工资'

2.内联表值型函数

示例:创建函数course_student,能够显示student表中选修了某门课程(课程名称)的所有学生的信息。 代码如下:
create function course_student(@course_name varchar(20 ))
returns table
as
return ( select s.*
from student as s,course as c
where s. cno=c .cno and c.cno in(select cno from course
where cname= @course_name))
View Code

调用函数语句:

select * from course_student ('数据库设计 ')
3.多声明表值型函数
示例:创建函数teacherInfo,用于查询指定系的教师的姓名、性别、所在系、所授课程的课程号、课程名以及学时信息。
create function [dbo].[TeacherInfo] (@depart varchar (20))
returns @TeacherInfo table
(T_name varchar (8),
T_sex char( 2),
T_dname varchar( 20),
T_cno int primary key ,
T_cname varchar( 20),
t_ctime int)
as
begin
insert @TeacherInfo
select T. TNAME,T .TSEX, T.DNAME ,T. CNO,C .CNAME, C.CTIME
FROM TEACHER AS T,COURSE AS C
WHERE C. CNO=T .CNO
AND T. CNO IN (SELECT CNO FROM TEACHER WHERE DNAME=@depart )
return
end
View Code

调用函数语句:

select * from teacherinfo ('数据库设计 ')

 

希望大家读后不要有不适、呕吐之感== 以上内容都是存储过程、函数基础知识,游标之类的没有出现 本着步步为营原则,先把基础的搞懂再说。我在这里主要起抛砖引玉的作用,希望各位同学能有一点收获就好

 
posted on 2015-11-08 19:53  oz  阅读(9147)  评论(0编辑  收藏  举报