Oracle之to_char()小碰撞

2014/11/6，今天在练习存储过程的时候，to_char()函数和我小小的闹腾了一把。

/* PROCEDURE RaceInfo
**   Join tables: BoatCrew，Boat and Person,
**   to get the information:
**     BoatCategory, BowNumber, Position, LastName, FirstName
**   Note:
**     the posible value of the column Position is between 0 and 9, if
**     the position is 0, print 'Cox';
**     others, print the string of three characters of the number.
**     for an instance, if position is 1, print '001'
**   Input: BoatCat -- the category of the Boat
**                     if BoatCat is 'ALL', fetch all the records,
**                     otherwise just print the records of the special
**                     boat category
**          BowNo   -- the bow number
**   Output: just print the information
*/
CREATE OR REPLACE PROCEDURE RaceInfo
(BoatCat IN VARCHAR2, BowNo IN VARCHAR2) AS
  v_Count INTEGER := 0;
  v_Position VARCHAR(3);
  CURSOR cv_allPerson IS
    SELECT BoatCategory, BowNumber, Position, 
           LastName, FirstName
    FROM BoatCrew   JOIN Boat 
                   USING (BoatID)
                    JOIN Person 
                   USING (PersonID)
    ORDER BY BoatCategory, BowNumber, Position;

  CURSOR cv_onePerson IS
    SELECT BoatCategory, BowNumber, Position, 
           LastName, FirstName
    FROM BoatCrew   JOIN Boat 
                   USING (BoatID)
                    JOIN Person 
                   USING (PersonID)
    WHERE UPPER(BoatCategory) = UPPER(BoatCat)
      AND UPPER(BowNumber) = UPPER(BowNo)
    ORDER BY BoatCategory, BowNumber, Position;

BEGIN
  SELECT COUNT(*)
  INTO v_Count
  FROM Boat
  WHERE UPPER(BoatCategory) = UPPER(BoatCat)
    AND UPPER(BowNumber) = UPPER(BowNo);

  IF UPPER(BoatCat) = 'ALL' THEN
    FOR X IN cv_allPerson LOOP
      IF X.Position = 0 THEN
        v_Position := 'Cox';
      ELSE
        v_Position := TO_CHAR(X.Position, '009');
      END IF;

      DBMS_OUTPUT.PUT_LINE(
        ' ' || X.BoatCategory ||
        ' ' || X.BowNumber ||
        ' ' || v_Position ||
        ' ' || X.LastName ||
        ' ' || X.FirstName
      );
    END LOOP;
  ELSIF v_Count > 0 THEN
    FOR X IN cv_onePerson LOOP
      IF X.Position = 0 THEN
        v_Position := 'Cox';
      ELSE
       v_Position := TO_CHAR(X.Position, '009');
      END IF;

      DBMS_OUTPUT.PUT_LINE(
        ' ' || X.BoatCategory ||
        ' ' || X.BowNumber ||
        ' ' || v_Position ||
        ' ' || X.LastName ||
        ' ' || X.FirstName
      );
    END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Bat parameters');
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END RaceInfo;
/

当我把，存储过程放到SQL * PLUS进行编译的时候，没有报错。但是我运行如下测试案例的时候，问题就出来了。

CALL RaceInfo('ALL', 'X');

CALL RaceInfo('Crew-8', '3');

报出的错误如下：

从出现的错误提示，比较容易判断可能是我定义的变量大小不太合适。我一开始就把注意力集中在了v_Position（v_Position VARCHAR2(3)）这个变量。但是，从存储过程的执行情况来看，它输出了一行数据，输出数据的Position列是‘Cox’，说明该行的值是0。说明v_Position := 'Cox';这个分支运行是没有问题的。然后问题就缩小到了v_Position := TO_CHAR(X.Position,'009');这个分支上。但是，老实说，以我目前掌握的情况，我真心没有看出这儿有什么问题。

从报出的问题来看，是我定义变量空间太小的缘故。所以，我抱着试一试的心态把我的变量空间调大一些。将v_PositionVARCHAR2(3)调成v_PositionVARCHAR2(4)，重新编译和调用之后，确实没有报错了。但是从输出情况来看，又引发了新的问题。以下是输出结果：

当Position列的值不为0的时候，走的是v_Position := TO_CHAR(X.Position,'009');这个分支，从输出情况来看，前面多出了一个空格。这次，情况很明显，问题应该出在TO_CHAR()函数身上。

然后就是，果断的的使用搜索引擎。

终于，在某个地方翻到了如下解释：

 

To_char(var, [format]);

当[format]为‘099’这种模式的时候，假设N代表[format]中0和9的总个数。

若var的位数小于或等于N，则转换后的字符串前面有一个空字符和(N – var的位数 – 1)个0;

若var的位数大于N时，则无法正确的转换。

 

到现在为止，一切都已经水落实出了。我一开始使用v_Position :=TO_CHAR(X.Position, '009');转化的时候，因为Position是NUMBER(1)，转化出来的结果就是空字符 + ‘001’这种模式，我调用存储过程的时候，就报出了“字符串缓冲区太小”（那个时候我是VARCHAR2(3)，显然不够）的错。当我将v_Position换成VARCHAR2(4)的时候，就刚好够了，但是，由于前面有一个空字符的原因，所有，打印出来的效果，就不太美观。

 

所以，最后的解决方式就是，用TRIM将空格给去掉：

v_Position := TRIM(TO_CHAR(X.Position, ‘009’));

输出结果如下：