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

Deleting and retrieving values using PL/SQL stored procedures - 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.

  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



The following is the code which simply deletes a row from the table based on the employee number sent to it:

create or replace procedure p_emp_delete (p_empno emp.empno%type) as
      Invalid_empno exception;
      delete from emp
      where empno=p_empno;
      if sql%notfound or sql%rowcount=0 then
            raise Invalid_empno;
      end if;
      when invalid_empno then
            raise_application_error(-20001, 'Employee does not
      when others then
            raise_application_error(-20011, sqlerrm);

You can understand that the above code is very similar to the “p_emp_update” code given in previous section.  Now we shall proceed with retrieving information.

There exist several methods for retrieving information available within an Oracle database.  If you would like to retrieve one or very few definite values, then it is advisable to proceed with OUT parameters.  If you would like to retrieve an entire row (or set of rows), it is preferable to work with REF CURSORS.  If you require more information about REF CURSORS, I have already contributed a separate article on REF CURSORS, which you can easily find on this web site.

Now, in this scenario, I shall simply proceed with retrieving a set of values using OUTPUT parameters.

create or replace procedure p_emp_details(p_empno emp.empno%type,
p_ename OUT emp.ename%type, p_sal OUT emp.sal%type, p_deptno OUT
      select ename, sal, deptno
      into p_ename, p_sal, p_deptno
      from emp
      where empno = p_empno;
      when no_data_found then
            raise_application_error(-20001, 'Employee not
      when too_many_rows then
            /* this would not happen generally */
            raise_application_error(-20002, 'More employees exist
with the same number');
      when others then
            raise_application_error(-20003, SQLERRM);

In the above stored procedure, you can observe that all the parameters (except p_empno) are OUTPUT parameters.  They are mainly used to push the data OUT of the stored procedure to the calling program.  To execute the above stored procedure, you may have to use a separate script (or PL/SQL block) as follows:

      s     emp.sal%type;
      en    emp.ename%type;
      d     emp.deptno%type;
      p_emp_details(7369, en,s,d);
      dbms_output.put_line('name of employee: ' || en);
      dbms_output.put_line('Salary: ' || s);
      dbms_output.put_line('Deptno: '||d);

The above script simply declares a few variables to hold the values returned by the stored procedure.

My next article will look into the aspects of retrieving multiple rows using REF CURSOR, packages, helper routines etc.  My further upcoming articles will deal with DAL (Data Access Layer) using ODP.NET together with ASP.NET to access these stored procedures (for www.aspfree.com).  I hope you enjoyed this article and any comments, suggestions, feedback, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


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