ANBOB™

SQL> conn anbob
Enter password:
Connected.
SQL> create table test (id int,name varchar2(10));

Table created.

SQL> create or replace p_replace
  2
SQL>
SQL> create or replace procedure p_replace
  2  is
  3  begin
  4  execute immediate 'drop table test';
  5  execute immediate 'create table test (id int,name varchar2(10))';
  6  end;
  7  /

Procedure created.

SQL> create or replace procedure p_insert
  2  is
  3  begin
  4  insert into  test values(trunc(dbms_random.value(10,1000)),'anbob.com');
  5  commit;
  6  end;
  7  /

Procedure created.

SQL> begin
  2  p_replace;
  3  p_insert;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "ANBOB.P_REPLACE", line 5
ORA-06512: at line 2

SQL> conn system/oracle
Connected.

SQL> grant create table to anbob;

Grant succeeded.

SQL> conn anbob/anbob
Connected.

SQL> begin
  2  p_replace;
  3  p_insert;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "ANBOB.P_INSERT"
ORA-06508: PL/SQL: could not find program unit being called: "ANBOB.P_INSERT"
ORA-06512: at line 3

SQL> exec p_replace;

PL/SQL procedure successfully completed.

SQL> exec p_insert;

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME
---------- ----------
       231 anbob.com

SQL> begin
  2  exec p_replace;
  3  execute immidate 'begin p_insert;end;';
  4  end;
  5  /
exec p_replace;
     *
ERROR at line 2:
ORA-06550: line 2, column 6:
PLS-00103: Encountered the symbol "P_REPLACE" when expecting one of the
following:
:= . ( @ % ;

SQL> l2
  2* exec p_replace;
SQL> c /exec/
  2*  p_replace;
        *
ERROR at line 3:
ORA-06550: line 3, column 9:
PLS-00103: Encountered the symbol "IMMIDATE" when expecting one of the
following:
:= . ( @ % ; immediate
The symbol ":= was inserted before "IMMIDATE" to continue.

SQL> l
  1  begin
  2   p_replace;
  3  execute immidate 'begin p_insert;end;';
  4* end;
SQL> l3
  3* execute immidate 'begin p_insert;end;';
SQL> c /immidate/immediate
  3* execute immediate 'begin p_insert;end;';
SQL> l
  1  begin
  2   p_replace;
  3  execute immediate 'begin p_insert;end;';
  4* end;
SQL> run
  1  begin
  2   p_replace;
  3  execute immediate 'begin p_insert;end;';
  4* end;

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME
---------- ----------
       274 anbob.com

SQL>

存储过程在编译时，自动检查语法错误、权限以及所有对象的依赖性。而等到执行的时候，Oracle则不会再进行类似的检查，而是直接运行过程，这也是存储过程拥有较高效率的原因之一。
当存储过程所依赖的对象发生了变化，Oracle会自动将存储过程的状态置为INVALID，而存储过程的状态如果为INVALID，则会在下次执行时尝试重新编译，如果编译通过则继续执行；如果编译失败则报错。

在调用P_INSERT_T过程之前对P_INSERT_T进行检查并重新编译，所以采用动态SQL不会报错

打赏

微信扫一扫，打赏作者吧～