DBI 모듈 설명 링크

www.dba-oracle.com/t_dbi_interface1.htm

부분부분 필요할때만 봤었는데, 메타정보 얻기와, autocommit 설정까지

궁금한 부분에 대해서 샘플까지 모두 나와 있음!!

혹시 몰라 소스 백업!

## END 로 finally 처리 하는부분과 if defined 활용부분

Example 1.

#!/usr/bin/perl -w

use strict;

use DBI;

my $db = DBI->connect( "dbi:Oracle:Local", "scott", "tiger" )

    || die( $DBI::errstr . "\n" );

$db->{AutoCommit}    = 0;

$db->{RaiseError}    = 1;

$db->{ora_check_sql} = 0;

$db->{RowCacheSize}  = 16;

my $SEL = "invalid SQL statement";

my $sth = $db->prepare($SEL);

print "If you see this, parse phase succeeded without a problem.\n";

$sth->execute();

print "If you see this, execute phase succeeded without a problem.\n";

END {

    $db->disconnect if defined($db);

}

##기본적인 fetchrow_array 활용법

Example 2.

 

#!/usr/bin/perl -w

use strict;

use DBI;

my $db = DBI->connect( "dbi:Oracle:Local", "scott", "tiger" )

    || die( $DBI::errstr . "\n" );

$db->{AutoCommit}    = 0;

$db->{RaiseError}    = 1;

$db->{ora_check_sql} = 0;

$db->{RowCacheSize}  = 16;

my $SEL = "SELECT * FROM EMP";

my $sth = $db->prepare($SEL);

$sth->execute();

 

while ( my @row = $sth->fetchrow_array() ) {

    foreach (@row) {

        $_ = "\t" if !defined($_);

        print "$_\t";

    }

    print "\n";

}

 

END {

    $db->disconnect if defined($db);

}

##파라미터를 이용한 바인딩

Example 3

 

#!/usr/bin/perl -w

use strict;

use DBI;

my $db = DBI->connect( "dbi:Oracle:Local", "scott", "tiger" )

                || die( $DBI::errstr . "\n" );

$db->{AutoCommit}    = 0;

$db->{RaiseError}    = 1;

$db->{ora_check_sql} = 0;

$db->{RowCacheSize}  = 16;

my $SEL = "SELECT e.ename,e.job,d.dname,e.sal

                      FROM   emp e, dept d

                      WHERE e.deptno=d.deptno AND

                                      e.empno=?";

my $sth = $db->prepare($SEL);

 

print "Enter EMPNO:";

my $empno = <STDIN>;

chomp($empno);

 

$sth->bind_param( 1, $empno );

$sth->execute();

my ( $ename, $job, $dept, $sal ) = $sth->fetchrow_array();

write if  $sth->rows>0;

 

no strict;

 

format STDOUT_TOP =

Employee Name          Job                            Department               Salary

----------------------------------------------------------------------------------------------------

.

 

format STDOUT =

@<<<<<<<<<<<        @<<<<<<<<<<<<   @<<<<<<<<<             @<<<<<<<<<

$ename,                       $job,                         $dept,                           $sal

.

 

END {

    $db->disconnect if defined($db);

}

##LOB파일 다루기. 테스트는 못해봤음

Example 5

#!/usr/bin/perl -w

use strict;

use DBI;

use DBD::Oracle qw(:ora_types);

use Getopt::Long;

my ( $user, $passwd, $db ) = ( "scott", "tiger", "local" );

my ( $file, $id );

my $stat = GetOptions( "u|user=s"     => \$user,

                       "p|password=s" => \$passwd,

                       "d|database=s" => \$db,

                       "f|file=s"     => \$file,

                       "h|help|?"     => \&usage

);

if ( !defined($file) or !$stat ) { usage(); }

my $dbh = db_connect( $user, $passwd, $db );

my $INS = "INSERT INTO DBI_LOB(NAME,DATA) VALUES (:FL,:BUFF)

                    RETURNING ID INTO :ID";

my $sth = $dbh->prepare($INS);

$sth->bind_param( ":FL", $file );

$sth->bind_param_inout( ":ID", \$id, 20 );

my $buff = gobble($file);

$sth->bind_param( ":BUFF", $buff, { ora_type => ORA_BLOB } );

 

$sth->execute();

 

print "BLOB ID=$id inserted\n";

$dbh->commit();

 

END {

    $dbh->disconnect() if defined($dbh);

}

 

sub db_connect {

    my ( $username, $passwd, $db ) = ( @_, $ENV{"TWO_TASK"} );

    my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd )

        || die( $DBI::errstr . "\n" );

    $dbh->{AutoCommit}    = 0;

    $dbh->{RaiseError}    = 1;

    $dbh->{ora_check_sql} = 0;

    $dbh->{RowCacheSize}  = 16;

     return ($dbh);

}

 

sub usage {

    print qq(

      USAGE:$0

               -u <username>  -p <password > -d <database>

               -f <file name>

           

      -------

      This script loads file defined by -f argument into database, as a

      BLOBS. This script was written as an educational tool and is free

      to use and modify as needed.

);

    exit(0);

}

 

sub gobble {

    my $file = shift;

    local $/ = undef;

    if ( !defined($file) ) { usage(); }

    open( FL, "<", $file ) or die "Cannot open file $file for reading:$!\n";

    my $buff = <FL>;

    close FL;

    return ($buff);

}

 

##오라클 배열 인서트

my @values;

my $INS="INSERT INTO TAB VALUES (:PLACEHOLDER)";

$sth=$db->prepare($INS);

$sth->bind_param_array(":PLACEHOLDER",\@array);

$sth->execute_array( { ArrayTupleStatus => \my @status });