PL/SQL 代码，用于授予新用户对其他用户拥有的视图的选择权限

Question

需要这方面的帮助...PL/SQL 的新手。我哪里出错了？

 DECLARE
 CREATE or REPLACE PROCEDURE grant_view_privs
      IS sys_refcursor;
       strVIEWS Varchar2(1000);
       strQuery varchar2(4000);
  BEGIN
  open result for
    select object_name from user_objects where object_type='VIEW'
      and status !='INVALID';
   loop
     fetch result into strVIEWS;
  IF SQL%NOTFOUND then
    DBMS_OUTPUT.PUT_LINE ('TABLE DOES NOT EXIST');
    ELSIF SQL%FOUND then
    DBMS_OUTPUT.PUT('Granting select on '||strVIEWS||' to BIOTICS_REPORT');
    strQuery := 'grant SELECT on '||strVIEWS||' to BIOTICS_REPORT';
    execute immediate strQuery;
    DBMS_OUTPUT.PUT_LINE('SUCCES');
    END IF;
end loop;
close result;
end;
/

Answer 1

你似乎在这里混淆了一些东西。

• 如果要创建存储过程，则不会以declare关键字开头，该关键字适用于匿名PL/SQL块。
• 你在IS和sys_refcursor之间缺少一个变量名;大概应该是result的。
• 当结果集中没有更多的行时，SQL%NOTFOUND是正确的;它并不表示根本没有数据，当然也不会指示表（user_objects，实际上是一个视图）不存在。
• 您的循环将永远持续下去，因为您没有检测到何时到达结果集的末尾。您将从 ELSE 部分获得所有确实存在的视图的合理输出，但随后每次后续迭代都会得到SQL%NOTFOUND;如果没有来自循环的exit，它只会尝试再次获取。

如果您使用的是SQL*Plus或SQL Developer，则可以使用"show errors"命令来查看为什么存储的代码块无法编译，或者您可以查询也可以在其他客户端中使用的user_errors视图。但在这种情况下，在您克服declare的PLS-00103: Encountered the symbol "CREATE"...错误之前，这不会做太多事情。（如果您实际上在问题中说明您遇到了什么错误，它总是很糟糕）。

我认为这相当于您似乎的目标：

create or replace procedure grant_view_privs is
  result sys_refcursor;
  strView user_objects.object_name%TYPE;
  strQuery varchar2(4000);
begin
  open result for
    select object_name
    from user_objects
    where object_type='VIEW'
    and status !='INVALID';
  loop
    fetch result into strView;
    exit when SQL%NOTFOUND;
    strQuery := 'grant SELECT on '||strView||' to BIOTICS_REPORT';
    dbms_output.put_line(strQuery);
    execute immediate strQuery;
  end loop;
  close result;
end grant_view_privs;
/

您可以使用不同形式的游标语法来简化这一点：

create or replace procedure grant_view_privs is
  strQuery varchar2(4000);
begin
  for curViews in (
    select object_name
    from user_objects
    where object_type='VIEW'
    and status !='INVALID'
  )
  loop
    strQuery := 'grant SELECT on '||curViews.object_name||' to BIOTICS_REPORT';
    dbms_output.put_line(strQuery);
    execute immediate strQuery;
  end loop;
end grant_view_privs;
/

如果在 select 中生成整个动态语句，则甚至不必定义strQuery：

create or replace procedure grant_view_privs is
begin
  for curViews in (
    select 'grant SELECT on '||object_name||' to BIOTICS_REPORT' as command
    from user_objects
    where object_type='VIEW'
    and status !='INVALID'
  )
  loop
    dbms_output.put_line(curViews.command);
    execute immediate curViews.command;
  end loop;
end grant_view_privs;
/