• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

SQLSEVER中的存储过程

互联网 diligentman 2天前 3次浏览

存储过程

  • 系统存储过程
  • 拓展存储过程
  • 用户自定义存储过程
    • 无参存储过程
      • 无参存储过程定义
    • 参数存储过程
      • 创建带参数的存储过程语法
      • 执行存储过程
      • 输出参数
  • 自定义错误信息
  • 预先储存好的sql程序(数据库操作语句,变量,逻辑控制语句等)
  • 保存在SQL Server中
  • 通过名称和参数执行,可返回结果。

存储过程的优点:

  • 执行速度快
  • 允许模块化程序设计
  • 提高系统安全性
  • 减少网络流通量

系统存储过程

  • 系统存储过程的名称一般以“sp_”开头
  • 由SQLServer创建、管理和使用
  • 存放在Resource数据库

sp_databases 列举服务器上所有数据库
sp_tables 返回当前环境下可查询的对象列表
sp_helptext 把另外一个未加密的存储过程打开查看
例子如下

execute sp_databases
exec sp_helptext sp_databases

拓展存储过程

  • 拓展存储过程的名称通常以“xp_”开头
  • 使用编辑语言(如C#)创建的外部存储过程
  • 以DLL形式单独存在

xp_cmdshell

  • 可以执行DOS命令下的一些操作
  • 可以以为本行的方式返回任何输出

写法如下:

EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

例子如下:

USE master
GO
/*---若xp_cmdShell作为服务器安全配置的一部分而被关闭,请使用如下的语句启用---*/
Exec sp_configure 'show advanced options ' , 1 --显示高级配置信息
GO
RECONFIGURE --重新配置
GO
exec sp_configure 'xp_cmdShell',1--打开xp_configure选项
GO
REconfigure --重新配置
/*---创建数据库bankDB,要求保存在D:bank---*/
Exec xp_cmdshell 'mkdir D:bank', NO_OUTPUT --创建文件夹D:bank
--创建数据库bankDB

用户自定义存储过程

无参存储过程

无参存储过程定义

CREATE PROC[ EDURE ] 存储过程名
AS
SQL 语句
GO

使用存储过程的语法:
EXECUTE 过程名
EXEC 过程名

如果执行存储过程的语句是批处理语句中的第一个语句,则可以不指定
EXECUTE关键字。

例子如下:

 use MySchool
go
    create proc stuResult
    as
    
    ----查找本次Java OOP 考试的平均分以及没有通过的人的信息


    ---定义变量保存最近一次考试时间

declare @maxDate datetime 




select @maxDate = MAX(ExamDate) from Result
  where SubjectId =(select SubjectId from Subject 
                    where SubjectName = 'Java OOP')
 
 
---计算平均分
select AVG(StudentResult) from Result
	where ExamDate = @maxDate
	and SubjectId = (select SubjectId from Subject 
                    where SubjectName = 'Java OOP') 
--查询未通过考试学生名单
select * from Student
		where StudentNo =(
	select StudentNo from Result
	where SubjectId =  (select SubjectId from Subject 
                    where SubjectName = 'Java OOP') 
    and ExamDate = @maxDate  
    and StudentResult < 80
    ) 
    go                                 

调用无参存储过程

Exec stuResult
    

带参数存储过程

创建带参数的存储过程语法

CREAT PROC[EDURE] 存储过程名
@参数1,@参数2,…,@参数n
AS
SQL语句
GO

执行存储过程语法

EXECUTE 过程名 参数
EXEC 过程名 参数

存储过程参数:

  • 输入参数:向存储过程传入值
  • 输出参数:存储过程输出值,使用output关键字

执行存储过程

EXEC 过程名 ’ 值 ',值

EXEC 存储过程名 参数1 = ’ ’ , 参数2 = ’ ’

两种方式,一种是使用键值对的形式传递,一种是按照顺序直接传递参数。

示例如下:

use MySchool
go

 
create proc proc_stuResultParam
	@subject varchar(10),
	@score int
    as
    
    ----查找本次Java OOP 考试的平均分以及没有通过的人的信息


    ---定义变量保存最近一次考试时间
declare @maxDate datetime 





select @maxDate = MAX(ExamDate) from Result
  where SubjectId =(select SubjectId from Subject 
                    where SubjectName = @subject)
 
 
---计算平均分
select AVG(StudentResult) from Result
	where ExamDate = @maxDate
	and SubjectId = (select SubjectId from Subject 
                    where SubjectName = @subject) 
--查询未通过考试学生名单
select * from Student
		where StudentNo in(
	select StudentNo from Result
	where SubjectId =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    and StudentResult < @score 
    ) 
    go  
    -- 调用有参存储过程
    exec dbo.proc_stuResultParam 'Java OOP',94
    
    exec dbo.proc_stuResultParam @subject ='C# oop',@score =80

也可以在声明形式参数的时候指明默认值默认值会被新参数覆盖。
在声明参数时要把带默认值的参数放在后面声明,否则要在传入参数时在参数前要加入default关键字。

输出参数

  • 使用时必须在属性后加入output关键字来表示参数是输出参数
  • 在接收传出的结果时要从新声明一个参数用于接收

例子如下:

use MySchool
go

 
create proc proc_stuResultParam
	@subject varchar(10), --输入参数
	@score int = 60,
	@count int output --输出参数
    as
    
    ----查找本次Java OOP 考试的平均分以及没有通过的人的信息


    ---定义变量保存最近一次考试时间
declare @maxDate datetime 





select @maxDate = MAX(ExamDate) from Result
  where SubjectId =(select SubjectId from Subject 
                    where SubjectName = @subject)
 
 
---计算平均分
select AVG(StudentResult) from Result
	where ExamDate = @maxDate
	and SubjectId = (select SubjectId from Subject 
                    where SubjectName = @subject) 
--查询未通过考试学生名单
select * from Student
		where StudentNo in(
	select StudentNo from Result
	where SubjectId =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    and StudentResult < @score 
    ) 
--统计不及格的人数

select @count = count(StudentNo) from Result
	where SubjectId  =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    and StudentResult < @score 
    go  
    declare @c int 
    -- 调用有参存储过程
   --exec dbo.proc_stuResultParam 'Java OOP',80, @c output
   --select @c
    
   exec dbo.proc_stuResultParam @subject ='C# oop',@count = @c output

也可以向方法一样用return ,不过需要在接收传出来的数据之前,要从新声明一个参数去接收return出来的参数,这样接收的参数只是return传出来的参数。
例子如下:

use MySchool
go

 
create proc proc_stuResultParam
	@subject varchar(10), --输入参数
	@score int = 60,
	@count int output --输出参数
    as
    
    ----查找本次Java OOP 考试的平均分以及没有通过的人的信息


    ---定义变量保存最近一次考试时间
declare @maxDate datetime 





select @maxDate = MAX(ExamDate) from Result
  where SubjectId =(select SubjectId from Subject 
                    where SubjectName = @subject)
 
 
---计算平均分
select AVG(StudentResult) from Result
	where ExamDate = @maxDate
	and SubjectId = (select SubjectId from Subject 
                    where SubjectName = @subject) 
--查询未通过考试学生名单
select * from Student
		where StudentNo in(
	select StudentNo from Result
	where SubjectId =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    and StudentResult < @score 
    ) 
--统计不及格的人数

select @count = count(StudentNo) from Result
	where SubjectId  =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    and StudentResult < @score 
   --存储过程返回最高分
   declare @max int = 0
   select @max = max(StudentResult) from Result
	where SubjectId  =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    return @max
    go  
    declare @c int
   
    -- 调用有参存储过程
   --exec dbo.proc_stuResultParam 'Java OOP',80, @c output
   --select @c
    declare @MaxReturnParam int --通过接收return方式返回的值
   exec @MaxReturnParam = dbo.proc_stuResultParam @subject ='C# oop',@count = @c output
   select @MaxReturnParam

自定义错误信息

使用RAISERROR来自定义错误信息:
RAISERROR(msg_id| msg_str,severity, state WITH option[,…n])
msg_id : 在sysmessages系统表中指定用户定义错误信息
msg_str: 用户定义的特定信息,最长255个字符
severity: 定义严重性级别。用户可以使用级别为0~18级

  • 0~10:不会跳到catch
  • 11-19:进入catch
  • 20-:终止数据库连接

state:表示错误的状态,1到127之间的值
option:指示是否将错误记录到服务器错误日中
示例如下:`

declare @age int 
set @age =1000



if(@age>=0 and @age<=100)
print @age
else
--文本,错误级别(0~18之间),状态(1~127)
raiserror('年龄应输入0~100之间',16,1)


喜欢 (0)