SQL存储过程使用参考代码.doc

存储过程use EBuygo-常用的系统存储过程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(id int primary key,name varchar(20) not null,pwd varchar(20) not null)insert into users values(1,zhangsan,zhang01)gocreate procedure addUser -创建存储过程(procedure可以简写为proc)id int,name varchar(20),pwd varchar(20)as begininsert into users(id,name,pwd) values(id,name,pwd)endexec addUser 2,lisi,lisi01 -执行存储过程(实参传递的顺序和所对应的形参顺序必须一致)exec addUser name=wangwu,pwd=wang01,id=3 -执行存储过程，这种方式顺序可以不一致declare v_id int,v_name varchar(20),v_pwd varchar(20)set v_id=5set v_name=zhaoliu2set v_pwd=zhao022exec addUser v_id,v_name,v_pwd -执行存储过程，调用存储过程并传递变量参数(顺序必须与定义变量的顺序一致)select * from users-制定默认值use Studyif exists(select * from sys.all_objects where name=increaseCredit and type=P)drop procedure increaseCreditgocreate proc increaseCreditstuName char(6)=null, -指定null为默认值credit int = 3 -制定整数值3为默认值asbeginif stuName=nullbeginprint 没有指定学号，更新无法完成returnendupdate student set credit=credit+credit where stuName=stuNameendexec increaseCredit 程琳,5 -提供所有参数exec increaseCredit 程琳 -提供部分参数，则第二个参数使用默认值3-指定参数方向if exists(select name from sys.all_objects where name=avgCreditForMajor and type=P)drop proc avgCreditForMajorcreate proc avgCreditForMajormajorId int, -输入参数avg decimal(4,1) output -输出参数asselect avg=avg(credit) from student where majorId=majorIdgodeclare avgCredit decimal(4,1) exec avgCreditForMajor 1,avgCredit output -将输出的返回值保存在avgCredit变量中,注意输出参数也必须指定output关键字print(专业平均学分：+cast(avgCredit as varchar) -显示储存过程的返回值go-使用return语句use EBuyif exists(select name from sys.all_objects where name=checkCity and type=P)drop proc avgCreditForMajorcreate proc checkCitycusid varchar(20)asif(select address from customer where cusId=cusid) like %上海%return 1else if(select address from customer where cusId=cusid) not like %上海%return 2else return 3declare ret_status intexec ret_status=checkCity 1001 -客户住在上海select ret_status as 返回状态declare ret_status intexec ret_status=checkCity 1004 -客户不住在上海select ret_status as 返回状态declare ret_status intexec ret_status=checkCity 13204 -客户不存在的情况select ret_status as 返回状态-事物和错误处理create proc procTestasdeclare num intselect num=9begin trywhile num0beginprint convert(varchar,sqrt(num)select num=num-1if num=5raiserror(基数已小于6,16,1)endend trybegin catchprint 放生错误的是：+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()end catchgoexec procTest -执行存储过程-存储过程中的事务use EBuycreate table account(accId int,type varchar(10),balance decimal(10,2)insert into account values(33,savings,3000)insert into account values(33,checking,1500)if exists(select name from sys.all_objects where name=savingsToChecking and type=P)drop proc savingsToCheckinggocreate proc savingsToCheckingaccid int,num decimal(10,2)asdeclare bal decimal(10,2)begin tryselect bal=balance from account where accId=accid and type=savingsif numbalreturn 1begin tranupdate account set balance=balance-num where accId=accid and type=savingsupdate account set balance=balance+num where accId=accid and type=checkingcommit tranreturn 0end trybegin catchrollback tranreturn 2end catchgodeclare retCode intbeginexec retCode=savingsToChecking 33,1500if retCode=0print 转账成功else if retCode=1print 转账金额大于账面余额，转账失败else print 数据库发生错误，转账失败end-存储过程的查看、修改和删除sp_helptext savingsToChecking -查看存储过程alter proc savingsToCheckingaccid int, -此处可以修改参数的定义num decimal(10,2)drop proc