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

上面主要介绍了绑定变量的对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