T-SQL 存储过程

存储过程的概念

T-SQL和C语言一样，是一门结构化的语言，他们具有类似的语法，

在C语言中使用函数可以提高代码段重用性，方便模块化设计,提高

执行效率。在C语言中使用函数的步骤：

• 第一步：定义函数，指定函数的参数的执行代码。
• 第二步：调用函数，通过函数的名称并指定相应的参数。

 

什么是存储过程

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

 

存储过程的优点

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

存储过程的分类：

系统存储过程

• 由系统定义，存放在master数据库中
• 类似C语言中的系统函数
• 系统存储过程的名称都以“sp_”开头或”xp_”开头

用户自定义存储过程

• 由用户在自己的数据库中创建的存储过程
• 类似C语言中的用户自定义函数

SQL SERVER 中常用的存储过程

 

系统存储过程	说明	使用语法
Sp_columns	查看指定列的信息	Execute_sp_columns ‘表名’
Sp_databases	列出服务器上所有数据库	Execute_sp_databases
Sp_help	查看某个表的所有信息	Execute_sp_help ‘表名’
sP_helpconstraint	查看某个列的约束	Execute_sp_helpconstraint’b表名’
Ap_helpdb	列出指定数据库或所有数据库信息	Execute_sp_helpdb ‘数据库名称’  或 execute_sp_helpdb
Sp_helptext	显示数据库对象的定义文本	Execute_sp_helptext ‘对象名称’
Sp_helpindex	查看指定表的索引信息	Execute sp_helpindex ‘表名’
Sp_renamedb	更改数据库名称	Execte sp_renamedb ‘原名称’
Sp_stored_procedures	列出当前环境可用的所有存储过程	Execute sp_stored_porc_edures
Sp_tables	列出当前环境下的表和视图对象的信息	Execute sp_tables

 

注意：出了系统存储过程，sql erver 还提供以xp_开头的扩展存储过程。

可以调用DOS命令的XP_cmdshell 存储过程，语法如下：

Exec xp_xmdshell dos 命令 【no_output】
No_output 为可选参数。

 

调用存储过程

EXECUTE（执行）语句用来调用存储过程。

 EXEC  过程名  [参数]

 

如何创建存储过程

• 定义存储过程的语法
• 和C语言的函数一样，参数可选
• 参数分为输入参数、输出参数
• 输入参数允许有默认值

 

CREATE  PROC[EDURE]  存储过程名                @参数1  数据类型 = 默认值 OUTPUT,               …… ,              @参数n  数据类型 = 默认值 OUTPUT             
 AS             
SQL语句  
 GO 

 

创建不带参数的存储过程

 

create procedure proc_getpc 
--没有参数 as
 select pcid as '电脑编号','使用状态'=case            when pcuse=0 then '空闲'        
                 when pcuse=1 then '使用'                    end,     
pcnote as '备注' from pcinfo where pcuse=0 
go

存储过程的参数几种:

• 输入参数
• 输出参数
• 输入参数

 

输出参数:

• 用于在调用存储过程后
• 返回结果，类似C语言的
• 按引用传递;

带输出参数的存储过程

create procedure proc_getpcbyparam     
@pcuse int 
 as
 select pcid as '电脑编号', '是用状态'=case             when pcuse=0 then '空闲'             when pcuse=1 then '使用'         
                         end 
pcnote as '备注' from pcinfo where pcuse=@pcuse go

带参数的默认值

 

create procedure proc_getpcbyparam     
@pcuse int=0 
 as 
select pcid as '电脑编号', '是用状态'=case                 when pcuse=0 then '空闲'             when pcuse=1 then '使用'                 end
 pcnote as '备注' from pcinfo where pcuse=@pcuse go

 

调用带多个参数默认值的存储过程

• Create  procedure  存储过程名称
• 参数1  参数1数据类型=默认值，
• 参数2  参数2数据类型=默认值   as……
• EXEC 存储过程名称  --都采用默认值
• EXEC 存储过程名称 参数1的值  --参数2采用默认值
• EXEC 存储过程名称 参数1的值 ,参数2的值  --都不采用默认值 
• 正确的调用方式：
• EXEC 存储过程名 称参数2=参数2的值

带参数的存储过程

 

select @balance=cardbalance from cardinfo where cardid=@cardid

go

--调用存储过程

declare @balance int --定义变量，用于存放调用存储过程是返回的结果

exec proc_getbalance 4,@balance output

--判断余额

if @balance>0

       print '余额为'+Convert(varchar(5),@balance)+',此卡可用'

else

       print '余额为'+Convert(varchar(5),@balance)+',此卡需要充值'

go

 

return关键字

return关键字可以终止存储过程或者返回数值

return  返回数值

创建存储过程，用户新增电脑，并使用return返回新增电脑的编号

create procedure proc_addpc

       @pcnode varchar(20)

as

       --插入数据，默认使用状态为空闲

       insert into pcinfo values(0,@pcnode)

       --返回当前自动增长列的值

       return @@identity

go

--调用存储过程

declare @pcid int--声明变量，用于保存存储过程的返回值

--执行存储过程，为变量赋值，并指定输入参数

exec @pcid=proc_addpc '新增的电脑'

select @pcid as '新增电脑编号' --输出变量

return使用（终止程序）

create procedure proc_addbalance

       @cardnumber varchar(20) ,--充值的卡号

       @money int --充值金额

as

               --判读卡号是否存在

       if not exists(select 1 from cardinfo where cardnumber=@cardnumber)

       begin

            print '充值失败，卡号：'+Convert(varchar,@cardnumber)+'不存在。'

              return

       end

       if (@money<=0)

       begin

              print '充值失败，充值金额必须大于'

              return --终止存储过程的执行

       end

       --进行充值（修改余额）

      …….

go

 

PRINT语句的使用

• 可指定严重级别
• 设置系统变量@@ERROR
• 记录所发生的错误等

RAISERROR语句的用法：

• msg_id：在sysmessages系统表中指定用户定义错误信息
• msg_str：用户定义的特定信息，最长255个字符
• severity：定义严重性级别。用户可使用的级别为0–18级
• state：表示错误的状态，1至127之间的值
• option：指示是否将错误记录到服务器错误日志中