3. SQL -- 存储过程

angela

　　存储过程
　　在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,>　　(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