区别存储过程中的硬解析和软解析

1.硬解析！

1.     SQL> declare
         2 v_num number;
         3 v_sql varchar2(100);
         4 begin
         5 v_num:=10;
         6 v_sql:=\'select trunc(avg(sal),0) from emp where deptno=\'||v_num;
         7 execute immediate v_sql into v_num;
         8 dbms_output.put_line(v_num);
         9 end;
        10 /
       2916

   
   

2.软解析！

1.     SQL> declare
         2 v_num number;
         3 v_sql varchar2(100);
         4 begin
         5 v_sql:=\'select trunc(avg(sal),0) from emp where deptno=:1\';
         6 execute immediate v_sql into v_num using 10;
         7 dbms_output.put_line(v_num);
         8 end;
         9 /
       2916

   
   

3.软解析2.0

1.     SQL> declare
         2 v_avg number;
         3 v_deptno emp.deptno%type;
         4 begin
         5 v_deptno:=10;
         6 select trunc(avg(sal),0) into v_avg from emp where deptno=v_deptno;
         7 dbms_output.put_line(v_avg);
         8 end;
         9 /
       2916

   
   

使用一下查询，查询解析情况。

    SQL> select sql_text,loads,sql_id from v$sqlarea where sql_text like 'sql';