实验9：存储过程

一、实验目的

存储过程的创建与执行

二、实验内容（要求所创建的数据库和相应的SQL语句存盘到U盘上）

1、创建并执行简单存储过程

在PUBS数据库中，创建一个名称为pr_searchorddate的存储过程，该存储过程将查询出sales 表中订购日期ord_date在1994年以后的记录信息。  代码如下：

CREATE PROC pr_searchorddate AS

SELECT * FROM sales

WHERE ord_date>='1994-1-1' GO

 pr_searchorddate的执行语句如下： EXECUTE pr_searchorddate

2、建立带输入参数的存储过程

(1) 一个输入参数的存储过程

 带参存储过程创建

用于删除PUBS数据库sales表中stor_id为某某值（如‘6380’）的所有记录。 实现的T-SQL语句如下:

USE pubs GO

CREATE PROC deletesales @stor_id char(4) AS

DELETE sales

WHERE stor_id =@ stor_id

GO

 带参存储过程的执行如下： Exec deletesales ‘6380’

（2）多个输入参数的存储过程

在PUBS数据库中创建一个存储过程pr_searchempl,查询出authors表中state字段为某个州且姓中包含某字符串的所有的员工信息。  参考代码如下：

CREATE PROC pr_searchempl

@state char(2),@str varchar(40) As

Select * From authors

Where state=@state and au_lname like ‘%’+@str+’%’  存储过程pr_searchempl的执行语句如下 EXECUTE pr_searchempl ‘CA’,’hi’

3、带输出参数的存储过程

在PUBS数据库中创建一个存储过程pr_titleprice,统计出titles表中pub_id字段为某编号的书籍总价格。  参考代码

CREATE PROC pr_titleprice

@ pub_id char(4),@sprice money output As

Select @sprice=sum(price) From titles

Where pub_id=@ pub_id  存储过程的执行: declare @ss money

exec pr_titleprice '0877',@ss output select @ss as 总价格

4、带返回值的存储过程

建立一个带参的存储过程，用于向PUBS数据库中的publishers表中添加一条记录，在添加记录前先查看是否存在此出版社编号（pub_id），如不存在则插入该条记录信息，存储过程返回值为0，如已存在此出版社编号，则不进行插入记录操作，存储过程返回值为-1。测试记录为： ‘0001’，’清华大学出版社’，’北京’，null,’中国’。  实现的T-SQL语句:

USE pubs

GO

CREATE PROC InsertPublishers @Pub_Id char(4),

@pub_name varchar(40), @city varchar(20), @state char(2),

@country varchar(30) AS

IF EXISTS (SELECT * FROM publishers WHERE pub_id=@pub_id) Return -1 ELSE BEGIN

INSERT Publishers(Pub_Id, pub_name, city, state, country) VALUES(@Pub_Id, @pub_name, @city , @state, @country) IF @@ERROR=0 RETURN 0 ELSE

RETURN -9 END GO

 存储过程的执行如下： DECLARE @result int

Exec @result=InsertPublishers ‘9901’,’清华大学出版社’,’北京’,null,’中国’

IF @result=0

PRINT ‘记录插入成功’ IF @result=-1

PRINT ‘出版社编号已存在，记录重复！’ IF @result=-9

PRINT ‘出版社编号违反检查约束，请重新输入！’ GO