Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Arya.

Asked: July 06, 2016 - 5:48 pm UTC

Answered by: Connor McDonald - Last updated: July 09, 2016 - 2:00 am UTC

Category: SQL*Plus - Version: 9i

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Back to Basics

You Asked

I've a Table with columns EMPCODE,DAY1,DAY2...DAY31 I've to loop from Day1 to Day31 so what is the best way of doing this,If I should use collections then how should I assign Day1--31 to a Collection and what Collection Should I use.

Can anyone please help me out

Thank You

and we said...

DAY1 .. DAY31 in itself raises a few questions about whether the table design is appropriate. But let's assume that is fixed. In which case, you can collect them explicitly into an array, and then work with that.

SQL> drop table t purge;

Table dropped.

SQL> create table t ( d1 int, d2 int, d3 int, d4 int);

Table created.

SQL> insert into t values (11,22,33,44);

1 row created.

SQL>
SQL> set serverout on
SQL> declare
  2    type col_list is table of int index by pls_integer;
  3    r col_list;
  4  begin
  5    select d1,d2,d3,d4
  6    into r(1), r(2), r(3), r(4)
  7    from t;
  8
  9    for i in 1 .. r.count loop
 10       dbms_output.put_line(i||' '||r(i));
 11    end loop;
 12  end;
 13  /
1 11
2 22
3 33
4 44

PL/SQL procedure successfully completed.




and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review

Reviews

July 08, 2016 - 4:36 pm UTC

Reviewer: Arya kumar

Thanq Connor McDonald..
Can You Tell Me why table design is not appropriate,This is a table which describe employees attendence,So we have given Day1..Day31 as column names with Employee code,Year,Month fields.
where each day is represented as day with leave codes as its value
So Can You Suggest me the better way to do the design
Connor McDonald

Followup  

July 09, 2016 - 2:00 am UTC

It opens up holes for data inconsistency.

eg

Mth=Feb
Day31=10

So...when did Feb suddenly have 31 days ?

I'd be more inclined just to have

emp_code
measurement_date

It easy to pivot that into columns, and having a date column takes care of all of the date validation etc.


More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here