Database Interaction with PL/SQL, part 2

This article picks up where part one left off. You will learn more about how to use %ROWTYPE and %TYPE, and be introduced to TYPE, RECORD, and TABLE declarations in PL/SQL.

This is part two of a series of articles focusing on database interactions with Oracle PL/SQL. In the previous article (part one), we covered the basics of retrieving information from the Oracle database using the SELECT..INTO statement as well as two attributes, namely “%TYPE” and “%ROWTYPE.” We concluded that article with some DML statements working within the PL/SQL program. In this article, we will examine some tips and tricks for using %ROWTYPE and %TYPE and finally concentrate a bit on TYPE, RECORD and TABLE declarations in PL/SQL.

Please note that all of the examples in this series have been tested only with Oracle 10g. I didn’t really test them with all the previous versions of Oracle. I suggest you to refer the documentation of the version you are using, if any of the programs failed to execute.

%ROWTYPE with INSERT and UPDATE

We already learned that %ROWTYPE can be used to declare a variable to hold an entire row of information. But how can we use it within DML statements directly? Let me explain through a very simple example.

declare

v_dept dept%rowtype;

begin

v_dept.deptno := 60;

v_dept.dname := ‘sample’;

v_dept.loc := ‘chicago’;

insert into dept values v_dept;

end;

The above program illustrates that an “insert” statement can accept a variable of its own table based row type. In the above example, I declared a variable “v_dept,” which can store an entire row based on the structure of “dept” table (dept%rowtype). The first three statements within the body assign new department values. The “insert” statement gets all the column values from the variable “v_dept” and inserts them into “dept” table.

The above program concentrates only on the “insert” statement with the “%rowtype” variable. But how do we work with the “update” statement using the same type of variable? The following example illustrates that.

declare

v_dept dept%rowtype;

begin

v_dept.deptno := 60;

v_dept.dname := ‘sample2’;

v_dept.loc := ‘dallas’;

update dept set ROW=v_dept where deptno=v_dept.deptno;

end;


This program is very similar to the earlier program, except for the “update” statement. If you carefully observe the “update” statement, I used a new keyword, “ROW.” That performs the magic. The “ROW” can accept any variable of “%rowtype” belonging to its own table (in this case, it is “dept”). This “ROW” keyword assigns each and every column of dept with the values available in “v_dept.” And of course, the rest can be understood easily.

Note that we will not be able to work with “%rowtype” with the DELETE statement (except in the WHERE condition), as the DELETE statement does not actually insert or modify rows in the respective table.

{mospagebreak title=UPDATE with RETURNING clause}

UPDATE in PL/SQL can be enhanced with the RETURING clause to achieve wonderful results. This clause actually eliminates the use of the SELECT statement after an UPDATE to check the back table (or row) and present the updated result to the user. Let me explain this in more detail.

Let us consider that we need to increase the salary of an employee by 500 and return the updated salary to the user. Generally, in this situation, we would write our program as the following:

declare

v_sal emp.sal%type;

v_empno emp.empno%type := &empno;

begin

update emp set sal = sal + 500

where empno = v_empno;

select sal into v_sal from emp

where empno = v_empno;

dbms_output.put_line (‘Salary increased to ‘ || v_sal);

end;

This is a very old and traditional way of writing the program. We can observe from the above program that there will be two round trips to the database. One round trip is for the UPDATE statement, and the other is for the SELECT statement. We can turn those two round trips to the database into one by using the RETURNING clause. The following program illustrates that.

declare

v_sal emp.sal%type;

v_empno emp.empno%type := &empno;

begin

update emp set sal = sal + 500

where empno = v_empno

RETURNING sal INTO v_sal;

dbms_output.put_line (‘Salary increased to ‘ || v_sal);

end;

I hope the above program is self-explanatory. But, what if I want a larger number of values to be returned? We can still use the same clause, but separated by commas, as shown in the following example.

Declare

v_ename emp.ename%type;

v_sal emp.sal%type;

v_empno emp.empno%type := &empno;

begin

update emp set sal = sal + 500

where empno = v_empno

RETURNING ename,sal INTO v_ename, v_sal;

dbms_output.put_line (‘Salary increased to ‘ || v_sal || ‘ for ‘ || v_ename);

end;

{mospagebreak title=TYPE with RECORD declaration}

Until now, we have been working either with %TYPE or %ROWTYPE. This means we are working with either one value or one complete record. How do we create our own data type, with our own specified number of values to hold? This is where TYPE and RECORD come in. Those who are familiar with the C language can consider this to be a structure declaration. Let me explain this to you in detail.

Let us consider a table of about 20 columns.  I always need to work with only seven of those columns. If I use %ROWTYPE, I get all 20 values unnecessarily. At the same time, my program will be bit clumsy if I use seven %TYPE declarations. A better way to solve this solution is by defining my own data type, which can hold seven values. Then I declare the variables of my new data type and work with them. The following example illustrates this.

declare

TYPE t_emprec IS RECORD

(

name emp.ename%type,

salary emp.sal%type,

job emp.job%type

);

v_emp t_emprec;

v_empno emp.empno%type := &empno;

begin

select ename,sal,job into v_emp

from emp where empno = v_empno;

dbms_output.put_line (‘Name : ‘ || v_emp.name);

dbms_output.put_line (‘Salary : ‘ || v_emp.salary);

dbms_output.put_line (‘Job : ‘ || v_emp.job);

end;

Let us examine the above program part by part. First, consider the following:

TYPE t_emprec IS RECORD

(

name emp.ename%type,

salary emp.sal%type,

job emp.job%type

);

The above defines a new data type named “t_emprec” (just like %ROWTYPE with limited specified fields) which can hold three fields, namely “name,” “salary” and “job.”

v_emp t_emprec;

The above statement declares a variable “v_emp” based on the datatype “t_emprec.” This means that “v_emp” internally contains the fields “name,” “salary” and “job.”

select ename,sal,job into v_emp

from emp where empno = v_empno;

The above statement places the values of “ename,” “sal” and “job” into fields “name,” “salary” and “job” of the variable “v_emp” respectively.

dbms_output.put_line (‘Name : ‘ || v_emp.name);

dbms_output.put_line (‘Salary : ‘ || v_emp.salary);

dbms_output.put_line (‘Job : ‘ || v_emp.job);

And the above displays all the values available fields of the variable “v_emp.”

{mospagebreak title=Accessing more than one row in PL/SQL (TYPE with TABLE)}

In all of the previous sample programs, we discussed retrieving and manipulating only a single row. How about retrieving more than one (or a set of rows based on a SELECT statement)? Before going into too many explanations, let us go through the following beautiful example:

declare

type t_emptbl is table of emp%rowtype;

v_emptbl t_emptbl;

begin

select * bulk collect into v_emptbl from emp;

for i in v_emptbl.first .. v_emptbl.last

loop

dbms_output.put_line(v_emptbl(i).ename || ‘ earns ‘ || v_emptbl(i).sal);

end loop;

end;

The above program displays all of the employee names with their respective salaries. Here I introduced a new declaration, TYPE..IS TABLE. Let me explain the above program part by part.

type t_emptbl is table of emp%rowtype;

The above statement defines a new data type, “t_emptbl” as an in-memory table representation. This means that it can store any number of rows (just like a table) from the table “emp” (as we included “emp%rowtype”) in memory (without having any relation to the physical database). Make sure that it is only a data type. If you know the C language, you can think of it as a structured array.

v_emptbl t_emptbl;

The above statement declares a variable “v_emptbl” which is authorized to store information based on the table type defined by “t_emptbl.”

select * bulk collect into v_emptbl from emp;

This is the most important statement, which does all of the operations. If you remove BULK COLLECT, it would raise an error (because you cannot work with more than one row at a time). The BULK COLLECT lets you fetch any number of rows based on the SELECT statement issued and store all of them in a TABLE typed variable. From the above statement, it is quite clear that we are retrieving all of the rows from the table “emp” and collecting them into the TABLE typed variable “v_emptbl.”

Now the next issue is to fetch all those rows of values and display them back on the screen. In general, each and every row of the TABLE typed variable is indexed, starting from one (just like an array). We retrieve each and every row with that index, using a FOR loop.

for i in v_emptbl.first .. v_emptbl.last

loop

dbms_output.put_line(v_emptbl(i).ename || ‘ earns ‘ || v_emptbl(i).sal);

end loop;

The above part is a FOR loop that starts from the first index of the TABLE typed variable “v_emptbl” and ends at the last index of the same. Here “first” and “last” are keywords which can be used with any TABLE typed variables. We retrieve each and every field of information by using the notation TABLE(INDEX).FIELDNAME (which is done in the DBMS_OUTPUT statement).

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

chat sex hikayeleri Ensest hikaye