1、1,Thinking in Perl -Perl DBI,Charlie Cao (Cao, Xiaobing) CMB Ext. 78272,2,Contents,Overview Data Types Variables Common Predefined Variables Constants Reference Control Structures Terseness Regular Expression Subroutines Using Modules DBI (Database Interface) Convert DBLib to DBI (DBD),3,What is Per
2、l?,Perl is short for “ Practical Extraction and Report Language“ (Pathologically Eclectic Rubbish Lister) designed by Larry Wall (12, Dec. 1987). Perl is a general-purpose programming language originally developed for text manipulation and now used for a wide range of tasks including system administ
3、ration, web development, network programming, GUI development, and more. The language is intended to be practical (easy to use, efficient, complete) rather than beautiful (tiny, elegant, minimal). Its major features are that its easy to use, supports both procedural and object-oriented (OO) programm
4、ing, has powerful built-in support for text processing, and has one of the worlds most impressive collections of third-party modules.,4,Running Perl programs,Windows (ActivePerl 5.10, Download): perl sample.pl sample.pl perl sample.pl perl -e “print hello world!“Unix/Linux (Perl 5.8.0): perl sample.
5、pl chmod 0777 (a+x) sample.pl sample.pl (#!/export/opt/perl/5.8.0/bin/perl) perl sample.pl perl -e “print hello world!“,5,Perldoc command,perldoc perl Overview of Perl languageperldoc module name (e.g DBI) help doc of the moduleperldoc f (subroutine name (e.g split) help doc of the subroutine,6,Emul
6、ated IDE- Ultra Edit,7,Emulated IDE- Ultra Edit,8,Emulated IDE- Ultra Edit,9,Data Types,Scalar Integer 0 -40 255 61298040283768 61_298_040_283_768 Float 1.25 255.000 7.25e45 -6.5e24 -12e-24 Octal 047 49 (Dec) Hex 0x1f 31 (Dec) Binary 0b11111111 255 (Dec),10,Data Types,Scalar String Double quote: $re
7、sult = 14; print (“The value of $result is $result.n“) #The value of $result is 14.,11,Data Types,Scalar String Single quote: $text = This is two lines of text ; $text = “This is twonlines of textn“; Subroutines: q /Hello world/ # gets Hello world qq /Hello world/ # gets “Hello world” length($string
8、); substr(string, offset, length) uc/lc(string) join(expr, list) # $text = join(, lines); ltrim/rtrim(string),12,Data Types,Scalar String/Numeric conversion$string = “43“;$number = 28;$result = $string + $number; # $result = 71$result = $string.$number; # $result = “4328“$result = $string x 3; # $re
9、sult = “434343“$result = “hello“ * 5; # $result = 0$result = “12a34“ +1; # $result = 13,13,Data Types,List Samples: （1，2，3) （1，2，3,） （） （1 100） （1757） （5 1） （0，2 6，10，12） （$m $n） （0 $#array） （“fred”, “barney”, “betty”, “wilma”, “dino”）($rocks0,$rocks1,$rocks2,$rocks3) = （“fred”, “barney”, “betty”, “
10、wilma”, “dino”); rocks = （“fred”, “barney”, “betty”, “wilma”, “dino”);($fred, $barney) = ($barney, $fred);copy = quarry;$abc=“00“,“01“,“10“,“11“; $var=$abc-11; Subroutines: QW qw(fred barney betty wilma dino) qw ! fred barney betty wilma dino ! qw # fred barney betty wilma dino # qw fred barney bett
11、y wilma dino ($rocks0,$rocks1,$rocks2,$rocks3) = qw/talc mica feldspar quartz/; rocks = qw / bedrock slate lava /; ($fred, $barney) = qw ;,14,Data Types,List Subroutines: pop/push array = 59; $fred = pop(array); #$fred = 9，array =（5，6，7，8） $barney = pop array; #$barney = 8, array =（5,6,7） pop array;
12、 #array =（5，6)push(array,0); #array = (5,6,0) push array,8; #array =（5，6，0，8） push array,110; #array others =qw/9 0 2 1 0 /; push array,others;,15,Data Types,List Subroutines: shift/unshift array = qw# dino fred barney #; $m = shift (array); #$m = “dino”, array = (“fred”, “barney”) $n = shift array;
13、 #$n = ”fred”, array =（“barney”） shift array; #array is empty $o = shift array; #$o = undef, array is still empty unshift(array,5); #array =（5） unshift array,4; #array = (4,5) others = 13; unshift array, others; #array =（1,2,3,4,5）,16,Data Types,List Subroutines: reverse fred = 6 10; barney = revers
14、e (fred); #gets 10，9，8，7，6 sort rocks = qw/ bedrock slate rubble granite /; sorted = sort(rocks); #gets bedrock, granite, rubble, slate back = reverse sort rocks; #from slate to bedrock rocks = sort rocks; #gets sorted rocks numbers = sort 97 102; #gets 100，101，102，97，98，99sort rocks; #error, the su
15、broutine must have a return value split $info = “Caine:Micheal:Actor:14,Leafy Drive”; personal = split(/:/,$info); #personal = (“Caine“, “Michael“, “Actor“, “14, Leafy Drive“);,17,Data Types,Scalar/List Context 42 + something #something must be a scalar sort something #something must be a listpeople
16、 = qw( fred barney betty ); $number = people; # $number = 3 sorted = sort people; #list context：barney , betty, fred $number = 42 + people; #scalar context：42+3， $number = 45backwards = reverse qw / yabba dabba doo /; #gets doo, dabba, yabba $backwards = reverse qw/ yabba dabba doo /; #gets oodabbad
17、abbayrocks = qw(talc quartz jade obsidian); print “How many rocks do you have?n”; print “I have ”, rocks, “rocks!n”; #error，outputs the name list of rocks print “I have ”, scalar rocks, “rocks!n”; # outputs the number,18,Data Types,Hash (Key-Value) Samples:%some_hash = (“foo”, 35, “bar”, 12.4, 2.5,
18、“hello”, “wilma”, 1.72e30, “betty”, “byen”);$returnvalue = $hash$some_key;$family_name“fred” = “flintstone”;$family_name“barney” = “rubble”foreach $person (qw)print “Ive heard of $person $family_name$person.n”;$foo = “bar”;print $family_name$foo . “ney”; #outputs “rubble”any_array = %some_hash;print
19、 “any_arrayn”; # the result maybe: ：betty bye(n)，wilma 1.72e+30 foo 35 2.5 hello bar 12.4%new_hash = %old_hash;%ip_address = reverse %host_name;,19,Data Types,Hash (Key-Value) Samples: %last_name = (“fred” = “flintstone”,“dino” = “smith”,“barney”= “rubble”;“betty”= “rubble”,); Subroutines: keys/valu
20、esmy %hash = (“a”=1, “b”=2, “c”=3);my k = keys %hash;my v = values %hash;foreach $person (sort keys %books)if($books$person)print “$person has $books$person itemsn” #fred has 3 books,20,Data Types,Hash (Key-Value) Subroutines: eachwhile ($key, $value) = each %hash)print “$key = $valuen”;existsif(exi
21、sts $books$dino)print “Hey, theres a library card for dino!n”; deletemy $person = “betty”;delete $books$person; #remove the library card of $person,21,Variables,3 types of variables: Scalar: $scalar_variable_name List: list_variable_name Hash: %hash_variable_name,22,Variables,3 types of variables: S
22、calar: $scalar_variable_name List: list_variable_name Hash: %hash_variable_nameScope of variables: default: global scope my: private scope local: local scopelist = qw/global scope/; # global scope variable sub mysub my $str = “This is a private string”;local list = qw/local scope/;print “list“.“n“;p
23、rint $str; print $str; # error print “list“; # outputs “global scope”,23,Common Predefined Variables,/while()print;while()print; $_ list = qw/str1 str2 str3/; list = qw/str1 str2 str3/; foreach(list) foreach $item(list)print; print $item;print “n“; print “n”; $” arrayName = qw/Tom John Jimmy Charlie
24、/; $“ = “|“;print “arrayName“; # with double quote,24,Common Predefined Variables,$, arrayName = qw/Tom John Jimmy Charlie/; $, = “|“;print arrayName; # without double quote $ arrayName = qw/Tom John Jimmy Charlie/; $ = “t“; foreach(list)print; $/ Terminator for read data from a file. $0 The name of
25、 the running Perl file. $ The current process ID. $? The latest error status.,25,Common Predefined Variables,$1、$2、$3、$_ = “Hello there, neighbor“;if(/(S+) (S+), (S+)/)print “The words are $1 $2 $3“; $、$STDOUT、STDERR ARGV INC,26,Constants,Scalar constantuse constant COUNT = 100;# -print COUNT ; List
26、 constant use constant WEEKDAYS = qw(Sunday Monday Tuesday Wednesday Thursday Friday Saturday);# -print “Today is “, (WEEKDAYS) 1 , “.n“; Hash constantuse constant WEEKABBR =Monday = Mon,Tuesday = Tue,Wednesday =Wed,Thursday = Thu,Friday =Fri)# -%abbr = WEEKABBR;$day = Wednesday;print “The abbrevaia
27、tion for $day is ”, $abbr$day;,27,Reference,Scalar reference$numberref = 42; # reference to a scalar value, is like # slice of a hash,28,Reference,subroutine reference$rs = # dereference: call the subroutine,29,Control Structures,Branch structures if-else-elsif if (condition1)doIfSomething();elsif (
28、condition2)doElsifSomething();elsedoElseSomething(); unless unless (condition)doUnlessSomething();,30,Control Structures,Loop structures while while (condition)continue# clauses after each loop; for for (pre_clause; end_condition; clause after each loop),pre_clause;while (end_condition)continueclaus
29、e after each loop;,31,Control Structures,Loop structures foreach foreach control variable (list) until until (condition),while (!condition) ,32,Control Structures,Loop structures do while do clause; while (condition);do until do clause; until (condition);,clause;while (condition) clause; ,clause;whi
30、le (!condition) clause; ,33,Terseness,if block if (condition1) if (condition2) clause; condition1 ) if-then-else block if (condition1) else express ? if_true_expr : if_false_expr,34,Subroutines,Definition sub function if (condition1) return ($value1);return (array1, $value2);Call ($return1, $return2
31、) = ,35,Subroutines,Parameters Definitionsub function my ($parameter1, $parameter2, parameter3)=_; Call # using references of the parameters,36,Subroutines,Dynamic call if (condition1) $function = “function1 $parameter1”; else $function = “otherwise”;eval $function;sub function1 my ($parameter1) = _
32、;sub otherwise ,37,Regular Expression,Common Regular Expressions,$a=“xxx123b5www123f5ssss12375“; $a=s/123(.*?)5/4/eg; print $a;,38,Regular Expression,Pattern-matching modifiers,$a=“xxx123b4www123f4ssss12374“; $a=s/123(.*?)4/,39,Regular Expression,Multi-match operators Miserliness * 0, 1 or multiple
33、+ 1 or multiple ? 0 or 1 X = X X, = X X,Y =X and = X, but as less as possible X,Y =X and =Y, but as less as possible,40,Regular Expression,Samples 1) my $target = “abcmnabILOVEcjhabcILOVEiuabILOVEccabc“; my $query = “ILOVE“; my $i = 0; while($target = m/$query/g) printf “Match %d begins at %dn“,+$i,
34、 $-0;2) $_ = “The HAL-9000 requires authorization to continue.”; if(/HAL-0-9+/) print “The string mentions some model of HAL computer.n”; 3) /fred( +|t+)barney/,41,Regular Expression,Samples 4) $_ =“green scaly dinosaur”; s/(w+) (w+)/$2, $1/; # “scaly, green dinosaur”; s/huge, /; # “huge, scaly, gre
35、en dinosaur” s/,.*een/; #replace with empty,“huge dinosaur” s/green/red/; #failure, “huge dinosaur” s/w+$/($!)$ # s/ return true if it succeed in replacing,42,Using Modules,ImportImport module path (INC),43,Using Modules,Samples1) use File:Basename qw/ /; #no subroutines were imported my $name = “/u
36、sr/local/bin/perl”; my $dirname = ,44,DBI（Database Interface）,DBI Architecture DBI Class Methods DBI Utility Functions DBI Dynamic Attributes Methods Common to All Handles Attributes Common to All Handles Database Handle Methods/Attributes Statement Handle Methods/Attributes,45,DBI（Database Interfac
37、e）,DBI Architecture,46,DBI（Database Interface）,DBI Architecture,47,DBI（Database Interface）,DBI Class Methods parse_dsn connect connect_cached available_drivers installed_drivers installed_versions data_sources trace,48,DBI（Database Interface）,DBI Class Methods parse_dsn SYNOPSIS: ($scheme, $driver,
38、$attr_string, $attr_hash, $driver_dsn) = DBI-parse_dsn($dsn) or die “Cant parse DBI DSN $dsn“; Example:($scheme, $driver, $attr_string, $attr_hash, $driver_dsn) = DBI-parse_dsn(“dbi:MyDriver(RaiseError=1):db=test;port=42“); # The following are the parsed results:# $scheme = dbi; # $driver = MyDriver
39、; # $attr_string = RaiseError=1; # $attr_hash = RaiseError = 1 ; # $driver_dsn = db=test;port=42;,49,DBI（Database Interface）,DBI Class Methods connect connect_cached SYNOPSIS: $dbh = DBI-connect($data_source, $username, $password , %attr) or die $DBI:errstr; $dbh = DBI-connect_cached($data_source, $
40、username, $password , %attr) or die $DBI:errstr; $data_source:dbi:DriverName:DSN #data source name, e.g MLOC_DEVLdbi:DriverName:database_namehostname:port dbi:DriverName:database=database_name;host=hostname;port=port %attr:PrintError = 0 or 1,PrintWarn = 0 or 1,RaiseError = 0 or 1, # die with error
41、messageAutoCommit = 0 or 1Example:$dbh = DBI-connect($data_source, $usern, $passw, PrintError = 0, AutoCommit = 0 ) or die “$DBI:errstr”;$dbh = DBI-connect($data_source, $usern, $passw, PrintError = 0, RaiseError=1, AutoCommit = 0 ) ;$dbh = DBI-connect (dbi:DriverName(PrintError = 0, RaiseError=1):D
42、SN, $usern, $passw) ;$dbh-RaiseError = 1; # disconnect from the database$dbh-disconnect or warn “Disconnection failed: $DBI:errstrn“;,50,DBI（Database Interface）,DBI Class Methods available_drivers installed_drivers installed_versions data_sources trace SYNOPSIS: #Returns a list of all available driv
43、ers by searching for DBD:* modules through the directories in INC ary = DBI-available_drivers; #Returns a list of driver name and driver handle pairs for all drivers installed (loaded) into the current process. %drivers = DBI-installed_drivers(); #Calls available_drivers() and attempts to load each
44、of them in turn using install_driver(). DBI-installed_versions; # Print out a formatted list of the installed drivers ary = DBI-installed_versions; # The list of successfully loaded drivers is returned %hash = DBI-installed_versions; #A hash contains entries for the DBI version, OS name, etc. #Retur
45、ns a list of data sources (databases) available via the named driver. ary = DBI-data_sources($driver); #Trace the process, and output information to the console or a file according different tracing level. DBI-trace($level , trace_log_file);,0 - Trace disabled. 1 - Trace top-level DBI method calls r
46、eturning with results or errors.2 - As above, adding tracing of top-level method entry with parameters. 3 - As above, adding some high-level information from the driver and some internal information from the DBI.4 - As above, adding more detailed information from the driver. This is the first level
47、to trace all the rows being fetched. 5 to 15 - As above but with more and more internal information.,51,DBI（Database Interface）,DBI Class Methods Example:# Load the DBI moduleuse DBI; #Set trace output to a file at level 1DBI-trace(1,trace.log); # Probe DBI for the installed drivers my drivers = DBI
48、-available_drivers(); die “No drivers found!n“ unless drivers; # should never happen #Print out a formatted list of the installed drivers DBI-installed_versions; # Iterate through the drivers and list the data sources for each one foreach my $driver ( drivers ) print “Driver: $drivern“; my dataSources = DBI-data_sources( $driver ); foreach my $dataSource ( dataSources ) print “tData Source is $dataSourcen“; ,
