PostgreSQL存储过程(3)-流程控制语句

2023-01-29 10:18:41

流程控制

讲解条件语句，循环语句。

本次环境会用到表emp；下面是emp执行语句。

drop table emp;

create table EMP(

EMPNO numeric(4) not null,

ENAME varchar(10),

JOB varchar(9),

MGR numeric(4),

HIREDATE date,

SAL numeric(7 ),

COMM numeric(7 ),

DEPTNO numeric(2));

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7369, 'SMITH', 'CLERK', 7902, to_date('1980-12-17', 'YYYY-MM-DD'), 800, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('1981-02-20', 'YYYY-MM-DD'), 1600, 300, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7521, 'WARD', 'SALESMAN', 7698, to_date('1981-02-22', 'YYYY-MM-DD'), 1250, 500, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7566, 'JONES', 'MANAGER', 7839, to_date('1981-04-02', 'YYYY-MM-DD'), 2975, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('1981-09-28', 'YYYY-MM-DD'), 1250, 1400, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7698, 'BLAKE', 'MANAGER', 7839, to_date('1981-05-01', 'YYYY-MM-DD'), 2850, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7782, 'CLARK', 'MANAGER', 7839, to_date('1981-06-09', 'YYYY-MM-DD'), 2450, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7788, 'SCOTT', 'ANALYST', 7566, to_date('1987-04-19', 'YYYY-MM-DD'), 3000, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7839, 'KING', 'PRESIDENT', null, to_date('1981-11-17', 'YYYY-MM-DD'), 5000, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7844, 'TURNER', 'SALESMAN', 7698, to_date('1981-09-08', 'YYYY-MM-DD'), 1500, 0, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7876, 'ADAMS', 'CLERK', 7788, to_date('1987-05-23', 'YYYY-MM-DD'), 1100, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7900, 'JAMES', 'CLERK', 7698, to_date('1981-12-03', 'YYYY-MM-DD'), 950, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7902, 'FORD', 'ANALYST', 7566,to_date('1981-12-02', 'YYYY-MM-DD'), 3000, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7934, 'MILLER', 'CLERK', 7782, to_date('1982-01-23', 'YYYY-MM-DD'), 1300, null, 10);

1. 条件语句

　IF和CASE语句让你可以根据某种条件执行命令。

　PL/pgSQL有三种形式的IF：

IF ... THEN ... END IF;

IF ... THEN ... ELSE ... END IF;

IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF;

以及两种形式的CASE:

CASE ... WHEN ... THEN ... ELSE ... END CASE;

CASE WHEN ... THEN ... ELSE ... END CASE;

1.1 IF条件语句

语法结构：

IF search_condition THEN

  statement_list

END IF;

-----------------------

IF search_condition THEN

    statement_list

ELSE

    statement_list

END IF;

-----------------------

IF search_condition THEN

    statement_list

ELSIF search_condition THEN

    statement_list

ELSE

    statement_list

END IF;

案例1：给员工薪水少于1500元提示“多加工资”；反之。

CREATE OR REPLACE FUNCTION if_test(vc_empno NUMERIC)

RETURNS void

AS $$

DECLARE

vn_sal numeric;

BEGIN

    select sal into vn_sal from emp where empno = vc_empno;

    IF vn_sal is null THEN

     RAISE NOTICE '该员工不存在！';

    ELSIF vn_sal < 1500 THEN

     RAISE NOTICE '老板；请多加点工资';

    ELSE

     RAISE NOTICE '老板；请少加点工资';

    END IF;

END;

$$ LANGUAGE PLPGSQL;

调用测试

lottu=# SELECT if_test(7399);

NOTICE:  该员工不存在！

lottu=# SELECT if_test(7369);

NOTICE:  老板；请多加点工资

lottu=# SELECT if_test(7566);

NOTICE:  老板；请少加点工资

1.2 CASE条件语句

-- 
CREATE OR REPLACE FUNCTION case_test1(x NUMERIC)

RETURNS void

AS $$

DECLARE

msg text;

BEGIN

    CASE x

    WHEN 1, 2 THEN

        msg := 'one or two';

    ELSE

        msg := 'other value than one or two';

   END CASE;

  RAISE NOTICE 'look it, it is %' ,msg;

END;

$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION case_test2(x NUMERIC)

RETURNS void

AS $$

DECLARE

msg text;

BEGIN

  -- msg :=  CASE WHEN x in (1,2) THEN 'one or two' ELSE 'other value than one or two' END;

   CASE

    WHEN x in (1,2) THEN

        msg := ''one or two'';

    ELSE

        msg := 'other value than one or two';

    END CASE;

  RAISE NOTICE 'look it, it is %' ,msg;

END;

$$ LANGUAGE PLPGSQL;

2.循环

2.1 简单循环

[ <<label>> ]

LOOP

    statements

    EXIT [ label ] [ WHEN boolean-expression ];

END LOOP [ label ];

LOOP定义一个无条件的循环，无限循环，直到由EXIT或RETURN语句终止。
可选的label可以由EXIT和CONTINUE语句使用，用于在嵌套循环中声明应该应用于哪一层循环。
如果声明了WHEN，循环退出只有在boolean-expression为真的时候才发生，否则控制会落到EXIT后面的语句上。

案例1：输出一个1-10的列表

CREATE OR REPLACE FUNCTION LOOP_TEST_01()

RETURNS void

AS $$

DECLARE

n numeric := 0;

BEGIN

  LOOP

    n := n + 1;

    RAISE NOTICE 'n 的当前值为: %',n;

    EXIT WHEN n >= 10;

  END LOOP;

END;

$$ LANGUAGE PLPGSQL;

2.2 WHILE 循环

语法：

WHILE boolean-expression LOOP

    statements;

END LOOP;

只要条件表达式（boolean-expression）为真，WHILE语句就会不停的在一系列语句上进行循环，条件是在每次进入循环体的时候检查的。

CREATE OR REPLACE FUNCTION LOOP_TEST_02()

RETURNS void

AS $$

DECLARE

n numeric := 0;

BEGIN

    WHILE n < 10 LOOP

    n := n + 1;

    RAISE NOTICE 'n 的当前值为: %',n;

  END LOOP;

END;

$$ LANGUAGE PLPGSQL;

2.3. FOR (integer variant)

语法：

FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP

    statements

END LOOP [ label ];

每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT 退出循环。

FOR i IN 1..10 LOOP

    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop

END LOOP;

FOR i IN REVERSE 10..1 LOOP

    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop

END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP

    -- i will take on the values 10,8,6,4,2 within the loop

END LOOP;

2.4 for遍历命令结果

语法：

FOR target IN query LOOP

    statements

END LOOP [ label ];

这种在实际工作过程中；由于需要用到游标，经常用它来替换游标。

在这里注意到是：i变量必须要声明为RECORD; 这在oracle的存储过程没这个设置。

CREATE OR REPLACE FUNCTION LOOP_TEST_03()

RETURNS void

AS $$

DECLARE

    i RECORD;

BEGIN

   FOR i IN select ename,job from emp where deptno = 20 loop

     RAISE NOTICE '% job is %', i.ename ,i.job;

   end loop;

END;

$$ LANGUAGE PLPGSQL;

2.5 CONTINUE语句

语法：

CONTINUE [ label ] [ WHEN boolean-expression ];

CONTINUE可以用于所有类型的循环；它并不仅仅限于无条件循环,不会跳出循环。

CREATE OR REPLACE FUNCTION LOOP_TEST_04()

RETURNS void

AS $$

DECLARE

n numeric := 0;

BEGIN

    WHILE n < 10 LOOP

    n := n + 1;

    CONTINUE WHEN n = 5;

    RAISE NOTICE 'n 的当前值为: %',n;

  END LOOP;

END;

$$ LANGUAGE PLPGSQL;

2.6 EXIT语句

语法：

EXIT [ label ] [ WHEN boolean-expression ];

EXIT可以用于在所有的循环类型中，它并不仅仅限制于在无条件循环中使用。会跳出循环。

CREATE OR REPLACE FUNCTION LOOP_TEST_05()

RETURNS void

AS $$

DECLARE

n numeric := 0;

BEGIN

    WHILE n < 10 LOOP

       n := n + 1;

       EXIT WHEN n = 5;

       RAISE NOTICE 'n 的当前值为: %',n;

  END LOOP;

END;

$$ LANGUAGE PLPGSQL;

码农公寓