存储过程中的循环语句

最新推荐文章于 2024-07-15 09:46:01 发布

telancs

最新推荐文章于 2024-07-15 09:46:01 发布

阅读量1.4k

点赞数

分类专栏： SQL 文章标签：存储 dataset parameters table 数据库 sql

SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

WHILE
设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。

语法
WHILE Boolean_expression
    { sql_statement | statement_block }
    [ BREAK ]
    { sql_statement | statement_block }
    [ CONTINUE ]

参数
Boolean_expression

返回 TRUE 或 FALSE 的表达式。如果布尔表达式中含有 SELECT 语句，必须用圆括号将 SELECT 语句括起来。

{sql_statement | statement_block}

Transact-SQL 语句或用语句块定义的语句分组。若要定义语句块，请使用控制流关键字 BEGIN 和 END。

BREAK

导致从最内层的 WHILE 循环中退出。将执行出现在 END 关键字后面的任何语句，END 关键字为循环结束标记。

CONTINUE

使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后的任何语句。

注释
如果嵌套了两个或多个 WHILE 循环，内层的 BREAK 将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句，然后下一个外层循环重新开始执行。

示例
A. 在嵌套的 IF...ELSE 和 WHILE 中使用 BREAK 和 CONTINUE
在下例中，如果平均价格少于 $30，WHILE 循环就将价格加倍，然后选择最高价。如果最高价少于或等于 $50，WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过 $50，然后退出 WHILE 循环并打印一条消息。

USE pubs
GO
WHILE (SELECT AVG(price) FROM titles) < $30
BEGIN
   UPDATE titles
      SET price = price * 2
   SELECT MAX(price) FROM titles
   IF (SELECT MAX(price) FROM titles) > $50
      BREAK
   ELSE
      CONTINUE
END
PRINT 'Too much for the market to bear'

B. 在带有游标的过程中使用 WHILE
以下的 WHILE 结构是名为 count_all_rows 过程中的一部分。下例中，该 WHILE 结构测试用于游标的函数 @@FETCH_STATUS 的返回值。因为 @@FETCH_STATUS 可能返回 –2、-1 或 0，所以，所有的情况都应进行测试。如果某一行在开始执行此存储过程以后从游标结果中删除，将跳过该行。成功提取 (0) 后将执行 BEGIN...END 循环内部的 SELECT 语句。

USE pubs
DECLARE tnames_cursor CURSOR
FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN
      SELECT @tablename = RTRIM(@tablename)
      EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
            + @tablename )
      PRINT ' '
   END
   FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

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

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

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

3) 点添加后选课结束。

我们如果有SQL语句

我们先建立三张表：

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 
)

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

--省略数据库连接
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操作的频繁，会节省很多时间，方法三又避免用游标又可以节省不必要的开销。