Oracle 为表某个字段进行字母数字组合编码

本文内容

• 演示用 Oracle 函数为表的某个字段进行字母数字组合编码
• 数据类型——字符数组
• 分割字符串 comm_splitstr
• 字母数字组合进位 comm_numlettercarry
• 字母数字组合自增 comm_increasenumletter
• 修改记录

 

我们会遇到这样的情况：数据库的表，除了数字型的主键（ID）外，出于某种目的，有时，也需要为其他某个字段进行编码。比如，若编码规则有前缀，则建立视图后，根据编码，可以知道该条记录来自于“哪里（哪个业务流程）”。

也许，像 GUID 这样随机生成是个选择，但这种毫无规律的东西，在业务里很难使用。而且，实际中不确定是否能生成唯一的，虽然理论上不唯一的概率很小。

本文演示编码规则：字母数字组合自增，也就是 0~9 和 A~Z。字母不区分大小写（你可以自己修改代码，让它区分大小写，这样编码更多）。至于多少位 VARCHAR2(...)，酌情处理。至于何时溢出，字母数字组合，加上足够的位数，应该够了，再多，就分表吧。

你也许会想，用编程语言一样能实现该功能，而且写起来还容易，那为什么还要用 Oracle 来写？这也许是对的。但若业务逻辑很复杂，同时，需要向数据库几张表写数据，并在每个表写多条记录时，要是这多条记录互相有关联性，就不那么容易了，特别是，SQL 的执行是事务型的，就更不容易控制了——要访问多少次数据库啊。

把功能性的东西，用 Oracle 写好，函数也好，存储过程也好，再在应用程序创建 Oracle 匿名的过程，是个不错的选择。将复杂性分散开，Oracle 承担一部分，应用程序承担一部分。而应用程序主要承担业务本身和它们之间的逻辑。

因此，软件开发是数据库，应用程序等，各个方面的问题，如果仅仅认为编程，就过于简单了。

 

演示用 Oracle 函数为表的某个字段进行字母数字组合编码

若执行以下 SQL ，获得指定字母数字组合字符串加一的字符串，其中，Comm_IncreaseNumLetter(v_str in varchar2) 为自定义函数：

select codesystem.comm_increasenumletter('5') as v1,

       codesystem.comm_increasenumletter('aA') as v2,

       codesystem.comm_increasenumletter('01a') as v3,

       codesystem.comm_increasenumletter('01aZ') as v4,

       codesystem.comm_increasenumletter('0zzZzz') as v5,

       codesystem.comm_increasenumletter('0000zZzzZ') as v6,

       codesystem.comm_increasenumletter('0000zzZZabCDe') as v7

from dual;

下面实现这个功能。分为一个自定义数组和三个自定义函数：

• 数据类型——字符数组
• 分割字符串 comm_splitstr 用于分隔字母数字组合的字符串，并将其放到字符数组里。
• 字母数字组合进位 comm_numlettercarry 用于获得进位后的字符。
• 字母数字组合自增 comm_increasenumletter 用于字母数字组合字符串加一。

 

数据类型——字符数组

CREATE OR REPLACE TYPE codesystem_chr_tbl IS TABLE of VARCHAR2(1)

/

Oracle 包头和包体

CREATE OR REPLACE PACKAGE codesystem IS

  -- Author  : ADMINISTRATOR

  -- Created : 2012-11-9 11:38:02

  -- Purpose : 编码系统

  -- 字符数组

  const_num_letter codesystem_chr_tbl;

  -- 字符数组 原串和目的串

  v_src_chr_tbl  codesystem_chr_tbl;

  v_desc_chr_tbl codesystem_chr_tbl;

  -- 长度和步长

  v_length NUMBER;

  v_step   NUMBER;

  FUNCTION Comm_increasenumletter(code IN VARCHAR2) return codesystem_chr_tbl;

  FUNCTION comm_numlettercarry(v_char IN VARCHAR2) return VARCHAR2;

  FUNCTION comm_splitstr(v_str IN VARCHAR2) RETURN codesystem_chr_tbl;

END codesystem;

CREATE OR REPLACE PACKAGE BODY codesystem IS

  /* 字母数字进位 */

  FUNCTION comm_numlettercarry(v_char in VARCHAR2) RETURN VARCHAR2 IS

    RESULT VARCHAR2(1);

    i      NUMBER;

  BEGIN

    IF const_num_letter IS NULL THEN

      const_num_letter := NEW codesystem_chr_tbl('0',

                                                 '1',

                                                 '2',

                                                 '3',

                                                 '4',

                                                 '5',

                                                 '6',

                                                 '7',

                                                 '8',

                                                 '9',

                                                 'A',

                                                 'B',

                                                 'C',

                                                 'D',

                                                 'E',

                                                 'F',

                                                 'G',

                                                 'H',

                                                 'I',

                                                 'J',

                                                 'K',

                                                 'L',

                                                 'M',

                                                 'N',

                                                 'O',

                                                 'P',

                                                 'Q',

                                                 'R',

                                                 'S',

                                                 'T',

                                                 'U',

                                                 'V',

                                                 'W',

                                                 'X',

                                                 'Y',

                                                 'Z');

    END IF;

    i := 1;

    WHILE i <= const_num_letter.count LOOP

      if const_num_letter(i) = v_char THEN

        exit;

      end if;

      i := i + 1;

    end LOOP;

    IF i = const_num_letter.count THEN

      RESULT := const_num_letter(1);

    ELSIF i > const_num_letter.count THEN

      RESULT := '';

    ELSE

      RESULT := const_num_letter(i + 1);

    end IF;

    RETURN RESULT;

  end Comm_NumletterCarry;

  /* 数字字母自增 */

  FUNCTION comm_increasenumletter(code in VARCHAR2) RETURN codesystem_chr_tbl IS

    RESULT  codesystem_chr_tbl;

    v_char  VARCHAR2(1);

    isCarry NUMBER; -- 是否有进位

    i       NUMBER;

  BEGIN

    v_src_chr_tbl  := comm_splitstr(upper(code));

    i              := v_src_chr_tbl.count;

    v_desc_chr_tbl := codesystem_chr_tbl();

    WHILE i >= 1 LOOP

      IF i = v_src_chr_tbl.count THEN

        -- 是否进位      

        IF v_src_chr_tbl(i) = 'Z' THEN

          isCarry := 1;

        ELSE

          isCarry := 0;

        end IF;

        -- 加 1    

        v_char := comm_numlettercarry(v_src_chr_tbl(i));

      ELSE

        IF isCarry = 1 THEN

          v_char := comm_numlettercarry(v_src_chr_tbl(i));

        ELSE

          v_char := v_src_chr_tbl(i);

        end IF;

        -- 是否再进位      

        IF v_src_chr_tbl(i) = 'Z' THEN

          IF isCarry = 1 THEN

            isCarry := 1;

          END IF;

        ELSE

          isCarry := 0;

        END IF;

      END IF;

      v_desc_chr_tbl.extend;

      v_desc_chr_tbl(v_desc_chr_tbl.count) := v_char;

      i := i - 1;

    END LOOP;

    IF isCarry = 1 THEN

      v_desc_chr_tbl.extend;

      v_desc_chr_tbl(v_desc_chr_tbl.count) := v_char;

    END IF;

    RESULT := v_desc_chr_tbl;

    RETURN RESULT;

  END comm_increasenumletter;

  /* 字符串单个分割 */

  FUNCTION comm_splitstr(v_str in VARCHAR2) RETURN codesystem_chr_tbl IS

    i NUMBER;

  BEGIN

    v_src_chr_tbl := codesystem_chr_tbl();

    i             := 1;

    v_length      := LENGTH(v_str);

    v_step        := 1;

    WHILE i <= v_length LOOP

      v_src_chr_tbl.extend; -- 增加

      v_src_chr_tbl(v_src_chr_tbl.count) := substr(v_str, i, 1);

      i := i + 1;

    END LOOP;

    RETURN v_src_chr_tbl;

  END Comm_SplitStr;

END;

修改记录

• 2012-11-09 第一次 [UPDATE]

下载 Demo