Decrease salary with user procedure : Procedure « Function Procedure Packages « Oracle PL/SQL Tutorial

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.

27.5.Procedure
	27.5.1.	Procedures
	27.5.2.	A procedure block.
	27.5.3.	Wrapping a task into a procedure
	27.5.4.	Storing PL/SQL procedure in the Database
	27.5.5.	Creating stored procedures
	27.5.6.	Execute a procedure
	27.5.7.	Demonstration of a nested procedure block
	27.5.8.	Create or replace a procedure
	27.5.9.	Calling a Procedure
	27.5.10.	Pass ROWTYPE to a procedure
	27.5.11.	Insert debug line to the stored procedure with DBMS_OUTPUT.PUT_LINE
	27.5.12.	Listing Stored Procedure Information
	27.5.13.	Decrease salary with user procedure
	27.5.14.	Forward Referencing