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.

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)

{mospagebreak title=Validate information before inserting a row using a PL/SQL stored procedure: code}

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.

{mospagebreak title=Validate information before inserting a row using a PL/SQL stored procedure: explanation}

This section explains the code provided in the previous section.  Let me start with the following:

      Invalid_sal       exception;
 
     Invalid_deptno    exception;

“Invalid_Sal” and “Invalid_Deptno” are two user-defined exceptions which can be raised within our logic according to our requirements.  The following is the code fragment which checks for the validity of salary:

      if p_sal<100 or p_sal>10000 then
            raise invalid_sal;
      end if;

In the above code fragment, if the salary is not in between 100 and 10000, it raises the user-defined exception “Invalid_sal.”  Once it is raised, the control (or flow of execution) jumps to the exception section, and gets the following to be executed:

      when invalid_sal then
            raise_application_error(-20001, ‘Salary must be in
between 100 and 10000′);

The above statement returns a user-defined message to the application which called the stored procedure.

Coming to the department validation, I need to check whether the given department number exists in the “dept” table or not.  If it is not available, I would like to raise an exception and send a message back to the user.  The following is the PL/SQL block which is nested into the main block to handle the same:

      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;

The above checks to see whether the department exists or not.  If it doesn’t exist, the SELECT statement fails.  If it fails, “no_data_found” gets raised, which in turn fires the “invalid_deptno” exception.  If any other error occurs, it simply raises a default error.

If the “Invalid_deptno” exception is raised, control (or flow of execution) jumps to the following:

      when invalid_deptno then
            raise_application_error(-20001, ‘Department doesn”t exist’);

{mospagebreak title=How to update a row in a table using a PL/SQL stored procedure}

The following is the stored procedure which can be used to modify the information belonging to an employee:

create or replace procedure p_emp_update (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;
      Invalid_empno 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;
      update emp set
            ename=p_ename,
            sal=p_sal,
            deptno=p_deptno
      where empno=p_empno;
      if sql%notfound or sql%rowcount=0 then
            rollback;
            raise Invalid_empno;
      end if;
      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 invalid_empno then
            raise_application_error(-20001, ‘Employee does not
exist’);
      when others then
            raise_application_error(-20011, sqlerrm);
end;
/

In the above stored procedure, the following is the code fragment which needs some attention:

      if sql%notfound or sql%rowcount=0 then
            rollback;
            raise Invalid_empno;
      end if;

The above IF condition evaluates whether the previous DML statement (UPDATE in this scenario) updated any information successfully or not.

{mospagebreak title=Deleting and retrieving values using PL/SQL stored procedures}

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;
begin
      delete from emp
      where empno=p_empno;
      if sql%notfound or sql%rowcount=0 then
            rollback;
            raise Invalid_empno;
      end if;
      commit;
exception
      when invalid_empno then
            raise_application_error(-20001, ‘Employee does not
exist’);
      when others then
            raise_application_error(-20011, sqlerrm);
end;
/

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
emp.deptno%type)
as
begin
      select ename, sal, deptno
      into p_ename, p_sal, p_deptno
      from emp
      where empno = p_empno;
exception
      when no_data_found then
            raise_application_error(-20001, ‘Employee not
found’);
      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);
end;
/

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:

declare
      s     emp.sal%type;
      en    emp.ename%type;
      d     emp.deptno%type;
begin
      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);
end;
/

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

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye