Update table and return if success : Update « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. Update

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> INSERT INTO EMP VALUES(13, 'Fake', 'Helper', 4,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

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 procedure UPDATE_EMP(id number, val number,isSuccess out boolean) is
  2   begin
  3   if val = 0 then
  4       isSuccess := false;
  5   else
  6      update EMP set SAL = SAL / val where empno = id;
  7   isSuccess := true;
  8   end if;
  9  end;
 10  /

Procedure created.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

SQL>