[20211009]使用bash计算sql语句的sql_id.txt

[20211009]使用bash计算sql语句的sql_id.txt --//以前写的只能在命令行输入sql语句，如果语句很长很明显这样的方式不是很好，尝试支持从包含sql语句的文本文件来计算sql_id. $ cat sql_idx.sh #! /bin/bash # calcucate sql_text of full_hash_value(16),hash_value(10),sql_id(32). # argv1 sql statement or sql of text file # argv2 flag: 0= sql statement 1=sql of text file odebug=${ODEBUG:-0} oflag=${2:-0} if [ $oflag -eq 0 ] then     sql_text=${1}'\0' fi if [ $oflag -eq 1 ] then #   sql_text="$( cat $1 | dos2unix | sed '$s/;\s*//')"'\0'     sql_text="$( cat $1 | sed "s/ $//" | sed '$s/;\s*//')"'\0' #   sql_text="$( cat $1 | sed '$s/;\s*//')"'\0' fi v1=$(echo -e -n "$sql_text" | md5sum | sed 's/  -//' | xxd -r -p | od -t x4 |  sed   -n  -e 's/^0\+ //' -e 's/ //gp' | tr 'a-z' 'A-Z') v2=${v1:(-16):16} v3=${v2:(-8):8} # v2=$(echo "obase=16;ibase=16; $v1 % 10000000000000000" | bc| tr -d '\\\r\n') # v3=$(echo "obase=10;ibase=16; $v1 % 100000000" | bc| tr -d '\\\r\n') if [ $odebug -eq 1 ] ; then         echo v1=$v1 v2=$v2 v3=$v3 fi echo "sql_text = $sql_text" echo "full_hash_value(16) = $v1 " echo "hash_value(10) = $(( 16#$v3 )) " BASE32=($(echo {0..9} {a..z} | tr -d 'eilo')) res='' for i in $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n') do         res=${res}${BASE32[$(( 10#$i ))]} done echo "sql_id(32) = $(printf "%13s" $res | tr ' ' '0')" echo "sql_id(32) = $(printf "%013s" $res)" res1=$(eval $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n' | awk 'BEGIN{RS=" +"; printf "echo " }/./{printf "${BASE32[$(( 10#%02d))]}", $1}' )) echo "sql_id(32) = $(printf "%013s" $res1)" --//注如果安装dos2unix可以使用它,或者使用 代替.实际上文本oracle做了格式化处理变成unix格式来计算,另外文本最后不能有空行. --//测试如下: 1.测试1,直接输入sql语句. $ ./sql_idx.sh 'select * from dept where deptno=10' sql_text = select * from dept where deptno=10\0 full_hash_value(16) = 1431C45DBDDBB9E74EAA74D53650F131 hash_value(10) = 911274289 sql_id(32) = 4xamnunv51w9j sql_id(32) = 4xamnunv51w9j sql_id(32) = 4xamnunv51w9j  ./sql_idx.sh 'select * from dept where deptno=10' 0 sql_text = select * from dept where deptno=10\0 full_hash_value(16) = 1431C45DBDDBB9E74EAA74D53650F131 hash_value(10) = 911274289 sql_id(32) = 4xamnunv51w9j sql_id(32) = 4xamnunv51w9j sql_id(32) = 4xamnunv51w9j SCOTT@book> select * from dept where deptno=10;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ ---------  911274289 4xamnunv51w9j            0  3650f131 2.测试2,使用文件aa1.txt: $ cat -v aa1.txt select * from dept where deptno=10;                                     ^M $ ./sql_idx.sh aa 1 sql_text = select * from dept where deptno=10\0 full_hash_value(16) = 1431C45DBDDBB9E74EAA74D53650F131 hash_value(10) = 911274289 sql_id(32) = 4xamnunv51w9j sql_id(32) = 4xamnunv51w9j sql_id(32) = 4xamnunv51w9j SCOTT@book> @ /home/oracle/bin/aa1.txt     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ ---------  911274289 4xamnunv51w9j            0  3650f131 --//Ok正确. $ cat -v aa1.txt select * from dept where deptno=10                                    ; ^M $ ./sql_idx.sh aa1.txt 1 sql_text = select * from dept where deptno=10                                    \0 full_hash_value(16) = 8FA1DE8E2715321F0E36B1BFE045F536 hash_value(10) = 3762681142 sql_id(32) = 0wdpjrzh4bx9q sql_id(32) =  wdpjrzh4bx9q sql_id(32) =  wdpjrzh4bx9q --//我安装的这个bash shell脚本有点问题,前面的0不会补上. SCOTT@book> @ /home/oracle/bin/aa1.txt     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 3762681142 0wdpjrzh4bx9q            0  e045f536 3.测试3,文本多行的情况. $ cat -v aa1.txt select *^M  from dept where deptno=10;                                     ^M $ file aa1.txt aa1.txt: ASCII text, with CRLF line terminators  $ ./sql_idx.sh aa1.txt 1 sql_text = select *  from dept where deptno=10\0 full_hash_value(16) = 1F6FA161B0609741DD5B66C5D8ABB5BB hash_value(10) = 3635131835 sql_id(32) = duqv6srcarddv sql_id(32) = duqv6srcarddv sql_id(32) = duqv6srcarddv SCOTT@book> @ /home/oracle/bin/aa1.txt     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 3635131835 duqv6srcarddv            0  d8abb5bb --//Ok正确. $ dos2unix aa1.txt dos2unix: converting file aa1.txt to UNIX format ... $ ./sql_idx.sh aa1.txt 1 sql_text = select *  from dept where deptno=10\0 full_hash_value(16) = 1F6FA161B0609741DD5B66C5D8ABB5BB hash_value(10) = 3635131835 sql_id(32) = duqv6srcarddv sql_id(32) = duqv6srcarddv sql_id(32) = duqv6srcarddv SCOTT@book> @ /home/oracle/bin/aa1.txt     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 3635131835 duqv6srcarddv            0  d8abb5bb