Perl-DBI

[oracle@jhoa 3]$ perl dbi.pl sh-3.2$ c
sh: c: command not found
sh-3.2$ cat dbi.pl 
#!/usr/bin/perl   
use strict;  
use DBI;  
my $dbName = 'orcl';  
my $dbUser = 'test';  
my $dbUserPass = 'test';  
my $dbh = DBI->connect("dbi:Oracle:$dbName", $dbUser, $dbUserPass) or die "can't connect to database ";
my $hostSql = qq{select table_name,tablespace_name,status from user_tables};  
#你用print是打印sql语句和sth对象，看到这个说明没有错误  
#另外，perl中打印对象或其它复杂数据结构最好用use Data::Dumper;print Dumper $obj;这种形式，要不你就只能看到HA  
#$dbh->prepare($sql),只是生成了一个statement handle对象而已，该对象调用execute后，通过它的fetchrow_array()就  
#  

my $sql = "select * from v$lock";  

my $sth = $dbh->prepare($sql);  
print "$sth..
";  
  
$sth->execute();  

while (my  @arr = $sth->fetchrow_array()){
print "@arr
";
}
  
$sth->finish;  
sh-3.2$ perl dbi.pl | more
DBI::st=HASH(0x5276730)..
0000000580C36DC0 0000000580C36DE0 1650 XR 4 0 1 0 799125 0
0000000580C36E58 0000000580C36E78 1650 CF 0 0 2 0 799125 0
0000000580C36F88 0000000580C36FA8 1650 RS 25 1 2 0 799121 0
0000000580C370B8 0000000580C370D8 1651 RT 1 0 6 0 799121 0
0000000580C37318 0000000580C37338 1652 MR 1 0 4 0 799121 0
0000000580C373B0 0000000580C373D0 1652 MR 2 0 4 0 799121 0



$array = $sth->fetchrow_array取得下一行，返回字段值的数组。

selectall_arrayref:          返回数组的引用


#!/usr/bin/perl   
#use strict;  
use DBI;  
my $dbName = 'orcl';  
my $dbUser = 'test';  
my $dbUserPass = 'test';  
my $dbh = DBI->connect("dbi:Oracle:$dbName", $dbUser, $dbUserPass) or die "can't connect to database ";
#  
#数组引用

my $users = $dbh->selectall_arrayref("SELECT username FROM dba_users ORDER BY 1", { Slice => {} } );
print "$users is $users
";
#$user 为HASH
foreach my $user ( @$users ) { print "dba_users: $user->{USERNAME}
";}
$rc  = $dbh->disconnect;


  
sh-3.2$ perl dbi1.pl 
$users is ARRAY(0x1c49e0e0)
dba_users: ANONYMOUS
dba_users: COWORK_CZSH
dba_users: CTXSYS
dba_users: CUPS
dba_users: DBSNMP
dba_users: DIP

my $dbh = DBI->connect("dbi:Oracle:$dbName", $dbUser, $dbUserPass) or die "can't connect to database "; 

$dbh 数据库句柄

$sth->execute(); 执行语句句柄

$dbh->prepare($sql),只是生成了一个statement handle对象而已，该对象调用execute后

connect 建立到一个数据库服务器的连接
disconnect 断开数据库服务器的连接
prepare 准备执行一个SQL语句
execute 执行准备好的语句
do 准备并执行一个SQL语句
quote 加引号于要插入的字符串或BLOB值
fetchrow_array 作为一个字段数组取出下一行
fetchrow_arrayref 作为一个字段的引用数组取出下一行
fetchrow_hashref 作为一个哈希表的引用取出下一行
fetchall_arrayref 作为一个字段数组取出所有数据
finish 完成一条语句并且让系统释放资源
rows 返回受影响的行数
data_sources 返回可在localhost上得到的数据库的数组
ChopBlanks 控制fetchrow_*方法是否剥去空格
NUM_OF_PARAMS 在准备的语句中的占位(placeholder-参数)的数目
NULLABLE 其列可以是NULL
trace 执行调试跟踪
##########################################################################
$dbh 数据库句柄
$sth 语句句柄
$rc 返回代码(经常是一个状态)
$rv 返回值(经常是一个行数)