SQL - 運維篇

SQL筆記-While語法

MySQL while語法

### MySQL while語法
DELIMITER //
DROP PROCEDURE IF EXISTS Pgm_while_001;
CREATE PROCEDURE Pgm_while_001() BEGIN
	DECLARE flag INT DEFAULT 0;
	DECLARE tmphost VARCHAR(40);
	DECLARE num int default 0;
	DECLARE hostlist CURSOR FOR SELECT HostName FROM `sql_table1` WHERE IPv4Address != '';
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;
	OPEN hostlist;
	FETCH hostlist INTO tmphost;
	WHILE flag <> 1 DO
		SET @newipv4=(SELECT IPv4Address FROM `sql_table1` WHERE IPv4Address != '' AND HostName = tmphost LIMIT 0,1);
		SET @newmac=(SELECT MacAddress FROM `sql_table1` WHERE MacAddress != '' AND HostName = tmphost LIMIT 0,1);
		UPDATE `sql_table1` SET IPv4Address=@newipv4,MacAddress=@newmac WHERE HostName = tmphost AND IPv4Address = '';
        FETCH hostlist INTO tmphost;
	END WHILE;
	CLOSE hostlist;
END
//
DELIMITER ;

CALL Pgm_while_001();

MSSQL while語法-案例1

### MSSQL While語法
# 案例1
USE sql_database
DECLARE
    @Id uniqueidentifier,
	@UserCode nvarchar(50) ,
	@UserAlias nvarchar(50) ,
	@HostName nvarchar(50) ,
	@IPv4Address nvarchar(50) ,
	@MacAddress nvarchar(50) ,
	@LoginName nvarchar(50) ,
	@DeleteTime datetime
DECLARE id_cursor CURSOR
	FOR (SELECT TOP 1000 Id,UserCode,UserAlias,HostName,IPv4Address,MacAddress,LoginName,DeleteTime FROM sql_table1)
	OPEN id_cursor
	FETCH NEXT FROM id_cursor INTO @Id,@UserCode,@UserAlias,@HostName,@IPv4Address,@MacAddress,@LoginName,@DeleteTime
	WHILE @@FETCH_STATUS=0
	BEGIN
		SET @var001=CONVERT(nvarchar(36),@Id)+'.txt'
		INSERT INTO sql_table2
		VALUES (@Id,@UserCode,@UserAlias,@HostName,@IPv4Address,@MacAddress,@LoginName,@var001,@DeleteTime)
	FETCH NEXT FROM id_cursor INTO @Id,@UserCode,@UserAlias,@HostName,@IPv4Address,@MacAddress,@LoginName,@DeleteTime
END
CLOSE id_cursor
DEALLOCATE id_cursor

MSSQL while語法-案例2

### MSSQL While語法
# 案例2
USE sql_database
DECLARE
	@HostName nvarchar(50)
DECLARE a_cursor CURSOR
	FOR (SELECT HostName FROM sql_table1 WHERE IPv4Address='')
	OPEN a_cursor
	FETCH NEXT FROM a_cursor INTO @HostName
	WHILE @@FETCH_STATUS=0
	BEGIN
		UPDATE sql_table1 SET IPv4Address=(SELECT TOP 1 IPv4Address FROM sql_table1 WHERE HostName=@HostName and IPv4Address <> ''),
		MacAddress=(SELECT TOP 1 MacAddress FROM sql_table1 WHERE HostName=@HostName AND MacAddress <> '') WHERE HostName=@HostName AND IPv4Address = ''
		
	FETCH NEXT FROM a_cursor INTO @HostName
END
CLOSE a_cursor
DEALLOCATE a_cursor

參考來源

無文章來源地址http://www.zghlxwxcb.cn/news/detail-568359.html

到了這里，關(guān)于[個人筆記] SQL筆記-While語法的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！