我用vc+oracle 写了一个数据库程序,想把表中数据按一定条件选取并导入到文本文件中,请问怎么办?
解决方案 »
- 求一个SQL语句的写法!希望大家帮帮忙!
- 英文数据库设计
- 急!急!急急Oracle数据库无法登陆是怎么回事
- 查询系统视图很慢
- 请教高手我如何将提取出的数据为空值的替代为0
- pl/sql能和用户交互吗?
- sql语句实现列值逐级累加(仅影响上一级)
- Oracle组合查询问题
- 新打的win2000的"震荡波"补丁,现在oracle816无法自动启动,服务中显示正常,但是连接数据库时出现:oracle not available,手动可以启动
- 请教高手:oracl中的疑惑?
- 如何把xls、doc文件以纯粹的二进制文件形式保存到oracle表的某一条记录的一个字段中去?
- 如何设置Oracle的参数,是Oracle数据库初始化最合理?关于倒入倒出的问题???
string tablename
int currow , i, rowcount ,bz=0
integer fp
int col_count = 0
double num
string str
date rq
datetime rqtime
time my_time
string column_type
int column_len
string column_def, nullable, column_name
string colstr
string s
int ncurrow = w_main.dw_table.getrow()
if currow < 1 then return -1
tablename = w_main.dw_table.object.table_name[currow]
fp = FileOpen(w_main.txt_dirname.text+w_main.txt_filename.text, LineMode!, Write!, LockWrite!, REPLACE!)
if fp <= 0 then
messagebox( "", "文件" + w_main.txt_dirname.text+w_main.txt_filename.text + "打开失败!" )
FileClose( fp )
return -1
end ifsqlcreate = "CREATE TABLE " + tablename + "( "for i = 1 to w_main.dw_column.rowcount()
if w_main.dw_column.isselected ( i ) then
column_name = w_main.dw_column.object.column_name[i]
column_type = w_main.dw_column.object.data_type[i]
column_len = w_main.dw_column.object.data_len[i]
column_def = w_main.dw_column.object.data_default[i]
nullable = w_main.dw_column.object.nullable[i]
col_count ++ if bz = 0 then
bz = 1
else
sqlcreate = sqlcreate + ", "
sqlcolumn = sqlcolumn + ", "
end if
sqlcreate = sqlcreate + "~r~n~t"
sqlcolumn = sqlcolumn + w_main.dw_column.object.column_name[i]
sqlcreate = sqlcreate + column_name + " " +column_type
if NOT ( column_type = "DATE" or column_type = "date") then
sqlcreate = sqlcreate+ "(" + string( column_len ) + ")"
end if
if not ( isnull( column_def ) and column_def <> "" ) then
sqlcreate = sqlcreate + "DEFAULT " + column_def
end if
if nullable = "非" then
sqlcreate = sqlcreate + " NOT NULL "
end if
end if
next
sqlcreate = sqlcreate + " ~r~n);~r~n"
if create_bz = 1 then
// filewrite( fp, "rem~r~nrem~r~nrem~t创建表结构 ~r~nrem~r~nrem~r~nHOST ECHO 'CREATE TABLE " + tablename + "...'~r~n" )
filewrite( fp, "rem~r~nrem~r~nrem~t创建表结构, 表名:"+tablename+ " ~r~nrem~r~nrem" )
filewrite( fp, "DROP TABLE " + tablename + " CASCADE CONSTRAINTS;~r~n" )
filewrite( fp, sqlcreate )
end if
//filewrite( fp, "rem~r~nrem~r~nrem~t插入表数据 ~r~nrem~r~nrem~r~nHOST ECHO 'INSERT DATA TO " + tablename + "...'~r~n" )
filewrite( fp, "rem~r~nrem~r~nrem~t插入表数据,表名:"+tablename+" ~r~nrem~r~nrem" )
sqlinsert = "INSERT INTO "+tablename + "(" + sqlcolumn + ") "sqlselect = "SELECT " + sqlcolumn + " FROM " + tablename + sqlwherePREPARE SQLSA FROM :sqlselect ;
if sqlca.sqlcode <> 0 then
messagebox( megstring, "动态SQL出错, PREPARE SQLSA FROM :sqlselect" + sqlca.sqlerrtext + "~r~n" + sqlselect )
FileClose( fp )
return -1
end if
DESCRIBE SQLSA INTO SQLDA ;
if sqlca.sqlcode <> 0 then
messagebox( megstring, "动态SQL出错, DESCRIBE SQLSA INTO SQLDA" + sqlca.sqlerrtext + "~r~n" + sqlselect )
FileClose( fp )
return -1
end if
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
if sqlca.sqlcode <> 0 then
messagebox( megstring, "动态SQL出错, DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA " + sqlca.sqlerrtext + "~r~n" + sqlselect )
FileClose( fp )
return -1
end if
OPEN DYNAMIC my_cursor USING DESCRIPTOR SQLDA ;
if sqlca.sqlcode <> 0 then
messagebox( megstring, "动态SQL出错, OPEN DYNAMIC my_cursor USING DESCRIPTOR SQLDA " + sqlca.sqlerrtext + "~r~n" + sqlselect )
FileClose( fp )
return -1
end if
DO
FETCH my_cursor USING DESCRIPTOR SQLDA ;
if sqlca.sqlcode = 0 then
sqlrecord = ""
bz = 0
for i = 1 to col_count
if bz = 1 then
sqlrecord = sqlrecord + ", "
elseif bz = 0 then
bz = 1
end if
colstr = ""
CHOOSE CASE SQLDA.OutParmType[i]
CASE TypeString!
Str = GetDynamicString(SQLDA, i)
if isnull( str ) then
colstr = s_null
else
colstr = "'" + str + "'"
end if CASE TypeInteger!, TypeLONG!, TypeDecimal!, TypeDouble! , TypeReal!
num = GetDynamicNumber(SQLDA, i)
if isnull( num ) then
colstr = s_null
else
colstr = string(num)
end if case TypeDate!
rq = GetDynamicDate(SQLDA, i)
if isnull( rq ) then
colstr = s_null
else
colstr = "TO_DATE('" + string(rq,"YYYY/MM/DD") + "', 'YYYY/MM/DD')"
end if
CASE TypeDateTime!
rqtime = GetDynamicDateTime(SQLDA, i)
if isnull( rqtime ) then
colstr = s_null
else
s = string(rqtime,"YYYY/MM/DD-HH:MM:SS am/pm" )
n = len( s )
if right ( s, 2 ) = 'am' or right ( s, 2 ) = 'AM' then
s = left ( s, len(s) - 2 ) + "上午"
else
s = left ( s, len(s) - 2 ) + "下午"
end if
colstr = "TO_DATE('" + string(rqtime,"YYYY/MM/DD-HH:MM:SS" ) + "', 'YYYY/MM/DD-HH24:MI:SS')"
end if
case TypeTime!
my_time = GetDynamicTime(SQLDA, i)
if isnull( my_time ) then
colstr = s_null
else
s = string(my_time,"HH:MM:SS am/pm")
n = len( s )
if right ( s, 2 ) = 'am' or right ( s, 2 ) = 'AM' then
s = left ( s, len(s) - 2 ) + "上午"
else
s = left ( s, len(s) - 2 ) + "下午"
end if colstr = "TO_DATE('" + string(my_time,"HH:MM:SS") + "', 'HH24:MI:SS')"
end if
CASE TypeBoolean!
END CHOOSE
sqlrecord = sqlrecord + colstr
next
filewrite( fp, sqlinsert + "VALUES( " + sqlrecord + ");" )
end if
loop until sqlca.sqlcode <> 0
CLOSE my_cursor ;
filewrite( fp, "COMMIT;~r~nQUIT;~r~n" )
FileClose( fp )
return 1
已经经过测试,我一直再使用。
把数据用cursor一条条取出,用ufile.fopen打开,ufile.put_line写入就行了
用UFILE包来作.
如果你是把查询所获得的数据写入文本的话就这么作
当然也用SQLLDR回写.但找个办法有一个缺陷就是如果写入文本的数据要作别的使用
也要作一定的处理. 要看你实际的应用了
我这个方法为了写成一个。SQL的文件里面的全都是SQL语句,还有建立表的脚本。
既然你要写程序,功能强大点不好吗?