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

Validate information before inserting a row using a PL/SQL stored procedure: code - Oracle

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

In the previous stored procedure, we didnít do any validations for the information passed to it.  But validating data is the most important priority for any type of solution.

Validation of data can be achieved using several methods (at the database level).  The simplest one involves using constraints.  But constraints may not be suitable if we have any complex validations.  The better option is to use database triggers.  Database triggers are very similar to stored procedures, but they are automatically executed when the respective DML statement is issued on a table.  We can use database triggers to validate the information and to perform a few calculations as well. 

As we are dealing with stored procedures, I would like to deal with validations (or even calculations) using stored procedures.  Let us modify the previous stored procedure to handle a few validations as follows:

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
      Invalid_sal exception;
      Invalid_deptno    exception;
begin
      if p_sal<100 or p_sal>10000 then
            raise invalid_sal;
      end if;
      declare
            dummy_var   varchar(10);
      begin
            select 'exists' into dummy_var
            from dept
            where deptno = p_deptno;
      exception
            when no_data_found then
                  raise Invalid_deptno;
            when others then
                  raise_application_error(-20011, sqlerrm);
      end;
      insert into emp
      (
            empno,
            ename,
            sal,
            deptno
      )
      values
      (
            p_empno,
            p_ename,
            p_sal,
            p_deptno
      );
      Commit;
exception
      when invalid_sal then
            raise_application_error(-20001, 'Salary must be in
between 100 and 10000');
      when invalid_deptno then
            raise_application_error(-20001, 'Department doesn't
exist');
      when dup_val_on_index then
            raise_application_error(-20001, 'Employee already
exists');
      when others then
            raise_application_error(-20011, sqlerrm);
end;
/

The next section will give you a complete explanation for the above stored procedure.



 
 
>>> 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: