ITPub博客

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

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

原创 Oracle 作者:yaanzy 时间:2006-09-28 08:50:04 0 删除 编辑

在不同版本中, 在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 --> CHAR mxl=32(01) --> 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 --> VARCHAR2 mxl=32(01) --> 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 --> VARCHAR2 mxl=32(01) --> 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 --> CHAR mxl=4000(4000) --> 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    108
  • 访问量
    762224