用PLSQL将整数实现逆序

用PLSQL将整数实现逆序，比如123转换为321 无 /* Formatted on 2015/7/15 14:56:34 (QP5 v5.163.1008.3004) */CREATE OR REPLACE FUNCTION get_reverse_value (id IN NUMBER) RETURN VARCHAR2IS ls_id VARCHAR2 (10); ls_last_item VARCHAR2 (10); ls_curr_it

用PLSQL将整数实现逆序， 比如 123 转换为 321 <无>

/* Formatted on 2015/7/15 14:56:34 (QP5 v5.163.1008.3004) */
CREATE OR REPLACE FUNCTION get_reverse_value (id IN NUMBER)
   RETURN VARCHAR2
IS
   ls_id          VARCHAR2 (10);
   ls_last_item   VARCHAR2 (10);
   ls_curr_item   VARCHAR2 (10);
   ls_zero        VARCHAR2 (10);
   li_len         INTEGER;
   lb_stop        BOOLEAN;
BEGIN
   ls_id := TO_CHAR (id);
   li_len := LENGTH (ls_id);
   ls_last_item := '';
   ls_zero := '';
   lb_stop := FALSE;

   WHILE li_len > 0
   LOOP
      ls_curr_item := SUBSTR (ls_id, li_len, 1);

      IF ls_curr_item = '0' AND lb_stop = FALSE
      THEN
         ls_zero := ls_zero || ls_curr_item;
      ELSE
         lb_stop := TRUE;
         ls_last_item := ls_last_item || ls_curr_item;  
      END IF;

      ls_id := SUBSTR (ls_id, 1, li_len - 1);
      li_len := LENGTH (ls_id);
   END LOOP;

   RETURN (ls_last_item || ls_zero);
END get_reverse_value;

/*
此程序如下：例如 123
循环1： ls_curr_item=3, ls_last_item= '||3=3, ls_id=12, li_en=2
循环2：ls_curr_item=2, ls_last_item=3||2=32, ls_id=1, li_en=1
循环3：ls_curr_item=1, ls_last_item=32||1=321, ls_id='', li_en=0
此时 li_en=0 跳出循环，返回 ls_last_item=321

例如100
循环1： ls_curr_item=0, ls_zero= ''||0=0, ls_id=20, li_en=2
循环2：ls_curr_item=0, ls_zero=0||0=00, ls_id=1, li_en=1
循环3：ls_curr_item=1, ls_last_item=''||1=1, ls_id='', li_en=0
此时 li_en=0 跳出循环，返回 ls_last_item=1,ls_zero=00,ls_last_item || ls_zero=100
*/

//运行结果为
SQL> select * from rebuild_test_cf where name <20;

        ID NAME
---------- ----------
         1 1
         2 2
         3 3
         4 4
         5 5
         6 6
         7 7
         8 8
         9 9
        10 10
        11 11

        ID NAME
---------- ----------
        21 12
        31 13
        41 14
        51 15
        61 16
        71 17
        81 18
        91 19