[20191003]oracle number型別儲存轉化指令碼.txt
[20191003]oracle number型別儲存轉化指令碼.txt
--//完善指令碼,增加支援科學記數法。比如1e4之類的寫法。
2.測試:
$ 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_4.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
1e-130 80,02
-1e-130 7f,64,66
9.999999999999999999999999999999999999999e125 ff,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64
-9.999999999999999999999999999999999999999e125 00,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02,02
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秒。
2.附上轉化指令碼如下:
$ cat num2raw_4.sh
#! /bin/bash
#! number convert oracle raw.
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 e or trailing 0s in decimals or 0000.000 output 0 ,
v_num=$(echo $v_num + 0 | sed -e "s/[eE]/*10^/" -e "s/^/scale=180;/" | 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"
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2658891/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20190930]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- [20191013]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- [20190930]oracle raw型別轉化number指令碼.txtOracle型別指令碼
- Oracle 儲存型別Oracle型別
- oracle中dump函式及oracle NUMBER型別內部儲存機制Oracle函式型別
- 【NUMBER】Oracle資料庫最佳化之理解NUMBER儲存機制Oracle資料庫
- [20150503]關於oracle的number型別.txtOracle型別
- Oracle Lob型別儲存淺析Oracle型別
- [20160828]number型別.txt型別
- ORACLE NUMBER資料型別Oracle資料型別
- 【NUMBER】Oracle的NUMBER資料型別特點Oracle資料型別
- Oracle的number資料型別Oracle資料型別
- Oracle中number型別詳解Oracle型別
- Oracle基本資料型別儲存格式淺析——RAW型別Oracle資料型別
- [20191219]oracle timestamp資料型別的儲存.txtOracle資料型別
- rman儲存指令碼指令碼
- oracle資料型別與儲存結構Oracle資料型別
- 轉摘_儲存型別與連線方式型別
- mysql 轉換表的儲存引擎指令碼MySql儲存引擎指令碼
- Oracle基本資料型別儲存格式淺析(五)——RAW型別Oracle資料型別
- ORACLE NUMBER型別內部實現Oracle型別
- Oracle基本資料型別儲存格式淺析(四)——ROWID型別Oracle資料型別
- Oracle基本資料型別儲存格式淺析(三)——日期型別(一)Oracle資料型別
- Oracle基本資料型別儲存格式淺析(三)——日期型別(二)Oracle資料型別
- Oracle基本資料型別儲存格式淺析(三)——日期型別(三)Oracle資料型別
- Oracle基本資料型別儲存格式淺析(三)——日期型別(四)Oracle資料型別
- [20241009]oracle timestamp with time zone資料型別的儲存.txtOracle資料型別
- Oracle基本資料型別儲存格式淺析[zt]Oracle資料型別
- 字元型別的字元儲存與位元組儲存字元型別
- 建立NFS型別的儲存NFS型別
- Oracle timestamp型別轉換date格式指令碼(精確到秒)Oracle型別指令碼
- 一個簡單函式—Number型別轉換Date型別函式型別
- Oracle基本資料型別儲存格式淺析(二)——數字型別Oracle資料型別
- EF:oracle的number型別對映為C#的boolean型別Oracle型別C#Boolean
- MYSQL 資料型別儲存-數值型MySQL 資料型別
- oracle中number型欄位長度、精度及實際儲存狀態測試(zt)Oracle
- jsp頁面number型別自動轉為String型別JS型別
- Block型別及儲存區域BloC型別