Home arrow Oracle arrow Developing Simple PL/SQL Stored Procedures for CRUD Operations

Developing Simple PL/SQL Stored Procedures for CRUD Operations

In this article, I shall go through a set of PL/SQL stored procedures which are very frequently used for CRUD operations. These stored procedures are mainly helpful for the developers who develop client applications (involving business logic or user interface design and programming) and who need a data layer to be implemented using PL/SQL stored procedures. The article is not targeted at pure PL/SQL developers.

TABLE OF CONTENTS:
  1. Developing Simple PL/SQL Stored Procedures for CRUD Operations
  2. Validate information before inserting a row using a PL/SQL stored procedure: code
  3. Validate information before inserting a row using a PL/SQL stored procedure: explanation
  4. How to update a row in a table using a PL/SQL stored procedure
  5. Deleting and retrieving values using PL/SQL stored procedures
By: Jagadish Chatarji
Rating: starstarstarstarstar / 41
February 19, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

All the examples in this series have been tested only with Oracle 10g (V10.2).  I didn’t really test any of the examples in any of the previous versions.  If you have any problems during the execution of these examples, please post in the discussion area. 

How to insert a row into a table using a PL/SQL stored procedure

The following is the stored procedure which deals with inserting a particular row into a table.

create or replace procedure p_emp_insert (p_empno emp.empno%type,
p_ename emp.ename%type, p_sal emp.sal%type, p_deptno emp.deptno%
type) as
begin
      insert into emp
      (
            empno,
            ename,
            sal,
            deptno
      )
      values
      (
            p_empno,
            p_ename,
            p_sal,
            p_deptno    
      );
      Commit;
exception
      when dup_val_on_index then
            raise_application_error(-20001, 'Employee already
exists');
      when others then
            raise_application_error(-20011, sqlerrm);
end;
/

The above is a stored procedure named “p_emp_insert” which accepts four parameters.  Once all the parameters are passed in, it executes an INSERT statement to insert (or add) those values in the form of a row.  The following is the code available in the error handling (or exception handling) section, which mainly deals with errors/exceptions raised at run-time:

      when dup_val_on_index then
            raise_application_error(-20001, 'Employee already
exists');
      when others then
            raise_application_error(-20011, sqlerrm);

I mainly worked with two of the pre-defined exceptions, namely “dup_val_on_index” and “others.”  The “dup_val_on_index” is mainly raised when the runtime tries to insert a row with a repeated value in a unique or primary column.  The “others” exception is raised when the runtime come across any other errors (apart from “dup_val_on_index,” as it is already handled).  The SQLERRM is a built-in item which mainly contains the error message for the error or exception raised.

To execute the above stored procedure, you can issue the following command at the SQL prompt:

SQL> EXEC p_emp_insert(1001, 'Jag', 3400, 30)



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: