Disadvantage ignoring COMMIT Statement in Oracle stored procedure

DML(Data Manipulation Language) Statements like INSERT, UPDATE and DELETE is not Autocommit by nature. If you want to make permanent changes in table values by stored procedure you must use COMMIT Statement. 

Lets see the Disadvantage if you are not using COMMIT Statement inside stored procedure.

EXAMPLE  ( Without COMMIT Statement)

# Step 1 : Create a table & Insert rows by Stored Procedure

SQL> CREATE TABLE Stu_Info

            (

                 stuid number,

                 name varchar2(10),

                 city varchar2(10),

                 amount number

            );

#Step2 : Create Procedure p_insertStuInfo to insert record into table.

SQL> CREATE OR REPLACE PROCEDURE p_InsertStuInfo

          ( p_stuid IN number,

             p_name  IN VARCHAR2,

             p_city  IN VARCHAR2,

             p_amount IN NUMBER)

           IS

           BEGIN

              INSERT INTO Stu_info
                 values(p_stuid,p_name,p_city,p_amount);

           END;

           /

Step 3 : Execute Procedure multiple times with different values.

SQL> EXEC p_insertStuInfo(1000,'RAM','NEW DELHI',1000);

SQL> EXEC p_insertStuInfo(1001,'SACHIN','MUMBAI',2000);

SQL> EXEC p_insertStuInfo(1002,'SMITH','NEW YORK',3000);

SQL> EXEC p_insertStuInfo(1003,'CHENG','BEIJING',4000);

Setp 4 : Check table records.

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

Setp 5 : Execute ROLLBACK & recheck table's data.

SQL> ROLLBACK;

SQL> SELECT * from Stu_Info;

no rows available;

#Disadvantage ignoring COMMIT Statement

If somebody execute ROLLBACK Statement you may lost all inserted values. Even if the user don't know the structure and the name of your table he or she can easily execute rollback statement.

How to delete table's row using Oracle stored procedure

# Step 1 : Create table.

SQL> CREATE TABLE Stu_Info

            (

                 stuid number,

                 name varchar2(10),

                 city varchar2(10),

                 amount number

            );

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.

#Step2 : Create Procedure p_deleteStuInfo to delete table's row.

SQL> CREATE OR REPLACE PROCEDURE p_deleteStuInfo

          (p_stuid  IN stu_info.stuid%TYPE)

           IS

           BEGIN

              DELETE  Stu_info  where stuid=p_stuid;

           END;

 SQL> /

Step 3 : Execute Procedure to delete table's row.
Once you created procedure successfully, you can use it multiple times simply passing parameter's value .
Execute Procedure# : Delete rows where stuid is 1000.

SQL> EXEC p_deleteStuInfo(1000);

Setp 4 : Check table Stu_info after DELETE.

SQL> SELECT * from Stu_Info;

STUID	NAME	CITY	AMOUNT
1001	SACHIN	MUMBAI	2000
1002	SMITH	NEW YORK	3000
1003	CHENG	BEIJING	4000

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

How to insert values into table using Oracle stored procedure

Stored procedure is widely used by programmer to insert record into tables. IN parameter is used to insert values into tables.  

Steps
1. create simple table in which you want to insert values by procedure.
2. create procedure to insert record in table.
3. execute stored procedure multiple times to insert values into table.
4. Now use select command to view table's values.

# Step 1 : Create table.

SQL> CREATE TABLE Stu_Info

            (

                 stuid number,

                 name varchar2(10),

                 city varchar2(10),

                 amount number

            );

#Step2 : Create Procedure p_insertStuInfo to insert record into table.

SQL> CREATE OR REPLACE PROCEDURE p_InsertStuInfo

          ( p_stuid IN number,

             p_name  IN VARCHAR2,

             p_city  IN VARCHAR2,

             p_amount IN NUMBER)

           IS

           BEGIN

              INSERT INTO Stu_info
                 values(p_stuid,p_name,p_city,p_amount);

           END;

           /

Step 3 : Execute Procedure multiple times with different values.
Once you created procedure successfully, you can use multiple times passing simply parameters values.

SQL> EXEC p_insertStuInfo(1000,'RAM','NEW DELHI',1000);

SQL> EXEC p_insertStuInfo(1001,'SACHIN','MUMBAI',2000);

SQL> EXEC p_insertStuInfo(1002,'SMITH','NEW YORK',3000);

SQL> EXEC p_insertStuInfo(1003,'CHENG','BEIJING',4000);

Setp 4 : Check table records.

Now you can check Stu_Info table 
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

Sample Stored Procedure in Oracle

A Stored Procedure is a named PL/SQL code block. It is a logically grouped set of SQL and PL/SQL statements that perform a specific task. A Procedure can be compiled and stored in database as schema object. Procedures promote reusability and maintainability. when validated, they can be used in any number of application.

 

Syntax of stored procedure

CREATE OR REPLACE PROCEDURE Procedure_Name

(parameter_name1 IN/OUT/IN OUT datatype1,

  parameter_name2 IN/OUT/IN OUT datatype2,

  .......................................................................)

IS/AS

          variable declarations;

          constant declarations;

BEGIN

         PL/SQL statements block;

EXCEPTION

        exception PL/SQL block;

END;

 

NOTE:

1. You can create procedure without any parameter.

2. Variable and constant declarations is also optional.

3. Exception is also an optional statement in procedure.

Sample Procedure#1 : Display Simple Message

SQL> CREATE OR REPLACE PROCEDURE Proc_DisplayMsg

  2  IS

  3  BEGIN

  4  DBMS_OUTPUT.PUT_LINE('Welcome to oracle.readerviewer.com');

  5  END;

  6  /

NOTE : On Set serveroutput to display message on sql prompt.

SQL> SET SERVEROUTPUT ON

SQL> EXEC PROC_DisplayMsg;

Result : Welcome to oracle.readerviewer.com 

Sample Procedure#2 : Display Message using IN Parameter

SQL> CREATE OR REPLACE PROCEDURE 

     Proc_DisplayMsg_IN(vName IN varchar2)

  2  IS

  3  BEGIN

  4  DBMS_OUTPUT.PUT_LINE('You are watching ' || vName);

  5  END;

  6  /

SQL> EXEC PROC_DISPLAYMSG_IN('STAR MOVIES');

Result : You are watching STAR MOVIES                               

Sample Procedure#3 : Addition of two No using IN Parameter

SQL> CREATE OR REPLACE PROCEDURE

     Proc_Addition(vNo1 IN Number,vNo2 IN number)

  2  IS

  3  BEGIN

  4  DBMS_OUTPUT.PUT_LINE('Addition of two no is ' || (vNo1+vNo2));

  5  END;

  6  /

SQL> EXEC PROC_ADDITION(10,20);

Result : Addition of two no is 30  

Sample Procedure#4 : Multiplication of two No using IN Parameter

SQL> CREATE OR REPLACE PROCEDURE

     Proc_Multiplication(vNo1 IN Number,vNo2 IN number)

  2  IS

  3  BEGIN

  4  DBMS_OUTPUT.PUT_LINE('Multiplication of two no is ' |(vNo1*vNo2));

  5  END;

  6  /

SQL> EXEC PROC_MULTIPLICATION(10,20);

Result : Multiplication of two no is 200

  

Sample Procedure#5 : Division of  No using IN Parameter

SQL> CREATE OR REPLACE PROCEDURE

     Proc_Division(vNo1 IN Number,vNo2 IN number)

  2  IS

  3  BEGIN

  4  DBMS_OUTPUT.PUT_LINE('Division of two no is ' || (vNo1/vNo2));

  5  END;

  6  /

SQL> EXEC Proc_Division(100,20);

Result : Division of two no is 5