如何实现由数字0到9然后是a到z组成的字段累加

Question

由一个表a 表中有一个字段id varchar2(10) 
字段值由数字0到9然后是a到z组成的
如何实现该字段自增
比如：字段值为10zz 加1后变成1100 

Answer 1

估计这个要将字段拆分，然后通过ascii()这个来实现
我不会，等高手

Answer 2

--不好意思，我只能实现0——z的循环，对于满位再进位，还没想到：
--再想想。 DROP TABLE test;
 CREATE TABLE test(id VARCHAR2(100),NAME VARCHAR2(100));
 DROP SEQUENCE test_seq;
 CREATE  SEQUENCE test_seq START WITH 1 INCREMENT BY 1 MAXVALUE 36 CYCLE;
 DROP TRIGGER  trg_test;
 CREATE  OR REPLACE TRIGGER trg_test
 BEFORE INSERT ON test
 FOR EACH ROW 
 DECLARE
 num NUMBER;
 id  VARCHAR2(100);
 BEGIN 
     SELECT test_seq.NEXTVAL-1 INTO num FROM dual;
     IF num>=10 THEN
         SELECT chr(num-10+97) INTO :NEW.id FROM dual;
     ELSE 
         :NEW.id:=num;
     END IF;
 END; BEGIN 
 FOR i IN 1..40 LOOP 
 INSERT INTO test VALUES('test'||i,'name||'||i);
 END LOOP;
 END;

Answer 3

SQL> create or replace function f_trans_36(i_num number)return varchar2
  2   as
  3   v_i  number;
  4   v_str varchar2(32767);
  5   begin
  6     v_i:=trunc(i_num);
  7     v_str:=null;
  8     loop
  9     v_str:=case when mod(v_i,36)<10 then mod(v_i,36)||'' else chr(mod(v_i,36)+87) end||v_str;
 10     v_i:=trunc(v_i/36);
 11     exit when v_i=0;
 12     end loop;
 13     return v_str;
 14   end;
 15  /
 
Function created
 
SQL> 
SQL> create sequence SEQ_trans_36
  2  minvalue 1
  3  maxvalue 999999999999999999999999999
  4  start with 1
  5  increment by 1
  6  nocache;
 
Sequence created
 
SQL> 
SQL> create table tbl_36
  2  (id varchar2(100),
  3   name varchar2(100));
 
Table created
 
SQL> insert into tbl_36
  2  select f_trans_36(SEQ_trans_36.nextval),rownum from dual connect by rownum<=40;
 
41 rows inserted
 
SQL> select * from tbl_36;
 
ID                                                                               NAME
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1                                                                                1
2                                                                                2
3                                                                                3
4                                                                                4
5                                                                                5
6                                                                                6
7                                                                                7
8                                                                                8
9                                                                                9
a                                                                                10
b                                                                                11
c                                                                                12
d                                                                                13
e                                                                                14
f                                                                                15
g                                                                                16
h                                                                                17
i                                                                                18
j                                                                                19
k                                                                                20
 
ID                                                                               NAME
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
l                                                                                21
m                                                                                22
n                                                                                23
o                                                                                24
p                                                                                25
q                                                                                26
r                                                                                27
s                                                                                28
t                                                                                29
u                                                                                30
v                                                                                31
w                                                                                32
x                                                                                33
y                                                                                34
z                                                                                35
10                                                                               36
11                                                                               37
12                                                                               38
13                                                                               39
14                                                                               40
15                                                                               41
 
41 rows selected
 
SQL>

Answer 4

SQL> create or replace trigger tri_36
  2    before insert on tbl_36
  3    for each row
  4  declare
  5    -- local variables here
  6  v_num number;
  7  begin
  8    select SEQ_trans_36.nextval into v_num from dual;
  9    :new.id:= f_trans_36(v_num);
 10  end tri_36;
 11  /
 
Trigger created
 
SQL> insert into tbl_36(name)values(42);
 
1 row inserted
 
SQL> select * from tbl_36 where name='42';
 
ID                                                                               NAME
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
16                                                                               42
 
SQL> 

Answer 5

还是使用序列加trigger实现自增列,只不过把10进制转成36进制.
使用连除进行数制转换