Adjust salary with pl/sql : Insert « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Insert

Adjust salary with pl/sql

   
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );

Table created.



SQL> INSERT INTO EMP VALUES(2, 'Jack', 'Tester', 6,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(3, 'Wil', 'Tester', 6,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(4, 'Jane', 'Designer', 9,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(5, 'Mary', 'Tester', 6,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(6, 'Black', 'Designer', 9,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(7, 'Chris', 'Designer', 9,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES(8, 'Smart', 'Helper', 4,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(9, 'Peter', 'Manager', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES(10, 'Take', 'Tester', 6,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.



SQL> INSERT INTO EMP VALUES(13, 'Fake', 'Helper', 4,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.



SQL>
SQL> CREATE TABLE DEPT(
  2      DEPTNO NUMBER(2),
  3      DNAME VARCHAR2(14),
  4      LOC VARCHAR2(13)
  5  );

Table created.

SQL>
SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

1 row created.

SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');

1 row created.

SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');

1 row created.

SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

1 row created.

SQL>
SQL> create table empLog (
  2   ENAME VARCHAR2(20),
  3   HIREDATE DATE,
  4   SAL NUMBER(7,2),
  5   DNAME VARCHAR2(20),
  6   MIN_SAL VARCHAR2(1) );

Table created.

SQL>
SQL>
SQL> create or replace procedure report_sal_adjustment is
  2       avgSalary emp.sal%type;
  3       minSalary emp.sal%type;
  4       deptName dept.dname%type;
  5       cursor empList is select empno, ename, deptno, sal, hiredate from emp;
  6  begin
  7       for empRec in empList loop
  8           select avg(sal) into avgSalary from emp where deptno = empRec.deptno;
  9           if empRec.sal - avgSalary > 0 then
 10               select dept.dname, min(emp.sal) into deptName, minSalary from dept, emp where dept.deptno = empRec.deptno and emp.deptno = dept.deptno group by dname;
 11               if minSalary = empRec.sal then
 12                   insert into empLog values ( empRec.ename, empRec.hiredate, empRec.sal, deptName, 'Y');
 13               else
 14                   insert into empLog values ( empRec.ename, empRec.hiredate, empRec.sal, deptName, 'Y');
 15               end if;
 16           end if;
 17      end loop;
 18  end;
 19  /

Procedure created.

SQL>
SQL> exec report_sal_adjustment

PL/SQL procedure successfully completed.

SQL> /

Procedure created.

SQL>
SQL> select * from empLog;

ENAME                HIREDATE         SAL DNAME                M
-------------------- --------- ---------- -------------------- -
Black                01-MAY-81       2850 SALES                Y

SQL>
SQL> drop table empLog;

Table dropped.

SQL> drop table emp;

Table dropped.

SQL> drop table dept;

Table dropped.

   
    
    
  

Related examples in the same category