Cycle or something similar in PL/SQL

arrayoracleplsql

Quite often it is necessary to do some repetetive operations / few line queries against set of objects. Let's say you have a list of accounts to unlock via 

alter user %username% account unlock

and instead of typing the same line multiple times for each user I believe some cycle construction can be used but don't know how to write it in PL/SQL. Can somebody advice on this?

Best Answer

You can generate this sort of statements using dynamic SQL, e.g.

select 'alter user ' || username || ' account unlock' as cmd
  from dba_users
 where account_status = 'LOCKED';

Following up on your latest comment, if you want to manually enumerate the usernames you can use a PL/SQL collection such as a nested table, e.g.

SQL> set serveroutput on
SQL> declare
  2     type users_t is table of dba_users.username%type;
  3     my_users users_t := users_t('USER1', 'USER2', 'USER3');
  4  begin
  5     for i in my_users.first .. my_users.last loop
  6        dbms_output.put_line('alter user ' || my_users(i) || ' account unlock');
  7     end loop;
  8  end;
  9  /
alter user USER1 account unlock
alter user USER2 account unlock
alter user USER3 account unlock

PL/SQL procedure successfully completed.

To actually run the commands instead of just printing them, substitute dbms_output.put_line with execute immediate.

Related Question