Decrease salary with user procedure : Update Data « PL SQL « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » PL SQL » Update Data 




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

Table created.



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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.



SQL>
SQL> select from emp;
Enter...

     Jack       Tester         6 20-02-1981   1600    300     30
     Wil        Tester         6 22-02-1981   1250    500     30
     Jane       Designer       9 02-04-1981   2975  [N/A]     20
     Mary       Tester         6 28-09-1981   1250   1400     30
     Chris      Designer       9 09-06-1981   2450  [N/A]     10
     Smart      Helper         4 09-12-1982   3000  [N/A]     20
     Peter      Manager    [N/A17-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

rows selected.

SQL> create or replace
  2   procedure UPDATE_EMP(p_empno number, p_decrease numberis
  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...

     Jack       Tester         6 20-02-1981   1600    300     30
     Wil        Tester         6 22-02-1981   1250    500     30
     Jane       Designer       9 02-04-1981   2975  [N/A]     20
     Mary       Tester         6 28-09-1981   1250   1400     30
     Chris      Designer       9 09-06-1981   2450  [N/A]     10
     Smart      Helper         4 09-12-1982   3000  [N/A]     20
     Peter      Manager    [N/A17-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

rows selected.

SQL> drop table emp;

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.Change price and output the result
11.Run an anonymous block that updates the number of book IN STOCK
12.Run an anonymous block that updates the number of pages for this book
13.Run the anonymous block to update the position column
14.Ajust price based on price range
15.Bundle several update and insert statements into one procedure
16.Use procedure to update table
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.