摘要:[MSSQL][PLSQL]FOR LOOP
如果要用SQL新增語法 但是又要連續執行1~X
因為MSSQL沒有ORACLE的FOOR LOOP
所以MSSQL用這個
DECLARE @i int = 0
WHILE @i < 20 BEGIN
SET @i = @i + 1
/* do some work */
END
--------------------
下如範例應用
/****** 新增 SetPnlGrade&SetSinglePnlGrade ******/
/****** 用途:提供客戶的nlGrade判斷 ******/
if not exists (select * from sysobjects where name='SetPnlGrade' and xtype='U')
begin
CREATE TABLE SetPnlGrade
(
BCNo nvarchar(2),
[LineNo] nvarchar(1),
Value nvarchar(1),
Name nvarchar(10),
CONSTRAINT pk_SetPnlGrade_Id PRIMARY KEY (BCNo, [LineNo],Value)
);
DECLARE @i int = 1
WHILE @i < 20 BEGIN
INSERT INTO SetPnlGrade Values(@i,1,1,'G');
INSERT INTO SetPnlGrade Values(@i,1,2,'N');
INSERT INTO SetPnlGrade Values(@i,1,3,'R');
INSERT INTO SetPnlGrade Values(@i,1,4,'P');
INSERT INTO SetPnlGrade Values(@i,1,5,'1');
INSERT INTO SetPnlGrade Values(@i,1,6,'2');
INSERT INTO SetPnlGrade Values(@i,1,7,'3');
INSERT INTO SetPnlGrade Values(@i,1,8,'H');
SET @i = @i + 1
END
END
go
================================================
ORACLE的用法
- FOR i IN 1..10
- LOOP
- DBMS_OUTPUT.put_line('i = ' || i);
- END LOOP;