也谈Oracle 数据库的绑定变量特性及应用

封烨 · 发表于 2009-7-14 16:46

首先感谢楼主的分享~
我有个问题，类似12楼的情况，在存储过程中，要查询的语句是动态的。我尝试使用绑定变量，但绑定变量只是相当于把值赋值给变量，而不会去执行“查询”，我把SQL贴出来：
insert_str := '
   INSERT INTO bam_cbbs_base_kpi_sum_l2_l(busi_id
                                       , user_type
                                       , chrg_prov_id
                                       , oper_time
                                       , base_kpi_id
                                       , value
                                       , dimension_level
                                       , is_valid
                                       , created
                                       , modified
                                       )
';
select_str := '
      SELECT :busi_id
         , user_type
         ,

rov_name
         , SUBSTR(curr_deal, 1, 6)
         , :kpi_id
         , :sum_str
         , 5
         , 1
         , sysdate
         , sysdate
      FROM :tab
         AND SUBSTR(curr_deal, 1, 6) = to_char(:time)
      GROUP BY substr(curr_deal,1,6),user_type,

rov_name
';
execute immediate insert_str || select_str using busi_id,prov_id,kpi_id(i),sum_str,curr_deal;
这个语句执行后，会把对应的表达式赋值给绑定变量，但却没任何记录返回，因为他只简单的赋值了，却没执行select操作。但是如果这个绑定变量的内容是数据库某条记录的一个值，却可以。如select * from tab1 where a = :a;这个a是个值，如'123','abc'什么的，都可以。
我为了解决上述的问题，我想了一个笨办法。
我先通过tmp_str := 'select

rov_name from dual';
execute immediate tmp_str into new_prov_str using prov_name(i);
的办法获得这个new_str,然后上面的查询语句改成了绑定变量+拼接字符串的形式。如：
select_str := '
      SELECT :busi_id
         , user_type
         , '||new_prov_str||'
         , SUBSTR(curr_deal, 1, 6)
         , :kpi_id
         , '||new_sum_str||'
         , 5
         , 1
         , sysdate
         , sysdate
         '||new_where_str||'
         AND SUBSTR(curr_deal, 1, 6) = to_char(:time)
      GROUP BY substr(curr_deal,1,6),user_type,'||new_prov_str||'
   ';
但是这个办法其实是“错误的”，并没有用到绑定变量的特性。而是有点蒙混过关的感觉，呵呵。

不知道大家有没明白我说的？
扼要的说：绑定变量，如果其内容是个表达式的话，应该如何使用绑定变量？

封烨 · 发表于 2009-7-14 16:54

晕，上图的

的内容是 : prov_name

lakepanda · 发表于 2009-7-22 22:51

请问在c和C++程序中怎么使用oracle的绑定变量啊

Kenniu · 发表于 2009-8-7 10:29

原帖由封烨于 2009-7-14 16:46 发表
首先感谢楼主的分享~
我有个问题，类似12楼的情况，在存储过程中，要查询的语句是动态的。我尝试使用绑定变量，但绑定变量只是相当于把值赋值给变量，而不会去执行“查询”，我把SQL贴出来：
insert_str := '
   INSERT INTO bam_cbbs_base_kpi_sum_l2_l(busi_id
                                       , user_type
                                       , chrg_prov_id
                                       , oper_time
                                       , base_kpi_id
                                       , value
                                       , dimension_level
                                       , is_valid
                                       , created
                                       , modified
                                       )
';
select_str := '
      SELECT :busi_id
         , user_type
         , rov_name
         , SUBSTR(curr_deal, 1, 6)
         , :kpi_id
         , :sum_str
         , 5
         , 1
         , sysdate
         , sysdate
      FROM :tab
         AND SUBSTR(curr_deal, 1, 6) = to_char(:time)
      GROUP BY substr(curr_deal,1,6),user_type,rov_name
   ';
execute immediate insert_str || select_str using busi_id,prov_id,kpi_id(i),sum_str,curr_deal;
这个语句执行后，会把对应的表达式赋值给绑定变量，但却没任何记录返回，因为他只简单的赋值了，却没执行select操作。但是如果这个绑定变量的内容是数据库某条记录的一个值，却可以。如select * from tab1 where a = :a;这个a是个值，如'123','abc'什么的，都可以。
我为了解决上述的问题，我想了一个笨办法。
我先通过tmp_str := 'select rov_name from dual';
execute immediate tmp_str into new_prov_str using prov_name(i);
的办法获得这个new_str,然后上面的查询语句改成了绑定变量+拼接字符串的形式。如：
select_str := '
      SELECT :busi_id
         , user_type
         , '||new_prov_str||'
         , SUBSTR(curr_deal, 1, 6)
         , :kpi_id
         , '||new_sum_str||'
         , 5
         , 1
         , sysdate
         , sysdate
         '||new_where_str||'
         AND SUBSTR(curr_deal, 1, 6) = to_char(:time)
      GROUP BY substr(curr_deal,1,6),user_type,'||new_prov_str||'
   ';
但是这个办法其实是“错误的”，并没有用到绑定变量的特性。而是有点蒙混过关的感觉，呵呵。

不知道大家有没明白我说的？
扼要的说：绑定变量，如果其内容是个表达式的话，应该如何使用绑定变量？

其实，我不建议你这么做；
原因是你用在Store-Procedure中；只要写在Store-procedure,Oracle 系统现在都自动帮你把你写的Sql,转义成你要用的绑定变量;尤其Oralce9i以后的版本;当然Oracle 8I以前的版本是无法做到的;

我强烈建议你用静态绑定；你的动态绑定会花费更多的资源，且效率有可能并是你想的那么好;

另外，你想知道是否Oracle在使用绑定变量，其实我在文章已经说的很清楚了;

Kenniu · 发表于 2009-8-7 10:30

原帖由 lakepanda 于 2009-7-22 22:51 发表
请问在c和C++程序中怎么使用oracle的绑定变量啊

这个看起来，我要Study一下...呵呵...

Kenniu · 发表于 2009-8-7 10:32

这里也有一篇，“何时Oracle使用绑定变量性能反而更差”的文章，供大家参考;内容请参考别人写的东西...

http://www.mscto.com/Oracle/2009010137072.html

Kenniu · 发表于 2009-8-7 10:44

原帖由 lakepanda 于 2009-7-22 22:51 发表
请问在c和C++程序中怎么使用oracle的绑定变量啊

这里找到了C程序中的使用oracle的绑定变量的方法：

这篇文章的实在基于Linux写的C程序编写，引用了基于Linux下的一个函数库,Kai Poitschke开发的Libsqlora8;
或许你也可以用Oracle数据库对C语言的接口就是OCI(Oracle Common Interface) C-Library；
但具体思想应该没什么错误;

下面是来自于另一位网友(http://www.cndw.com/tech/server/2006040533965.asp)的代码内容，思路大同:

char *update_stmt =
"UPDATE ocitest.upload_log SET upload_fresh = '0' where log_name = :1";
if (0 <= (sth = sqlo_prepare(dbh, update_stmt)))
{ if (SQLO_SUCCESS !=
(sqlo_bind_by_name(sth, ":1", SQLOT_STR, packet_name, 64, NULL, 0)
))
{ printf("sqlo_bind_param failed failed: %s\n", sqlo_geterror(dbh) );
return 0;
}
}
if (SQLO_SUCCESS != sqlo_execute(sth, 1))
{ printf("sqlo_execute failed: %s\n", sqlo_geterror(dbh) );
return 0;
}
上面的代码显示了如何通过名字绑定变量，“：1”在Oracle SQL语句中表示为一个变量（名字随意），在sqlo_bind_by_name函数中与packet_name变量绑定。在变量绑定完毕后，就可以调用sqlo_execute函数来执行这个SQL语句。