建立存储过程

存储过程

 

use EBuy

go

--常用的系统存储过程

sp_addmessage  --将新的用户定义错误消息存储在SQL Server数据库实例中

sp_helptext --显示用户定义的规则、默认值、存储过程、函数、视图等对象的实际文本

sp_xml_preparedocument  --读取作为输入提供的XML文本，然后使用MSXML分析器对其进行分析，并提供分析后的文档供使用

sp_helpdb 'EBuy'  --查看指定数据库的信息

sp_databases  --列出服务器上的所有数据库

sp_server_info  --列出服务器信息，如字符集、版本和排列顺序等

sp_stored_procedures  --列出当前环境中的所有存储过程

sp_configure  --修改SQL Server全局配置选项

sp_adduser --向当前数据库中添加新的用户

sp_addrole --在当前数据库中创建新的数据库角色

 

 

--用户自定义存储过程

create table users(

       idint primary key,

       namevarchar(20) not null,   

       pwdvarchar(20) not null

)

insert into usersvalues(1,'zhangsan','zhang01')

go

 

create procedure addUser  --创建存储过程(procedure可以简写为proc)

       @idint,

       @namevarchar(20),

       @pwdvarchar(20)

as

       begin

              insert into users(id,name,pwd)values(@id,@name,@pwd)

       end

 

exec addUser 2,'lisi','lisi01'  --执行存储过程(实参传递的顺序和所对应的形参顺序必须一致)

 

exec addUser@name='wangwu',@pwd='wang01',@id=3  --执行存储过程，这种方式顺序可以不一致

 

declare @v_id int,@v_namevarchar(20),@v_pwd varchar(20)

       set@v_id=5

       set@v_name='zhaoliu2'

       set@v_pwd='zhao022'

exec addUser @v_id,@v_name,@v_pwd  --执行存储过程，调用存储过程并传递变量参数(顺序必须与定义变量的顺序一致)

 

select * from users

--制定默认值

use Study

if exists(select * from sys.all_objectswhere name='increaseCredit' and type='P')

       dropprocedure increaseCredit

go

 

create proc increaseCredit

       @stuNamechar(6)=null,  --指定null为默认值

       @creditint = 3  --制定整数值3为默认值

as

       begin

              if @stuName=null

                     begin

                            print '没有指定学号，更新无法完成'

                            return

                     end

       updatestudent set credit=credit+@credit where stuName=@stuName

       end

 

exec increaseCredit '程琳',5  --提供所有参数

exec increaseCredit '程琳' --提供部分参数，则第二个参数使用默认值3

 

 

--指定参数方向

if exists(select name from sys.all_objectswhere name='avgCreditForMajor' and type='P')

       dropproc avgCreditForMajor

 

create proc avgCreditForMajor

       @majorIdint,  --输入参数

       @avgdecimal(4,1) output  --输出参数

as

       select@avg=avg(credit) from student where majorId=@majorId

go

 

declare @avgCredit decimal(4,1) 

exec avgCreditForMajor 1,@avgCreditoutput  --将输出的返回值保存在@avgCredit变量中,注意输出参数也必须指定output关键字

print('专业平均学分：'+cast(@avgCredit as varchar)) --显示储存过程的返回值

go

 

--使用return语句

use EBuy

if exists(select name from sys.all_objectswhere name='checkCity' and type='P')

       dropproc avgCreditForMajor

 

create proc checkCity

       @cusidvarchar(20)

as

       if(selectaddress from customer where cusId=@cusid) like '%上海%'

              return 1

       elseif(select address from customer where cusId=@cusid) not like '%上海%'

return 2

       else

              return 3

 

declare @ret_status int

exec @ret_status=checkCity '1001'  --客户住在上海

select @ret_status as '返回状态'

 

declare @ret_status int

exec @ret_status=checkCity '1004'  --客户不住在上海

select @ret_status as '返回状态'

 

declare @ret_status int

exec @ret_status=checkCity '13204'  --客户不存在的情况

select @ret_status as '返回状态'

 

--事物和错误处理

create proc procTest

as

       declare@num int

       select@num=9

       begintry

              while @num>0

              begin

                     printconvert(varchar,sqrt(@num))

                     select@num=@num-1

                     if@num=5

                            raiserror('基数已小于6',16,1)

              end

       endtry

       begincatch

              print '放生错误的是：'+convert(varchar,error_procedure())

              print '错误编号：'+convert(varchar,error_number())

              print '错误描述：'+convert(varchar,error_message())

              print '错误级别：'+convert(varchar,error_severity())

              print '错误位于：'+convert(varchar,error_line())

              print '错误状态：'+convert(varchar,error_state())

       endcatch

go

 

exec procTest  --执行存储过程

 

 

 

--存储过程中的事务

use EBuy

create table account(

       accIdint,

       typevarchar(10),

       balancedecimal(10,2)

)

insert into accountvalues(33,'savings',3000)

insert into accountvalues(33,'checking',1500)

 

if exists(select name from sys.all_objectswhere name='savingsToChecking' and type='P')

       dropproc savingsToChecking

go

create proc savingsToChecking

       @accidint,

       @numdecimal(10,2)

as

       declare@bal decimal(10,2)

       begintry

              select @bal=balance from account whereaccId=@accid and type='savings'

              if @num>@bal

                     return1

              begin tran

                     updateaccount set balance=balance-@num where accId=@accid and type='savings'

                     updateaccount set balance=balance+@num where accId=@accid and type='checking'

              commit tran

              return 0

       endtry

       begincatch

              rollback tran

              return 2

       endcatch

go

 

declare @retCode int

begin

       exec@retCode=savingsToChecking 33,1500

       if@retCode=0

              print '转账成功'

       elseif @retCode=1

              print '转账金额大于账面余额，转账失败'

       else

              print '数据库发生错误，转账失败'

end

 

 

--存储过程的查看、修改和删除

sp_helptext 'savingsToChecking'  --查看存储过程

 

alter proc savingsToChecking

       @accidint,  --此处可以修改参数的定义

       @numdecimal(10,2)

 

drop proc savingsToChecking  --删除存储过程

 

 

--重新编译存储过程

sp_recompile savingsToChecking

 

--2

create proc savingsToChecking

       @accidint,

       @numdecimal(10,2)

with recompile   --创建存储过程的时候指定

as

......

--3

exec savingsToChecking 33,1500 withrecompile