#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
|
#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;
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.
Update #3 : Update name,city and salary.
SQL> EXEC p_updateStuInfo(1002,'JOHN','LONDON',5000);
Setp 4 : Check UPDATED values.
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