当前位置： 首页 > news >正文

【基础6】存储过程的 创建与调用

news 2025/7/6 11:23:31

目录

什么是存储过程

用户自定义存储过程

练习

什么是存储过程

  1. 什么是存储过程

    • 类似于C语言中的函数。
    • 用来执行管理任务或应用复杂的业务规则
    • 存储过程可以带参数，也可以返回结果
    • 存储过程可以包含数据操纵等语句、变量、逻辑控制语句等。（单个select语句、select语句块、select语句与逻辑控制语句）

  2. 存储过程的优点

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

  3. 存储过程的分类

    1. 系统存储过程
      • 由系统定义，存放在master数据库中。
      • 类似c语言中的系统函数
      • 系统存储过程的名称都以“SP_ ”开头或“XP_ ”开头
    2. 用户自定义存储过程
      • 由用户在自己的数据库中创建的存储过程
      • 类似C语言中的用户自定义函数

  4. 常用的系统存储过程：XP_cmdshell

    --系统存储过程
exec xp_cmdshell 'mkdir D:\bank' ,no_output    --加上no_output  只提示命令已完成，不显示 几行受影响，也不显示执行结果
exec xp_cmdshell 'dir D:\bank'					--查看文件信息exec sp_helpdb Sales							--查看数据库信息 
exec sp_databases								--查看当前服务器的所有数据库use Sales
go
exec sp_help Employees						--查看Employees表的所有  信息
exec sp_helpconstraint Employees		--查看Employees表的所有  约束
exec sp_helpindex Employees				--查看Employees表的所有  索引exec sp_helptext V_number		 			--显示未加密的存储过程exec sp_stored_procedures					--列出当前库中的所有存储过程

     

    • 可以执行dos命令下的一些操作

    • 以文本方式返回任何输出

    • 调用语法：

      exec xp _ cmdshell DOS命令[no_output]

    • 在sql 2005中启用扩展存储过程：xp_cmdshell

      要在外围配置管理器中启用

    • 在sql 2008及以上中启用扩展存储过程：xp_cmdshell

      --启用xp_cmdshell
USE master 
EXEC sp_configure 'show advanced options', 1 
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure 'xp_cmdshell', 1 
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure   'show advanced options', 0
RECONFIGURE WITH OVERRIDE --关闭xp_cmdshell
USE master 
EXEC sp_configure 'show advanced options', 1 
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure 'xp_cmdshell', 0 
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure   'show advanced options', 0
RECONFIGURE WITH OVERRIDE

用户自定义存储过程

  1. 用户自定义存储过程

    定义存储过程的语法

    create proc[edure] 存储过程名@参数1 数据类型@参数2 数据类型=默认值				 --有默认值的话才有（=默认值 ），这个是带默认值的输入参数。@参数3 数据类型=默认值 output 	 --默认值后面有output的，这个是带默认值的输出参数········@参数n 数据类型=默认值 output as  SQL语句					--可以是学过的任何语句
go

    无参存储过程的创建，调用存储过程 对存储过程的加密，显示创建存储过程的文本

       --创建存储过程：proc_销售额    ，查看每个人的销售情况--这个存储过程是显示所有人的销售额，不需要传参数，所以创建的是无参的存储过程use Salesgoselect * from Employeesselect * from Goodsselect * from Sellif exists (select * from sysobjects where name='proc_销售额')							--视图、表、存储过程 都是存储过程，全部的数据库对象：sysobjectsdrop procedure proc_销售额gocreate procedure proc_销售额as									--因为是没有参数，所以as前面不用写参数print '各员工的销售情况如下：'select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 group by E.编号,E.姓名--调用存储过程 execute 简写：exec 
--在创建存储过程的过程中，已经编译过一次，以后再也不需要进行编译，直接调用就可以
--语法：exec 过程名 [参数]		--有参数的话加参数，没有就不用。  
exec proc_销售额--查看存储过程创建的文本
exec sp_helptext proc_销售额--修改存储过程文本 为加密
--1、if exists (select * from sysobjects where name='proc_销售额')							--视图、表、存储过程 都是存储过程，全部的数据库对象：sysobjectsdrop procedure proc_销售额gocreate procedure proc_销售额--在这里加一个   with encryptionwith encryption --加密存储过程创建的文本as									--因为是没有参数，所以as前面不用写参数print '各员工的销售情况如下：'select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 group by E.编号,E.姓名--2、取消对文本的加密，创建存储过程的文本中，create  改成  alter  然后把加密的那行文本注释/删除
alter procedure proc_销售额
--with encryption --加密存储过程创建的文本
as									--因为是没有参数，所以as前面不用写参数
print '各员工的销售情况如下：'
select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end 
from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 group by E.编号,E.姓名

  2. 输入过程的参数分为两种：

    • 输入参数：往里面传值，
    • 输出参数 ：调用完成后，传出一个结果值，这个值可以进一步使用

    输入参数存储过程的创建 /调用/多个参数有默认值的调用

    --输入参数的存储过程
if exists (select * from sysobjects where name='proc_销售额')					
drop procedure proc_销售额
go
create procedure proc_销售额
@EmployeesID int
as						
set nocount on		
print '编号为'+cast(@EmployeesID as char(4))+'的员工销售情况如下：'
select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end 
from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 where 编号=@EmployeesID
group by E.编号,E.姓名
go--调用输入参数的存储过程    如果有多个输入参数，那么调用时，要与创建时的参数一一对应
exec proc_销售额 '1301'
--另一种调用：按名称调用，与参数位置无关
exec proc_销售额 @EmployeesID='1301'--带输入参数的默认值的存储过程
if exists (select * from sysobjects where name='proc_销售额')	
drop procedure proc_销售额
go
create procedure proc_销售额
@EmployeesID int='1301'
as									
set nocount on		
print '编号为'+cast(@EmployeesID as char(4))+'的员工销售情况如下：'
select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end 
from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 where 编号=@EmployeesID
group by E.编号,E.姓名
go--调用有输入参数的存储过程 
exec proc_销售额 				--使用默认值
exec proc_销售额 '1302'		--不使用默认值--多个参数只有一个默认值调用时
--按位置 这个默认值一般放在最后，调用时只用写不是默认值的，最后的默认值省略不写，就是调用的默认值。
--按名称   如要使用默认值，则默认值不用写，其他按名称调用即可

  3. 带输出参数的存储过程的创建

    ​ 如果希望调用存储过程后，返回了一个或多个值，这时就需要使用输出函数了。

    --输出参数的存储过程
--根据销售额判断是什么员工
if exists (select * from sysobjects where name='proc_销售额')						
drop procedure proc_销售额
go
create proc proc_销售额
@EmployeesID int='1301', @sellsum money output
as								
set nocount on			
print '编号为'+cast(@EmployeesID as char(4))+'的员工销售情况如下：'
select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end 
from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 where 编号=@EmployeesID
group by E.编号,E.姓名
if @EmployeesID in (select 售货员工编号 from Sell)select @sellsum=sum(零售价*S.数量) from Goods G , Sell S where G.商品编号=S.商品编号 and  售货员工编号=@EmployeesID
elseset @sellsum=0
go--调用有输出参数的存储过程
--调用时，要再定义一个变量接收输出值(类型要与输出值对应)，且调用时变量后要+output
declare @a money
exec proc_销售额 '1302' ,@a output
if @a>=30000print '优秀员工'
else if @a>=10000print '合格员工'
else print '无销售员工'--按名称调用
declare @a money
exec proc_销售额 @EmployeesID='1302' ,@sellsum=@a output
if @a>=30000print '优秀员工'
else if @a>=10000print '合格员工'
else print '无销售员工'--使用默认值
declare @a money
exec proc_销售额 @sellsum=@a output
if @a>=30000print '优秀员工'
else if @a>=10000print '合格员工'
else print '无销售员工'

  4. 处理存储工程的中的错误

    • 可以使用print语句显示错误信息，但是这些信息是临时的，只能显示给用户
    • raiserror 显示用户定义的错误信息时：可指定严重级别，设置系统变量@@error ，记录发生的错误等

  5. 返回值

    调用时要专门定义一个变量返回这个值，然后用输出语句输出

总结：

  • 存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等。
  • 存储过程允许带参数，参数分为：输入参数 / 输出参数
  • 其中，输入参数可以有默认值
    • 输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值
    • 输出参数从存储过程中返回(输出)值，后面跟output关键字
    • raiserror语句用来向用户报告错误

练习

use XK
goselect * from Student
select * from StuCou
select * from Course
select * from Class--1、在Xk数据库中，创建存储过程proc_学生选课情况1,完成如下功能。
--查询学生的选课信息（要求显示学号、姓名，课程名，志愿号）（1）对存储过程进行加密（2）调用存储过程if exists (select * from sysobjects where name='proc_学生选课情况1')					
drop procedure proc_学生选课情况1
go
create procedure proc_学生选课情况1with encryptionasset nocount on	print '学生选课信息如下：'select S.StuNo 学号,StuName 姓名,CouName 课程名,WillOrder 志愿号 from Student S ,StuCou SC ,Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo
go--调用
exec proc_学生选课情况1--2、在Xk数据库中，创建存储过程proc_学生选课情况2,完成如下功能。
--（1）查询给定学号的学生选课情况（要求显示学号、姓名，课程名，志愿号）；
--（2）存储过程带1个输入参数，表示学号；
--（3）执行存储过程完成指定学号'00000001' , '00000025', '000000001'的选课信息查询if exists (select * from sysobjects where name='proc_学生选课情况2')					
drop procedure proc_学生选课情况2
go
create procedure proc_学生选课情况2
@StuID  nvarchar(8)				--这里的类型一定要与StuNo相匹配
as
set nocount on	
if @StuId in (select StuNo from StuCou)beginprint '该学生选课信息如下：'select S.StuNo 学号,StuName 姓名,CouName 课程名,WillOrder 志愿号 from Student S ,StuCou SC ,Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@StuIDend
else if @StuId in (select StuNo from Student)print '该学生无选课信息！'
elseprint '无此人，请重新输入'
go--调用
exec proc_学生选课情况2 '00000001'	
exec proc_学生选课情况2 '00000025'	
exec proc_学生选课情况2 '000000001'	--3、在Xk数据库中，创建存储过程proc_学生选课情况3,完成如下功能。
--（1）查询指定学号的学生的选课情况（要求显示学号、姓名、课程名称，志愿号）。
--（2）存储过程带2个参数，输入参数表示学号，输出参数用于返回每个学生的选课门数。
--（3）完成指定学号的选课情况查询，选过课程的，存储过程返回1； 若没有选过课程的则给出提示“该学生没有选过程课程！”，存储过程返回0。
--（4）选课门数等于5门，显示“已经达到选课要求！”；选课门数大于等于4门，显示“请继续选课，还差1门达到选课要求！”；选课门数等于3门，显示“请继续选课，还差2门达到选课要求！”；选课门数等于2门，显示“请继续选课，还差3门达到选课要求！”；选课门数等于1门，显示“请继续选课，还差4门达到选课要求！”
--（5）对创建存储过程的文本进行加密。
--（6）分别以'00000001' , '00000005', '00000025', '000000001'学号进行存储过程调用的测试。（执行结果如下图所示）if exists (select * from sysobjects where name='proc_学生选课情况3')					
drop procedure proc_学生选课情况3
go
create procedure proc_学生选课情况3
@StuID  nvarchar(8) ,
@Choosesum int output
with encryption
as
set nocount on	
if @StuId in (select StuNo from StuCou)beginprint '该学生选课信息如下：'select S.StuNo,StuName,CouName,WillOrder from Student S ,StuCou SC ,Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@StuIDselect @Choosesum=count(StuName) from (	select S.StuNo,StuName,CouName,WillOrder from Student S ,StuCou SC ,Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@StuID) xuanke print '该学生选课门数：'+cast(@Choosesum as char(4))if @Choosesum=5print '已达到选课计划要求'else if @Choosesum=4print '请继续选课，还差1门达到选课要求！'else if @Choosesum=3print '请继续选课，还差2门达到选课要求！'else if @Choosesum=2print '请继续选课，还差3门达到选课要求！'elseprint '请继续选课，还差4达到选课要求！'return 1end
else if @StuId in (select StuNo from Student)beginprint '该学生没有选过课程！'return 0end
elseraiserror('非本校学生，请重新输入！',16,1)
go--调用
declare @sum int ,@a int
exec @a=proc_学生选课情况3 '00000001',@sum output
print '返回值：'+cast(@a as char(1))--调用
declare @sum int,@a int
exec @a=proc_学生选课情况3 '00000005',@sum output
print '返回值：'+cast(@a as char(1))--调用
declare @sum int,@a int
exec @a=proc_学生选课情况3 '00000025',@sum output
print '返回值：'+cast(@a as char(1))--调用
declare @sum int,@a int
exec @a=proc_学生选课情况3 '000000001',@sum output
print '返回值：'+cast(@a as char(100))
http://www.lryc.cn/news/69872.html

相关文章：

  • 如何快速实现接口自动化测试，常规接口断言封装实践
  • java+nodejs+vue+python+php家教信息管理系统
  • 课程分享：鸿蒙HarmonyOS系统及物联网开发实战课程（附课程视频及源码下载）
  • 【数据结构】线性表之栈、队列
  • StringUtils.substring\[XX]()字符串截取方法总结
  • SeaweedFs使用-通过http接口实现文件操作
  • 成绩管理系统
  • 【MYSQL】事务的4大属性，对隔离级别的详细讲解
  • 如何在宝塔面板后的阿里云服务器运行Flask项目并公网可以访问？
  • HTTP（九）-- response对象 -- 向页面响应数据
  • 音视频windows安装ffmpeg6.0并使用vs调试源码笔记
  • Midjourney|文心一格prompt教程[进阶篇]：Midjourney Prompt 高级参数、各版本差异、官方提供常见问题
  • 什么是Java虚拟机（JVM）？
  • 【HISI IC萌新虚拟项目】Package Process Unit模块整体方案·PART3
  • 分布式事务常见解决方案
  • 061：cesium设置棋盘图材质（material-5）
  • 【AI Earth试玩】权限配置与openAPI调用工具库
  • Tomcat安装与使用
  • 大数据课程-学习二十四周总结
  • 【GPT科技系列】国内开发者调用openAI-API科技方法
  • 基于QGIS的长株潭城市群边界范围融合实战
  • 【MySQL联合查询】轻松实现数据关联
  • Windows安装Ubuntu双系统
  • 【大数据之Hadoop】三十四、Hadoop综合调优之小文件优化方法
  • 周赛 Round#3 题解
  • 详解HTTPS加密过程
  • 聚观早报 | ChatGPT登顶美区iOS免费榜；库克不满苹果首款MR设备
  • position:sticky-粘性吸附布局
  • 【MySQL】-【数据库的设计规范】
  • 全面解析缓存应用经典问题