PL/SQL、Pro*C中避免是用绑定变量的方法（二）

 

PL/SQL、Pro*C中避免是用绑定变量的方法（一）http://blog.chinaunix.net/uid-22948773-id-3078314.html

 

上面主要介绍了绑定变量的对SQL执行计划的影响，以及在PL/SQL中如何避免。

下面简单介绍一下如何在Pro*C中避免。

当然如果SQL是拼凑起来的动态SQL,譬如：

sprintf(sqltext,"select object_name from test where object_type='%s' and object_id=%d","SYNONYM",1234);

这种情况下是肯定可以的，因为每一个SQL语句都会硬解析一次，每一个SQL语句都有自己的执行计划。

为了将硬解析减少到最少，我们尽量在object_id列上也用绑定变量，而在object_type上采用非绑定变量。

下面是测试数据：

SQL> select object_id,object_name,object_type from test where object_id in (605,8473);

 OBJECT_ID OBJECT_NAME          OBJECT_TYP
---------- -------------------- ----------

       605 MAP_OBJECT           SYNONYM
      8473 FGR$AUTOPURGE_JOB    JOB

下面介绍2种方法来实现绑定变量和非绑定变量的同时使用

方法1-采用动态SQL方法3

刷新共享池，清除以前SQL语句的执行计划。

SQL> alter system flush shared_pool;

System altered.

SQL>  select sql_text,executions,sql_id from v$sql where sql_text like 'select object_name from test where object_type%';

no rows selected

 

Pro*C代码如下：

#include
#include
exec sql include sqlca;

main()
{
 
  exec sql begin declare section;
     char *username="test";
     char *password="test";
     char *dbserver="10.223.18.116/yansp";
     char sqltext[128];
     char v_object_type[30];
     int  v_object_id;
     varchar v_object_name[30];
  exec sql end declare section;
 
  exec sql declare s statement;
  exec sql declare c cursor for s;

  exec sql connect :username identified by :password using :dbserver;
 

  strcpy(v_object_type,"SYNONYM");
  v_object_id=605;
   
  sprintf(sqltext,"select object_name from test where object_type='%s' and object_id=:object_id",v_object_type);
  exec sql prepare s from :sqltext;
  exec sql open c using :v_object_id;
  exec sql fetch c into v_object_name;
  v_object_name.arr[v_object_name.len]='\0';
  printf("object_name=[%s]\n",v_object_name.arr);
  exec sql close c;
 
  strcpy(v_object_type,"JOB");
  v_object_id=8473;
   
  sprintf(sqltext,"select object_name from test where object_type='%s' and object_id=:object_id",v_object_type);
  exec sql prepare s from :sqltext;
  exec sql open c using :v_object_id;
  exec sql fetch c into v_object_name;
  v_object_name.arr[v_object_name.len]='\0';
  printf("object_name=[%s]\n",v_object_name.arr);
  exec sql close c;
 
  exec sql commit work release;
}

 

编译成可执行程序test 执行结果如下：

/home/cpicsrv/yansp > ./test
object_name=[MAP_OBJECT]
object_name=[FGR$AUTOPURGE_JOB]
/home/cpicsrv/yansp >

共享池中的SQL执行情况如下：

SQL> select sql_text,executions,sql_id from v$sql where sql_text like 'select object_name from test where object_type%';

SQL_TEXT                                 EXECUTIONS SQL_ID
---------------------------------------- ---------- -------------

select object_name from test where objec          1 4z597uqf88aqt
t_type='JOB' and object_id=:object_id

select object_name from test where objec          1 93n27g7ykmvmg
t_type='SYNONYM' and object_id=:object_i
d

 

 

再次执行./test

/home/cpicsrv/yansp > ./test
object_name=[MAP_OBJECT]
object_name=[FGR$AUTOPURGE_JOB]
/home/cpicsrv/yansp >

SQL> select sql_text,executions,sql_id from v$sql where sql_text like 'select object_name from test where object_type%';

SQL_TEXT                                 EXECUTIONS SQL_ID
---------------------------------------- ---------- -------------

select object_name from test where objec          2 4z597uqf88aqt
t_type='JOB' and object_id=:object_id

select object_name from test where objec          2 93n27g7ykmvmg
t_type='SYNONYM' and object_id=:object_i
d

每条SQL的执行次数都变为了2。

 

 

方法2-嵌入式PL/SQL

#include
#include
exec sql include sqlca;

main()
{
 
  exec sql begin declare section;
     char *username="test";
     char *password="test";
     char *dbserver="10.223.18.116/yansp";
     char sqltext[128];
     char v_object_type[30];
     int  v_object_id;
     varchar v_object_name[30];
  exec sql end declare section;

  exec sql connect :username identified by :password using :dbserver;
 
  v_object_name.len=sizeof(v_object_name.arr);

  strcpy(v_object_type,"SYNONYM");
  v_object_id=605;
  sprintf(sqltext,"select object_name from test where object_type='%s' and object_id=:object_id",v_object_type);
 
  exec sql execute
    begin
      execute immediate :sqltext into :v_object_name using :object_id;
    end;
  end-exec;
  v_object_name.arr[v_object_name.len]='\0';
  printf("object_name=[%s]\n",v_object_name.arr); 
 
  strcpy(v_object_type,"JOB");
  v_object_id=8473;
  sprintf(sqltext,"select object_name from test where object_type='%s' and object_id=:object_id",v_object_type);
 
  exec sql execute
    begin
      execute immediate :sqltext into :v_object_name using :object_id;
    end;
  end-exec;
  v_object_name.arr[v_object_name.len]='\0';
  printf("object_name=[%s]\n",v_object_name.arr);
 
  exec sql commit work release;
}

 

将上述代码编译成可执行程序test。

再次清空共享池

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_text,executions,sql_id from v$sql where sql_text like 'select object_name from test where object_type%';

no rows selected

 

/home/cpicsrv/yansp > ./test
object_name=[MAP_OBJECT]
object_name=[FGR$AUTOPURGE_JOB]
/home/cpicsrv/yansp >

再次查看共享池的结果如下：

SQL> select sql_text,executions,sql_id from v$sql where sql_text like 'select object_name from test where object_type%';

SQL_TEXT                                 EXECUTIONS SQL_ID
---------------------------------------- ---------- -------------

select object_name from test where objec          1 4z597uqf88aqt
t_type='JOB' and object_id=:object_id

select object_name from test where objec          1 93n27g7ykmvmg
t_type='SYNONYM' and object_id=:object_i
d