Oracle和MSSQL中循环的使用

•  ORACLE

CREATE OR REPLACE FUNCTION SETSTATE(OLDVALUE VARCHAR2, POS NUMBER, SVALUE VARCHAR2)
RETURN VARCHAR2
IS
RETURN_VALUE VARCHAR2 (20);
LEN NUMBER(8);
I NUMBER(8);
TEMP_VALUE VARCHAR2(1);
BEGIN
     LEN := LENGTH(OLDVALUE);
     IF POS > LEN THEN
        RETURN '指定的索引大于字符串的长度！';
     END IF;
     I := 0;
     RETURN_VALUE := '';
     FOR I IN 1..LEN LOOP
         TEMP_VALUE := SUBSTR(OLDVALUE, I, 1);
         IF I = POS THEN
            TEMP_VALUE := SVALUE;
         END IF;
         RETURN_VALUE := RETURN_VALUE || TEMP_VALUE;
     END LOOP;
     RETURN RETURN_VALUE;
END;

 

 

• MSSQL

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'SETSTATE')
 DROP FUNCTION SETSTATE
GO
CREATE FUNCTION SETSTATE(@OLDVALUE NVARCHAR(20), @POS INT, @SVALUE NVARCHAR(1))
RETURNS NVARCHAR(20)
AS
BEGIN
 DECLARE @RETURN_VALUE NVARCHAR(20),
 @LENGTH INT,
 @I INT,
 @TEMP_VALUE NVARCHAR(1);
 SET @LENGTH = LEN(@OLDVALUE);
 SET @I = 1;
 SET @RETURN_VALUE = '';
 IF(@POS > @LENGTH)
  SET @RETURN_VALUE = @OLDVALUE;
 ELSE
  BEGIN
   WHILE(@I <= @LENGTH)
   BEGIN
    SET @TEMP_VALUE = SUBSTRING(@OLDVALUE, @I, 1);
    IF (@I = @POS)
     SET @TEMP_VALUE = @SVALUE;
    SET @RETURN_VALUE = @RETURN_VALUE + @TEMP_VALUE;
    SET @I = @I + 1;
   END
  END
 RETURN @RETURN_VALUE;
END
GO