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(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>
SQL> select * from emp;
Enter...
2 Jack Tester 6 20-02-1981 1600 300 30
3 Wil Tester 6 22-02-1981 1250 500 30
4 Jane Designer 9 02-04-1981 2975 [N/A] 20
5 Mary Tester 6 28-09-1981 1250 1400 30
7 Chris Designer 9 09-06-1981 2450 [N/A] 10
8 Smart Helper 4 09-12-1982 3000 [N/A] 20
9 Peter Manager [N/A] 17-11-1981 5000 [N/A] 10
10 Take Tester 6 08-09-1981 1500 0 30
13 Fake Helper 4 03-12-1981 3000 [N/A] 20
9 rows selected.
SQL> create or replace
2 procedure UPDATE_EMP(p_empno number, p_decrease number) is
3 begin
4 update EMP
5 set SAL = SAL / p_decrease
6 where empno = p_empno;
7 end;
8 /
Procedure created.
SQL> exec UPDATE_EMP(1,2);
PL/SQL procedure successfully completed.
SQL> exec UPDATE_EMP(1,0);
PL/SQL procedure successfully completed.
SQL>
SQL> select * from emp;
Enter...
2 Jack Tester 6 20-02-1981 1600 300 30
3 Wil Tester 6 22-02-1981 1250 500 30
4 Jane Designer 9 02-04-1981 2975 [N/A] 20
5 Mary Tester 6 28-09-1981 1250 1400 30
7 Chris Designer 9 09-06-1981 2450 [N/A] 10
8 Smart Helper 4 09-12-1982 3000 [N/A] 20
9 Peter Manager [N/A] 17-11-1981 5000 [N/A] 10
10 Take Tester 6 08-09-1981 1500 0 30
13 Fake Helper 4 03-12-1981 3000 [N/A] 20
9 rows selected.
SQL> drop table emp;
Table dropped.