转载

Sql Server存储过程和函数浅谈

今天给大家总结一下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 Server存储过程和函数浅谈

存储过程

初印象：存储过程是由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 Server存储过程和函数浅谈

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 ('数据库设计 ')

正文到此结束

所属分类：编程技术

本文标签： 代码工资 db 数据 SQL Server tab UI 总结编译 sql 参数 update 安全 value 游标 cat key 文章学生 Select http 数据库时间 src
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

Sql Server存储过程和函数浅谈

使用表结构

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流