第8章 存储过程

例8.3 创建带输入参数的存储过程
查询某家银行的贷款情况
create proc p_in_loan @bankname nvarchar(10) --输入参数 as select ename,ldate,lamount,bname from bankt b join loanT L on b.bno=l.Bno join legalEntityT le on le.Eno=l.Eno where bname = @bankname go
--执行存储过程 exec p_loan
执行存储过程-exec
语法格式: EXEC[UTE] 存储过程名
[@参数名=]参数值|@变量名[output]|[default]
@参数名:存储过程参数名，如果使用了@参数名 =参数值，则后续参数都必须使用此格式，可省 参数值：指定给存储过程的参数值，可取缺省值 @变量名：用来存储参数或返回参数的变量 Output：指定存储过程返回一个参数，该存储过 程参数定义时必须为output Default：取参数定义时的默认值
执行
使用execute执行 在SSMS中执行
8.2.1 使用T-SQL语句创建和执行存储过程
语法格式： 语法格式： CREATE PROC [EDURE] 存储过程名称 [@参数名 类型 [=默认值] [OUTPUT]] [,…n.] [WITH Encryption] AS SQL 语句序列
8.2.1 使用T-SQL语句创建和执行存储过程
执行
Sp_helptext p_e_loan ---显示已加密 通过ssms查看
例8.9 根据不同情况执行执行不同语句
create table book( bookno int primary key, bookname nvarchar(10), num int ) create proc p_in_book @bookNo int, @bookName nvarchar(10), @num int as if exists (select num from book where bookno=@bookno) update book set num=num +@num where bookno = @bookNo else insert into book values(@bookNo,@bookName,@num) --执行 exec p_in_book 1,'数据库应用技术',2 select * from book exec p_in_book 1,'数据库应用技术',3 select * from book
例8.8 加密
with encryption
create procedure p_e_loan with encryption as select ename,ldate,lamount,bname from bankt b join loanT l on b.bno=l.Bno join legalEntityT le on le.Eno = l.eno where bname = '工商银行北京A支行‘
例8.11 调用sp_in_loan
declare @r int exec sp_in_loan 'a','b',300,1,@r output print @r if @r=0 print '插入成功' else if @r=1 print '银行不存在' else if @r =2 print '企业不存在'
--执行
exec p_inde_loan exec p_inde_loan '工商银行北京A支行' exec p_inde_loan default
例8.5 创建带多个输入参数的存储过程
查询某个法人在某个银行的贷款情况
create proc p_MulIn_loan @LEName nvarchar(15), @bankName nvarchar(10) as select ename,bname,ldate,lamount from bankt b join loanT L on b.bno=l.Bno join legalEntityT le on le.Eno=l.Eno where (eName=@leName) and (bName = @bankName ) go
--执行该存储过程
exec p_in_loan @bankname='工商银行北京A支行‘ 或 exec p_in_loan '工商银行北京A支行'
例8.4 创建带输入参数默认值的存储过程
指定银行默认值为工商银行北京分行
create proc p_inde_loan @bankname nvarchar(10)='工商银行北京分行' --输入参数 as select ename,ldate,lamount,bname from bankt b join loanT L on b.bno=l.Bno join legalEntityT le on le.Eno=l.Eno where bname = @bankname go
保证系统的安全性
可以不授予用户访问存储过程中涉及的表的权限，只授予 执行存储过程的权限。
8.1 存储过程概述—分类
用户自定义存储过程 系统存储过程
Sp_helptext, Sp_rename, sp_helpdb等
扩展存储过程
后续版本将去掉该功能
8.2 创建和执行存储过程
创建存储过程
T-SQL：Create procedure 模板
8.1 存储过程概述—特点
执行速度快
存储过程创建时就经过语法检查和性能优化，执行时不必 重复检查，第一次调用后驻留内存；
模块化的程序设计： 模块化的程序设计：存储过程创建一次，可重复 调用； 减少网络通信量
存储过程作为一个独立的单元使用，一条语句即可实现存 储过程中的大量的SQL语句，减少了反复连接访问网络；
例8.10 插入贷款表
create proc sp_in_loan @bname char(5), @ename char(3), @lamount int, @lterm int, @res int output as begin if @lterm<=0 set @lterm = 1 if @lterm>10 set @lterm =10 declare @bno char(5) declare @eno char(3) select @bno = bno from bankt where bname= @bname select @eno =eno from legalEntityT where Ename=@ename if @bno is null --'银行不存在' begin set @res= 1 return end if @eno is null --'企业不存在' begin set @res= 2 return end insert into loanT values(@eno,@bno,GETDATE(),@lamount,@lterm) set @res=0 end
取存储过程的返回值
可以在存储过程中返回整数值 在存储过程中使用return返回的值的取得是 通过Exec @变量名=<存储过程名> 参数来 取得的
例8.12 比较放贷金额
create proc P_比较放贷金额 @bname1 nvarchar(15), @bname2 nvarchar(15) as begin declare @s1 int,@s2 int select @s1 = SUM(lamount) from loanT join bankt on loant.Bno = bankt.bno and bname = @bname1 select @s2 = SUM(lamount) from loanT join bankt on loant.Bno = bankt.bno and bname = @bname2 if @s1>@s2 return 1 else return 0 end --执行 declare @res int exec @res=P_比较放贷金额 '工商银行北京A支行','建设银行上海A支行' If @res=1 print ‘大’ else print ’小‘
Default：为参数指定默认值 Output：指定参数为输出参数 With Encryption：加密，不输出文本
例8.1 创建简单的存储过程
查询工商银行北京A支行的贷款情况
create proc p_loan as select ename,ldate,lamount,bname from bankt b join loanT L on b.bno=l.Bno join legalEntityT le on le.Eno=l.Eno where bname = '工商银行北京A支行'
---执行
exec p_MulIn_loan '浦庆石化有限公司','建设银行上海A支行' exec p_MulIn_loan @bankName = '交通银行北京分行‘, @LEName='浦庆石化有限公司'
例8.5 加提示的存储过程
当银行或法人不存在时，提示用户
create proc p_MulIn_loan1 @LEName nvarchar(15), @bankName nvarchar(10) as if not exists(select ename from legalEntityT where ename=@LEName) print '该法人不存在' else if @bankName not in(select bname from bankt) print '该银行不存在' else select ename,bname,ldate,lamount from bankt b join loanT L on b.bno=l.Bno join legalEntityT le on le.Eno=l.Eno where (eName=@leName) and (bName = @bankName )