SQL 执行正常，但 PL 里查无数据的问题

问题描述

OceanBase 数据库 Oracle 模式下，SQL 语句在 PL 存储过程执行无数据，但手动单独执行有数据。

测试案例如下。

1. 创建测试表。

   obclient [OBORACLE]>  create table test(cstno char(15) not null,  stt char(1) not null,passwd varchar2(75) not null); 
   Query OK, 0 rows affected (1.059 sec) 
   

2. 插入测试数据。

   obclient [OBORACLE]>  insert into test( cstno, stt, passwd ) values( 'EX10001','0','AASBBDD'); 
   Query OK, 1 row affected (0.416 sec)
   

3. 提交插入数据。

   obclient [OBORACLE]> commit; 
   Query OK, 0 rows affected (0.005 sec) 
   

4. 手动执行如下语句。

   obclient [OBORACLE]> select * from test where cstno='EX10001';
   

   输出结果如下。

   +-----------------+-----+---------+
   | CSTNO           | STT | PASSWD  |
   +-----------------+-----+---------+
   | EX10001         | 0   | AASBBDD |
   +-----------------+-----+---------+
   1 row in set (0.059 sec)
   

5. 开启打开系统默认设置的输出功能。

   obclient [OBORACLE]> set serveroutput on;
   Query OK, 0 rows affected (0.452 sec)
   

6. 创建 PL 存储过程，当存在值，输出对应的 PASSWD 列值，如果数据不存在 输出 ERROR，当传入的值为 'EX10001' 时，输出了ERROR。

   obclient [OBORACLE]> declare
                        a varchar2(15);
                        b varchar2(20);
                        begin
                        a:='EX10001';  --- 这里使用未补全的值
                        proc_mytest(a,b);
                        dbms_output.put_line('in_custNo:'  ||a);
                        dbms_output.put_line('out_retcode:'||b);
                        end;
                        /
   Query OK, 1 row affected (0.051 sec)
   

   输出结果如下。

   in_custNo:EX10001
   out_retcode:ERROR
   

问题原因

DDL 中字段 CSTNO 的定义与 PL 变量参数 in_custNo 的类型和长度不一致，DDL 中为 CHAR(15)，而 PL 变量为 VARCHAR2。在 Oracle 模式下 CHAR 为定长类型，实际存储的是补充空字符到指定长度的值，而对字符进行比较时 Oracle 不会主动去掉字符后面的空格，所以在这个案例中出现了因字符长度不同而不匹配的结果。

此问题不仅仅存在 PL 中，如果在 SQL 中主动对字符类型做了转换，也可能会出现相同的现象。

示例如下。

obclient [OBORACLE]> select * from test where cstno=CAST('EX10001' AS VARCHAR2(15));
Empty set (0.001 sec)

适用版本

OceanBase 数据库所有版本。

解决方案

1. 把 PL 的变量类型改为和字段类型一致，使用 CHAR 类型。

   obclient [OBORACLE]> declare
                           a varchar(15);
                           b varchar(20);
                           begin
                           a:='EX10001';    --- 这里使用未补全的值
                           proc_mytest(a,b);
                           dbms_output.put_line('in_custNo:'  ||a);
                           dbms_output.put_line('out_retcode:'||b);
                           end;
                           /
    Query OK, 1 row affected (0.066 sec)
   

   输出结果如下。

   in_custNo:EX10001
   out_retcode:AASBBDD
   

2. 手动执行如下语句。

   obclient [OBORACLE]> select length('EX10001        ') from dual;
   

   输出结果如下。

   +-------------------+
   | LENGTH('EX10001') |
   +-------------------+
   |                15 |
   +-------------------+
   1 row in set (0.002 sec)
   

3. 开启打开系统默认设置的输出功能。

     obclient [OBORACLE]> set serveroutput on;
     Query OK, 0 rows affected (0.458 sec)
   

4. 创建 PL 存储过程，按照字段定义对 PL 变量的值主动用空格来补齐长度。

   obclient [OBORACLE]> declare
            a varchar2(15);
            b varchar2(20);
            begin
            a:='EX10001        '; --- 这里使用补全后的值
            proc_mytest(a,b);
            dbms_output.put_line('in_custNo:'  ||a);
            dbms_output.put_line('out_retcode:'||b);
            end;
            /
   Query OK, 1 row affected (0.049 sec)
   

   输出结果如下。

   in_custNo:EX10001  
   out_retcode:AASBBDD