empno=100不存在的,改成有的数据了。
#!/bin/bash
result=$(sqlplus -s 'scott/tiger@dbname'<<EOF
spool testtxt
set pages 0
set feed off
set heading off;
set feedback off;
set verify off;
set linesize 1000;
SELECT FROM scottemp where empno=7369;
spool off
EOF
)
echo $result
~
~
~
~
~
~
~
~
~
"testsh" 14L, 256C written
oracle@:~> /testsh
7369 SMITH CLERK 7902 17-DEC-80 224006 20
oracle@:~> more testtxt
7369 SMITH CLERK 7902 17-DEC-80 224006
20
perl脚本如下,执行的结果会放在与表对应的csv文件中, 有些代码折行了,请在折行的代码区域双击一下即可展开。
#!/usr/bin/env perl
use strict;
use warnings;
use DBI;
# 说明 #
# 假设:
# 两个database分别为db1, db2
# 两个table分别为tbl1, tbl2
# 从Oracle数据库导出的文件为csv格式(逗号分隔), 分别为tbl1csv, tbl2csv
#执行步骤 #
# Step1 连接Oracle数据库
$dbname1="db1";
$dbname2="db2";
$user="user";
$passwd="password";
$dbh1=DBI->connect("DBI:Oracle:$dbname1",$user,$passwd) || die "cannot connect to Oracle:$!\n";
$dbh2=DBI->connect("DBI:Oracle:$dbname2",$user,$passwd) || die "cannot connect to Oracle:$!\n";
# Step2 导出数据库中的tables到csv文件
db_to_csv( $dbh1, 'tbl1csv', 'SELECT FROM db1tbl1' );
db_to_csv( $dbh2, 'tbl2csv', 'SELECT FROM db2tbl2' );
# Step3 断开数据库连接
$dbh1->disconnect;
$dbh2->disconnect;
# Step4 将比较结果写入文件compare_resultscsv中
open( my $fh, '>', 'compare_resultscsv' ) or die $!;
compare_dump( 'tbl1csv', 'tbl2csv', $fh );
close $fh;
# 以下为一些自定义subroutines #
# 从数据库读取数据并导出为csv文件
sub db_to_csv
{
my $dbh = shift;
my $file = shift;
my $sql = shift;
my $sth = $dbh->prepare($sql);
$sth->execute;
my $numFields = $sth->{'NUM_OF_FIELDS'};
open( my $fh, '>', $file ) or die $!;
while (my $ref = $sth->fetchrow_arrayref) {
my $file;
for (my $i = 0; $i < $numFields; $i++) {
$line = $$ref[$i] if(defined $$ref[$i]);
$line = ",";
}
printf $fh ( "%s\n", $line );
}
close $fh;
}
# 对比两个表并输出同colum不同值的lines
sub compare_dump {
my $file1 = shift;
my $file2 = shift;
my $fh_result = shift;
open( my $fh1, '<', $file1 ) or die $!;
open( my $fh2, '<', $file2 ) or die $!;
my (@col1, @col2, @col3, @col4);
while( defined (my $line1 = <$fh1>) ){
chomp $line1;
my ($tbl1_col1, $tbl1_col2, $tbl1_col3, $tbl1_col4) = ( $line1 =~ /(\w+), (\w+), (\w+), (\w+)/ );
my $line2 = chomp(<$fh2>);
my ($tbl2_col1, $tbl2_col2, $tbl3_col3, $tbl4_col4) = ( $line1 =~ /(\w+), (\w+), (\w+), (\w+)/ );
if ($tbl1_col1 !~ /$tbl2_col1){
push @col1, [ $tbl1_col1, $tbl2_col1 ];
}
if ($tbl1_col2 !~ /$tbl2_col2){
push @col2, [ $tbl1_col2, $tbl2_col2 ];
}
if ($tbl1_col3 !~ /$tbl2_col3){
push @col3, [ $tbl1_col3, $tbl2_col3 ];
}
if ($tbl1_col4 !~ /$tbl2_col4){
push @col4, [ $tbl1_col4, $tbl2_col4 ];
}
}
close $fh1;
close $fh2;
# 写入不同的值column1
printf $fh_result ( "%-8s\t%-8s\n", 'AREAID_tbl1', 'AREAID_tbl2' );
for my $ref_elem (@col1) {
printf $fh_result ( "%-8s\t%-8s\n", ${$ref_elem}[0], @${$ref_elem}[1] );
}
# 写入不同的值column2
printf $fh_result ( "%-8s\t%-8s\n", 'CODED_tbl1', 'CODE_tbl2' );
for my $ref_elem (@col2) {
printf $fh_result ( "%-8s\t%-8s\n", ${$ref_elem}[0], @${$ref_elem}[1] );
}
# 写入不同的值column3
printf $fh_result ( "%-8s\t%-8s\n", 'AREANO_tbl1', 'AREANO_tbl2' );
for my $ref_elem (@col3) {
printf $fh_result ( "%-8s\t%-8s\n", ${$ref_elem}[0], @${$ref_elem}[1] );
}
# 写入不同的值column4
printf $fh_result ( "%-8s\t%-8s\n", 'AREANNAME_tbl1', 'AREANNAME_tbl2' );
for my $ref_elem (@col4) {
printf $fh_result ( "%-8s\t%-8s\n", ${$ref_elem}[0], @${$ref_elem}[1] );
}
}
楼主真没素质,写这么多也不给分。
empno=100不存在的,改成有的数据了。
#!/bin/bash
result=$(sqlplus -s 'scott/tiger@dbname'<<EOF
spool testtxt
set pages 0
set feed off
set heading off;
set feedback off;
set verify off;
set linesize 1000;
SELECT FROM scottemp where empno=7369;
spool off
EOF
)
echo $result
~
~
~
~
~
~
~
~
~
"testsh" 14L, 256C written
oracle@:~> /testsh
7369 SMITH CLERK 7902 17-DEC-80 224006 20
oracle@:~> more testtxt
7369 SMITH CLERK 7902 17-DEC-80 224006
20
以上就是关于linux下编写以个shell脚本,实现对oracle数据库抽取指定条件的数据并且保存在一个文本文件中。全部的内容,包括:linux下编写以个shell脚本,实现对oracle数据库抽取指定条件的数据并且保存在一个文本文件中。、写个脚本使用perl或shell对比oracle表数据,急啊,高分悬赏!、linux下编写以个shell脚本,实现对oracle数据库的查询结果保存在一个变量中等相关内容解答,如果想了解更多相关内容,可以关注我们,你们的支持是我们更新的动力!
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)