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

    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

    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

    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