oricle 操作类
define('oci_return_alls',oci_both + oci_return_lobs);
class oracle {
/**
* select方法返回的最大记录数
*/
const max_row_num = 1000;
/**
* 数据查询结果集对象
* @var object $dataset
*/
public $dataset = null ;
/**
* 数据源对象
* @var object $ds
*/
public $ds = null ;
/**
* 查询的sql语句
* @var string $sql
*/
public $sql = '' ;
/**
* 执行查询的模式，值为 oci_commit_on_success 或 oci_default
* @var string $excutemode
*/
public $executemode = oci_commit_on_success ;
/**
* 构造函数
* @param object $ds 数据库
* @param string $sql 要初始化查询的sql语句
*/
function __construct($ds=null , $sql=null) {
if (!$ds) {
$this->error(dbexception::db_unconnected, '数据库还
未连接。');
} else {
$this->ds = $ds;
if ($sql) {
$this->open($sql);
}
}
}
/**
* 释放所占用的内存
* @param object $dataset 需要释放资源的结果集
* @access public
*/
public function close($dataset=null) {
if ($dataset) {
@oci_free_statement($dataset);
} else {
@oci_free_statement($this->dataset);
$this->eof = false ;
$this->recordcount = 0 ;
$this->recno = -1 ;
}
}
/**
* 对$pass进行数据库加密,返回加密之后的值
* @param string $pass 要加密的字符串
* @return string
* @access public
*/
public function encodepassword($pass) {
return md5($pass);
}
/**
* 得到错误信息和错误代号
* @param integer $queryresult 查询结果
* @return array
* @access protected
*/
protected function errorinfo($queryresult = null) {
$result = oci_error($this->ds->connect);
return $result;
}
/**
* 错误信息处理
* @param string $errorid 错误id
* @param string $errormessage 错误信息
* @access protected
*/
protected function error($errorid, $errormessage) {
throw new dbexception($errormessage, $errorid);
}
/**
* 执行sql语句
* @param string $sql sql语句
* @return object
* @param int $rowfrom 启始行号，行号从1开始
* @param int $rowto 结束行号，值为0表示
* @access public
* @see dbquery::open
*/
public function execute($sql = '', $rowfrom = 0, $rowto =
self::max_row_num) {
if ($rowfrom != 0 || $rowto != self::max_row_num) {
$sql = 'select * from (select row_.*, rownum rownum_
from ('
. $sql . ') row_ where rownum . $rowto . ') where rownum_ >= ' .
$rowfrom;
}
//echo $sql . '
';
//$start = microtime(true);
$dataset = @oci_parse($this->ds->connect, $sql);
$executesucceed = @oci_execute($dataset, $this-
>executemode);
//echo 'sql:'. ((string)(microtime(true)-$start)) . '
';
if (!$dataset || !$executesucceed) {
$sqlerror = $this->errorinfo();
$errormessage = '执行[' .
$sql
. ']出错！
color=#ff0000> ['
. $sqlerror['code'] . ']: '
. $sqlerror['message'] . '
' ;
$this->error(dbexception::db_query_error,
$errormessage);
}
return $dataset;
}
/**
* 执行sql语句，结果集保存到属性$dataset中
* @param string $sql sql语句
* @param int $rowfrom 启始行号，行号从1开始
* @param int $rowto 结束行号，值为0表示
* @return object
* @access public
* @see dbquery::execute
*/
public function open($sql='', $rowfrom = 0, $rowto =
self::max_row_num) {
$this->dataset = $this->execute($sql, $rowfrom, $rowto);
$this->sql = $sql ;
return $this->dataset;
}
/**
* 将一行的各字段值拆分到一个数组中
* @param object $dataset 结果集
* @param integer $resulttype 返回类型，oci_assoc、oci_num 或
oci_both
* @return array
*/
public function fetchrecord($dataset=null, $resulttype=oci_both) {
$result = @oci_fetch_array(($dataset) ? $dataset : $this-
>dataset, $resulttype);
if (is_array($result)) {
foreach ($result as $key => $value) {
if (!is_numeric($key)) {
$result[strtolower($key)] = $value;
}
}
}
return $result;
}
public function snext() {
$result = @oci_fetch_array($this->dataset, oci_both);
return $result;
}
/**
* 取得字段数量
* @param object $dataset 结果集
* @return integer
*/
public function getfieldcount($dataset = null) {
return oci_num_fields(($dataset) ? $dataset : $this-
>dataset);
}
/**
* 取得下一条记录。返回记录号，如果到了记录尾，则返回false
* @return integer
* @access public
* @see getprior()
*/
public function next() {
return $this->fetchrecord();
}
/**
* 得到当前数据库时间，格式为：yyyy-mm-dd hh:mm:ss
* @return string
* @access public
*/
public function getnow() {
return $this->getvalue('select to_char(sysdate, 'yyyy-mm-dd
hh24:mi:ss') dateofnow from dual');
}
/**
* 根据sql语句从数据表中取数据，只取第一条记录的值，
* 如果记录中只有一个字段，则只返回字段值。
* 未找到返回 false
*
* @param string $sql sql语句
* @return array
* @access public
*/
public function getvalue($sql = '', $hasclob = false) {
$dataset = $this->execute($sql, 1, 1);
if ($hasclob) {
$returntype = oci_return_alls;
} else {
$returntype = oci_both;
}
if ($result = $this->fetchrecord($dataset,$returntype)) {
$fieldcount = $this->getfieldcount($dataset);
$this->close($dataset);
return ($fieldcount } else {
return false ;
}
}
public function getseq($seqname = '') {
$dataset = $this->execute('select '.$seqname.'.nextval from
sys.dual');
if ($result = $this->fetchrecord($dataset)) {
$fieldcount = $this->getfieldcount($dataset);
$this->close($dataset);
return ($fieldcount } else {
return false ;
}
}
/**
* 表是否存在，返回true
* @param string $tablename 要查询的表名
* @return bool
* @access public
*/
public function tableisexists($tablename) {
return false;
}
/**
* 开始事务
* @access public
*/
public function begin() {
$this->executemode = oci_default;
}
/**
* 提交事务
* @access public
*/
public function commit() {
oci_commit($this->ds->connect);
$this->executemode = oci_commit_on_success;
}
/**
* 回滚事务
* @access public
*/
public function rollback() {
oci_rollback($this->ds->connect);
$this->executemode = oci_commit_on_success;
}
/**
* 插入一条记录
* @param string $tablename 表名
* @param array $fieldarray 字段数组
* @param string $whereforunique 唯一性条件
* @return int
* @access public
*/
public function insert($tablename, $fieldarray, $whereforunique =
null, $clobfield = null ) {
if (!$tablename || !$fieldarray || !is_array($fieldarray)) {
throw new exception('参数 $tablename 或 $fieldarray
的值不合法！');
}
if ($whereforunique) {
$where = ' where ' . $whereforunique;
$isexisted = $this->getvalue('select count(*) from '
. $tablename . $where);
if ($isexisted) {
throw new dbexception('记录已经存在！',
dbexception::db_record_is_existed);
}
}
$fieldnamelist = array();
$fieldvaluelist = array();
foreach ($fieldarray as $fieldname => $fieldvalue) {
if (!is_int($fieldname)) {
$fieldnamelist[] = $fieldname;
if ($clobfield && $clobfield==$fieldname) {
$fieldvaluelist[] = 'empty_clob()';
$hasclob = true;
$clobstr = str_replace
('',',$fieldvalue);
} else {
$fieldvaluelist[] = ''' .
$fieldvalue . ''';
}
}
}
$fieldname = implode(',', $fieldnamelist);
$fieldvalue = implode(',', $fieldvaluelist);
$sql = 'insert into ' . $tablename . '('
. $fieldname . ') values (' .
$fieldvalue . ')';
if ($hasclob) {
$sql .= ' returning content into :clob_string';
$dataset = oci_parse($this->ds->connect, $sql);
$clob = oci_new_descriptor($this->ds->connect,
oci_d_lob);
oci_bind_by_name($dataset, ':clob_string', $clob, -
1, oci_b_clob);
oci_execute($dataset, oci_default);
$clob->save($clobstr);
oci_commit($this->ds->connect);
return $dataset;
} else {
return $this->execute($sql);
}
}
/**
* 更新一条记录
* @param string $tablename 表名
* @param array $fieldarray 字段数组
* @param string $whereforupdate 查询条件
* @param string $whereforunique 唯一性条件
* @return int
* @access public
*/
public function update($tablename, $fieldarray,
$whereforupdate=null, $whereforunique=null, $clobfield = null) {
if (!$tablename || !$fieldarray || !is_array($fieldarray)) {
throw new exception('参数 $tablename 或 $fieldarray
的值不合法！');
}
if ($whereforunique) {
$where = ' where ' . $whereforunique;
$isexisted = $this->getvalue('select count(*) from '
. $tablename . $where);
if ($isexisted) {
throw new dbexception('记录已经存在！',
dbexception::db_record_is_existed);
}
}
$fieldnamevaluelist = array();
foreach ($fieldarray as $fieldname => $fieldvalue) {
if (!is_int($fieldname)) {
if ($clobfield && $clobfield==$fieldname) {
$fieldnamevaluelist[] = $fieldname .
'=empty_clob()';
$hasclob = true;
$clobstr = str_replace
('',',$fieldvalue);
} else {
$fieldnamevaluelist[] = $fieldname .
'='' . $fieldvalue . ''';
}
}
}
$fieldnamevalue = implode(',', $fieldnamevaluelist);
if ($whereforupdate) {
$whereforupdate = ' where ' . $whereforupdate;
}
$sql = 'update ' . $tablename
. ' set ' . $fieldnamevalue .
$whereforupdate;
if ($hasclob) {
$sql .= ' returning content into :clob_string';
$dataset = oci_parse($this->ds->connect, $sql);
$clob = oci_new_descriptor($this->ds->connect,
oci_d_lob);
oci_bind_by_name($dataset, ':clob_string', $clob, -
1, oci_b_clob);
oci_execute($dataset, oci_default);
$clob->save($clobstr);
oci_commit($this->ds->connect);
return $dataset;
} else {
return $this->execute($sql);
}
}
/**
* 选择一条记录
* @param string $sql sql语句
* @param string $dataformat 返回数据格式, 值
有array,hashmap,hashmap_str,dataset
* @param int $rowfrom 启始行号，行号从1开始
* @param int $rowto 结束行号，值为0表示
* @result array
* @access public
*/
public function select($sql, $dataformat = 'array', $rowfrom = 0,
$rowto = self::max_row_num, $hasclob = false) {
$dataset = $this->execute($sql, $rowfrom, $rowto);
switch ($dataformat) {
case 'array': //数组
$result = array();
$ismultifield = ($this->getfieldcount($dataset) >
1);
$i = 0;
if ($hasclob) {
$returntype = oci_return_alls;
} else {
$returntype = oci_both;
}
while ($data = $this->fetchrecord
($dataset,$returntype)) {
$result[$i] = ($ismultifield) ? $data :
$data[0];
$i++;
}
$this->close($dataset);
break;
case 'hashmap': //散列表
$result = array();
while ($data = $this->fetchrecord($dataset)) {
$result[ $data[0] ] = $data[1];
}
$this->close($dataset);
break;
case 'hashmap_str': //散列表字符串
$result = array();
while ($data = $this->fetchrecord($dataset,
oci_num)) {
$result[] = $data[0] . '=' . $data[1];
}
$result = implode('|', $result);
$this->close($dataset);
break;
default: //dataset 数据集，当返回数据格式为数据集时，select
方法的功能与execute方法相同
$result = $dataset;
}
return $result;
}
/**
* 返回最大值
* @param string $tablename 表名
* @param string $idfield 字段名
* @param string $where 查询条件
* @return int
* @access public
*/
public function getmax($tablename, $idfield, $where = null) {
$where = ($where) ? (' where ' . $where) : '';
return $this->getvalue('select max(' . $idfield . ') from '
. $tablename . $where);
}
}