PL/SQL While/For Loop

Question

我是一名DBA，只是想写一段代码来获取Oracle中的用户权限并写入表。下面的代码适用于一个用户，但如果ELSE部分有多个用户，我会得到错误："ORA-01422:精确获取返回的行数超过了请求的行数"。有道理，我意识到我需要一个for/wile循环来处理多行，有人能帮我吗？

''

declare
   altersystem       varchar2(550);
   altersystemconcat varchar2(550);
begin
   select grantee
     into altersystem
     from dba_sys_privs
    where privilege = 'ALTER SYSTEM'
      and grantee not in ('SYS', 'SYSTEM');
   if altersystem = 'No rows selected'
   then
      insert into catch
      values
         ('900'
         ,'No custom users with the Alter System privilege.');
   else
      select concat('The following user/role has the Alter System privilege, revoke if not required: '
                   ,altersystem)
        into altersystemconcat
        from dual;
      insert into catch
      values
         ('100'
         ,altersystemconcat);
   end if;
end;
/

''

Answer 1

select ... INTO ...只需要返回一行，否则会出现异常。通常你必须在光标上循环，但你甚至可以用这样的一个命令来完成：

BEGIN
    INSERT INTO catch
    SELECT 900, 'The following user/role has the Alter System privilege, revoke if not required: '||grantee 
    FROM dba_sys_privs 
    WHERE PRIVILEGE = 'ALTER SYSTEM' 
        AND grantee NOT IN ('SYS', 'SYSTEM');
   IF SQL%ROWCOUNT = 0 THEN
      INSERT INTO catch VALUES (100, 'No custom users with the Alter System privilege.');       
    END IF;
END;

Answer 2

嗯。。。不，ELSE的SELECT不返回too-many-rows。它从只有1行的DUAL中选择，因此。。。

我怀疑这是第一个声明，select grantee into altersystem。如果有多个用户满足这些条件，则不能将它们全部放入声明为varchar2的变量中。因此，是的，循环可能会有所帮助，它足够简单。

但是，为什么？为什么不简单地使用单个insert插入所有罪犯？类似这样的东西：

INSERT INTO catch
   SELECT '000', grantee
     FROM dba_sys_privs
    WHERE     privilege = 'ALTER SYSTEM'
          AND grantee NOT IN ('SYS', 'SYSTEM');

Answer 3

这可以在单个SQL语句中完成-不需要PL/SQL(除非您需要将其作为存储过程(：

INSERT INTO catch (col1, col2) -- change col1 and col2 to the correct column_names in catch that you're inserting into
  WITH no_users AS (SELECT 100 no_user_present_id, 'No custom users with the Alter System privilege.' no_user_text FROM dual),
    users_there AS (SELECT 900 user_present_id, 'The following user/role has the Alter System privilege, revoke if not required: '||grantee user_there_text
                    FROM   dba_sys_privs
                    WHERE  PRIVILEGE = 'ALTER SYSTEM'
                    AND    grantee NOT IN ('SYS', 'SYSTEM'))
  SELECT COALESCE(ut.user_present_id, nu.no_user_present_id) ID,
         COALESCE(ut.user_there_text, nu.no_user_text) text
  FROM   no_users nu
         LEFT OUTER JOIN users_there ut ON 1 = 1;

这是通过使用外部联接来实现的，这样在没有用户的情况下总是返回的行。如果没有用户，则返回的行为默认行。如果有具有指定授权的用户，则会返回这些用户的行。

Answer 4

使用listagg函数可以连接结果字符串。

注释掉了插入语句，因为我没有catch表。打印以输出。

如果找不到行，请使用异常处理程序。

declare
   altersystem       varchar2(550);
   altersystemconcat varchar2(550);
begin
   select listagg(grantee, ', ') within group(order by grantee) "Grantee"
     into altersystem
     from dba_sys_privs
    where privilege = 'ALTER SYSTEM'
      and grantee not in ('SYS', 'SYSTEM');
  dbms_output.put_line('The following user/role has the Alter System privilege, revoke if not required: ' || altersystem);
  /*
 insert into catch
   values
      ('100'
      ,'The following user/role has the Alter System privilege, revoke if not required: ' || altersystem);
  */
exception
   when no_data_found then
dbms_output.put_line('No custom users with the Alter System privilege.');
/*
      insert into catch
      values
         ('900'
         ,'No custom users with the Alter System privilege.');
*/
end;
/