在不同版本中, 在PLSQL中MAX/MIN函数对CHAR型数据处理的返回类型不同

在不同版本中, 在PLSQL中MAX/MIN函数对CHAR型数据处理的返回类型不同

以下版本返回VARCHAR2类型：
9.2.0.4
9.2.0.5
9.2.0.6
9.2.0.7
10.1.0.1
10.1.0.2
10.1.0.3

以下版本返回CHAR类型：
8.1.7.4
10.1.0.4
10.1.0.5
10.2.0.1

注意：无论在哪个版本下在sqlplus下返回都是char类型

测试例子：

[@more@]

create table tbl_a ( r1 char(1) );
create table tbl_b ( r1 char(6), r2 char(6) );
insert into tbl_a values ( '1' );
insert into tbl_b values ( '1', 'BB' );
commit;

DECLARE
CURSOR C1 IS
SELECT MAX(R1) A_R1 FROM TBL_A;
ORA_RTN NUMBER;
WK_R2 CHAR(6);
BEGIN
FOR C1REC IN C1 LOOP
SELECT R2 INTO WK_R2 FROM TBL_B
WHERE R1 = C1REC.A_R1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TBL_B.R2=' || WK_R2);
END;
/

在返回是VARCHAR2类型的版本中,会报错ORA-1403 no data found
在返回是CHAR类型的版本中, 会正确执行

使用跟踪事件10046得到跟踪信息：

CURSOR #3 is "SELECT R2 FROM TBL_B WHERE R1 = :b1"

** 10.1.0.5
=====================================================================
BINDS #6:
bind 0: dty=96 mxl=32(01) mal=00 scl=00 pre=00 oacflg=10 oacfl2=0001 size=32 offset=0
bfp=b750a664 bln=32 avl=01 flg=05
value="1"

=====================================================================
dty=96 --&gt CHAR mxl=32(01) --&gt length=1

** 10.1.0.3
=====================================================================
BINDS #4:
bind 0: dty=1 mxl=32(01) mal=00 scl=00 pre=00 oacflg=13 oacfl2=206001 size=32 offset=0
bfp=00000000 bln=32 avl=00 flg=09
value="1"

=====================================================================
dty=1 --&gt VARCHAR2 mxl=32(01) --&gt length=1
.

** 9.2.0.4
=====================================================================
BINDS #3:
bind 0: dty=1 mxl=32(01) mal=00 scl=00 pre=00 oacflg=03 oacfl2=c000000000000001 size=32 offset=0
bfp=800000010017c7a0 bln=32 avl=01 flg=05
value="1"

=====================================================================
dty=1 --&gt VARCHAR2 mxl=32(01) --&gt length=1
.

TBL_A.R1 is CHAR(1). But bind variable is VARCHAR2(1).

So "nonpadded comparison" is used. '1 ' is not equal to '1'.
And ORA-100 occurs
.
.

** 8.1.7.4
=====================================================================
BINDS #3: bind 0: dty=96 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=03 oacfl2=5000000001 size=4000 offset=0
bfp=800000010010fab8 bln=4000 avl=4000 flg=05
value="1
.
.
"...
=====================================================================
dty=96 --&gt CHAR mxl=4000(4000) --&gt length=4000
.
TBL_A.R1 is CHAR(1). But bind variable is CHAR(4000).
So "blank-padded comparison" is used and the record is returned.
.
When SELECT MAX(R1) A_R1 FROM TBL_A is executed from SQL*Plus, A_R1 is returned as CHAR(1).
But in PL/SQL, A_R1 is returned as VARCHAR2(1).
.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3898/viewspace-867807/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3898/viewspace-867807/