Oricle 数据库操作类

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);
 }
}