Database Interaction with PL/SQL, part 3

Jagadish Chatarji has been writing about database interactions with Oracle PL/SQL. The last part started on TYPE, RECORD, and TABLE declarations of PL/SQL. This one now goes further into TABLE, RECORD, and using them together. It will also introduce NESTED TABLES.

Please note that all the examples in this series have been tested only with Oracle 10g, not with all the previous versions of Oracle.  I suggest you to refer the documentation of respective version you are using if any of these programs fails to execute.

Accessing More Than One Row in PL/SQL (Using WHILE)

In our previous article, we have seen how to display more than one row using a FOR loop. Now, we will see the same program being used with WHILE loop. Let us go through the following example first.

declare
    type t_emptbl is table of emp%rowtype;
    v_emptbl    t_emptbl;
    i           integer;
begin
   
select * bulk collect into v_emptbl from emp;
   
i := v_emptbl.first;
    while i is not null
   
loop
       
dbms_output.put_line(v_emptbl(i).ename || ‘ earns ‘
          || v_emptbl(i).sal);
       
i := v_emptbl.next(i);
    end loop;
end;

In the above program, all the declarations and other parts are identical to that of the previous article, except that we used WHILE loop here. You may be wondering if the FOR loop is simpler than the WHILE loop. And of course, I admit it is. But by using this example, I wanted to introduce the collection method NEXT which can be used with a TABLE typed variable (whereas it may not be necessary when using a FOR loop).

In the above program, we use a variable ‘i’ as a counter. We initialize it using ‘v_emptbl.first’ and we increment it using ‘v_emptbl.next(i)’. If no more entries are available (beyond last index), it returns NULL into the variable ‘i’, which is being checked as a condition of the WHILE loop.

We can also display the result in reverse using collection method PRIOR (in combination with collection method LAST as initial value of index) as shown below.

declare
    type t_emptbl is table of emp%rowtype;
   
v_emptbl    t_emptbl;
   
i           integer;
begin
   
select * bulk collect into v_emptbl from emp;
   
i := v_emptbl.last;
   
while i is not null
   
loop
       
dbms_output.put_line(v_emptbl(i).ename || ‘ earns ‘
          || v_emptbl(i).sal);
       
i := v_emptbl.prior(i);
   
end loop;
end;

{mospagebreak title=Using TABLE Without Interacting With Database}

First of all, consider my apologies for inserting this topic inside an article titled as “database interactions using PL/SQL”. I thought that this would be necessary, as some of our logic depends on some PL/SQL tables which don’t always have to be filled with database information. And of course, using this topic, I can also introduce few new collection methods on working with PL/SQL tables.

Let us consider the following example. Even though it is bit lengthy, I can introduce all issues at once.

declare
   
type t_Numtbl is table of number;
   
v_numtbl t_numtbl := t_Numtbl(10,20,30,40,50,60,70);
begin
    dbms_output.put_line(‘Displaying complete List:’);
    for i in v_numtbl.first .. v_numtbl.last
    loop
       
dbms_output.put(v_numtbl(i) || ‘,’);
    end loop;
    dbms_output.new_line; –just skipping to next line
   
v_numtbl.delete(3);
   
dbms_output.put_line(‘Displaying complete List after deletion:’);
   
for i in v_numtbl.first .. v_numtbl.last
   
loop
       
if v_numtbl.exists(i) then
           
dbms_output.put(v_numtbl(i) || ‘,’);
       
end if;
   
end loop;
   
dbms_output.new_line;
   
dbms_output.put_line(‘No. of elements ‘ || v_numtbl.count);
end;

Let us consider the above program line by line. I don’t think I need to cover FOR loops in the above program, as they have been already covered in my previous article.

type t_Numtbl is table of number;

The above line defines ‘t_Numtbl’ as a new data type which can hold only a set of values of type ‘number’.

v_numtbl t_numtbl := t_Numtbl(10,20,30,40,50,60,70);

The above line declares and initializes the variable ‘v_numtbl’ with some sample values. Make a note that they are not from database! Within the body of the above program, I used FOR loops twice to display the contents of the variable ‘v_numtbl’.

v_numtbl.delete(3);

After displaying the values using the first FOR loop, I am deleting the third element (index starts from 1) from the table using the above statement.

if v_numtbl.exists(i) then
   
dbms_output.put(v_numtbl(i) || ‘,’);
end if;

The above condition checks to see if the specified index exists within the table or not before trying to display it. If you don’t include this, we would get an error.

dbms_output.put_line(‘No. of elements ‘ || v_numtbl.count);

You can use ‘v_numtbl.count’ to get the number of elements present within the PL/SQL table.

{mospagebreak title=Combining TABLE and RECORD}

In my previous article, I explained PL/SQL records. Now we shall combine RECORDs with TABLEs to achieve effective results in a simple way. Let us consider the following example.

 

declare
   
type t_empRec is record
   
(
       
ename emp.ename%type,
       
sal emp.sal%type,
       
deptno emp.deptno%type
   
);
   
type t_emptbl is table of t_emprec;
    v_emptbl    t_emptbl;
begin
   
select ename,sal,deptno 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 || ‘,’ ||
         v_emptbl(i).sal || ‘,’ || v_emptbl(i).deptno);
   
end loop;
end;

The above program retrieves ‘ename’, ‘sal’ and ‘deptno’ columns from ’emp’ table and displays all of those details. In my previous article, I displayed the same but used %ROWTYPE. Now in this program, I am combining the definitions of RECORD and TABLE to store only the data we need (but not the entire row). The most important statement in the above program is the following:

type t_emptbl is table of t_emprec;

That statement defines a PL/SQL table named ‘t_emptbl’. But the content (rows) within that table should match with the structure defined in the following declaration:

type t_empRec is record
(
   
ename emp.ename%type,
   
sal emp.sal%type,
   
deptno emp.deptno%type
);

So, indirectly ‘t_emptbl’ can have any number of rows with only the fields ‘ename’, ‘sal’ and ‘deptno’. This is a wonderful technique to define PL/SQL tables with our own fields. The rest of the program is just similar to the example I gave in my previous article.

{mospagebreak title=NESTED TABLES and PL/SQL}

NESTED TABLE is an excellent way to store nested information. We can implement ONE to MANY relationships within each record (in the same table) by using this concept. Even though it looks bit confusing at the beginning, it would be very easy to implement once we master it. Best of all, it can integrate with PL/SQL in the same way we do it in SQL. Instead of speaking a lot, let us go through an example.

First of all, execute these scripts from within SQL *Plus

CREATE TYPE SubjectList AS TABLE OF VARCHAR2(20);
/
CREATE TABLE employee (name VARCHAR2(20), Subjects
  SubjectList) NESTED TABLE Subjects STORE AS subjects_tab;

The above script would create a TYPE named ‘SubjectList’ (which can hold any number of string values) within the database itself! That means, you can use that data type to create columns for any number of tables. And the most wonder is that, it need not be redefined in the PL/SQL program. That is the advantage of NESTED TABLE. Now let us insert some rows.

Insert into employee values (‘win’, SubjectList(‘VB’,’.NET’,’J2EE’));
Insert into employee values (‘suni’, SubjectList(‘VB’,’MSSQL’,’.NET’));
Insert into employee values (‘jag’, SubjectList(‘Oracle’,’.NET’,’Java’));

The above script would insert three rows into the table ’employee’ with employee name and the subjects he/she is familiar with. Observe the constructor ‘SubjectList’ we are using within the INSERT statement. It is very important here, as we are working with TYPE in the form of a NESTED TABLE. How to get all the subjects of a particular employee? The following query provides the result:

select a.* from
table(select subjects from employee where name=’jag’) a

Now that we prepared data in SQL, we need to access the same in PL/SQL. Now you can go through the following PL/SQL script to access the data inserted above.

declare
    v_subjects SubjectList;
   
v_name employee.name%type := ‘&name’;
begin
   
select Subjects into v_subjects
   
from employee where name=v_name;
   
dbms_output.put_line(‘Subjects of ‘ || v_name);
   
dbms_output.put_line(‘———————–‘);
   
for i in v_subjects.first .. v_subjects.last
   
loop
       
dbms_output.put_line(v_subjects(i));
   
end loop;
end;

If you observe the above program, it doesn’t have any definition of ‘SubjectList’. Indeed it has been directly acquired from the database. I hope the rest is same.

{mospagebreak title=Using DML Commands on NESTED TABLES Within PL/SQL}

Just like in SQL, we can also insert, update or delete NESTED TABLE information from within PL/SQL, but using typed variables. Let us consider the following example which updates a row in the same table.

declare
   
v_subjects SubjectList;
   
v_name employee.name%type := ‘&name’;
begin
   
v_subjects := SubjectList(‘all databases’,’all languages’);
   
update employee set Subjects = v_subjects
   
where name=v_name;
   
dbms_output.put_line(‘Updated succesfully…’);
end;

I hope the example above is very much clear. And even in the same manner we can also insert into the same table something like the following:

declare
   
v_subjects SubjectList;
   
v_name employee.name%type := ‘&name’;
begin
   
v_subjects := SubjectList(‘all databases’,’all languages’);
   
insert into employee values(v_name, v_subjects);
   
dbms_output.put_line(‘inserted succesfully…’);
end;

Actually, it is bit difficult to do some kinds of DML operations on NESTED TABLEs (and on the individual elements within the NESTED TABLE especially). Though I am not covering much of the SQL here, we can overcome the complexity with vigorous practice of SQL.

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

chat sex hikayeleri Ensest hikaye