Looping in SQL

The following SQL is an example of looping through user data and presenting it as a comma separated list of roles for each user.

This SQL will only work on Oracle databases.

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_PAT 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.