Looping in SQL

The following SQL is a modification of this example of looping through data and presenting it as a comma separated list in one row.

It gives a comma separated list of roles for each user. Note that this will only work on Oracle databases as far as I'm aware.

select
   ROLEUSER as USER_ID,
   substr(SYS_CONNECT_BY_PATH(ROLENAME, ','),2) as ROLES
from
   (
   select
     ROLEUSER,
     ROLENAME,
     count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT,
     ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE
   from
     PSROLEUSER
   where
     ROLEUSER is not null)
where
   ROLECOUNT=ROLESEQUENCE
start with
   ROLESEQUENCE = 1
connect by prior
   ROLESEQUENCE+1=ROLESEQUENCE
and prior
   ROLEUSER=ROLEUSER
;

How does it work? Well here's a break down.

The query inside the FROM clause essentially groups the data for looping.

select
   ROLEUSER,
   ROLENAME,
   count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT,
   ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE
from
   PSROLEUSER
where
   ROLEUSER is not null

It returns the user (ROLEUSER), the name of the role ((ROLENAME), a count of the number of roles for that user ((ROLECOUNT) and a sequence number for each role row (ROLESEQUENCE).

So for example if the user PS has the roles:

  • PeopleSoft Administrator
  • PeopleTools
  • Portal Administrator
  • ProcessSchedulerAdmin
  • Security Administrator

The result would look like this:

ROLEUSER ROLENAME ROLECOUNT ROLESEQUENCE
PS PeopleSoft Administrator 5 1
PS PeopleTools 5 2
PS Portal Administrator 5 3
PS ProcessSchedulerAdmin 5 4
PS Security Administrator 5 5

This essentially sets up the loop - PS has 5 roles and each of them has been placed in sequence using the Oracle system ROW_NUMBER partitioned over the role user and ordered by the role name.

The outside part of the query then uses the SYS_CONNECT_BY_PATH starting with the ROLECOUNT equal to the ROLESEQUENCE which starts at 1 and is incremented by 1 each time. To ensure that we are looping for the same user each time, the SYS_CONNECT_BY_PATH uses the line prior ROLEUSER=ROLEUSER. So when we move to the next user, the loop starts again with a ROLESEQUENCE of 1.

You don't have to fully understand the syntax, but what this SQL demonstrates is how to create a simple loop in Oracle using plain old SQL.

sql
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License