zhuxianfei.com

﹤首页 > 编程技术 > php

php 调用oracle存储过程代码(1/3)

发布时间：2016-11-25 16:39

/*
速度和效率。使用存储过程的速度显然更快。在效率上，如果应用一次需要做一系列sql操作，则需要往返于php与oracle，不如把该应用直接放到数据库教程方以减少往返次数，增加效率。但是在internet应用上，速度是极度重要的，所以很有必要使用存储过程
*/

//建立一个test表

create table test (

id number(16) not null,

name varchar2(30) not null,

primary key (id)

);

//插入一条数据

insert into test values (5, 'php/index.html>php_book');

//建立一个存储过程

create or replace procedure proc_test (

p_id in out number,

p_name out varchar2

) as

begin

select name into p_name

from test

where id = 5;

end proc_test;

/

--------------------------------------------------------------------------------

php/index.html>php代码:--------------------------------------------------------------------------------


//建立数据库连接

$user = "scott"; //数据库用户名

$password = "tiger"; //密码

$conn_str = "tnsname"; //连接串(cstr : connection_string)

$remote = true //是否远程连接

 代码如下 复制代码

class cls_mysql{
var $querynum = 0;
var $link;
var $histories;

var $dbhost;
var $dbuser;
var $dbpw;
var $dbcharset;
var $pconnect;
var $tablepre;
var $time;

var $goneaway = 5;

function connect($dbhost, $dbuser, $dbpw, $dbname = '', $dbcharset = '', $pconnect = 0, $tablepre='', $time = 0) {
$this->dbhost = $dbhost;
$this->dbuser = $dbuser;
$this->dbpw = $dbpw;
$this->dbname = $dbname;
$this->dbcharset = $dbcharset;
$this->pconnect = $pconnect;
$this->tablepre = $tablepre;
$this->time = $time;

if($pconnect) {
if(!$this->link = mysql_pconnect($dbhost, $dbuser, $dbpw)) {
$this->halt('can not connect to mysql server');
}
} else {
if(!$this->link = mysql_connect($dbhost, $dbuser, $dbpw)) {
$this->halt('can not connect to mysql server');
}
}

if($this->version() > '4.1') {
if($dbcharset) {
mysql_query("set character_set_connection=".$dbcharset.", character_set_results=".$dbcharset.", character_set_client=binary", $this->link);
}

if($this->version() > '5.0.1') {
mysql_query("set sql_mode=''", $this->link);
}
}

if($dbname) {
mysql_select_db($dbname, $this->link);
}

}

function fetch_array($query, $result_type = mysql_assoc) {
return mysql_fetch_array($query, $result_type);
}

function result_first($sql) {
$query = $this->query($sql);
return $this->result($query, 0);
}

function fetch_first($sql) {
$query = $this->query($sql);
return $this->fetch_array($query);
}

function fetch_all($sql, $id = '') {
$arr = array();
$query = $this->query($sql);
while($data = $this->fetch_array($query)) {
$id ? $arr[$data[$id]] = $data : $arr[] = $data;
}
return $arr;
}

function cache_gc() {
$this->query("delete from {$this->tablepre}sqlcaches where expiry<$this->time");
}

function query($sql, $type = '', $cachetime = false) {
$func = $type == 'unbuffered' && @function_exists('mysql_unbuffered_query') ? 'mysql_unbuffered_query' : 'mysql_query';
if(!($query = $func($sql, $this->link)) && $type != 'silent') {
$this->halt('mysql query error', $sql);
}
$this->querynum++;
$this->histories[] = $sql;
return $query;
}

function affected_rows() {
return mysql_affected_rows($this->link);
}

function error() {
return (($this->link) ? mysql_error($this->link) : mysql_error());
}

function errno() {
return intval(($this->link) ? mysql_errno($this->link) : mysql_errno());
}

function result($query, $row) {
$query = @mysql_result($query, $row);
return $query;
}

function num_rows($query) {
$query = mysql_num_rows($query);
return $query;
}

function num_fields($query) {
return mysql_num_fields($query);
}

function free_result($query) {
return mysql_free_result($query);
}

function insert_id() {
return ($id = mysql_insert_id($this->link)) >= 0 ? $id : $this->result($this->query("select last_insert_id()"), 0);
}

function fetch_row($query) {
$query = mysql_fetch_row($query);
return $query;
}

function fetch_fields($query) {
return mysql_fetch_field($query);
}

function version() {
return mysql_get_server_info($this->link);
}

function close() {
return mysql_close($this->link);
}

function halt($message = '', $sql = '') {
$error = mysql_error();
$errorno = mysql_errno();
if($errorno == 2006 && $this->goneaway-- > 0) {
$this->connect($this->dbhost, $this->dbuser, $this->dbpw, $this->dbname, $this->dbcharset, $this->pconnect, $this->tablepre, $this->time);
$this->query($sql);
} else {
$s = '<b>error:</b>'.$error.'<br />';
$s .= '<b>errno:</b>'.$errorno.'<br />';
$s .= '<b>sql:</b>:'.$sql;
exit($s);
}
}
}

数据库连接是一种有限的昂贵的资源，数据库连接影响到程序的性能指标。数据库连接池正是针对这个问题提出来的。数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而再不是重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。


/*
 * created on 2010-3-8
 * make by:suniteboy
 * my first mysql class
 *
 */

 class mysql{
 private $server   ="";
 private $user     ="";
 private $pwd      ="";
 private $database ="";
 private $linkmode = 1; //连接模式，0表示普通连接，1表示永久连接
 private $conn     = 0;
 private $sql      =""; //sql语句
 private $result   =""; //query查询结果
 private $record   =""; //保存记录

 //============================================
 // 构造函数
 //============================================
 public function __construct($server,$user,$pwd,$database,$charset="utf8",$linkmode=0)
 {
  if(empty ( $server )| empty( $user ) | empty( $database ))
  {
   $this->show_error("连接信息不完整,请检查是否提供了服务器地址,用户名以及连接的数据库信息");
   return 0;
  }
  $this->server = $server;
  $this->user = $user;
  $this->pwd = $pwd;
  $this->database = $database;
  $this->charset = $charset;
  $this->linkmode = $linkmode;
  $this->connect();
 }

 //============================================
 // 连接函数
 //============================================
 public function connect()
 {
  $this->conn = $this->linkmode?mysql_pconnect($this->server,$this->user,$this->pwd):
  mysql_connect($this->server,$this->user,$this->pwd);
  if(!$this->conn)
  {
   $this->show_error('无法连接服务器');
   return 0;
  }

  if(!mysql_select_db($this->database))
  {
   $this->show_error('无法连接数据库'.$this->database);
   return 0;
  }
// $this->query('set names '.$this->charset);
 return $this->conn;

 }
 //============================================
 // mysql查询函数
 //============================================
 public function query($sql)
 {
  if(empty($sql))
  {
   $this->show_error('sql语句为空');
   return 0;
  }
  $this->sql = preg_replace('/ {2,}/',' ',trim($sql));
  $this->result = mysql_query($this->sql,$this->conn);
  if(!$this->result)
  {
   $this->show_error('sql语句错误',true);
   return 0;
  }
  return $this->result;
 }

 //============================================
 // 函数
 //============================================
 public function select_db($dbname)
 {
  return mysql_select_db($dbname);
 }

 public function fetch_array($query,$result_type=mysql_assoc)
 {
  return mysql_fetch_array($query,$result_type);
 }

 public function fetch_row($query)
 {
  return mysql_fetch_row($query);
 }
 //============================================
 // 取得前一次mysql操作所影响到的记录行数
 //============================================
 public function affected_rows()
 {
  return mysql_affected_rows();
 }
 public function num_fields($query)
 {
  return mysql_num_fields($query);
 }

 public function num_rows($query)
 {
  return @mysql_num_rows($query);
 }

 public function insert_id()
 {
  return mysql_insert_id();
 }

 public function close()
 {
  return mysql_close();
 }
 //============================================
 // 从记录中取出一条结果
 //============================================
 public function getone($sql)
 {
  $res = $this->query($sql);
  if($res!==false)
  {
   $row = mysql_fetch_row($res);
   if($row!==false)
   {
    return $row;
   }
   else
   {
    return '';
   }
  }
  else
  {
   return false;
  }
 }

 //============================================
 // 从记录中取出所有结果
 //============================================
 public function getall($sql)
 {
  $res = $this->query($sql);
  if($res!==false)
  {
   $arr = array();
   while($row = mysql_fetch_assoc($res))
   {
    $arr[] = $row;
   }
   return $arr;
  }
  else
  {
   return false;
  }
 }

 //============================================
 // 错误提示函数
 //============================================
 public function show_error($msg='',$sql=false)
 {
  $err = '['.mysql_errno().']'.mysql_error();
  if($sql) $sql='sql语句:'.$this->sql;
  if($msg=='')
  {
   echo $err;
   echo "</br>";
  }
  elseif($sql &&$msg)
  {
   echo $msg;
   echo "</br>";
   echo $sql;
  }
  else
  {
   echo $msg;
   echo "</br>";
  }

 }

 }

include_once('adodb5/adodb.inc.php教程');

 代码如下 复制代码
$conn=adonewconnection('odbc_mssql');
$conn->pconnect("driver={sql server};server=dlut-pcsqlexpress;database=yuyan;",'zhouhao','950288');
$adodb_fetch_mode=adodb_fetch_assoc;
$sqlstr='select * from yuyan where ps教程nno=?';
$rst=$conn->execute($sqlstr,'005') or die('connect error');
while(!$rst->eof)
{
//echo $rst->fields['ps教程nname'].'->'.$rst->fields['psnno'].'';
echo 'dajiahao';
$rst->movenext();
}
$rst->close();
$conn->close();
 代码如下 复制代码
$connstr = "driver=microsoft access driver (*.mdb);dbq=".realpath("db.mdb");   
$connid = odbc_connect($connstr,"","",sql_cur_use_odbc);
  
$odbc_exec = odbc_exec($connid, "insert into tablename (name) values('name')");   
 
$query = odbc_do($connid, "select * from tablename ");  
$odbc_result_all = odbc_result_all($query);   
echo $odbc_result_all; 


//保存数据

 代码如下 复制代码

$connstr = "driver=microsoft access driver (*.mdb);dbq=".realpath("db.mdb");
$connid = odbc_connect($connstr,"","",sql_cur_use_odbc);

$odbc_exec = odbc_exec($connid, "insert into t……")

//查询数据www.111cn.net

 代码如下 复制代码

$a = 0;
$begintime = time();
while($a<100)
{
   
    $conn = odbc_connect("forphp教程","sa","sa");
    //odbc_select_db("[testyouhui]",$conn);

    $result = odbc_exec("select [id], [name] from [test1].[dbo].[category]",$conn);
    $a++;
    odbc_close($conn);
}
$endtime = time();
echo $begintime."<br>";
echo $endtime."<br>";

echo date('u秒',$endtime-$begintime);
echo '<br />';
标签：[!--infotagslink--]

您可能感兴趣的文章: