gendb

学习oracle的脚本,包括备份,proc编程的,例子,是学习oracle的好资料

#!/usr/bin/perl -w

use strict;
use FileHandle;

my ($sTableSql, $fhTableSql);
my ($sTableH, $fhTableH);
my ($sTablePc, $fhTablePc);
my ($sLine, $sSql, $sTable);
my ($sCols, @aCols);
my ($sPKCols, @aPKCols, $iPKColsCount);
my ($ahColsRef, $iColsCount)=([], 0);
my ($sColLine);
my ($name, $type, $length, $default, $null); 
my ($sTmp, $ColRef, $i, $j);

if(@ARGV != 3)
{
    print "Usage: gendb {table_sql} {db_h} {table_pc}\n";
    exit;
}
$sTableSql=shift(@ARGV);
$sTableH=shift(@ARGV);
$sTablePc=shift(@ARGV);

$fhTableSql=new FileHandle($sTableSql) || die "File open fail";
$sSql='';
while($sLine=<$fhTableSql>)
{
    chop($sLine);
    $sSql=$sSql." ".$sLine;
}
$fhTableSql->close();
$sSql =~ s/\t/ /g;
$sSql =~ s/^\s+(.*)\s+$/$1/;
$sSql =~ s/\s{2,}/ /g;
$sSql =~ s/(\(\s*\d+\s*),(\s*\d+\s*\))/$1.$2/g;
$sSql = lc($sSql);

#得到表名
( $sTable ) = ( $sSql =~ m/create table (.+?)\b/ );

#得到域和主键
#( $sCols ) = ( $sSql =~ m/\((.*),(\s*)primary key(.*)\);/ );
if( $sSql !~ m/\((.*),(\s*)primary key(.*)\);/ )
{
    die("Sql error");
}
$sCols = $1;
$sCols =~ s/^\s*(.*)\s*$/$1/;
@aCols = split(m/,/, $sCols);
$iColsCount=0;
foreach $sColLine (@aCols)
{
    $sColLine =~ s/^\s+(.*)\s+/$1/g;
#    $sColLine =~ s/\s+$//g;
#    $sColLine =~ s/\s+\(//g;

    ( $name ) = ( $sColLine =~ m/(.+?)\s/ );
    if($name =~ m/[()]/)
    {
        print "$sColLine\n";
        die("Column name Error"); 
    }
    ( $ahColsRef->[$iColsCount]{'name'} ) = $name; 

    ( $sTmp ) = ( $sColLine =~ m/\s+(.*)/ );

    if( $sTmp =~ m/(char|varchar2|number|date)\b/g )
    {   ( $ahColsRef->[$iColsCount]{'type'} ) = "$1";   }
    else
    {
        print "$sColLine\n";
        die("Column type error");
    }

    if( $ahColsRef->[$iColsCount]{'type'} ne 'date' )
    {
        if( $sTmp =~ m/\((.*)\)/g )
        {
            if( $1 !~ m/\s/ )
            {   $length=$1;   }
            else
            {
                print "$sColLine\n";
                die("Column type error");
            }
        }
        else
        {
            print "$sColLine\n";
            die("Column type error");
        }
    } 
    else
    {   $length=0;   }
    $ahColsRef->[$iColsCount]{'length'} = $length;

    if( $sTmp =~ m/\s+default\s+(\S+)/g )
    {   $ahColsRef->[$iColsCount]{'default'}="$1";   }
    elsif($ahColsRef->[$iColsCount]{'type'} eq 'char' ||
          $ahColsRef->[$iColsCount]{'type'} eq 'varchar2')
    {   $ahColsRef->[$iColsCount]{'default'}="' '";   } 
    elsif($ahColsRef->[$iColsCount]{'type'} eq 'number')
    {   $ahColsRef->[$iColsCount]{'default'}=0;   } 
    elsif($ahColsRef->[$iColsCount]{'type'} eq 'date')
    {   $ahColsRef->[$iColsCount]{'default'}="'19700101'";   } 
    else
    {   die("Column type error");   } 

    if( $sTmp =~ m/\s+not\s+null/g )
    {   $ahColsRef->[$iColsCount]{'null'} = 'N';   }
    else
    {   $ahColsRef->[$iColsCount]{'null'} = 'Y';   }

#    print $ahColsRef->[$iColsCount]{'name'}." ".
    $ahColsRef->[$iColsCount]{'type'}." ".
    $ahColsRef->[$iColsCount]{'length'}." ".
    $ahColsRef->[$iColsCount]{'null'}."\n"; 

    $iColsCount++; 
}

( $sPKCols ) = ( $sSql =~ m/primary key\s*\((.+?)\)/ );
$sPKCols =~ s/^\s+(.*)\s+$/&1/;
$sPKCols =~ s/\s*,\s*/,/g;
@aPKCols = split(m/,/, $sPKCols);
$iPKColsCount = @aPKCols;
#print "$iPKColsCount @aPKCols\n";


#生成宿主结构
$fhTableH=new FileHandle("> ".$sTableH) || die "File open fail";

print $fhTableH "typedef struct\n{\n";
foreach $ColRef (@$ahColsRef)
{
    if($ColRef->{'type'} eq 'char' ||
       $ColRef->{'type'} eq 'varchar2')
    {
        print $fhTableH "\tchar\t\t\t".$ColRef->{'name'}.
              "[".($ColRef->{'length'}+1)."];\n";
    }
    elsif($ColRef->{'type'} eq 'number')
    {
        print $fhTableH "\t";
        if($ColRef->{'length'} !~ m/\./)
        { 
            if($ColRef->{'length'} <= 6)
            {   print $fhTableH "int";  }
            else
            {   print $fhTableH "double";   }
        }
        else
        {   print $fhTableH "double";   }
        print $fhTableH "\t\t\t".$ColRef->{'name'}.";\n";
    }
    elsif($ColRef->{'type'} eq 'date')
    {
        print $fhTableH "\tchar"."\t\t\t".$ColRef->{'name'}."[15];\n";
    }
    else
    {   die("Column type error");   }
}
print $fhTableH "} ".$sTable."_t;\n";

$fhTableH->close();


#============================#
#      生成基本SQL函数       #
#============================#
$fhTablePc=new FileHandle("> ".$sTablePc) || die "File open fail";

print $fhTablePc "#include <stdio.h>\n";
print $fhTablePc "#include <stdlib.h>\n";
print $fhTablePc "EXEC SQL include sqlca;\n";
print $fhTablePc "EXEC SQL include sqlda;\n\n";
print $fhTablePc "#include \"const.h\"\n";
print $fhTablePc "#include \"db.h\"\n\n";
print $fhTablePc "#define MODULE_NAME \"".$sTable.".pc\"\n";
print $fhTablePc "#define TABLE_NAME \"".$sTable."\"\n\n";

print $fhTablePc "int Dbs".uc($sTable)."(int iFunc, ".
$sTable."_t *p_".$sTable.")\n{\n";
print $fhTablePc "    switch(iFunc)\n    {\n";

#----- INIT -----#
print $fhTablePc "      case DBS_INIT:\n";
for($i=0; $i<$iColsCount; $i++)
{
    $ColRef=$ahColsRef->[$i];
    print $fhTablePc "        ";
    if($ColRef->{'type'} eq 'char' ||
       $ColRef->{'type'} eq 'varchar2' ||
       $ColRef->{'type'} eq 'date')
    {   
        $sTmp=$ColRef->{'default'};
        $sTmp=~s/'/"/g;
        print $fhTablePc "strcpy(p_".$sTable."->".$ColRef->{'name'}.", ".$sTmp.")";
    }
    elsif($ColRef->{'type'} eq 'number')
    {
        print $fhTablePc "p_".$sTable."->".$ColRef->{'name'}."=".$ColRef->{'default'};
    }
    else
    {   die("Column type error");   }
    print $fhTablePc ";";
    print $fhTablePc "\n";
}
print $fhTablePc "        return 0;\n\n";

#----- SELECT -----#
print $fhTablePc "      case DBS_SELECT:\n";
print $fhTablePc "        EXEC SQL select\n";
for($i=0; $i<$iColsCount; $i++)
{
    $ColRef=$ahColsRef->[$i];
    print $fhTablePc "\t\t\t";
    if($ColRef->{'type'} eq 'varchar2' )
    {   print $fhTablePc "rtrim(".$ColRef->{'name'}.", ' ')";   }
    elsif($ColRef->{'type'} eq 'date')
    {
        print $fhTablePc "to_char(".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')";
    }
    else
    {   print $fhTablePc $ColRef->{'name'};   }
    if($i < $iColsCount-1) 
    {   print $fhTablePc ",";   }
    print $fhTablePc "\n";
}
print $fhTablePc "                 into\n";
for($i=0; $i<$iColsCount; $i++)
{
    $ColRef=$ahColsRef->[$i];
    print $fhTablePc "\t\t\t:p_".$sTable."->".$ColRef->{'name'};
    if($i < $iColsCount-1) 
    {   print $fhTablePc ",";   }
    print $fhTablePc "\n";
}
print $fhTablePc "\t\tfrom ".$sTable."\n";
print $fhTablePc "\t\twhere\n";
for($i=0; $i<$iPKColsCount; $i++)
{
    for($j=0; $j<$iColsCount; $j++)
    {   last if($aPKCols[$i] eq $ahColsRef->[$j]{'name'});   }
    $j==$iColsCount && die "Primary key error";
    $ColRef=$ahColsRef->[$j];
    print $fhTablePc "\t\t\t".$ColRef->{'name'}."=";
    if($ColRef->{'type'} eq 'varchar2')
    {
        print $fhTablePc "rtrim(:p_".$sTable."->".$ColRef->{'name'}.", ' ')";
    }
    elsif($ColRef->{'type'} eq 'date')
    {
        print $fhTablePc "to_date(:p_".$sTable."->".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')";
    }
    else
    {
        print $fhTablePc ":p_".$sTable."->".$ColRef->{'name'};
    }
    if($i < $iPKColsCount-1) 
    {   print $fhTablePc " and";   }
    print $fhTablePc "\n";
}
print $fhTablePc "\t\t\t;\n        SQL_CODE_PROCESS(\"SEL\");\n\n";

#----- SELECT with update -----#
print $fhTablePc "      case DBS_LOCK:\n";
print $fhTablePc "\tEXEC SQL select\n";
for($i=0; $i<$iColsCount; $i++)
{
    $ColRef=$ahColsRef->[$i];
    print $fhTablePc "\t\t\t";
    if($ColRef->{'type'} eq 'varchar2' )
    {   print $fhTablePc "rtrim(".$ColRef->{'name'}.", ' ')";   }
    elsif($ColRef->{'type'} eq 'date')
    {
        print $fhTablePc 
              "to_char(".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')";
    }
    else
    {   print $fhTablePc $ColRef->{'name'};   }
    if($i < $iColsCount-1) 
    {   print $fhTablePc ",";   }
    print $fhTablePc "\n";
}
print $fhTablePc "                 into\n";
for($i=0; $i<$iColsCount; $i++)
{
    $ColRef=$ahColsRef->[$i];
    print $fhTablePc "\t\t\t:p_".$sTable."->".$ColRef->{'name'};
    if($i < $iColsCount-1) 
    {   print $fhTablePc ",";   }
    print $fhTablePc "\n";
}
print $fhTablePc "\t\tfrom ".$sTable."\n";
print $fhTablePc "\t\twhere\n";
for($i=0; $i<$iPKColsCount; $i++)
{
    for($j=0; $j<$iColsCount; $j++)
    {   last if($aPKCols[$i] eq $ahColsRef->[$j]{'name'});   }
    $j==$iColsCount && die "Primary key error";
    $ColRef=$ahColsRef->[$j];
    print $fhTablePc "\t\t\t".$ColRef->{'name'}."=";
    if($ColRef->{'type'} eq 'varchar2')
    {
        print $fhTablePc "rtrim(:p_".$sTable."->".$ColRef->{'name'}.", ' ')";
    }
    elsif($ColRef->{'type'} eq 'date')
    {
        print $fhTablePc "to_date(:p_".$sTable."->".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')";
    }
    else
    {
        print $fhTablePc ":p_".$sTable."->".$ColRef->{'name'};
    }
    if($i < $iPKColsCount-1) 
    {   print $fhTablePc " and";   }
    print $fhTablePc "\n";
}
print $fhTablePc "\t\tfor update\n\t\t\t;\n";
print $fhTablePc "        SQL_CODE_PROCESS(\"LCK\");\n\n";

#----- UPDATE -----#
print $fhTablePc "      case DBS_UPDATE:\n";
print $fhTablePc "\tEXEC SQL update ".$sTable."\n";
print $fhTablePc "                 set\n";
for($i=0; $i<$iColsCount; $i++)
{
    $ColRef=$ahColsRef->[$i];
    print $fhTablePc "\t\t\t";
    if($ColRef->{'type'} eq 'date')
    {
        print $fhTablePc $ColRef->{'name'}."=to_date(:p_".$sTable."->".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')";
    }
    else
    {
        print $fhTablePc $ColRef->{'name'}."=:p_".$sTable."->".$ColRef->{'name'};
    }
    if($i < $iColsCount-1) 
    {   print $fhTablePc ",";   }
    print $fhTablePc "\n";
}
print $fhTablePc "\t\twhere\n";
for($i=0; $i<$iPKColsCount; $i++)
{
    for($j=0; $j<$iColsCount; $j++)
    {   last if($aPKCols[$i] eq $ahColsRef->[$j]{'name'});   }
    $j==$iColsCount && die "Primary key error";
    $ColRef=$ahColsRef->[$j];
    print $fhTablePc "\t\t\t".$ColRef->{'name'}."=";
    if($ColRef->{'type'} eq 'varchar2')
    {
        print $fhTablePc "rtrim(:p_".$sTable."->".$ColRef->{'name'}.", ' ')";
    }
    elsif($ColRef->{'type'} eq 'date')
    {
        print $fhTablePc "to_date(:p_".$sTable."->".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')";
    }
    else
    {
        print $fhTablePc ":p_".$sTable."->".$ColRef->{'name'};
    }
    if($i < $iPKColsCount-1) 
    {   print $fhTablePc " and";   }
    print $fhTablePc "\n";
}
print $fhTablePc "\t\t\t;\n";
print $fhTablePc "        SQL_CODE_PROCESS(\"UPD\");\n\n";

#----- DELETE -----#
print $fhTablePc "      case DBS_DELETE:\n";
print $fhTablePc "\tEXEC SQL delete from ".$sTable."\n";
print $fhTablePc "\t\twhere\n";
for($i=0; $i<$iPKColsCount; $i++)
{
    for($j=0; $j<$iColsCount; $j++)
    {   last if($aPKCols[$i] eq $ahColsRef->[$j]{'name'});   }
    $j==$iColsCount && die "Primary key error";
    $ColRef=$ahColsRef->[$j];
    print $fhTablePc "\t\t\t".$ColRef->{'name'}."=";
    if($ColRef->{'type'} eq 'varchar2')
    {
        print $fhTablePc "rtrim(:p_".$sTable."->".$ColRef->{'name'}.", ' ')";
    }
    elsif($ColRef->{'type'} eq 'date')
    {
        print $fhTablePc "to_date(:p_".$sTable."->".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')";
    }
    else
    {
        print $fhTablePc ":p_".$sTable."->".$ColRef->{'name'};
    }
    if($i < $iPKColsCount-1) 
    {   print $fhTablePc " and";   }
    print $fhTablePc "\n";
}
print $fhTablePc "\t\t\t;\n";
print $fhTablePc "        SQL_CODE_PROCESS(\"DEL\");\n\n";

print $fhTablePc "      default:  return(543);\n";
print $fhTablePc "    }\n";
print $fhTablePc "}\n";
$fhTablePc->close();