How to change this SQL query to PL/SQL command line or code?

SELECT username, account_status FROM dba_users;

I tried

DECLARE
  user_name VARCHAR2(20) := 'username';
  account_status VARCHAR2(20) := 'account_status';
BEGIN
  FOR user_name IN (SELECT username FROM dba_users) LOOP
    FOR account_status IN (SELECT account_status FROM dba_users) LOOP
      dbms_output.put_line(user_name.username || ' - ' || user_record.account_status);
    END LOOP;
  END LOOP;
END;

it works but the output is repeating

CodePudding user response：

This is pure SQL:

SQL> select username, account_status from dba_users where rownum <= 5;

USERNAME             ACCOUNT_STATUS
-------------------- --------------------
SYS                  OPEN
AUDSYS               LOCKED
SYSTEM               OPEN
SYSBACKUP            LOCKED
SYSDG                LOCKED

To "convert" it into PL/SQL, use one loop (why using two?):

SQL> set serveroutput on
SQL> begin
  2    for cur_r in (select username, account_status from dba_users where rownum <= 5)
  3    loop
  4      dbms_output.put_line(cur_R.username ||' - '|| cur_r.account_status);
  5    end loop;
  6  end;
  7  /
SYS - OPEN
AUDSYS - LOCKED
SYSTEM - OPEN
SYSBACKUP - LOCKED
SYSDG - LOCKED

PL/SQL procedure successfully completed.

Your code, fixed: if you use nested loops (once again, no need for that), you have to correlate one loop query to another - that's what you are missing - see line #4:

SQL> begin
  2    for cur_user in (select username from dba_users where rownum <= 5) loop
  3      for cur_acc in (select account_status from dba_users
  4                      where username = cur_user.username
  5                     )
  6      loop
  7        dbms_output.put_line(cur_user.username ||' - '|| cur_acc.account_status);
  8      end loop;
  9    end loop;
 10  end;
 11  /
SYS - OPEN
AUDSYS - LOCKED
SYSTEM - OPEN
SYSBACKUP - LOCKED
SYSDG - LOCKED

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

Just use this block:

cl scr
set SERVEROUTPUT ON

BEGIN
  FOR i IN (SELECT distinct username FROM dba_users order by username) LOOP
    FOR j IN (SELECT  distinct account_status FROM dba_users where username=i.username order by account_status) LOOP
      dbms_output.put_line(i.username || ' - ' || j.account_status);
    END LOOP;
  END LOOP;
END;