Greenplum中exit,return和label的注意事项

来源：互联网发布：淘宝代购没有中文标签编辑：程序博客网时间：2024/05/29 09:45

Greenplum中exit和return的注意事项

1. 先来看exit

CREATE OR REPLACE FUNCTION test_exit()RETURNS integer AS$$DECLARE     count int;BEGIN    count := 1;    LOOP        count := count + 1;        begin                     raise notice '<<<begin start>>> xact: %', count;                        EXECUTE 'select pg_sleep(1)';            IF count > 5 THEN                EXIT;       --这里使用EXIT只会跳出了begin, 而不会跳出LOOP循环            END IF;            raise notice '<<<begin end>>> not exit xact: %', count;        end;        raise notice '<<<loop>>> xact: %', count;        raise notice '==================================';    END LOOP;    return 1;END$$ LANGUAGE plpgsql;zhangyun_db=> select test_exit();NOTICE:  <<<begin start>>> xact: 2NOTICE:  <<<begin end>>> not exit xact: 2NOTICE:  <<<loop>>> xact: 2NOTICE:  ==================================NOTICE:  <<<begin start>>> xact: 3NOTICE:  <<<begin end>>> not exit xact: 3NOTICE:  <<<loop>>> xact: 3NOTICE:  ==================================NOTICE:  <<<begin start>>> xact: 4NOTICE:  <<<begin end>>> not exit xact: 4NOTICE:  <<<loop>>> xact: 4NOTICE:  ==================================NOTICE:  <<<begin start>>> xact: 5NOTICE:  <<<begin end>>> not exit xact: 5NOTICE:  <<<loop>>> xact: 5NOTICE:  ==================================NOTICE:  <<<begin start>>> xact: 6NOTICE:  <<<loop>>> xact: 6NOTICE:  ==================================NOTICE:  <<<begin start>>> xact: 7NOTICE:  <<<loop>>> xact: 7NOTICE:  ==================================................... 一直执行，此处省略

可以看到当count为6时，直接跳出begin...end语句块，begin...end剩下的语句都不执行，但是并没有跳出loop循环。

2. 下面我们来看一下return语句

CREATE OR REPLACE FUNCTION test_return()RETURNS integer AS$$DECLARE     count int;BEGIN    count := 1;    LOOP        count := count + 1;        begin                     raise notice '<<<begin start>>> xact: %', count;                        EXECUTE 'select pg_sleep(1)';            IF count > 5 THEN                return 0;            END IF;            raise notice '<<<begin end>>> not exit xact: %', count;        end;        raise notice '<<<loop>>> xact: %', count;        raise notice '==================================';    END LOOP;    return 1;END$$ LANGUAGE plpgsql;

我们可以看到，改成return后，当count为6时，退出整个函数。

3. 通过使用label控制循环

CREATE OR REPLACE FUNCTION test_label()RETURNS integer AS$$DECLARE     count int;BEGIN    count := 1;    <<loop1>>    LOOP        count := count + 1;        <<label>>        begin            EXECUTE 'select pg_sleep(1)';                        raise notice '<<<begin start>>> xact: %', count;            exit loop1 when count > 5 ;            raise notice '<<<begin end>>> not exit xact: %', count;        end label;        raise notice '<<<loop>>> xact: %', count;        raise notice '==================================';    END LOOP loop1;    return 1;END$$ LANGUAGE plpgsql;<pre name="code" class="sql">zhangyun_db=> select test_return();NOTICE:  <<<begin start>>> xact: 2NOTICE:  <<<begin end>>> not exit xact: 2NOTICE:  <<<loop>>> xact: 2NOTICE:  ==================================NOTICE:  <<<begin start>>> xact: 3NOTICE:  <<<begin end>>> not exit xact: 3NOTICE:  <<<loop>>> xact: 3NOTICE:  ==================================NOTICE:  <<<begin start>>> xact: 4NOTICE:  <<<begin end>>> not exit xact: 4NOTICE:  <<<loop>>> xact: 4NOTICE:  ==================================NOTICE:  <<<begin start>>> xact: 5NOTICE:  <<<begin end>>> not exit xact: 5NOTICE:  <<<loop>>> xact: 5NOTICE:  ==================================NOTICE:  <<<begin start>>> xact: 6 test_return -------------           0(1 row)zhangyun_db=>

可以看出使用label可以灵活控制循环的阶段，当初count为6时，直接跳转到loop循环外。

0 0