变量范围嵌套

SQLScript 支持在嵌套块中使用局部变量声明。局部变量仅在定义它们的块范围内可见。还可以在 LOOP / WHILE /FOR / IF-ELSE 控制结构中定义局部变量。

考虑以下代码：

CREATE PROCEDURE nested_block(OUT val INT) LANGUAGE SQLSCRIPT 
READS SQL DATA AS   
BEGIN       
	DECLARE a INT = 1;       
	BEGIN           
		DECLARE a INT = 2;          
		BEGIN               
			DECLARE a INT;               
			a = 3;           
		END;           
		val = a;       
	END;          
END;

调用此过程时，结果为：

call nested_block(?)
--> OUT:[2]

在此结果中，您可以看到内部最嵌套的块值 3 尚未传递到 val 变量。现在，让我们重新定义过程，而不使用最内部的 DECLARE 语句：

DROP PROCEDURE nested_block;   
CREATE PROCEDURE nested_block(OUT val INT) LANGUAGE SQLSCRIPT 
READS SQL DATA AS   
BEGIN       
	DECLARE a INT = 1;       
	BEGIN           
		DECLARE a INT = 2;          
		BEGIN                             
			a = 3;           
		END;           
		val = a;       
	END;          
END;

现在，当您调用此修改过程时，结果为：

call nested_block(?)
--> OUT:[3]

在此结果中，您可以看到最内层的嵌套块使用了在第二级嵌套块中声明的变量。

控制结构中的局部变量

条件

CREATE PROCEDURE nested_block_if(IN inval INT, OUT val INT) LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
    DECLARE a INT = 1;
    DECLARE v INT = 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        val = :a;
    END;

    v = 1 /(1-:inval);

    IF :a = 1 THEN
        DECLARE a INT = 2;
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            val = :a;
        END;

        v = 1 /(2-:inval);

        IF :a = 2 THEN
            DECLARE a INT = 3;
            DECLARE EXIT HANDLER FOR SQLEXCEPTION
            BEGIN
                val = :a;
            END;
            v = 1 / (3-:inval);
        END IF;
        v = 1 / (4-:inval);
    END IF;
    v = 1 / (5-:inval);
END;

call nested_block_if(1, ?)
-->OUT:[1]

call nested_block_if(2, ?)
-->OUT:[2]

call nested_block_if(3, ?)
-->OUT:[3]

call nested_block_if(4, ?)
--> OUT:[2]

call nested_block_if(5, ?)
--> OUT:[1]

While 循环

CREATE PROCEDURE nested_block_while(OUT val INT) LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
    DECLARE v int = 2;
	val = 0;
    WHILE v > 0
    DO
        DECLARE a INT = 0;
        a = :a + 1;
        val = :val + :a;
        v = :v - 1;
    END WHILE;
END;

call nested_block_while(?)
--> OUT:[2]

For 循环

CREATE TABLE mytab1(a int);
CREATE TABLE mytab2(a int);
CREATE TABLE mytab3(a int);
INSERT INTO mytab1 VALUES(1);
INSERT INTO mytab2 VALUES(2);
INSERT INTO mytab3 VALUES(3); 
CREATE PROCEDURE nested_block_for(IN inval INT, OUT val INT) LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
    DECLARE a1 int default 0;
    DECLARE a2 int default 0;
    DECLARE a3 int default 0;
    DECLARE v1 int default 1;
    DECLARE v2 int default 1;
    DECLARE v3 int default 1;
    DECLARE CURSOR C FOR SELECT * FROM mytab1;

    FOR R as C DO
        DECLARE CURSOR C FOR SELECT * FROM mytab2;
        a1 = :a1 + R.a;

        FOR R as C DO
            DECLARE CURSOR C FOR SELECT * FROM mytab3;
            a2 = :a2 + R.a;

            FOR R as C DO
                a3 = :a3 + R.a;
            END FOR;
        END FOR;
    END FOR;

    IF inval = 1 THEN
        val = :a1;
    ELSEIF inval = 2 THEN
        val = :a2;
    ELSEIF inval = 3 THEN
        val = :a3;
    END IF;
END;

call nested_block_for(1, ?)
--> OUT:[1]

call nested_block_for(2, ?)
--> OUT:[2]

call nested_block_for(3, ?)
--> OUT:[3]

循环备注以下示例使用在上述 For 循环示例中创建的表和值。

CREATE PROCEDURE nested_block_loop(IN inval INT, OUT val INT) LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
    DECLARE a1 int;
    DECLARE a2 int;
    DECLARE a3 int;
    DECLARE v1 int default 1;
    DECLARE v2 int default 1;
    DECLARE v3 int default 1;
    DECLARE CURSOR C FOR SELECT * FROM mytab1;

    OPEN C;
    FETCH C into a1;
    CLOSE C;

    LOOP
        DECLARE CURSOR C FOR SELECT * FROM mytab2;
        OPEN C;
        FETCH C into a2;
        CLOSE C;

        LOOP
            DECLARE CURSOR C FOR SELECT * FROM mytab3;
            OPEN C;
            FETCH C INTO a3;
            CLOSE C;
            IF :v2 = 1 THEN
                BREAK;
            END IF;
        END LOOP;
        IF :v1 = 1 THEN
            BREAK;
        END IF;
    END LOOP;

    IF :inval = 1 THEN
        val = :a1;
    ELSEIF :inval = 2 THEN
        val = :a2;
    ELSEIF :inval = 3 THEN
        val = :a3;
    END IF;
END;

call nested_block_loop(1, ?)
--> OUT:[1]

call nested_block_loop(2, ?)
--> OUT:[2]

call nested_block_loop(3, ?)
--> OUT:[3]