用绑定变量和不用绑定变量的差别

最新推荐文章于 2021-04-03 21:51:50 发布

congqingm32098

最新推荐文章于 2021-04-03 21:51:50 发布

阅读量252

点赞数

今天看了TOM大师写的<> 一书

测试一下用绑定变量和不用绑定变量的差别

(1) 创建视图 stat

create or replace view stats as
select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch;

(2) 创建临时表

-- Create table
create global temporary table RUN_STATS
(
RUNID VARCHAR2(15),
NAME VARCHAR2(80),
VALUE INTEGER
)
on commit preserve rows;

(3) 创建包体

create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0 );
end;

(4) 创建包体

create or replace package body runstats_pkg as

g_start number;
g_run1 number;
g_run2 number;

procedure rs_start is
begin
delete from run_stats;

insert into run_stats
select 'before', stats.* from stats;

g_start := dbms_utility.get_time;
end;

procedure rs_middle is
begin
g_run1 := (dbms_utility.get_time - g_start);

    insert into run_stats
      select 'after 1', stats.* from stats;
    g_start := dbms_utility.get_time;

end;

procedure rs_stop(p_difference_threshold in number default 0) is
begin
g_run2 := (dbms_utility.get_time - g_start);

    dbms_output.put_line('Run1 ran in ' || g_run1 || ' hsecs');
    dbms_output.put_line('Run2 ran in ' || g_run2 || ' hsecs');
    dbms_output.put_line('run 1 ran in ' ||
                         round(g_run1 / g_run2 * 100, 2) ||
                         '% of the time');
    dbms_output.put_line(chr(9));

insert into run_stats
select 'after 2', stats.* from stats;

dbms_output.put_line(rpad('Name', 30) || lpad('Run1', 10) ||
lpad('Run2', 10) || lpad('Diff', 10));

    for x in (select rpad(a.name, 30) ||
                     to_char(b.value - a.value, '9,999,999') ||
                     to_char(c.value - b.value, '9,999,999') ||
                     to_char(((c.value - b.value) - (b.value - a.value)),
                             '9,999,999') data
                from run_stats a, run_stats b, run_stats c
               where a.name = b.name
                 and b.name = c.name
                 and a.runid = 'before'
                 and b.runid = 'after 1'
                 and c.runid = 'after 2'
                 and (c.value - a.value) > 0
                 and abs((c.value - b.value) - (b.value - a.value)) >
                     p_difference_threshold
               order by abs((c.value - b.value) - (b.value - a.value))) loop
      dbms_output.put_line(x.data);
    end loop;

    dbms_output.put_line(chr(9));
    dbms_output.put_line('Run1 latches total versus runs -- difference and pct');
    dbms_output.put_line(lpad('Run1', 10) || lpad('Run2', 10) ||
                         lpad('Diff', 10) || lpad('Pct', 8));

    for x in (select to_char(run1, '9,999,999') ||
                     to_char(run2, '9,999,999') ||
                     to_char(diff, '9,999,999') ||
                     to_char(round(run1 / run2 * 100, 2), '999.99') || '%' data
                from (select sum(b.value - a.value) run1,
                             sum(c.value - b.value) run2,
                             sum((c.value - b.value) - (b.value - a.value)) diff
                        from run_stats a, run_stats b, run_stats c
                       where a.name = b.name
                         and b.name = c.name
                         and a.runid = 'before'
                         and b.runid = 'after 1'
                         and c.runid = 'after 2'
                         and a.name like 'LATCH%')) loop
      dbms_output.put_line(x.data);
    end loop;
end;

end;
/

(4) drop table t;

(5) 16:04:28 test@ORCLDEV> create table t (x int);

Table created.

(6) 创建过程proc1 （用绑定变量）

16:04:40 test@ORCLDEV> create or replace procedure proc1
16:05:01   2 as
16:05:03   3 begin
16:05:08   4    for i in 1 .. 10000
16:05:16   5    loop
16:05:20   6       execute immediate
16:05:30   7       ' insert into t values (:x) ' using i ;
16:05:50   8    end loop;
16:05:57   9 end ;
16:06:00 10 /

Procedure created.

(7) 创建过程proc2 （不用绑定变量）

16:07:41 test@ORCLDEV> create or replace procedure proc2
16:08:00   2 as
16:08:00   3 begin
16:08:02   4 for i 1 .. 10000
16:08:14   5 loop
16:08:15   6     execute immediate
16:08:27   7     ' insert into t values ('||i||')';
16:08:50   8 end loop;
16:08:53   9 end ;
16:08:55 10 /

Warning: Procedure created with compilation errors.

16:08:56 test@ORCLDEV> show error;
Errors for PROCEDURE PROC2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/7 PLS-00103: Encountered the symbol "1" when expecting one of the
following:
in
The symbol "in" was substituted for "1" to continue.

16:09:06 test@ORCLDEV> ed
Wrote file afiedt.buf

1 create or replace procedure proc2
2 as
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate
7 ' insert into t values ('||i||')';
8 end loop;
9* end ;
16:09:22 test@ORCLDEV> /

Procedure created.

结果:

16:09:23 test@ORCLDEV> exec runstats_pkg.rs_start;