Saturday 3 January 2015

How to update table's value using Oracle stored procedure

#Step 1 :Sample Table Stu_Info.
SQL> SELECT * from Stu_Info;

STUID
NAME
CITY
AMOUNT
1000
RAM
NEW DELHI
1000
1001
SACHIN
MUMBAI
2000
1002
SMITH
NEW YORK
3000
1003
CHENG
BEIJING
4000
Note : STUID is primary key in this table.


#Step 2 : Create Procedure p_updateStuInfo to update table's value.
SQL> CREATE OR REPLACE PROCEDURE p_updateStuInfo
         2 (p_stuid  IN number,
         3   p_name  IN VARCHAR2,
         4   p_city  IN VARCHAR2,
         5   p_amount IN NUMBER)
         6  IS
         7  BEGIN
         8     UPDATE  Stu_info SET
                  name = p_name,
                  city = p_city,
                  amount =p_amount  where stuid=p_stuid;
         9  END;
        10  /


Step 3 : Execute Procedure multiple times with different values.
Once you created procedure successfully, you can use multiple times passing simply parameters values.
Update #1 : Update City.
SQL> EXEC p_updateStuInfo(1000,'RAM','BANGALORE',1000);
Update #2 : Update Salary
SQL> EXEC p_updateStuInfo(1001,'SACHIN','MUMBAI',3000);
Update #3 : Update name,city and salary.
SQL> EXEC p_updateStuInfo(1002,'JOHN','LONDON',5000);


Setp 4 : Check UPDATED values.
SQL> SELECT * from Stu_Info;

STUID
NAME
CITY
AMOUNT
1000
RAM
BANGALORE
1000
1001
SACHIN
MUMBAI
3000
1002
JOHN
LONDON
5000
1003
CHENG
BEIJING
4000

No comments:

Post a Comment