Bundle several update and insert statements into one procedure : Update Data « PL SQL « Oracle PL / SQL






Bundle several update and insert statements into one procedure

    
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> alter table dept add tot_sal number;

Table altered.

SQL>
SQL> update dept set tot_sal = ( select sum(sal) from emp where deptno = dept.deptno );

4 rows updated.


SQL>
SQL> create table EMP_AUDIT (date_rec date, empno number, sal number(4));

Table created.

SQL>
SQL> insert into emp_deltas select empno, null from emp;

10 rows created.


SQL>
SQL> create or replace procedure UPDATE_EMP(id number, p_sal number) is
  2   begin
  3       update DEPT set TOT_SAL = TOT_SAL + ( select p_sal-sal from EMP where empno = id );
  4
  5       update EMP set sal = p_sal where empno = id;
  6
  7       update EMP_DELTAS set change_type = 'SAL' where empno = id;
  8
  9       insert into EMP_AUDIT values (sysdate,id,p_sal);
 10
 11   exception
 12   when others then
 13       rollback;
 14       raise;
 15   end;
 16  /

Procedure created.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

SQL> drop table dept;

Table dropped.

   
    
    
    
  








Related examples in the same category

1.UPDATE statement can be used within PL/SQL programs to update a row or a set of rows
2.Select for update
3.Check row count being updating
4.Update with variable
5.Two UPDATE statements.
6.Check SQL%ROWCOUNT after updating
7.Exception handling for update statement
8.Update salary with stored procedure
9.Update table and return if success
10.Decrease salary with user procedure
11.Change price and output the result
12.Run an anonymous block that updates the number of book IN STOCK
13.Run an anonymous block that updates the number of pages for this book
14.Run the anonymous block to update the position column
15.Ajust price based on price range
16.Use procedure to update table