Oracle sql绑定变量

方法一、存储过程中绑定变量

PROCEDURE PRO_TEST(O_NUMBER OUT NUMBER) AS     --INIT     M_COUNT         NUMBER;     M_VARCHAR2      VARCHAR2(20);     M_SQL           VARCHAR2(2000);     M_CUR           CUR_TYPE;     M_CUR_ITEM      M_CUR%ROWTYPE;   BEGIN     -- TEST 1      M_VARCHAR2 := '20110305%';           M_SQL := 'SELECT count(*) FROM T_CZW_YW_TBD T WHERE T.CLDEPT like :M_VARCHAR2';      --M_SQL := 'SELECT COUNT(*) FROM DUAL T WHERE 20110305 =:M_VARCHAR2';      EXECUTE IMMEDIATE M_SQL INTO M_COUNT USING M_VARCHAR2;      O_NUMBER := M_COUNT;

方法2 java代码绑定

String p_id = 'xxxxx';String m_sql = 'select * from table_name where id = ? '; //嵌入绑定变量stmt = con.prepareStatement( v_sql );stmt.setString(1, p_id ); //为绑定变量赋值stmt.executeQuery();

方法3 sqlplsu 绑定变量

SQL> DEFINE V_TEST='V_TEST';SQL> DEFINEDEFINE _SQLPLUS_RELEASE = "000000000" (CHAR)DEFINE _EDITOR          = "PLSQLDev" (CHAR)DEFINE _DATE            = "2014-5-9" (CHAR)DEFINE _PRIVILEGE       = "" (CHAR)DEFINE _O_VERSION       = "" (CHAR)DEFINE _O_RELEASE       = "000000000" (CHAR)DEFINE _USER            = "HNXZZF" (CHAR)DEFINE _CONNECT_IDENTIFIER = "10.161.109.230/ORCL" (CHAR)DEFINE V_TEST           = "V_TEST" (CHAR)SQL> select count(*) from dual where 'HNXZZF'='&_USER';  COUNT(*)----------         1SQL> select count(*) from dual where 'V_TEST'='&V_TEST';  COUNT(*)----------         1

--UNDEFINE V_TEST  清楚变量 V_TEST

99 清除共享池（注意，清除会消耗大量系统性能，同时也会降低sql的触发概率，以后的执行计划将会cost多）

alter system flush shared_pool;

参考资料

1、SQL语句硬分析(Hard Parse)太多，严重消耗CPU资源，延长了SQL语句总的执行时间。
SQL语句的执行过程分几个步骤：语法检查、分析、执行、返回结果。其中分析又分为硬分析(Hard Parse)和软分析(Soft Parse)。
一条SQL语句通过语法检查后，Oracle 会先去shared pool 中找是否有相同的sql，如果找着了，就叫软分析，然后执行SQL语句。
硬分析主要是检查该sql所涉及到的所有对象是否有效以及权限等关系，然后根据RBO或CBO模式生成执行计划，然后才执行SQL语句。
可以看出，硬分析比软分析多了很多动作，而这里面的关键是“在shared pool 中是否有相同的sql”，而这就取决于是否使用绑定变量。


2、共享池中SQL语句数量太多，重用性极低，加速了SQL语句的老化，导致共享池碎片过多。
共享池中不同的SQL语句数量巨大，根据LRU原则，一些语句逐渐老化，最终被清理出共享池；这样就导致shared_pool_size 里面命中率
下降，共享池碎片增多，可用内存空间不足。而为了维护共享池内部结构，需要使用latch，一种内部生命周期很短的lock，这将使用大量
的cpu 资源，使得性能急剧下降。
不使用绑定变量违背了oracle 的shared pool 的设计的原则，违背了这个设计用来共享的思想。