Perl Perl Programming Programming Part III Part III

hollowtexicoΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 7 μήνες)

74 εμφανίσεις

© New Age Training, Inc.1
Perl
Perl
Programming
Programming
Part III
Part III
© New Age Training, Inc.2
Perl
Perl
DBI module
DBI module
-
-
The Database
The Database
Interface
Interface
perl
script
DBI switch
DBD:Oracle
DBD:Sybase
DBD:CSV
RDBMS
RDBMS
flat files
© New Age Training, Inc.3
Some DBI examples
Some DBI examples

Basic access to Oracle

Select

Insert

Delete

Loading data

Accessing OS commands from PL/SQL

Oracle stored procedures
PERL
and
Oracle
© New Age Training, Inc.4
Basic access to Oracle
Basic access to Oracle

Connect to the database
•l
o
a
d
s
D
B
D
•r
e
q
u
i
r
e
s
N
e
t
8

Open a cursor

Fetch the results

Close the cursor

Close the database connection
© New Age Training, Inc.5
Connect
Connect
FILE
dbparam.pl
$host = "bigora";
$user = "user1";
$pass = "passwd";
#!/usr/bin/perl
use DBI;
do "dbparam.pl";
$dbh
= DBI->connect("dbi:Oracle:$host", $user, $pass)
or die "Database connection not made: $DBI::errstr";
print "Connected\n";
$dbh->disconnect();
© New Age Training, Inc.6
Select
Select
$sth
= $dbh->prepare("SELECT * FROM demo");
$sth->execute;
print("\nSELECT\n");
while (@row = $sth->fetchrow()) {
print "Row returned: @row\n";
# Row returned: $row[0] $row[1] $row[2]
}
SELECT
Row returned: 10 ACCOUNTING NEW YORK
Row returned: 20 RESEARCH DALLAS
Row returned: 30 SALES CHICAGO
Row returned: 40 OPERATIONS BOSTON
Row returned: 50 TRAINING RICHMOND
Row returned: 60 MANUFACTURING ROANOKE
© New Age Training, Inc.7
Insert from ASCII file
Insert from ASCII file
open (DATA, "demo1.dat") or die "demo1.dat: $!\n";
print "\nINSERT\n";
while (<DATA>) {
chop;
($deptno, $dname, $loc) = split(/,/, $_, 3);
$sth
= $dbh->prepare
("INSERT INTO demo VALUES (?, ?, ?)");
$sth->execute($deptno, $dname, $loc);
print "$deptno
$dname
$loc\n";
}
close (DATA);
print("\nSELECT\n");
$sth
= $dbh->prepare("SELECT * FROM demo");
$sth->execute;
while (@row = $sth->fetchrow()) { print "@row\n"; }
© New Age Training, Inc.8
--
--
Results (Insert)
Results (Insert)
FILE demo1.dat
70,RESEARCH,SAN FRANCISCO
80,SALES,LOS ANGELES
RESULTS
INSERT
70 RESEARCH SAN FRANCISCO
80 SALES LOS ANGELES
SELECT
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 TRAINING RICHMOND
60 MANUFACTURING ROANOKE
70 RESEARCH SAN FRANCISCO
80 SALES LOS ANGELES
© New Age Training, Inc.9
Delete … Parse result set
Delete … Parse result set
print("\nDELETE\n");
$dbh->do("DELETE FROM demo WHERE
deptno
>= 50");
$sth
= $dbh->prepare("SELECT * FROM demo");
$sth->execute;
print("\nSELECT\n");
while (($deptno, $dname, $loc) = $sth->fetchrow()) {
print "deptno: $deptno\n";
print "dname: $dname\n";
print "loc: $loc\n\n";
}
© New Age Training, Inc.10
--
--
Results (Delete)
Results (Delete)
DELETE
deptno: 10
dname: ACCOUNTING
loc: NEW YORK
deptno: 20
dname: RESEARCH
loc: DALLAS
deptno: 30
dname: SALES
loc: CHICAGO
deptno: 40
dname: OPERATIONS
loc: BOSTON
© New Age Training, Inc.11
Dump / Load
Dump / Load
print "\nDUMP\n";
$sth
= $dbh->prepare("SELECT * FROM demo");
$sth->execute;
%demo = ();
while (($deptno, $dname, $loc) = $sth->fetchrow()) {
$demo{$deptno} = $dname
. "," . $loc;
}
print "\nDELETE\n";
$dbh->do("DELETE FROM demo");
print "\nLOAD\n";
format STDOUT_TOP =
Dept.No Dept.Name Dept.Location
-------
-------------
-------------
.
© New Age Training, Inc.12
Dump / Load (continued)
Dump / Load (continued)
format STDOUT =
@<<<< @<<<<<<<<<<<<< @<<<<<<<<<<<<
$new_deptno, $dname, $loc
.
$new_deptno
= 100;
foreach
$old_deptno
(reverse sort keys %demo) {
($dname, $loc) = split(/,/, $demo{$old_deptno}, 2);
$dname
= ucfirst(lc($dname));
$sth
= $dbh->prepare
("INSERT INTO demo VALUES (?, ?, ?)");
$sth->execute($new_deptno, $dname, $loc);
write;
$new_deptno
+= 20;
}
© New Age Training, Inc.13
--
--
Results (Dump/Load)
Results (Dump/Load)
DUMP
DELETE
LOAD
Dept.No Dept.Name Dept.Location
-------
-------------
-------------
100
Operations BOSTON
120
Sales CHICAGO
140
Research DALLAS
160
Accounting NEW YORK
© New Age Training, Inc.14
Access OS Command
Access OS Command
Oracle
Perl
PL/SQL
UNIX -
NT-
VMS
cp, ls, mv, zip,
unzip, tar, dir,
sqlldr, imp, exp,
mail, etc.
© New Age Training, Inc.15
my $rv;
eval{ my $func
= $dbh->prepare
(q { BEGIN
:rv
:= check_candidate(p_empno
=> :emp_no);
END; } );
$func->bind_param(":emp_no", 7900);
$func->bind_param_inout(":rv", \$rv, 20);
$func->execute(); };
if ($@){warn "Execution of function failed:", $DBI::errstr,"\n";
$dbh->rollback; }
print "Execution of function returned $rv\n";
Calling stored procedure
Calling stored procedure
© New Age Training, Inc.16
Calling stored procedure
Calling stored procedure
Call function CHECK_CANDIDATE:
Enter EMP_NO (4 digits): 7990
Execution of function returned NOT CANDIDATE
Call function CHECK_CANDIDATE:
Enter EMP_NO (4 digits): 7777
Execution of function returned NOT CANDIDATE
Call function CHECK_CANDIDATE:
Enter EMP_NO (4 digits): 7900
Execution of function returned CANDIDATE
© New Age Training, Inc.17
Books
Books

Learning Perl
(Llama Book
) –
an excellent
textbook

Programming Perl (Camel Book
) –
a
Perl
user
reference

Perl
Cookbook (Ram Book
) –
a large
collection of Perl
recipes
© New Age Training, Inc.18
Resources
Resources

UNIX manual pages (on-line documentation):
man perl

www.perl.org

www.perl.com

www.cpan.org (huge
Perl
archive)

www.activestate.com

www.oreilly.com (books on Perl)

www.bookpool.com
© New Age Training, Inc.19
In A Nutshell ...
In A Nutshell ...
Perl
Perl
is designed to make the easy jobs easy,
is designed to make the easy jobs easy,
without making the hard jobs impossible.
without making the hard jobs impossible.

Larry Wall