Oracle sql绑定变量

最新推荐文章于 2023-07-22 12:25:31 发布

pioayang

最新推荐文章于 2023-07-22 12:25:31 发布

阅读量856

点赞数

分类专栏： oracle 文章标签： oracle plsql sql

本文链接：https://blog.csdn.net/pioayang/article/details/25406291

版权

oracle 专栏收录该内容

89 篇文章 1 订阅

订阅专栏

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

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> DEFINE
DEFINE _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(*)
----------
         1

SQL> 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 的设计的原则，违背了这个设计用来共享的思想。