sql - 创建或替换 v/s 删除一个函数并重新创建它

Question

两者有什么区别？在这两种情况下？授予此功能的特权会发生什么变化？在这两种情况下都自动撤销并且在重新创建时必须再次提供？请解释。

Answer 1

当一个对象被删除时，所有与之关联的东西也会被删除，包括特权。这不适用于 CREATE OR REPLACE。

SQL> create procedure p1 is
  2  begin
  3      null;
  4  end;
  5  /

Procedure created.

SQL> grant execute on p1 to xyz;

Grant succeeded.

SQL> select * from user_tab_privs_made
  2  /

GRANTEE                        TABLE_NAME                     GRANTOR                        PRIVILEGE                     GRA HIE
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
XYZ                            P1                             APC                            EXECUTE                       NO  NO

SQL> create or replace procedure p1 is
  2    n pls_integer;
  3  begin
  4   n := 1;
  5  end;
  6  /

Procedure created.

SQL> select * from user_tab_privs_made
  2  /

GRANTEE                        TABLE_NAME                     GRANTOR                        PRIVILEGE                     GRA HIE
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
XYZ                            P1                             APC                            EXECUTE                       NO  NO

SQL> drop procedure p1;

Procedure dropped.

SQL> create or replace procedure p1 (p in out pls_integer) is
  2  begin
  3    p := p+1;
  4  end;
  5  /

Procedure created.

SQL> select * from user_tab_privs_made
  2  /

no rows selected

SQL>