3. SQL -- 存储过程

存储过程

在Sql Server中,可以定义子程序存放在数据库中,这样的子程序称为存储过程,它是数据库对象之一.

一存储过程的优点:

1: 存储过程在服务器端运行,执行速度快

2: 存储过程只执行一次,然后把编译的二进制代码保存在调整缓存中,以后可从中调用,提高系统的性能.

3: 确保数据库的安全.使用存储过程可以完成所有的数据库操作,并可通过编程方式来控制

4: 自动完成所需要的预先势利的任务.方便客户

二存储过程的类型(五类)

(1)     系统存储过程. 由系统提供的存储过程,可以作为命令执行各种操作.定义在系统数据库master中,前缀是sp_,例如常用的显示系统对象信息的sp_help存储过程

(2)     本地存储过程. 指用户数据库中创建的存储过程,这种存储过程完成特定的数据库任务

(3)     临时存储过程. 它属于本地存储过程,如果存储过程前面有一个’#’代表局部临时存储过程,如果有’##’代表全局临时存储过程,可以在所有的用户会话中使用.

(4)     远程存储过程. 指从远程服务上调用的存储过程

(5)     扩展存储过程. 在SQL Server环境之外执行的动态链接库称为扩展存储过程,前缀_sp,使用时要先加载到SQL Server系统中

三创建用户存储过程

    用户存储过程只能定义在当前数据库中,可以使用SQL语句,也可使用企业管理器,这里只用SQL语句,注意存储过程中不能定义如下的对象:

         Create view               (视图)

         Create default          (缺省)

         Create rule                (规则)

         Create procedure    (存储过程)

         Create trigger          (触发器)

1: 通过Sql命令来创建和执行存储过程(假设使用学生表)

    定义如下存储过程:

           Use student

           Go

           Create procedure student_grade

           As

           Select stu.stuID,stu.name,course.name,course.grade

                    From student_table as stu,course_table as course

                    Where stu.stuID=course.stuID and stu.courseID=course.courseID

使用存储过程:

Exce student_grade

Go

2: 创建存储过程语法格式

Create proc[edure] procedure_name [;number] –定义过程名

[{@parameter data_type}]                                   --定义参数的类型

[varying][ =default][output]                                    --定义参数的属性

[,…n1]

[with {recompile|encryption|recompile,encryption}] –定义存储过程的处理方式

[for replication]

As sql_statement[…n2]                                                 --执行的操作(所有的sql语句都可以)

说明:

(1)     参数number为可选的整数,用于区分同名的存储过程,以便用一条drop procedure语句删除一组存储过程

(2)     @parameter 为存储过程的形参,@符号作为第一个字符来指定参数名称.data_type为参数的数据类型(如int或varchar(32)等)

(3)     Default指定存储过程输入参数的默认值,必须是常量或NULL,默认值中可以有通配符(%,_,[]和[^])

(4)     Recompile表明每次运行该过程时,要重新编译;

Encryption表示SQL server加密syscomments表中包含create procedure语句文本的条目,就是加密了,别人复制数据库而不能复制它,以防以存储过程来读取数据库中定义

(5)     参数n2说明一个存储过程可以包含多条T-SQL语句

3: 存储过程要注意的几点:

(1)     用户定义的存储过程只能在当前数据库中使用(临时过程除外)

(2)     成功执行create procedure语句后,过程名存储在sysobjects系统表中,而create procedure语句的文本存储在syscomments中

(3)     自动执行存储过程.SQL Server启动时可自动执行一个或多个存储过程,这些存储过程必须定义在master数据库中,并在sysadmin固定服务器角色作为后台过程执行,并且不能有任何的参数

(4)     Sql_statement语句限制必须使用对象所有者名(就是这个数据库的所有者)对数据库对象进行限定的语句有:

Create table, alter table, drop table, truncate table, create index, drop index, update statistics及DBCC语句

(5)     权限. Create procedure的权限默认授予sysadmin固定服务器角色成员,db_ower和db_ddladmin默写数据库角色成员.可以把权限转让.

4: 存储过程执行语法

[exce[ute]]

{[@return_staus = ]

{procedure_name[;number]|@procedure_name_var}

[[@parameter = ]{value|@variable[ouput]|[default]}

[,..n]

[with recompile]}

说明:

         @return_status为可选的×××变量,保存存储过程的返回状态,execute语句使用该变量前,必须对其定义.

         Procedure_name和number用于调用定义一组存储过程中一某一个,procedure_name代表了存储过程的组名,number用来指定哪一个.

         Procedure_name_var为create procedure中定义的存储过程名

         @parameter为create procedure中定义的参数名,value为存储过程的实参;

         @variable为output参数返回的值

         Default表示不提供实参,而是使用对应的默认值

         n 表示可以实参可以有多个

(1)     设计简单的存储过程

从student数据库的三个表中查询,返回学生学号,姓名,课程名,成绩,学分

Use student

--检查是否已存在同名的存储过程,或有,删除

If object_id('student_info') is not null

           Drop procedure student_info

Go

Create procedure student_info

As

Select a.studentID,a.sname,c.cname,b.grade

           From student_table as a inner join student_course_table as b

                    On a.studentID = b.studentID inner join course_table as c

                    On b.courseID = c.courseID

执行:

exec student_info

或execute student_info

(2)     使用带参数的存储过程

从student数据库的三个表中查询某个人指定的成绩和学分

Use student

If exists(select name from sysobjects where name='student_info1' and type='p')

           Drop procedure student_info1

Go

Create procedure student_info1

           @sname char(8),@cname char(16)

As

Select a.studentID,a.sname,c.cname,b.grade

           From student_table as a inner join student_course_table as b

                    On a.studentID = b.studentID inner join course_table as c

                    On b.courseID = c.courseID

           where a.sname=@sname and c.cname=@cname

执行:(多种方式

 execute student_info1 ‘王五’,’C语言’

或

 Exec student_info1 @name=’王五’,@cname=’C语言’

(3)     使用带有通配符参数的存储过程

从三个表的连接中返回指定学生学号,姓名,所选课程名称及成绩,该存储过程使用了模式匹配,如果没有提供参数,则使用预设的默认值

Use student

If object_id('stu_info') is not null

           Drop procedure stu_info

Go

Create procedure stu_info

           @name varchar(30)='王%'

As

Select a.studentID,a.sname,c.cname,b.grade

           From student_table a inner join student_course_table b

                    on a.studentID = b.studentID inner join course_table c

                    on b.courseID =c.courseID

           where a.sname like @name

go     

        执行该存储过程

使用默认参数;execute stu_info

使用实参; exec stu_info @name=’王%’ 或exec stu_info ‘王%’

(4)     使用带output参数的存储过程

用于计算指定学生的总学分,存储过程中使用了一个输入参数和一个输出参数

Use student

Go

If exists(select name from sysobjects where name='totalcredit' and type='p')

           Drop procedure totalcredit

Go

Create procedure totalcredit @name varchar(30),@total int OUTPUT

As

Select @total=sum(grade)

           From student_table a,student_course_table b,course_table

           Where a.sname=@name and a.studentID=b.studentID

           Group by a.studentID

Go

注意:output变量必须在定义存储过程和使用该变量时都定义

执行:

Declare @t_credit char(20),@total int --@total将作为OUTPUT变量必须先定义

Exec totalcredit ‘王五’,@total OUTPUT ---OUTPUT必须为大写

Select ‘王五’,@total

go