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 (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.
1 Comment
Thank you so, SO MUCH!!! I have used Oracle for years but could not understand how to use SYS_CONNECT_BY_PATH. Your example and explanation immediately cleared it up for me. Great job! THANK YOU!