[20190612]NULL的数据类型.txt

[20190612]NULL的数据类型.txt --//前几天听别人讲了NULL的数据类型导致产生大量子光标的案例，java的程序，我检索看到类似链接： --//http://www.usn-it.de/index.php/2010/08/04/oracle112-mutex-s-too-many-child-cursors/ . setNUMBER(2)  => Bind Var. is number . setNULL(2, java.sql.Types.INTEGER)  => Bind Var. is NUMBER but setNUMBER(2)  => Bind Var. is varchar2. But simply using setNull by default means setNull.NULL, and in both cases the setter method will not automatically use the data type of the field in the table. Instead, the default is VARCHAR2, and exactly this makes my datatype different to previous executions! So in theory, setting over 30 numeric values to null and non-null in an alternating matter, creates 2^30 child cursors. Nice. --//我不熟悉java编程，似乎讲使用setNull定义的缺省类型是VARCHAR2,应该写成类似setNULL(2, java.sql.Types.INTEGER)的形式，才 --//能返回整形的NULL。 --//我记得以前yangtingkun讲过关于NULL的数据类型的一些例子，自己拿sqlplus验证看看： 1.环境： SCOTT@test01p> @ ver1 PORT_STRING          VERSION    BANNER                                                                       CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0 SCOTT@test01p> create table t as select rownum id1 ,rownum id2 from dual connect by level<=10; Table created. SCOTT@test01p> variable v_id2 number ; SCOTT@test01p> exec :v_id2 := null ; PL/SQL procedure successfully completed. SCOTT@test01p> update /*+ find_me */ t set id2 =:v_id2  where id1=3; 1 row updated. SCOTT@test01p> rollback ; Rollback complete. SCOTT@test01p> variable v_id2 varchar2(10) ; SCOTT@test01p> exec :v_id2 := null ; PL/SQL procedure successfully completed. SCOTT@test01p> update /*+ find_me */ t set id2 =:v_id2  where id1=3; 1 row updated. SCOTT@test01p> rollback ; Rollback complete. --//确定:sql_id=3mwwz3vay4zn7. 2.看看是否产生子光标： SCOTT@test01p> select sql_id,sql_text,child_number,executions from v$sql where sql_id='3mwwz3vay4zn7'; SQL_ID        SQL_TEXT                                                     CHILD_NUMBER EXECUTIONS ------------- ------------------------------------------------------------ ------------ ---------- 3mwwz3vay4zn7 update /*+ find_me */ t set id2 =:v_id2  where id1=3                    0          1 3mwwz3vay4zn7 update /*+ find_me */ t set id2 =:v_id2  where id1=3                    1          1 SCOTT@test01p> @ share 3mwwz3vay4zn7 SQL_TEXT                       = update /*+ find_me */ t set id2 =:v_id2  where id1=3 SQL_ID                         = 3mwwz3vay4zn7 ADDRESS                        = 000007FEFFB24E10 CHILD_ADDRESS                  = 000007FF1184DB48 CHILD_NUMBER                   = 0 REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(8)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>3</original_oacflg><original_oacdty>2</original_oacdty><new_oacdty>1</new_oacdty></ChildNode> -------------------------------------------------- SQL_TEXT                       = update /*+ find_me */ t set id2 =:v_id2  where id1=3 SQL_ID                         = 3mwwz3vay4zn7 ADDRESS                        = 000007FEFFB24E10 CHILD_ADDRESS                  = 000007FF11C97D58 CHILD_NUMBER                   = 1 REASON                         = <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(8)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>3</original_oacflg><original_oacdty>1</original_oacdty><new_oacdty>2</new_oacdty></ChildNode> -------------------------------------------------- PL/SQL procedure successfully completed. --//不能共享的原因是Bind mismatch.可以发现如果update字段许多类型是number,按照上面的编程设置NULL(指使用setnull(N))会出现大量的子光标. --//可以看出即使是NULL也是有数据类型的,不一致也会导致产生子光标.继续测试: SCOTT@test01p> variable v_id2 number ; SCOTT@test01p> exec :v_id2 := 2 ; PL/SQL procedure successfully completed. SCOTT@test01p> update /*+ find_me */ t set id2 =:v_id2  where id1=3; 1 row updated. SCOTT@test01p> rollback ; Rollback complete. SCOTT@test01p> select sql_id,sql_text,child_number,executions from v$sql where sql_id='3mwwz3vay4zn7'; SQL_ID        SQL_TEXT                                             CHILD_NUMBER EXECUTIONS ------------- ---------------------------------------------------- ------------ ---------- 3mwwz3vay4zn7 update /*+ find_me */ t set id2 =:v_id2  where id1=3            0          2 3mwwz3vay4zn7 update /*+ find_me */ t set id2 =:v_id2  where id1=3            1          1