在Oracle中用Java进行数据操作
一个PL /SQL的办法
让我们先来看看一个PL/SQL的方法。代码已经进行了一点优化,分解已经启动,在位置或迭代方面已经考虑到。这也是基于输入至少22位长的数字。
CREATE OR REPLACE FUNCTION gen_barcode36 (i_coupon_number NUMBER) RETURN VARCHAR2 IS v_number number; v_curval number; v_curinc number := power(36,14); v_true number := 0 ; v_pos number := 15; v_dec_count number := 0 ; v_pos_val number := 0 ; v_cur_num number := 0 ; v_test_num number := 0 ; v_new_num number := 0 ; v_cur_digit char := ''; v_new_val varchar2(15) := ''; BEGIN v_number := i_coupon_number; while ( v_true = 0 ) loop v_curinc := v_curinc * 36; v_curval := v_number / v_curinc; if ( v_curval < 1 ) then v_true := 1; else v_pos := v_pos + 1; end if; end loop; v_dec_count := v_pos; v_new_val := NULL; v_cur_num := v_number; WHILE ( v_dec_count > 0) LOOP v_pos_val := power(36,v_dec_count - 1); v_test_num := trunc(v_cur_num/v_pos_val); select decode(v_test_num,35,'z', 34,'y', 33,'x', 32,'w', 31,'v', 30,'u', 29,'t', 28,'s', 27,'r', 26,'q', 25,'p', 24,'o', 23,'n', 22,'m', 21,'l', 20,'k', 19,'j', 18,'i', 17,'h', 16,'g', 15,'f', 14,'e', 13,'d', 12,'c', 11,'b', 10,'a', 9,'9', 8,'8', 7,'7', 6,'6', 5,'5', 4,'4', 3,'3', 2,'2', 1,'1', '0') INTO v_cur_digit FROM DUAL; IF ( v_new_val IS NOT NULL ) THEN v_new_val := v_new_val || v_cur_digit; ELSE v_new_val := v_cur_digit; END IF; v_cur_num := v_cur_num - (v_pos_val * v_test_num); v_dec_count := v_dec_count - 1; END LOOP; RETURN v_new_val; END gen_barcode36; /
一个不同的版本有一些输出附件,所以你能够看到数字是如何减少的。
SQL> select gen_barcode36(7700000000000000000000) from dual; GEN_BARCODE36(7700000000000000000000) ----------------------------------------------------- 1950zn8fqxjygow Starting with 7700000000000000000000 Position: 6140942214464815497216 current number: 1 Position 14 current value: 1 Position: 170581728179578208256 current number: 9 Position 13 current value: 19 Position: 4738381338321616896 current number: 5 Position 12 current value: 195 Position: 131621703842267136 current number: 0 Position 11 current value: 1950 Position: 3656158440062976 current number: 35 Position 10 current value: 1950z Position: 101559956668416 current number: 23 Position 9 current value: 1950zn Position: 2821109907456 current number: 8 Position 8 current value: 1950zn8 Position: 78364164096 current number: 15 Position 7 current value: 1950zn8f Position: 2176782336 current number: 26 Position 6 current value: 1950zn8fq Position: 60466176 current number: 33 Position 5 current value: 1950zn8fqx Position: 1679616 current number: 19 Position 4 current value: 1950zn8fqxj Position: 46656 current number: 34 Position 3 current value: 1950zn8fqxjy Position: 1296 current number: 16 Position 2 current value: 1950zn8fqxjyg Position: 36 current number: 24 Position 1 current value: 1950zn8fqxjygo Position: 1 current number: 32 Position 0 current value: 1950zn8fqxjygow Final value: 1950zn8fqxjygow
0
相关文章