[20190930]oracle number类型存储转化脚本.txt

来源：这里教程网时间：2026-03-03 14:14:56 作者：

[20190930]oracle number类型存储转化脚本.txt --//没事写了一个oracle number类型存储转化脚本。本来想用C来编写,感觉不好写。 --//还是采用自己擅长的bash编程。 --//关于oracle number如何编码可以看yangtingkun的blog,链接如下： --//http://blog.itpub.net/4227/viewspace-68510/=>Oracle基本数据类型存储格式浅析（二）——数字类型 1.数据的输入问题： --//可能输入的数据并不"归整",比如 0 ，可能输入 0.00,或者-0.可能输入有逗号空格等等。 --//另外就是输入的数字可能是2.1000000 或者 5.00000000之类的情况，必须先预处理。 --//另外说明一下：我脚本输入数据不支持科学记数法。比如1e4之类的写法。 --//通过简单的加0处理，另外如果字串太长，执行bc后会折行，必须删除"\n\\\r"字符(注：windows下bc的输出带\r字符。) --//主要代码如下: v_num=$(echo $v_num + 0 | bc -l | tr -d '\n\\\r' | sed -e "s/\.$[0-9]*[1-9]$0\+$/.\1/" -e "s/\.0\+$//") 2.负数处理的问题： --//负数结尾要补上0x66作为结束。实际上如果位数很多小数点后有40位，不需要加上0x66。 --//补上0x66实际上为了排序的需要，负数越大实际上越小。 --//我的处理先转化正数，设置v_sign=1表示负数。 3.如何获得幂指数。 --//oracle number采用百进制表示,我开始采用bc l函数(实际上ln函数)获得幂指数,感觉这个存在精度的问题，而且使用bc math库函数。 --//最终放弃这个方案。 --//后来仔细思考可以利用".",只要知道"."在数字串的位置就可以知道对于100的幂指数,具体看代码： --//获得字串"."位置可以使用expr命令，例子如下： v_pos=$(expr index $v_num ".") 4.剩余部分的编码(小数点部分)： --//我仅仅执行如下,特别定义scale=180,基本不会精度问题，删除尾部0(小数点之后)，注意看sed部分，最后删除开头的小数点。 v_tmp=$(echo "scale=180 ; $v_num / 100^($v_exp) " | bc | tr -d '\n\\\r'| sed -e "s/\.$[0-9]*[1-9]$0\+$/.\1/" -e "s/\.0\+$//" -e "s/^\.//") --//因为oracle number采用百进制,要保证v_tmp的长度是偶数，如果不是在结尾不0。 --//剩下的就简单了。 5.测试： $ cat test.txt 0 1 2 25 123 4100 -4100 41000000 -41000000 132004078 2.01 .3 .00000125 115.200003 -.00000125 -.3 -1 -5 -20032 -234.432 999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000 -999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000 .0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 -.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 123456789012345678901234567890123456789012 -123456789012345678901234567890123456789012 0.123456789012345678901234567890123456789012 -0.123456789012345678901234567890123456789012 $ cat test.txt | xargs -n 1 -I {} bash -c "echo {} ;./num2raw.sh {}" | paste - - 0 80 1 c1,02 2 c1,03 25 c1,1a 123 c2,02,18 4100 c2,2a -4100 3d,3c,66 41000000 c4,2a -41000000 3b,3c,66 132004078 c5,02,21,01,29,4f 2.01 c1,03,02 .3 c0,1f .00000125 be,02,1a 115.200003 c2,02,10,15,01,04 -.00000125 41,64,4c,66 -.3 3f,47,66 -1 3e,64,66 -5 3e,60,66 -20032 3c,63,65,45,66 -234.432 3d,63,43,3a,51,66 999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000 ff,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64 -999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000 00,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02 .0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 80,02 -.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 7f,64,66 123456789012345678901234567890123456789012 d5,0d,23,39,4f,5b,0d,23,39,4f,5b,0d,23,39,4f,5b,0d,23,39,4f,5b -123456789012345678901234567890123456789012 2a,59,43,2d,17,0b,59,43,2d,17,0b,59,43,2d,17,0b,59,43,2d,17,0b 0.123456789012345678901234567890123456789012 c0,0d,23,39,4f,5b,0d,23,39,4f,5b,0d,23,39,4f,5b,0d,23,39,4f,5b -0.123456789012345678901234567890123456789012 3f,59,43,2d,17,0b,59,43,2d,17,0b,59,43,2d,17,0b,59,43,2d,17,0b SCOTT@test01p> select a,dump(a,16) c80 from ty; A C80 ---------- -------------------------------------------------------------------------------- 0 Typ=2 Len=1: 80 1 Typ=2 Len=2: c1,2 2 Typ=2 Len=2: c1,3 25 Typ=2 Len=2: c1,1a 123 Typ=2 Len=3: c2,2,18 4100 Typ=2 Len=2: c2,2a -4100 Typ=2 Len=3: 3d,3c,66 41000000 Typ=2 Len=2: c4,2a -41000000 Typ=2 Len=3: 3b,3c,66 132004078 Typ=2 Len=6: c5,2,21,1,29,4f 2.01 Typ=2 Len=3: c1,3,2 .3 Typ=2 Len=2: c0,1f .00000125 Typ=2 Len=3: be,2,1a 115.200003 Typ=2 Len=6: c2,2,10,15,1,4 -.00000125 Typ=2 Len=4: 41,64,4c,66 -.3 Typ=2 Len=3: 3f,47,66 -1 Typ=2 Len=3: 3e,64,66 -5 Typ=2 Len=3: 3e,60,66 -20032 Typ=2 Len=5: 3c,63,65,45,66 -234.432 Typ=2 Len=6: 3d,63,43,3a,51,66 ~ Typ=2 Len=21: ff,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64 -~ Typ=2 Len=21: 0,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2 1.000E-130 Typ=2 Len=2: 80,2 -1.00E-130 Typ=2 Len=3: 7f,64,66 1.2346E+41 Typ=2 Len=21: d5,d,23,39,4f,5b,d,23,39,4f,5b,d,23,39,4f,5b,d,23,39,4f,5b -1.235E+41 Typ=2 Len=21: 2a,59,43,2d,17,b,59,43,2d,17,b,59,43,2d,17,b,59,43,2d,17,b .123456789 Typ=2 Len=21: c0,d,23,39,4f,5b,d,23,39,4f,5b,d,23,39,4f,5b,d,23,39,4f,5b -.12345679 Typ=2 Len=21: 3f,59,43,2d,17,b,59,43,2d,17,b,59,43,2d,17,b,59,43,2d,17,b 28 rows selected. --//我家里的电脑有点慢，大概每个需要2秒。 6.附上转化脚本如下: $ cat num2raw.sh #! /bin/bash #! number convert oracle raw, input parameter do not like 1.1e3. odebug=${ODEBUG:-0} # process input parameter ,delete "," and all spaces. save to variable v_num. and length to variable v_len. v_num="$*" v_num=${v_num//[, ]/} # strip trailing 0s in decimals or 0000.000 output 0 v_num=$(echo $v_num + 0 | bc | tr -d '\n\\\r' | sed -e "s/\.$[0-9]*[1-9]$0\+$/.\1/" -e "s/\.0\+$//") if [[ "$v_num" =~ ^-.*$ ]]; then v_sign=1 v_num=${v_num:1:180} else v_sign=0 fi if [ $odebug -eq 1 ] ; then echo v_num="$v_num" fi v_res="" if [ "$v_num" == "0" ]; then v_res="80" echo "$v_res" exit 0 fi # Guarantee . occur # v_tmp1=$(echo "scale=180; $v_num/1" | bc |tr -d '\n\\\r' | sed -e "s/\.$[0-9]*[1-9]$0\+$/.\1/") # v_pos=$(expr index $v_tmp1 ".") v_pos=$(expr index $v_num ".") if [ $v_pos -gt 1 ]; then v_exp=$(( v_pos/2 )) elif [ $v_pos -eq 0 ]; then v_exp=$(( (${#v_num}+1) /2 )) elif [ $v_pos -eq 1 ]; then v_tmp1=${v_num:1:180} v_tmp2=$(echo $v_tmp1 | sed 's/^0\+//g') v_exp=$(( (${#v_tmp2} - ${#v_tmp1})/2 )) else echo "number $v_num don't find dot!!" exit 1 fi v_exp1=$(printf "%02x" $(( $v_exp+192 ))) if [ $v_sign -eq 1 ]; then v_exp1=$(printf "%02x" $(( 0xff - 0x${v_exp1} ))) fi v_res=${v_exp1}${v_res} v_tmp=$(echo "scale=180 ; $v_num / 100^($v_exp) " | bc | tr -d '\n\\\r'| sed -e "s/\.$[0-9]*[1-9]$0\+$/.\1/" -e "s/\.0\+$//" -e "s/^\.//") # oracle number type max length is 22 bytes (not 22 is 21 bytes??), 1 bytes exponent. v_tmp=${v_tmp:0:40} v_len=${#v_tmp} v_tmp1=$(( $v_len % 2 )) if [ $v_tmp1 -ne 0 ]; then v_tmp=${v_tmp}"0" v_len=$(( $v_len+1 )) fi if [ $odebug -eq 1 ] ; then echo v_num="$v_num" v_len="$v_len" v_exp="$v_exp" v_exp1="$v_exp1" v_tmp="$v_tmp" fi if [ $v_sign -eq 0 ]; then for ((i=0;i<$v_len;i+=2)) do v_tmp1=$(printf "%02x" $(( ${v_tmp:i:2} + 1 ))) v_res=${v_res}","${v_tmp1} done else for ((i=0;i<$v_len;i+=2)) do v_tmp1=$(printf "%02x" $(( 101 - ${v_tmp:i:2} ))) v_res=${v_res}","${v_tmp1} done fi # for ((i=0;i<$v_len;i+=2)) # do # if [ $v_sign -eq 0 ]; then # v_tmp1=$(printf "%02x" $(( ${v_tmp:i:2} + 1 ))) # else # v_tmp1=$(printf "%02x" $(( 101 - ${v_tmp:i:2} ))) # fi # v_res=${v_res}","${v_tmp1} # done if [ $v_sign -eq 1 -a $v_len -lt 40 ]; then v_res=${v_res}",""66" fi echo "$v_res"