Perl学习笔记4：数据库操作

这是Perl学习笔记的第三部分，数据库操作。

#!/usr/bin/perl
use strict;
use warnings;
use v5.10; # for say() function
 
use DBI;
# say "[INFO ] Perl MySQL Connect Demo";

# MySQL database configuration
# DB Sample 
# my XX private val
my $dsn = "DBI:mysql:database=expdf_cloud;host=192.168.8.35";
my $username = "root";
my $password = 'admin';

# connect to MySQL database
# my %attr = ( PrintError=>0,  # turn off error reporting via warn()
             # RaiseError=>1);   # turn on error reporting via die()           

say "[INFO ] Connected to the MySQL database.\n";
# my $dbh  = DBI->connect($dsn,$username,$password, \%attr);
my $dbh  = DBI->connect($dsn,$username,$password) or 
            die("[ERROR] Error connecting to the database: $DBI::errstr\n"); 


# SELECT
say "[INFO ] STEP1. Get Setting From Setting Table.";
my $FILE_FOLDER = query_sets($dbh,'FILE_FOLDER');
my $SVG_FOLDER = query_sets($dbh,'SVG_FOLDER');
my $TMP_FOLDER = query_sets($dbh,'TMP_FOLDER');
say "[INFO ] FILE_FOLDER  : $FILE_FOLDER";
say "[INFO ] SVG_FOLDER  : $SVG_FOLDER";
say "[INFO ] TMP_FOLDER  : $TMP_FOLDER";


# INSERT
say "\n[INFO ] STEP2. Insert Data to Table.";
insert_info($dbh,'FILE_FOLDER1','LALALA');
my $FILE_FOLDER1 = query_sets($dbh,'FILE_FOLDER1');
say "[INFO ] FILE_FOLDER  : $FILE_FOLDER1";

# UPDATE


# DELETE
say "\n[INFO ] STEP3. Delete Data from Table.";
del_info($dbh,'FILE_FOLDER1');


say "\n[INFO ] DisConnected to the MySQL database.";
$dbh->disconnect();



# 查询数据的子函数
sub query_sets{
  # query from the links table
 
  my ($dbh,$target) = @_;
  my $result = '';
  my $sql = "SELECT set_content
             FROM set_info
             WHERE set_item = ? ";
  my $sth = $dbh->prepare($sql);
 
  # execute the query
  $sth->execute($target);
 
  while(my @row = $sth->fetchrow_array()){
     $result  = $row[0];
  }       
  
  $sth->finish();
  
  return $result;
  
}

# 插入数据的子函数
sub insert_info{
   my ($dbh,$para1,$para2) = @_;
   
   my $sql = "INSERT INTO set_info(set_item,set_content)
	    VALUES(?,?)";
	 
   my $stmt = $dbh->prepare($sql);
   # execute the query
   if($stmt->execute($para1, $para2)){
	say "[INFO ] Inserted successfully";
   }else{
	say "[INFO ] Inserted error";
      }
   $stmt->finish();
 
}

# 删除数据的子函数
sub del_info{
   my ($dbh,$para1) = @_;
   
   my $sql = "DELETE FROM set_info WHERE set_item=?";
	 
   my $stmt = $dbh->prepare($sql);
   # execute the query
   if($stmt->execute($para1)){
	say "[INFO ] Deleted successfully";
   }else{
	say "[INFO ] Deleted error";
      }
   $stmt->finish();
 
}