Oracle和MSSQL中循环的使用
Oracle和MSSQL中循环的使用
1、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;
2、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
评论暂时关闭