求SQL高手写一个存储过程，多谢！

来自故乡的风 2011-07-08 12:02:49
数据库里有两个表：员工表Employee和部门表Department。部门表Department的结构和数据如下：

| DeptID | DeptName |
|--------+----------|
| 1 | Office |
|--------+----------|
| 2 | HR |
|--------+----------|
| 3 | IT |
|--------+----------|
| 4 | Sales |
|--------+----------|
| 5 | Other |
|--------+----------|

有三条数据：
| EmployeeID | FirstName | LastName | DepartmentID | Scores |
|------------+-----------+----------+--------------+--------|
| 15 | first15 | last15 | 2 | 80 |
|------------+-----------+----------+--------------+--------|
| 16 | first16 | last16 | 4 | 92 |
|------------+-----------+----------+--------------+--------|
| 17 | first17 | last17 | 5 | 75 |
|------------+-----------+----------+--------------+--------|

请写一个存储过程InsertRecords将这三条记录加入到员工表Employee中，并使用事务（transaction）检查DepartmentID是否已存在部门表Department中。一旦发现有不存在的情况，取消所有的插入操作。

（注：这是一道面试题，也没说两个表有无外键关联。是不是根据有无关联应该有两种写法？）
...全文
249 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
luoyefeng007 2011-07-11
  • 打赏
  • 举报
 回复
没意思！！
wang_jian_999 2011-07-09
  • 打赏
  • 举报
 回复
use tempdb
GO
Set nocount On

if object_id('Department') Is Not Null
Drop Table Department
if object_id('Employee') Is Not Null
Drop Table Employee


Create Table Department(DeptID int primary key,DeptName nvarchar(50))
Create Table Employee(EmployeeID int primary key ,FirstName nvarchar(50),LastName nvarchar(50),DepartmentID int,Scores int)

go
Insert Into Department(DeptID,DeptName) values(1,'Office')
Insert Into Department(DeptID,DeptName) values(2,'HR')
Insert Into Department(DeptID,DeptName) values(3,'IT')
Insert Into Department(DeptID,DeptName) values(4,'Sales')
Insert Into Department(DeptID,DeptName) values(5,'Other')
go

--存儲過程
if object_id('InsertRecords') Is Not Null
Drop proc InsertRecords
Go
Create proc InsertRecords
(
@EmployeeID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@DepartmentID int,
@Scores int
)
As
If Exists(Select 1 From Department Where DeptID=@DepartmentID)
Insert Into Employee(EmployeeID,FirstName,LastName,DepartmentID,Scores) values(@EmployeeID,@FirstName,@LastName,@DepartmentID,@Scores)
GO

--存儲過程調用

begin tran

Exec InsertRecords @EmployeeID=15,@FirstName='first15',@LastName='last15',@DepartmentID=2,@Scores=80
If @@ROWCOUNT =0 Goto ErrFlag

Exec InsertRecords @EmployeeID=16,@FirstName='first16',@LastName='last16',@DepartmentID=4,@Scores=92
If @@ROWCOUNT =0 Goto ErrFlag

Exec InsertRecords @EmployeeID=17,@FirstName='first17',@LastName='last17',@DepartmentID=5,@Scores=75
If @@ROWCOUNT =0 Goto ErrFlag

Commit Tran
Print N'Commit Tran'
Goto ExitFlag

ErrFlag:
Rollback Tran
Print N'Rollback Tran'

ExitFlag:

Go
Andy-W 2011-07-09
  • 打赏
  • 举报
 回复
要是说那三条记录一定要写在存储过程里面使用事务控制，那么那存储过程就没有多大的意义了。

倘若非要那样不可，可以参考：




use tempdb

GO

Set nocount On 

if object_id('Department') Is Not Null Drop Table Department

if object_id('Employee') Is Not Null Drop Table Employee

    

Create Table Department(DeptID int primary key,DeptName nvarchar(50))

Create Table Employee(EmployeeID  int primary key ,FirstName  nvarchar(50),LastName   nvarchar(50),DepartmentID int,Scores int)

go

Insert into Department(DeptID,DeptName) values(1,'Office'),(2,'HR'),(3,'IT'),(4,'Sales'),(5,'Other')

go  

--存储过程

if object_id('InsertRecords') Is Not Null

    Drop proc InsertRecords

Go

--存储过程

Create proc InsertRecords    

As

Begin Try

	begin tran

		If  not Exists(Select 1 From Department Where DeptID In(2,4,5) having Count(1)=3) 

			raiserror 50001 '存在无效的DepartmentID'

		Insert Into Employee(EmployeeID,FirstName,LastName,DepartmentID,Scores) 

			values	(15,N'first15',N'last15',2,80), 

					(16,N'first16',N'last16',4,92),

					(17,N'first17',N'last17',5,75)				

	Commit tran

End Try

Begin Catch

	Declare @ErrorMessage nvarchar(2047)

	Set @ErrorMessage=Error_message()

	Raiserror 50001 @ErrorMessage

	rollback tran	

End Catch 

Go

--存储过程调用

Exec InsertRecords

Go

Select * From Employee

解决问题的方法是多样的，结果是一样的。

Andy-W 2011-07-09
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 torontosky 的回复:]


我在SQL Server 2008里边试了一下，很好用！多谢。

原题要求所有的插入操作以及整个操作的Transaction都要写在一个存储过程里，请问这好实现吗？再次感谢！
[/Quote]
要是在SQL Serer 2008环境，更为方便，在存储过程，通过创建表类型参数，如：



use tempdb

GO

Set nocount On 

if object_id('Department') Is Not Null Drop Table Department

if object_id('Employee') Is Not Null Drop Table Employee

    

Create Table Department(DeptID int primary key,DeptName nvarchar(50))

Create Table Employee(EmployeeID  int primary key ,FirstName  nvarchar(50),LastName   nvarchar(50),DepartmentID int,Scores int)

go



Insert into Department(DeptID,DeptName) values(1,'Office'),(2,'HR'),(3,'IT'),(4,'Sales'),(5,'Other')

go

	

go    

--存储过程

if object_id('InsertRecords') Is Not Null

    Drop proc InsertRecords

Go

--创建表类型

if Exists(Select 1 From sys.types Where name='Employee')

	Drop type Employee

Create type Employee  As Table(

  EmployeeID int,

  FirstName nvarchar(50),

  LastName nvarchar(50),

  DepartmentID int,

  Scores int

)

Go

--存储过程

Create proc InsertRecords

(

	@Employee Employee Readonly

)     

As

If Exists(Select 1 From @Employee As a Where Not Exists(Select 1 From Department Where DeptID=a.DepartmentID))

begin 

	Raiserror 50001 N'发现无效的DepartmentID'

End 

Else 

    Insert Into Employee(EmployeeID,FirstName,LastName,DepartmentID,Scores) 

		Select EmployeeID,FirstName,LastName,DepartmentID,Scores From @Employee

go



--存储过程调用

Declare @Employee As Employee 

Insert into @Employee(EmployeeID,FirstName,LastName,DepartmentID,Scores) 

	values	(15,N'first15',N'last15',2,80), 

			(16,N'first16',N'last16',4,92),

			(17,N'first17',N'last17',5,75)



Exec dbo.InsertRecords @Employee = @Employee



Go

Select * From Employee

来自故乡的风 2011-07-09
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 dvd_01 的回复:]
SQL code


use tempdb
GO
Set nocount On

if object_id('Department') Is Not Null
Drop Table Department
if object_id('Employee') Is Not Null
Drop Table Employee


Create Table……
[/Quote]

我在SQL Server 2008里边试了一下，很好用！多谢。

原题要求所有的插入操作以及整个操作的Transaction都要写在一个存储过程里，请问这好实现吗？再次感谢！

wujianfeng32 2011-07-08
  • 打赏
  • 举报
 回复
事务结束后才会成交. if not exit() goto ERR:rollback
chuanzhang5687 2011-07-08
  • 打赏
  • 举报
 回复
不过可以加一个判断！ 判断一下 是否存在 如果存在就插入。
来自故乡的风 2011-07-08
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 zoezs 的回复:]
你应该自己先写，等有错了再找人帮忙，都不动手，这样你还是啥也学不会。
[/Quote]

您说得在理。但我平时写增删改查还可以，从来没写过SP，也没用过Transaction。不知从哪里下手。
chuanzhang5687 2011-07-08
  • 打赏
  • 举报
 回复
只不过没有明确说明罢了！这不是你的错！是面试公司的问题。但你也应该考虑到呀，他俩就是关联[Quote=引用 5 楼 torontosky 的回复:]
引用 2 楼 chuanzhang5687 的回复:
DepartmentID这不是关联部门表的id吗？


员工表里的DepartmentID确实是对应部门表里的DeptID，但是题目里没说是否关联。这是不是应该写两个SP，怎样写？多谢！
[/Quote]
chuanzhang5687 2011-07-08
  • 打赏
  • 举报
 回复 
create proc procname



as

begin



insert into employee values(...)

insert into employee values(...)

insert into employee values(...)



end

--事务不会
来自故乡的风 2011-07-08
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 chuanzhang5687 的回复:]
DepartmentID这不是关联部门表的id吗？
[/Quote]

员工表里的DepartmentID确实是对应部门表里的DeptID，但是题目里没说是否关联。这是不是应该写两个SP，怎样写？多谢！
快溜 2011-07-08
  • 打赏
  • 举报
 回复
DepartmentID 是设置为外键的话正常插入就好了
begin try
begin tran
insert into Employee
select 15 | first15 | last15 | 2 | 80 |
union
sleect ...
...
commit
end tran
end try
begin catch
rollback
end
Zoezs 2011-07-08
  • 打赏
  • 举报
 回复
你应该自己先写，等有错了再找人帮忙，都不动手，这样你还是啥也学不会。
chuanzhang5687 2011-07-08
  • 打赏
  • 举报
 回复
DepartmentID这不是关联部门表的id吗？
快溜 2011-07-08
  • 打赏
  • 举报
 回复
高手吃饭去了。
ly745455 2011-07-08
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 dvd_01 的回复:]
SQL code

use tempdb
GO
Set nocount On

if object_id('Department') Is Not Null
Drop Table Department
if object_id('Employee') Is Not Null
Drop Table Employee


Create Table D……
[/Quote]
+1
Andy-W 2011-07-08
  • 打赏
  • 举报
 回复 


use tempdb

GO

Set nocount On 



if object_id('Department') Is Not Null

	Drop Table Department

if object_id('Employee') Is Not Null

	Drop Table Employee



	 

Create Table Department(DeptID int primary key,DeptName nvarchar(50))

Create Table Employee(EmployeeID  int primary key ,FirstName  nvarchar(50),LastName   nvarchar(50),DepartmentID int,Scores int)



go

Insert Into Department(DeptID,DeptName) values(1,'Office')

Insert Into Department(DeptID,DeptName) values(2,'HR')

Insert Into Department(DeptID,DeptName) values(3,'IT')

Insert Into Department(DeptID,DeptName) values(4,'Sales')

Insert Into Department(DeptID,DeptName) values(5,'Other')

go



--存儲過程

if object_id('InsertRecords') Is Not Null

	Drop proc InsertRecords

Go

Create proc InsertRecords

(

	@EmployeeID int,

	@FirstName nvarchar(50),

	@LastName nvarchar(50),

	@DepartmentID int,

	@Scores int

)	 

As

If Exists(Select 1 From Department Where DeptID=@DepartmentID)

	Insert Into Employee(EmployeeID,FirstName,LastName,DepartmentID,Scores) values(@EmployeeID,@FirstName,@LastName,@DepartmentID,@Scores)

GO



--存儲過程調用



begin tran 



Exec InsertRecords @EmployeeID=15,@FirstName='first15',@LastName='last15',@DepartmentID=2,@Scores=80 

If @@ROWCOUNT =0 Goto ErrFlag



Exec InsertRecords @EmployeeID=16,@FirstName='first16',@LastName='last16',@DepartmentID=4,@Scores=92 

If @@ROWCOUNT =0 Goto ErrFlag



Exec InsertRecords @EmployeeID=17,@FirstName='first17',@LastName='last17',@DepartmentID=5,@Scores=75 

If @@ROWCOUNT =0 Goto ErrFlag



Commit Tran

Print N'Commit Tran'

Goto ExitFlag



ErrFlag:

Rollback Tran

Print N'Rollback Tran'



ExitFlag:



Go
一复杂存储过程写法！
求一复杂存储过程写法！楼主suppanda007（小宝）2006-08-28 17:44:07 在 MS-SQL Server / 基础类 提问有一个表   Table   A   结构如下     姓名         颜色1         颜色2             评语     name       color1        
一个分页存储过程
/*****听以前的同事说asp页面上的分页太慢了（如果数据多了），就想了这么个笨办法。有些地方还要考虑----比如select top 22 * from cat_list where T_id not in (select T_id from #change)是否有效率问题；数据不能重复等等不过灵活性挺好。希望各位高手再给帮忙改正；多谢chair3的帮助---这个存储过程还可以在加入几个变量，
SQL injection的实现与应用
本文作者：angel文章性质：原创发布日期：2004-04-02前言 　　SQL injection可以说是一种漏洞，也可以说成是一种攻击方法，程序中的变量处理不当，对用户提交的数据过滤不足，都可能产生这个漏洞，而攻击原理就是利用用户提交或可修改的数据，把想要的SQL语句插入到系统实际SQL语句中，轻则获得敏感的信息，重则控制服务器。SQL injection并不紧紧局限在Ms
不知哪位高手有jsp做的日历控件，可以用来记事用！
不知哪位高手有jsp做的日历控件，可以用来记事用。不同的用户可以记录自己的信息。如果有，请提供源代码，在下多谢！ 问题点数：100、回复次数：9Top 1 楼jeanven（）回复于 2002-11-29 10:59:07 得分 0 我自己做了一个日历当然是很简单的那种如果你需要可以在这个基础上添加功能。给我发信我发给你jeanven@sohu.comTop2 楼jeanven（）
SQL Server 数据库备份和还原认识和总结（二）
通过《SQL Server 数据库备份和还原认识和总结（一）》，相信您对数据备份和还原有了一个更深入的认识，在上文中我没有对事务日志做剖析，在此推荐宋沄剑的文章，对事务日志做了比较详细的讲解：http://www.cnblogs.com/CareySon/archive/2012/02/13/2349751.html。本文将针对上文继续进行数据备份和还原讲解，主要讲解备份和还原的一些关键选...
MS-SQL Server

34,591

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章