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