关系数据库SQL之可编程性存储过程

关注

原创最新推荐文章于 2022-11-17 19:49:54 发布 972 阅读 AI 写同款· GEO 优化 ›

SQL server数据库可编程要点 SQL server 摘要接上基础篇，参考csdn部分博客，文中有链接和陕西国防工业职业技术学院SQL server2016数据库及应用 mooc课程以及工作中常用的数据库可编程性总结一视图视图是查一个或多个表时生成的虚拟表，不包含数据，只包含查询定义，操作和操作表一样，可以对其进行查询，修改（有限制）和删除简化用户操作，使用户关注自己感兴趣的数据 1.视图创建 create view 视图名 [(<字段一>),(<字段一>)...] --不能与现有表和视图重名,视阅读详情

前言

前面关系数据库SQL之可编程性函数（用户自定义函数）一文提到关系型数据库提供了可编程性的函数、存储过程、事务、触发器及游标，前文已介绍了函数，本文来介绍一下存储过程的创建、执行、删除。(还是以前文中银行系统为例)

图片来自网络

概述

存储过程是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数（如果该存储过程带有参数）来执行。

语法

创建存储过程

--创建存储过程
CREATE PROC[EDURE] <存储过程名称> 
    -- 添加存储过程所需的参数
    [
     <@参数1> <参数类型1> [= 默认值] [OUTPUT], 
     ……
     <@参数n> <参数类型n> [= 默认值] [IN|OUT|OUTPUT]
    ]
AS
BEGIN
    -- 这里面可以写为变量赋值语句
    SQL语句块
END

注意：

其中存储过程名不能超过128个字，每个存储过程中最多设定1024个参数；
存储过程所需的参数可有可无，如果有参数为带参数存储过程，没有参数就是无参数存储过程；
带参数存储过程参数后面有关键字OUT|OUTPUT为带输出参数存储过程;

[IN|OUT|OUTPUT]介绍：

IN:在参数后面加了IN关键字的表示为输入参数，默认的情况就是输入参数；
OUT:在参数后面加了OUT关键字的表示为输出参数；
OUTPUT:在参数后面加了OUTPUT关键字表示为输入输出参数，既是传入参数也是输出参数。

调用存储过程

--调用存储过程
EXEC<存储过程名称> [参数列表]

删除存储过程

--删除存储过程
DROP PROC[EDURE] <存储过程名称>

示例

/*
* 查询交易信息表中总的交易金额，以及支取和存入的总金额，并打印出来
*/
--判断存储过程是否存在
--存在则删除
if exists(select * from sysobjects where name = 'proc_getTransMoney')
drop procedure proc_getTransMoney
go
--创建无参数存储过程
create proc proc_getTransMoney
as
begin
    declare @sum_money money        --交易总额
    declare @get_money money        --支出总额
    declare @sav_money money        --存入总额
    select @sum_money = sum(TransMoney) from TransInfo
    select @get_money = sum(TransMoney) from TransInfo where TransType = '存款'
    select @sav_money = sum(TransMoney) from TransInfo where TransType = '取款'
    print '交易总额='+ltrim(convert(char,@sum_money))
    print '存入总额='+ltrim(convert(char,@sav_money))
    print '支出总额='+ltrim(convert(char,@get_money))
end
go
--执行存储过程
exec proc_getTransMoney
go

/*
* 查询指定账户挂失的账户信息
*/
--判断存储过程是否存在
--存在则删除
if exists(select * from sysobjects where name = 'proc_getLostAccount')
drop procedure proc_getLostAccount
go
--创建带参数输出存储过程
create proc proc_getLostAccount
--
@CId varchar(20) OUTPUT
as
begin
    select A.CustName as 姓名, A.IDCard AS 身份证号, A.TelePhone as 电话号码,
    C.CardID as 银行卡号,A.Address as 地址
    from AccountInfo as A inner join CardInfo as C on A.CustID = C.CustID
    where C.CardID =@CId AND C.IsLost = '是'
end
go
--执行存储过程
declare @CardID  varchar(20);
set @CardID= '银行卡号';
exec proc_getLostAccount  @CardID output;
print @CardID;
go