PostgreSQL Foundation Finishing (II.)

Stored Procedures

Implementation function: For salary scale under 30 years of age, salary increase 10% 30 to 40 increase 20% 40 + 30% + bonus (admission) return average compensation

• To create a table:

DROP TABLEEmps;CREATE TABLEEmps (useridint PRIMARY KEY, ageint, salary numeric);INSERT  intoEmpsVALUES(Ten, -,1000.0),( One, -,1500.0),( A, -,1300.0),( -, *,3000.0), ( -, $,4000.0);

• To create a stored procedure:

    DROP FUNCTION IF EXISTSadd_salary (_bonus numeric); CREATE OR REPLACE FUNCTIONadd_salary (_bonus numeric)RETURNSNumeric as$body $DECLARELevel1 Numeric:=  -; Level2 Numeric:= +; Res Numeric:= 0; Pageint; Puseridint;                     MyCursor Refcursor; BEGIN            OPENMyCursor for SELECTUserID, age fromEmps; FETCHMyCursor intoPuserid, page; RAISE NOTICE'Age is :----%', Puserid||','||page;  whileFOUND LOOPIFPage<=Level1 Then                    UPDATEEmpsSETSalary=Salary* 1.1 WHEREUserid=Puserid; elsif page>Level1 andPage<=Level2 Then                    UPDATEEmpsSETSalary=Salary*1.2 WHEREUserid=Puserid; ELSE                    UPDATEEmpsSETSalary=Salary*1.3 WHEREUserid=Puserid; END IF; FETCHMyCursor intoPuserid, page; ENDLOOP; SELECT AVG(SALARY) fromEmps intoRes; RETURNRes; END$body $ Language plpgsql;

• Execution results

PostgreSQL Foundation Finishing (II.)