存储过程--表变量和while简单运用

1、sql创建表
/*订单*/
CREATE TABLE Orders
(
PRIMARY KEY(Id),
Id int,
Name varchar(20)
)
2、存储过程ProTestDT
    1)创建表变量@NewDT
    2)给表变量@NewDT赋值(INSERT)
    3)循环表变量@NewDT,更新实际表dbo.Orders

 CREATE PROCEDURE ProTestDT
AS
BEGIN
DECLARE @NewDT TABLE
(
dtID int,
dtName varchar(20)
) INSERT INTO @NewDT(dtID,dtName) SELECT id,Name FROM dbo.Orders DECLARE @c INT SET @c=0
DECLARE @i INT SET @i=0
DECLARE @id INT
DECLARE @name varchar(20) SELECT @c=COUNT(*) FROM @NewDT
PRINT @c WHILE(@i<@c)
BEGIN
SELECT TOP 1 @id=dtID,@name=dtName FROM @NewDT /*表变量查询一条数据*/
SET @name=CONVERT(VARCHAR,@id)+@name+CONVERT(VARCHAR,@id)
UPDATE dbo.Orders SET Name=@name WHERE id=@id /*更新表*/
DELETE FROM @NewDT WHERE dtID=@id /*表变量删除查询出来的数据*/ SET @i=@i+1
PRINT @i
END
END
上一篇:linux 下的 mkfifo、exec 命令使用


下一篇:Error querying database. Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'ItemsCustom' in 'class com.pojo.OrderDetailCustom