Perl DBI实例讲解
此文档的目的是给使用Perl DBI模块访问数据库的开发人员提供一些实列。也为了减轻Perl DBI邮件列表的负担。 基础知识: 我们要做的第一件事情是安装DBI模块。这在 DBI INSTALL 文档中有介绍。接着我们需要安装数据库驱动,或者称为DBD。其安装简介也在每个 use?strict; use?DBI; my?$dbh?=?DBI->connect(?'dbi:Oracle:orcl',????????????????????????'jeffrey',????????????????????????'jeffspassword',??????????????????????); ????????????????????? use?strict; use?DBI; my?$dbh?=?DBI->connect(?'dbi:Oracle:orcl',??????????????????????)?||?die?"Database?connection?not?made:?$DBI::errstr"; $dbh->disconnect(); 使用disconnect()方法将避免出现“Database handle destroyed without explicit disconnect”这样的错误。 use?strict; use?DBI; my?$dbh?=?DBI->connect(?'dbi:Oracle:orcl',????????????????????????{ ??????????????????????????RaiseError?=>?1,??????????????????????????AutoCommit?=>?0 ????????????????????????} ??????????????????????)?||?die?"Database?connection?not?made:?$DBI::errstr"; $dbh->disconnect();
use?strict; use?DBI; my?$dbh?=?DBI->connect(?'dbi:Oracle:orcl',??????????????????????????AutoCommit?=>?0 ????????????????????????} ??????????????????????)?||?die?"Database?connection?not?made:?$DBI::errstr"; my?$sql?=?qq{?CREATE?TABLE?employees?(?id?INTEGER?NOT?NULL,? ???????????????????????????????????????name?VARCHAR2(128),? ???????????????????????????????????????title?VARCHAR2(128),? ???????????????????????????????????????phone?CHAR(8)? ?????????????????????????????????????)?}; $dbh->do(?$sql?); $dbh->disconnect(); ??? use?strict; use?DBI; my?$dbh?=?DBI->connect(?'dbi:Oracle:orcl',??????????????????????????AutoCommit?=>?0 ????????????????????????} ??????????????????????)?||?die?"Database?connection?not?made:?$DBI::errstr"; my?$sql?=?qq{?SELECT?*?FROM?employees?}; my?$sth?=?$dbh->prepare(?$sql?); $sth->execute(); $dbh->disconnect(); ??? ?? use?strict; use?DBI; my?$dbh?=?DBI->connect(?'dbi:Oracle:orcl',??????????????????????????AutoCommit?=>?0 ????????????????????????} ??????????????????????)?||?die?"Database?connection?not?made:?$DBI::errstr"; my?$sql?=?qq{?SELECT?id,?name,?title,?phone?FROM?employees?}; my?$sth?=?$dbh->prepare(?$sql?); $sth->execute(); my(?$id,?$name,?$title,?$phone?); $sth->bind_columns(?undef,?$id,?$name,?$title,?$phone?); while(?$sth->fetch()?)?{ ??print?"$name,?$phonen"; } $sth->finish(); $dbh->disconnect(); 这是个打印公司电话薄的好程序。但是WHERE字句怎么处理呢?我们将使用 bind_param方法一次准备一个SQL声明,执行将非常快。 use?strict; use?DBI?qw(:sql_types); my?$dbh?=?DBI->connect(?'dbi:Oracle:orcl',??????????????????????????AutoCommit?=>?0 ????????????????????????} ??????????????????????)?||?die?"Database?connection?not?made:?$DBI::errstr"; my?@names?=?(?"Larry%",?"Tim%",?"Randal%",?"Doug%"?); my?$sql?=?qq{?SELECT?id,?phone?FROM?employees?WHERE?name?LIKE???}; my?$sth?=?$dbh->prepare(?$sql?); for(?@names?)?{ ??$sth->bind_param(?1,?$_,?SQL_VARCHAR?); ??$sth->execute(); ??my(?$id,?$phone?); ??$sth->bind_columns(?undef,?$phone?); ??while(?$sth->fetch()?)?{ ????print?"$name,?$phonen"; ??} } $sth->finish(); $dbh->disconnect(); 高级: use?strict; use?DBI?qw(:sql_types); my?$dbh?=?DBI->connect(?'dbi:Oracle:orcl',??????????????????????????AutoCommit?=>?0 ????????????????????????} ??????????????????????)?||?die?"Database?connection?not?made:?$DBI::errstr"; my?@records?=?( ????????????????[?0,?"Larry?Wall",??????"Perl?Author",??"555-0101"?],????????????????[?1,?"Tim?Bunce",???????"DBI?Author",???"555-0202"?],????????????????[?2,?"Randal?Schwartz",?"Guy?at?Large",?"555-0303"?],????????????????[?3,?"Doug?MacEachern",?"Apache?Man",???"555-0404"?]? ??????????????); my?$sql?=?qq{?INSERT?INTO?employees?VALUES?(??,??,???)?}; my?$sth?=?$dbh->prepare(?$sql?); for(?@records?)?{ ??eval?{ ????$sth->bind_param(?1,?@$_->[0],?SQL_INTEGER?); ????$sth->bind_param(?2,?@$_->[1],?SQL_VARCHAR?); ????$sth->bind_param(?3,?@$_->[2],?SQL_VARCHAR?); ????$sth->bind_param(?4,?@$_->[3],?SQL_VARCHAR?); ????$sth->execute(); ????$dbh->commit(); ??}; ??if(?$@?)?{ ????warn?"Database?error:?$DBI::errstrn"; ????$dbh->rollback();?#just?die?if?rollback?is?failing ??} } $sth->finish(); $dbh->disconnect(); ? 调用Oracle存储过程: use?strict; use?DBI; my?$dbh?=?DBI->connect( ????'dbi:Oracle:orcl',????'jeffrey',????'jeffspassword',????{ ????????RaiseError?=>?1,????????AutoCommit?=>?0 ????} )?||?die?"Database?connection?not?made:?$DBI::errstr"; eval?{ ????my?$func?=?$dbh->prepare(q{ ????????BEGIN ????????????jwb_function( ????????????????parameter1_in?=>?:parameter1 ????????????); ????????END; ????}); ?? ????$func->bind_param(":parameter1",?'Bunce');?#位置占位符时方便的 ????$func->execute; ?? ????$dbh->commit; }; if(?$@?)?{ ????warn?"Execution?of?stored?procedure?failed:?$DBI::errstrn"; ????$dbh->rollback; } $dbh->disconnect; 下一个程序调用带返回值的存储过程,从一个函数中返回一个值,我们使用bind_param_inout来绑定占位符。当使用这种方法时,我们必须 use?strict; use?DBI; my?$dbh?=?DBI->connect( ????'dbi:Oracle:orcl',????????AutoCommit?=>?0 ????} )?||?die?"Database?connection?not?made:?$DBI::errstr"; my?$rv;?#holds?the?return?value?from?Oracle?stored?procedure eval?{ ????my?$func?=?$dbh->prepare(q{ ????????BEGIN ????????????:rv?:=?jwb_function( ????????????????parameter1_in?=>?:parameter1 ????????????); ????????END; ????}); ?? ????$func->bind_param(":parameter1",?'Bunce'); ????$func->bind_param_inout(":rv",?$rv,?6); ????$func->execute; ?? ????$dbh->commit; }; if(?$@?)?{ ????warn?"Execution?of?stored?procedure?failed:?$DBI::errstrn"; ????$dbh->rollback; } print?"Execution?of?stored?procedure?returned?$rvn"; $dbh->disconnect; 备注:1.这些例子中的finish方法不是必需的。如果你是用声明句柄而不是你自己的程序来做,那么你应该调用finish方法。2.永远使用use strict句法。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |