oracle获取系统时间出错,hammerora--获取系统当前时间
#!/usr/local/bin/tclsh8.5
if [catch {package require Oratcl} ] { error "Failed to load
Oratcl - Oracle OCI Library Error" }
#EDITABLE
OPTIONS##################################################
set total_iterations 10000 ;# Number of transactions before
logging off
set RAISEERROR "false" ;# Exit script on Oracle error (true or
false)
set KEYANDTHINK "false" ;# Time for user thinking and keying
(true or false)
set connect tpcc/tpcc@10.11 ;# Oracle connect string for tpc-c
user
#EDITABLE
OPTIONS##################################################
#RANDOM NUMBER
proc RandomNumber {m M} {return [expr
{int($m+rand()*($M+1-$m))}]}
#STANDARD SQL
proc standsql { curn sql } {
set ftch ""
if {[catch {orasql $curn $sql} message]} {
error "SQL statement failed: $sql : $message"
} else {
orafetch $curn -datavariable output
while { [ oramsg $curn ] == 0 } {
lappend ftch $output
orafetch $curn -datavariable output
}
return $ftch
}
}
#Default NLS
proc SetNLS { lda } {
set curn_nls [oraopen $lda ]
set nls(1) "alter session set NLS_LANGUAGE = AMERICAN"
set nls(2) "alter session set NLS_TERRITORY = AMERICA"
for { set i 1 } { $i <= 2 } { incr i } {
if {[ catch {orasql $curn_nls $nls($i)} message ] } {
puts "$message $nls($i)"
puts [ oramsg $curn_nls all ]
}
}
oraclose $curn_nls
}
#NURand function
proc NURand { iConst x y C } {return [ expr {((([RandomNumber
0 $iConst] | [RandomNumber $x $y]) + $C) % ($y - $x + 1)) + $x
}]}
#RANDOM NAME
proc randname { num } {
array set namearr { 0 BAR 1 OUGHT 2 ABLE 3 PRI 4 PRES 5 ESE 6
ANTI 7 CALLY 8 ATION 9 EING }
set name [ concat $namearr([ expr {( $num / 100 ) % 10
}])$namearr([ expr {( $num / 10 ) % 10 }])$namearr([ expr {( $num /
1 ) % 10 }]) ]
return $name
}
#TIMESTAMP
proc gettimestamp { } {
set tstamp [ clock format [ clock seconds ] -format
%Y%m%d%H%M%S ]
return $tstamp
}
#KEYING TIME
proc keytime { keying } {
after [ expr {$keying * 1000} ]
return
}
#THINK TIME
proc thinktime { thinking } {
set thinkingtime [ expr {abs(round(log(rand()) * $thinking))}
]
after [ expr {$thinkingtime * 1000} ]
return
}
#NEW ORDER
proc neword { curn_no no_w_id w_id_input RAISEERROR } {
#2.4.1.2 select district id randomly from home warehouse where
d_w_id = d_id
set no_d_id [ RandomNumber 1 10 ]
#2.4.1.2 Customer id randomly selected where c_d_id = d_id and
c_w_id = w_id
set no_c_id [ RandomNumber 1 3000 ]
#2.4.1.3 Items in the order randomly selected from 5 to
15
set ol_cnt [ RandomNumber 5 15 ]
#2.4.1.6 order entry date O_ENTRY_D generated by SUT
set date [ gettimestamp ]
orabind $curn_no :no_w_id $no_w_id :no_max_w_id $w_id_input
:no_d_id $no_d_id :no_c_id $no_c_id :no_o_ol_cnt $ol_cnt
:no_c_discount {} :no_c_last {} :no_c_credit {} :no_d_tax {}
:no_w_tax {} :no_d_next_o_id {0} :timestamp $date
if {[catch {oraexec $curn_no} message]} {
if { $RAISEERROR } {
error "New Order : $message [ oramsg $curn_no all ]"
} else {
puts $message
} } else {
orafetch $curn_no -datavariable output
puts $output
}
}
#PAYMENT
proc payment { curn_py p_w_id w_id_input RAISEERROR } {
#2.5.1.1 The home warehouse id remains the same for each
terminal
#2.5.1.1 select district id randomly from home warehouse where
d_w_id = d_id
set p_d_id [ RandomNumber 1 10 ]
#2.5.1.2 customer selected 60% of time by name and 40% of time
by number
set x [ RandomNumber 1 100 ]
set y [ RandomNumber 1 100 ]
if { $x <= 85 } {
set p_c_d_id $p_d_id
set p_c_w_id $p_w_id
} else {
#use a remote warehouse
set p_c_d_id [ RandomNumber 1 10 ]
set p_c_w_id [ RandomNumber 1 $w_id_input ]
while { ($p_c_w_id == $p_w_id) && ($w_id_input != 1) }
{
set p_c_w_id [ RandomNumber 1 $w_id_input
]
}
}
set nrnd [ NURand 255 0 999 123 ]
set name [ randname $nrnd ]
set p_c_id [ RandomNumber 1 3000 ]
if { $y <= 60 } {
#use customer name
#C_LAST is generated
set byname 1
} else {
#use customer number
set byname 0
set name {}
}
#2.5.1.3 random amount from 1 to 5000
set p_h_amount [ RandomNumber 1 5000 ]
#2.5.1.4 date selected from SUT
set h_date [ gettimestamp ]
#2.5.2.1 Payment Transaction
#change following to correct values
orabind $curn_py :p_w_id $p_w_id :p_d_id $p_d_id :p_c_w_id
$p_c_w_id :p_c_d_id $p_c_d_id :p_c_id $p_c_id :byname $byname
:p_h_amount $p_h_amount :p_c_last $name :p_w_street_1 {}
:p_w_street_2 {} :p_w_city {} :p_w_state {} :p_w_zip {}
:p_d_street_1 {} :p_d_street_2 {} :p_d_city {} :p_d_state {}
:p_d_zip {} :p_c_first {} :p_c_middle {} :p_c_street_1 {}
:p_c_street_2 {} :p_c_city {} :p_c_state {} :p_c_zip {} :p_c_phone
{} :p_c_since {} :p_c_credit {0} :p_c_credit_lim {} :p_c_discount
{} :p_c_balance {0} :p_c_data {} :timestamp $h_date
if {[ catch {oraexec $curn_py} message]} {
if { $RAISEERROR } {
error "Payment : $message [ oramsg $curn_py all ]"
} else {
puts $message
} } else {
orafetch $curn_py -datavariable output
puts $output
}
}
#ORDER_STATUS
proc ostat { curn_os w_id RAISEERROR } {
#2.5.1.1 select district id randomly from home warehouse where
d_w_id = d_id
set d_id [ RandomNumber 1 10 ]
set nrnd [ NURand 255 0 999 123 ]
set name [ randname $nrnd ]
set c_id [ RandomNumber 1 3000 ]
set y [ RandomNumber 1 100 ]
if { $y <= 60 } {
set byname 1
} else {
set byname 0
set name {}
}
orabind $curn_os :os_w_id $w_id :os_d_id $d_id :os_c_id $c_id
:byname $byname :os_c_last $name :os_c_first {} :os_c_middle {}
:os_c_balance {0} :os_o_id {} :os_entdate {} :os_o_carrier_id
{}
if {[catch {oraexec $curn_os} message]} {
if { $RAISEERROR } {
error "Order Status : $message [ oramsg $curn_os all ]"
} else {
puts $message
} } else {
orafetch $curn_os -datavariable output
puts $output
}
}
#DELIVERY
proc delivery { curn_dl w_id RAISEERROR } {
set carrier_id [ RandomNumber 1 10 ]
set date [ gettimestamp ]
orabind $curn_dl :d_w_id $w_id :d_o_carrier_id $carrier_id
:timestamp $date
if {[ catch {oraexec $curn_dl} message ]} {
if { $RAISEERROR } {
error "Delivery : $message [ oramsg $curn_dl all ]"
} else {
puts $message
} } else {
orafetch $curn_dl -datavariable output
puts $output
}
}
#STOCK LEVEL
proc slev { curn_sl w_id stock_level_d_id RAISEERROR } {
set threshold [ RandomNumber 10 20 ]
orabind $curn_sl :st_w_id $w_id :st_d_id $stock_level_d_id
:THRESHOLD $threshold
if {[catch {oraexec $curn_sl} message]}
{
if { $RAISEERROR } {
error "Stock Level : $message [ oramsg $curn_sl all ]"
} else {
puts $message
} } else {
orafetch $curn_sl -datavariable output
puts $output
}
}
proc prep_statement { lda curn_st } {
switch $curn_st {
curn_sl {
set curn_sl [oraopen $lda ]
set sql_sl "BEGIN slev(:st_w_id,:st_d_id,:threshold);
END;"
oraparse $curn_sl $sql_sl
return $curn_sl
}
curn_dl {
set curn_dl [oraopen $lda ]
set sql_dl "BEGIN
delivery(:d_w_id,:d_o_carrier_id,TO_DATE(:timestamp,'YYYYMMDDHH24MISS'));
END;"
oraparse $curn_dl $sql_dl
return $curn_dl
}
curn_os {
set curn_os [oraopen $lda ]
set sql_os "BEGIN
ostat(:os_w_id,:os_d_id,:os_c_id,:byname,:os_c_last,:os_c_first,:os_c_middle,:os_c_balance,:os_o_id,:os_entdate,:os_o_carrier_id);
END;"
oraparse $curn_os $sql_os
return $curn_os
}
curn_py {
set curn_py [oraopen $lda ]
set sql_py "BEGIN
payment(:p_w_id,:p_d_id,:p_c_w_id,:p_c_d_id,:p_c_id,:byname,:p_h_amount,:p_c_last,:p_w_street_1,:p_w_street_2,:p_w_city,:p_w_state,:p_w_zip,:p_d_street_1,:p_d_street_2,:p_d_city,:p_d_state,:p_d_zip,:p_c_first,:p_c_middle,:p_c_street_1,:p_c_street_2,:p_c_city,:p_c_state,:p_c_zip,:p_c_phone,:p_c_since,:p_c_credit,:p_c_credit_lim,:p_c_discount,:p_c_balance,:p_c_data,TO_DATE(:timestamp,'YYYYMMDDHH24MISS'));
END;"
oraparse $curn_py $sql_py
return $curn_py
}
curn_no {
set curn_no [oraopen $lda ]
set sql_no "begin
neword(:no_w_id,:no_max_w_id,:no_d_id,:no_c_id,:no_o_ol_cnt,:no_c_discount,:no_c_last,:no_c_credit,:no_d_tax,:no_w_tax,:no_d_next_o_id,TO_DATE(:timestamp,'YYYYMMDDHH24MISS'));
END;"
oraparse $curn_no $sql_no
return $curn_no
}
}
}
#RUN TPC-C
set lda [oralogon $connect]
SetNLS $lda
oraautocom $lda on
foreach curn_st {curn_no curn_py curn_dl curn_sl curn_os} {
set $curn_st [ prep_statement $lda $curn_st ] }
set curn1 [oraopen $lda ]
set sql1 "select max(w_id) from warehouse"
set sql4 "insert into time select to_char(sysdate,'yyyy-mm-dd
hh24:mi:ss') from dual" --------预先创建time表create
time(time varchar2(30)) ;将系统的当前时间插入到time表中
orasql $curn1 $sql4 ----------执行插入语句
oracommit $lda ----------commit
set w_id_input [ standsql $curn1 $sql1 ]
#2.4.1.1 set warehouse_id stays constant for a given
terminal
set w_id [ RandomNumber 1 $w_id_input ]
set sql2 "select max(d_id) from district"
set d_id_input [ standsql $curn1 $sql2 ]
set stock_level_d_id [ RandomNumber 1
$d_id_input ]
set sql3 "BEGIN DBMS_RANDOM.initialize (val =>
TO_NUMBER(TO_CHAR(SYSDATE,'MMSS')) * (USERENV('SESSIONID') -
TRUNC(USERENV('SESSIONID'),-5))); END;"
oraparse $curn1 $sql3
if {[catch {oraplexec $curn1 $sql3} message]} {
error "Failed to initialise DBMS_RANDOM $message have you run
catoctk.sql as sys?" }
#oraclose $curn1
puts "Processing $total_iterations transactions without output
suppressed..."
for {set it 0} {$it < $total_iterations} {incr it} {
if { [ tsv::get application abort ]
} { break }
set choice [ RandomNumber 1 23 ]
if {$choice <= 10} {
puts "new order"
if { $KEYANDTHINK } { keytime 18 }
neword $curn_no $w_id $w_id_input $RAISEERROR
if { $KEYANDTHINK } { thinktime 12 }
} elseif {$choice <= 20} {
puts "payment"
if { $KEYANDTHINK } { keytime 3 }
payment $curn_py $w_id $w_id_input $RAISEERROR
if { $KEYANDTHINK } { thinktime 12 }
} elseif {$choice <= 21} {
puts "delivery"
if { $KEYANDTHINK } { keytime 2 }
delivery $curn_dl $w_id $RAISEERROR
if { $KEYANDTHINK } { thinktime 10 }
} elseif {$choice <= 22} {
puts "stock level"
if { $KEYANDTHINK } { keytime 2 }
slev $curn_sl $w_id $stock_level_d_id $RAISEERROR
if { $KEYANDTHINK } { thinktime 5 }
} elseif {$choice <= 23} {
puts "order status"
if { $KEYANDTHINK } { keytime 2 }
ostat $curn_os $w_id $RAISEERROR
if { $KEYANDTHINK } { thinktime 5 }
}
}
set sql5 "insert into time select to_char(sysdate,'yyyy-mm-dd
hh24:mi:ss') from dual"
#set end_time [orasql $curn1 $sql4]
orasql $curn1 $sql5
oracommit $lda
oraclose $curn1
oraclose $curn_no
oraclose $curn_py
oraclose $curn_dl
oraclose $curn_sl
oraclose $curn_os
oralogoff $lda