ai_tcl命令集合

AI_TCL命令集合基本命令db2_connect建立数据库连接格式:db2_connect DB名user名password;例子:Set Conn [ db2_connect sccrm dbinst1 dbinst1]AI封装:proc aidb_connect {db username password} {return [db2_connect $db $username $password] }Db2_disconnect删除数据库连接格式:db2_disconnect 数据库连接句柄例子:db2_disconnect $dbhandleAI封装:proc aidb_disconnect {dbhandle} {db2_disconnect $dbhandle# ToDo: write log here}db2_open打开数据库连接格式:db2_open数据库连接句柄例子:db2_open $dbhandleAI封装:proc aidb_open {dbhandle} {set handle [db2_open $dbhandle]return $handle}db2_close断开数据库连接格式:db2_close数据库连接句柄例子:db2_close $dbhandleAI封装:proc aidb_close {dbhandle} {db2_close $handle# ToDo: write log here}db2_exec 执行SQL语句格式:db2_exec数据库连接句柄sql语句例子:db2_exec $dbhandle $sqlstmtAI封装:proc aidb_sql {handle sqlstmt} {db2_exec $handle $sqlstmt#ToDo: write log here}db2_fetchrow执行游标格式:db2_fetchrow 数据库连接句柄例子:db2_fetchrow $dbhandleAI封装:proc aidb_fetch {handle} {return [db2_fetchrow $handle] }db2_commit 事务提交格式:db2_commit 数据库连接句柄例子:db2_commit $dbhandleAI封装:proc aidb_commit {dbhandle} { db2_commit $dbhandle }db2_roll 事务回滚格式:db2_roll 数据库连接句柄例子:db2_roll $dbhandleAI封装:proc aidb_roll {dbhandle} {db2_roll $dbhandle}db2_msg错误信息格式:db2_msg 数据库连接句柄例子:db2_msg $dbhandleAI封装:proc aidb_errcode {dbhandle} {set rc [ db2_msg $handle]if {$rc !=0 && $rc!=100} {if {$rc < 0} {return "-1"} else {return "-2"}}return 0}db2_msg 错误信息格式:db2_msg 数据库连接句柄例子:db2_msg $dbhandleAI封装:proc aidb_errcode {dbhandle} {set rc [ db2_msg $handle]if {$rc !=0 && $rc!=100} {if {$rc < 0} {return "-1"} else {return "-2"}}return 0}其他封装表清空proc aidb_truncate {dbhandle handle table_name} {db2_exec $handle "alter table $table_name activate not logged initially with empty table"db2_commit $dbhandle}建表proc aidb_create_tmptable {dbhandle handle tablename tablelike tablespace tbsindex partitionkey} {db2_exec $handle "create table $tablename like $tablelike \in $tablespace \index in $tbsindex \partitioning key ($partitionkey) \not logged initially"db2_exec $handle "ALTER TABLE $tablename ACTIVATE NOT LOGGED INITIALLY \ LOCKSIZE TABLE"db2_commit $dbhandle}建临时表proc aidb_create_tmptable2 {dbhandle handle tablename fields tablespace tbsindex partitionkey } {db2_exec $handle "create table $tablename ($fields) \in $tablespace \index in $tbsindex \partitioning key ($partitionkey) \not logged initially"db2_exec $handle "ALTER TABLE $tablename ACTIVATE NOT LOGGED INITIALLY \LOCKSIZE TABLE"db2_commit $dbhandle}根据模板建表proc aidb_create_table_as { handle tablename tablelike } {db2_exec $handle "create table $tablename like $tablelike"}变更表名称proc aidb_rename {handle old_tablename new_tablename} {db2_exec $handle "rename table $old_tablename to $new_tablename" }日期增加月份proc ai_addmonths {date mon} {return "$date + $mon months "}日期减少月份proc ai_minusmonths {date mon} {return "$date - $mon months "}日期增加天数proc ai_adddays {date day} {return "$date + $day days"}日期减去天数proc ai_minusdays {date day} { return "$date - $day days" }字符转换成日期proc ai_to_date {date} { return "date('$date')";}日期转换成字符proc ai_to_char {date} { return "$date";}获取系统日期proc ai_sysdate {} {return "current date";}置空proc ai_nvl {s} {return "value($s)"}获取日期的天的值proc ai_day {s} {return "day($s)"}获取日期的月份proc ai_month {s} {return "month($s)"}获取日期的年份proc ai_year {s} {return "year($s)"}字符转换成整数proc ai_int {s} {return "int($s)"}获取两个日期之间的月份差异proc ai_months_between {date1 date2 } {return "year($date1)*12 + month($date1) - year($date2)*12 - month($date2)" }字符转换成double型proc ai_double {s} {return "double($s)"}获取日期的小时数proc ai_hour {s} {return "hour($s)"}字符转换成timestamp型proc ai_char_to_timestamp {s} {return "timestamp('$s')"}proc ai_int_to_int {} {return "integer"}proc ai_short_to_short {} {return "smallint"}proc ai_long_to_long {} {return "bigint"}proc ai_int_to_char {s} {return "char($s)"}。