PostgreSQL基礎整理（二）

標籤：

預存程序

實現功能：針對工資表30歲以下，工資提升10% 30至40提升20% 40以上提升30% + 獎金（入參）返回平均薪酬

• 建立表：

DROP TABLE emps;CREATE TABLE emps(userid int PRIMARY KEY, age int, salary numeric );INSERT INTO emps VALUES(10, 25, 1000.0),(11, 26, 1500.0),(12, 27, 1300.0),(13, 35, 3000.0), (14, 45, 4000.0);

• 建立預存程序：

    DROP FUNCTION IF EXISTS add_salary(_bonus numeric );    CREATE OR REPLACE FUNCTION add_salary(_bonus numeric )    RETURNS numeric AS $body$        DECLARE        level1 numeric  := 30;        level2 numeric  :=40;        res numeric  := 0;        page int;        puserid int;        mycursor refcursor ;                     BEGIN            OPEN mycursor FOR SELECT userid, age FROM emps;             FETCH mycursor INTO puserid, page;            RAISE NOTICE ‘age is :----%‘,puserid||‘,‘||page;            WHILE FOUND LOOP                IF  page <= level1                 THEN                    UPDATE emps SET salary = salary * 1.1 WHERE userid = puserid;                ELSIF  page > level1 AND page <= level2                 THEN                    UPDATE emps SET salary = salary*1.2 WHERE userid = puserid;                ELSE                    UPDATE emps SET salary = salary*1.3 WHERE userid = puserid;                END IF;                                        FETCH mycursor INTO puserid, page;                            END LOOP;                            SELECT AVG(SALARY) FROM emps INTO res;                RETURN res;        END    $body$    Language plpgsql;

• 執行結果

PostgreSQL基礎整理（二）