Perl is an established language with lots of code examples provided on the Web. It is a powerful language (meaning you can do a lot with very little code). Perl has a rich library set, especially in regards to text processing. The language can take some getting use to because of its heavy use of control character. For example: ‘.’ means concatenate strings, and the leading character (@, $, & and %) indicates the data type being accessed (eg. $foobar is a scalar while @foobar is an array). Below is an example of a database containing two tables, customers and purchase order. The Customer table has customerid (the unique identifier for the customer), name (The customer’s name) and age (the customer’s age). The PurchaseOrder table has POID (the unique id for the purchase order), customerid (which refers back to the customer who made the purchase) and Purchase (what the purchase was). |
||||||||||||||||||||||||||||||||||||||||||||||||||||
Example: | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
SELECT
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file select.pl with the following code:
#!/usr/bin/perl -w
my @file_lines; # Loop through data and print lines
while ( my $line = <> ) { # Print Line
print $line . \n ; # Populate next line
push @file_lines, $line; } Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
ORDER BY
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file orderby.pl with the following code:
#!/usr/bin/perl -w
# Open the file
open(DATA, $ARGV[0]); my $line; # Initialize variables
$iCnt =0; # Loop through data and populate a array for later use.
while($line = ) # Copy data to an arry skipping header (first line with field names)
if ($iCnt >0) $iCnt ++; # Close file object
close DATA; # Sort using on custom sort based on second field
@sorted = sort{@PANa = split( \, , $a); @PANb = split( \, , $b); return $PANa[2] cmp $PANb[2] } (@lines); # Loop through data and print lines
while($iLoop < $iCnt-1) Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
WHERE
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file select_by_id.pl with the following code:
#!/usr/bin/perl -w my @file_lines;
# Loop through passed file
while ( my $line = <> ) { # Split line using a comma
@PAN =split( \, , $line); # If ID is passed ID then print
if ($out eq 1 ) # Populate next line
push @file_lines, $line; } Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
INNER JOIN
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file innerjoin.pl with the following code:
#!/usr/bin/perl -w
# Open both files
open(DATA1, $ARGV[0]); # Initialize variables
$iCnt =0; # Loop through data and populate a array for later use.
while($lineOrder = # Close file object
close DATA2; # Split line using a comma
@PAN1 =split( \, , $lineCust); # Remove newline character
chop($PAN1[2]); $iLoop =0; while($iLoop < $iCnt) # Split line using a comma
@PAN2 =split( \, , $orders[$iLoop]); #If there is a match print
if ($out1 eq $out2) $iLoop ++; # Close file object
close DATA1; Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
LEFT OUTER JOIN
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file leftouterjoin with the following code:
#!/usr/bin/perl -w
# Open both files
open(DATA1, $ARGV[0]); # Initialize variables
$iCnt =0; # Loop through data and populate a array for later use.
while($lineOrder = # Close file object
close DATA2; while($lineCust = # Split line using a comma
@PAN1 =split( \, , $lineCust); # Remove newline character
chop($PAN1[2]); $iLoop =0; while($iLoop < $iCnt) # Split line using a comma
@PAN2 =split( \, , $orders[$iLoop]); #If there is a match print
if ($out1 eq $out2) $iLoop ++; #If there was no match print
if ($iFound ==0) # Close file object
close DATA1; Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
GROUP BY
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file groupby.pl with the following code:
#!/usr/bin/perl -w
#Open the file
open(DATA1, $ARGV[0]); my $line; # Initialize variables
$iCnt =1; # Loop through data
while($line = # Split line using a comma
@PAN1 =split( \, , $line); # Remove newline character
chop($age); if ($iloop <2 ) if ($prior_id eq $id) $iloop ++; print $prior_name . , . $prior_age . , . $iCnt . \n ; # Close file object
close DATA; Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
UPDATE
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file update.pl with the following code:
#!/usr/bin/perl -w
# Open the file
open(DATA, $ARGV[0]); # Loop through data
while() # Split line using a comma
@PAN =split( \, , $_); # Pint if ID is not equal to passed ID otherwise update.
if ($out eq $ARGV[1]) # Close file object
close DATA; Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
INSERT
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file insert.pl with the following code:
#!/usr/bin/perl -w
#Open the file
open(DATA, $ARGV[0]); # Loop through data and print lines
while() # Add new line from passed arguments
print $ARGV[1]. , . $ARGV[2] . , . $ARGV[3] . \n ; # Close file object
close DATA; Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
DELETE
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file delete.pl with the following code:
#!/usr/bin/perl -w
#Open the file
open(DATA, $ARGV[0]); # Loop through data
while() # Split line using a comma
@PAN =split( \, , $_); # If ID is not passed ID then print
if ($out ne $ARGV[1]) # Close file object
close DATA; Then run the code: |
|