存储过程嵌套示例

 

代码

create table Student -- 学生表
(
StudentId int not null , -- 学生ID
StudentName VARCHAR2 ( 30 ), -- 学生姓名
StuentSN VARCHAR2 ( 30 ), -- 学生学号
StudentGradu int -- 学生是否毕业 0代表毕业，1代表未毕业
)
create table Subject -- 课程表
(
SubjectId int not null , -- 课程ID
SubjectName VARCHAR2 ( 20 ) -- 课程名称
)
create table SelSubject -- 选好课程表
(
SubjectId int not null , -- 课程ID
StudentID int not null -- 学生ID
)

 

 

问题产生背景：

想嵌套循环得删除关联表内容 ，之前一直都是在前台处理的。发觉 挺浪费资源的，也影响运行速度。从而查了资料，发觉用游标可解决。

之前也接触到过，但是限制在理解别人的写的代码中，自己倒不怎么去碰触，总感觉 游标是个挺 高深的问题。原来我错了，“吸收没消化”

果真 如师父所言。

1) 事例数据库表：
　　企业信息表
　　
　　表名称
　　prd_wxt_enterprise_tbl_EnterpriseInfo
　　数据来源
　　
　　存储数据
　　企业信息表
　　主键/外键
　　
　　字段名称
　　说明
　　类型（精度范围）
　　备注
　　cEnterpriseId
　　n 企业ID
　　Char(10)
　　
　　sEnterpriseName
　　n 企业名称
　　Varchar(200)
　　
　　sShortName
　　n 简称
　　Varchar(50)
　　
　　sAddress
　　n 地址
　　nVarchar(200)
　　
　　E_system
　　n 企业所属系统（0新系统，空或1为旧系统）
　　Char(1)
　　
　　产品表
　　
　　表名称
　　Shop_tbl_ProductInfo
　　数据来源
　　
　　存储数据
　　产品表
　　主键/外键
　　
　　字段名称
　　说明
　　类型（精度范围）
　　备注
　　P_Code(PK)
　　n 产品SKU号,即下单号，产品系统编号(p123456789)
　　Char(10)
　　not null
　　EnterpriseId
　　n 企业ID
　　Char(10)
　　not null
　　P_Name
　　n 产品名称
　　Nvarchar(100)
　　not null
　　P_Price
　　n 产品价格（单位元）
　　Money
　　
　　P_UserPrice
　　n 产品会员价（单位元）
　　Money
　　
　　Remark
　　n 备注
　　Nvarchar(500)
　　
　　
　　
　　
　　
　　2) 要实现的功能：
　　删除旧企业数据及相关的产品，要求用嵌套存储过程实现
　　3) 存储过程：包括两个存储过程
　　a) 存储过程一：根据企业ID，删除该企业及下面的产品：
　　存储过程代码如下：
　　

代码

/*
　　功能：删除企业，同时删除关联的表，包括企业表及其关联的产品表数据
　　创建人：
　　创建日期：2007-4-10
　　修改日期：2007-4-10
　 */
　　 CREATE PROCEDURE [ DeleteEnterprise ]
　　(
　　 @EnterpriseID nvarchar ( 10 )
　　)
　　 AS
　　 declare @strSQL nvarchar ( 4000 )
　　 -- 删除企业产品表
　　 select @strSQL = " delete from Shop_tbl_ProductInfo where EnterpriseId = ' "+@EnterpriseID+" ' ";
　　 exec ( @strSQL )
　　 -- 删除企业表
　　 select @strSQL = " delete from prd_wxt_enterprise_tbl_EnterpriseInfo where cEnterpriseID = ' "+@EnterpriseID+" ' ";
　　 exec ( @strSQL )
　　 GO
　　

 

 

　

b) 存储过程二：选出旧企业的数据，然后通过循环嵌套的方式，通过循环语句调用存储过程一，删除所有的旧企业数据及其产品数据
　　存储过程代码如下：
　

代码

　 /*
　　功能：删除旧企业数据，同时删除关联的表，包括
　　创建人：
　　创建日期：2007-4-11
　　修改日期：2007-4-11
　　 */
　　 CREATE PROCEDURE [ DeleteOldEnterprise ]
　　 AS
　　 declare @strSQL nvarchar ( 4000 )
　　 declare @EnterpriseID nvarchar ( 10 )
　　 DECLARE Enterprise_CURSOR Cursor FOR
　　 Select cEnterpriseId from prd_wxt_enterprise_tbl_EnterpriseInfo where e_system = ' 1 ' or e_system is null
　　 OPEN Enterprise_CURSOR
　　 Fetch next from Enterprise_CURSOR
　　 into @EnterpriseID
　　 -- 以下一直到END都是循环语句
　　 WHILE @@FETCH_STATUS = 0
　　 BEGIN
　　 -- 下面这行是调用存储过程一删除企业及其产品数据
　　 exec DeleteEnterprise @EnterpriseID
　　 Fetch next from Enterprise_CURSOR
　　 into @EnterpriseID
　　 END
　　 CLOSE Enterprise_CURSOR
　　 DEALLOCATE Enterprise_CURSOR
　　 GO

 

利用存储过程实现记录集循环
作者：   2008-07-14 22:07   来源: IT专家网  
相关标签: SQL  存储过程  记录集  循环

在应用程序开发的时候，我们经常可能会遇到下面的应用，我们会通过查询数据表的记录集，循环每一条记录，通过每一条的记录集对另一张表进行数据进行操作，如插入与更新，我们现在假设有一个这样的业务：老师为所在班级的学生选课，选的课程如有哲学、马克思主义政治经济学、邓小平理论这些课，现在操作主要如下：

1) 先要查询这些还没有毕业的这些学生的名单，毕业过后的无法进行选课;

2) 在批量的选取学生的同时，还需要添加对应的某一门课程;

3) 点添加后选课结束。

我们如果有SQL语句

我们先建立三张表：



在实现上面的业务功能的时候，我们可能很多人一开始就在程序里面直接实现了，代码如下：

--省略数据库连接

代码

DataTable dt = GetDs().Tables[ 0 ];
int subjectid = Convert.ToInt32( this .DrpList.SelectedValue);
if (dt.Rows.Count > 0 )
{
Add(subjectid,Convert.ToInt32(dt.Rows[i][ 0 ].ToString()));
}

public DataSet GetDs()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[ " conn " ]

.ConnectionString);
SqlCommand cmd = new SqlCommand( " select studentid from Student where StudentGradu =

1 " , con);
SqlDataAdapter dat = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
dat.Fill(ds);
return ds;
}

public void Add( int subjectid, int studentinfoid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append( " insert into SelSubject( " );
strSql.Append( " SubjectId,StudentId) " );
strSql.Append( " values ( " );
strSql.Append( " @SubjectId,@StudentId) " );
SqlParameter[] parameters = {
new SqlParameter( " @SubjectId " , SqlDbType.Int, 4 ),
new SqlParameter( " @StudentId " , SqlDbType.Int, 4 )};
parameters[ 0 ].Value = subjectid;
parameters[ 1 ].Value = studentinfoid;
DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}

 

 

我们从上述的代码中可以看到，在我们取出来表的记录集的时候，然后通过满足条件的记录集在程序中循环的去传条件进行数据的插入的操作。

如果数据量少可能看不出用上面这种办法实现的弱点，因为它每次在操作数据库的时候，都存在着频繁的和数据库的I/O直接交互，这点性能的牺牲实属不应该，那我们就看下面的方法，通过存储过程的游标方法来实现：

建立存储过程：

代码

Create PROCEDURE P_InsertSubject
@SubjectId int
AS

DECLARE rs CURSOR LOCAL SCROLL FOR
select studentid from student where StudentGradu = 1

OPEN rs
FETCH NEXT FROM rs INTO @tempStudentID
WHILE @@FETCH_STATUS = 0
BEGIN
Insert SelSubject values ( @SubjectId , @tempStudentID )
FETCH NEXT FROM rs INTO @tempStudentID
END
CLOSE rs
GO

 

 

使用游标对记录集循环进行处理的时候一般操作如以下几个步骤：

1、把记录集传给游标;

2、打开游标

3、开始循环

4、从游标中取值

5、检查那一行被返回

6、处理

7、关闭循环

8、关闭游标

上面这种方法在性能上面无疑已经是提高很多了，但我们也想到，在存储过程编写的时候，有时候我们尽量少的避免使用游标来进行操作，所以我们还可以对上面的存储过程进行改造，使用下面的方法来实现：

代码

Create PROCEDURE P_InsertSubject
@SubjectId int
AS

declare @i int ,
@studentid

DECLARE @tCanStudent TABLE
(
studentid int
,FlagID TINYINT
)

BEGIN
insert @tCanStudent select studentid, 0 from student where StudentGradu = 1
SET @i = 1
WHILE ( @i >= 1 )
BEGIN

SELECT @studentid = ''

SELECT TOP 1 @studentid = studentid FROM @tCanStudent WHERE flagID = 0
SET @i = @@ROWCOUNT

IF @i <= 0 GOTO Return_Lab

Insert SelSubject values ( @SubjectId , @studentid )
IF @@error = 0
UPDATE @tCanStudent SET flagID = 1 WHERE studentid = @studentid
Return_Lab:
END
End
GO

 

 

我们现在再来分析以上这个存储过程，它实现的方法是先把满足条件的记录集数据存放到一个表变量中，并且在这个表变量中增加一个FLAGID进行数据初始值为0的存放，然后去循环这个记录集，每循环一次，就把对应的FLAGID的值改成1，然后再根据循环来查找满足条件等于0的情况，可以看到，每循环一次，处理的记录集就会少一次，然后循环的往选好课程表里面插入，直到记录集的条数为0时停止循环，此时完成操作。

比较以上的几种循环方法的应用，就会知道，有时候可能对于同一种功能我们实现的方法不同，而最终应用程序性能的影响的差异就会很大，第二种、第三种就大大的减少的数据库交互I/O操作的频繁，会节省很多时间，方法三又避免用游标又可以节省不必要的开销。

转载于:https://www.cnblogs.com/Sue_/articles/1702005.html