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.
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 (
So for example if the user PS has the roles:
- PeopleSoft Administrator
- Portal Administrator
- Security Administrator
The result would look like this:
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.