oracle常用

import:
#!/usr/bin/perl
my $corpID = "160000000000";
my $mobile = "13000000001";
my $shortNum = "660000";
my $ID = "0";
my $mb = "0";
my $data = "./data";

open(ACCNO,    ">$data/corpinfo.txt") || die "Error: $1\n";  #output filename
foreach (1..500)
{
    $ID = $corpID;
    printf ACCNO "%s,%s,0,0,-1,1,0\n", $corpID++,$ID;

    foreach (1..1000)
    {
         $mb = $mobile;
         printf member "%s,%s,%s,%s,0,0\n", $ID,$mobile++,$mb,$shortNum++;
    }
    $shortNum = "66000";
}
close ACCNO;

#!/bin/bash
#sql load
main()
{

        v_p=`pwd`
        v_str=/opt/oracle/product/11g/db/bin/
        corpinfo=`su - oracle -c "${v_str}sqlldr userid=$user/$pwd control=${v_p}/ctl/corpinfo.ctl errors=100000">/dev/null`       
        if [ $? -eq 0 ]
        then
                echo "insert  successful !"
        else
                echo "insert  error!"
        fi
}
modifyFile()
{
        v_path=`pwd`
        v_str=./ctl/template
        rm -rf ./ctl/*.ctl
        chown oracle:dba log
        sed "s#infile.*#infile '${v_path}\/data\/corpinfo.txt'#g" ${v_str}/corpinfo.ctl | sed "s#badfile.*#badfile '${v_path}\/log\/corpinfo.bad'#g" > ./ctl/corpinfo.ctl
}
echo "Enter db userName"
read user < /dev/tty
echo "Enter db password"
read pwd < /dev/tty
modifyFile
main

./ctl/template    ../date  ../log
load data
infile 'corpinfo.txt'
badfile 'corpinfo.bad'
replace into table CORPINFO
fields terminated by ','
(corpid, corpname, corptype,
corpcode "replace(:corpcode,:corpcode,SEQ_CORPCODE_ID.NEXTVAL)"
, areaid, feemeal, parentcorpid)