class db_sql {
var $debug = false;
var $home = /u01/app/oracle/product/8.0.4;
var $remote = 1;
/* this query will be sent directly after the first connection
example:
var $connectquery=alter session set nls_date_language=german nls_date_format='dd.mm.rrrr';
-> set the date format for this session, this is fine when your ora-role
cannot be altered */
var $connectquery='';
/* due to a strange error with oracle 8.0.5, apache and php3.0.6
you don't need to set the env - on my system apache
will change to a zombie, if i don't set this to false!
instead i set these env-vars before the startup of apache.
if unsure try it out, if it works. */
var $oraputenv = true;
var $database = ;
var $user = ;
var $password = ;
var $link_id = 0;
var $query_id = 0;
var $record = array();
var $row;
var $errno = 0;
var $error = ;
var $ora_no_next_fetch=false;
/* copied from db_mysql for completeness */
/* public: identification constant. never change this. */
var $type = oracle;
var $revision = revision: 1.3;
var $halt_on_error = yes; ## yes (halt with message), no (ignore errors quietly), report (ignore errror, but spit a warning)
/* public: constructor */
function db_sql($query = ) {
$this->query($query);
}
/* public: some trivial reporting */
function link_id() {
return $this->link_id;
}
function query_id() {
return $this->query_id;
}
function connect() {
## see above why we do this
if ($this->oraputenv) {
putenv(oracle_sid=$this->database);
putenv(oracle_home=$this->home);
}
if ( 0 == $this->link_id ) {
if($this->debug) {
printf(
connect()ing to $this->database...
n);
}
if($this->remote) {
if($this->debug) {
printf(
connect() $this->user/******@$this->database
n);
}  
$this->link_id=ora_plogon
($this->user/$this->password@$this->database,);
/************** (comment by ssilk)
this dosn't work on my system:
$this->link_id=ora_plogon
($this->user@$this->database.world,$this->password);
***************/
} else {
if($this->debug) {
printf(
connect() $this->user, $this->password
n);
}  
$this->link_id=ora_plogon($this->user,$this->password);
/* (comment by ssilk: don't know how this could work, but i leave this untouched!) */
}
if($this->debug) {
printf(
connect() link_id: $this->link_id
n);
}
if (!$this->link_id) {
$this->halt(connect() link-id == false .
($this->link_id), ora_plogon failed);
} else {
//echo commit on;
ora_commiton($this->link_id);
}
if($this->debug) {
printf(
connect() obtained the link_id: $this->link_id
n);
}
## execute connect query
if ($this->connectquery) {
$this->query($this->connectquery);
}
}
}
## in order to increase the # of cursors per system/user go edit the
## init.ora file and increase the max_open_cursors parameter. yours is on
## the default value, 100 per user.
## we tried to change the behaviour of query() in a way, that it tries
## to safe cursors, but on the other side be carefull with this, that you
## don't use an old result.
##  
## you can also make extensive use of ->disconnect()!
## the unused queryids will be recycled sometimes.
function query($query_string)  
{
/* no empty query please. */
if (empty($query_string))
{
return 0;
}
$this->connect();
$this->lastquery=$query_string;
if (!$this->query_id) {
$this->query_id= ora_open($this->link_id);
}
if($this->debug) {
printf(debug: query = %s
n, $query_string);
printf(
debug: query_id: %d
n, $this->query_id);
}
if(!@ora_parse($this->query_id,$query_string)) {
$this->errno=ora_errorcode($this->query_id);
$this->error=ora_error($this->query_id);
$this->halt(
ora_parse() failed:
$query_string
snap & paste this to sqlplus!);
} elseif (!@ora_exec($this->query_id)) {
$this->errno=ora_errorcode($this->query_id);
$this->error=ora_error($this->query_id);
$this->halt(
n$query_stringn
snap & paste this to sqlplus!);
}
$this->row=0;
if(!$this->query_id) {
$this->halt(invalid sql: .$query_string);
}
return $this->query_id;
}
function next_record() {
if (!$this->ora_no_next_fetch &&  
0 == ora_fetch($this->query_id)) {
if ($this->debug) {
printf(
next_record(): id: %d row: %d
n,
$this->query_id,$this->row+1);
// more info for $this->row+1 is $this->num_rows(),
// but dosn't work in all cases (complicated selects)
// and it is very slow here
}
$this->row +=1;
$errno=ora_errorcode($this->query_id);
if(1403 == $errno) { # 1043 means no more records found
$this->errno=0;
$this->error=;
$this->disconnect();
$stat=0;
} else {
$this->error=ora_error($this->query_id);
$this->errno=$errno;
if($this->debug) {
printf(
%d error: %s,
$this->errno,
$this->error);
}
$stat=0;
}
} else {
$this->ora_no_next_fetch=false;
for($ix=0;$ixquery_id);$ix++) {
$col=strtolower(ora_columnname($this->query_id,$ix));
$value=ora_getcolumn($this->query_id,$ix);
$this->record[ $col ] = $value;
$this->record[ $ix ] = $value;
#dbg echo[$col]: $value
n;
}
$stat=1;
}
return $stat;
}
## seek() works only for $pos - 1 and $pos
## perhaps i make a own implementation, but my
## opinion is, that this should be done by php3
function seek($pos) {
if ($this->row - 1 == $pos) {
$this->ora_no_next_fetch=true;
} elseif ($this->row == $pos ) {
## do nothing
} else {
$this->halt(invalid seek(): position is cannot be handled by api.
.
only a seek to the last element is allowed in this version
.
difference too big. wanted: $pos current pos: $this->row);
}
if ($this->debug) echo
debug: seek = $pos
;
$this->row=$pos;
}
function lock($table, $mode = write) {
if ($mode == write) {
$result = ora_do($this->link_id, lock table $table in row exclusive mode);
} else {
$result = 1;
}
return $result;
}
function unlock() {
return ora_do($this->link_id, commit);
}
// important note: this function dosn't work with oracle-database-links!
// you are free to get a better method. :)
function metadata($table,$full=false) {
$count = 0;
$id = 0;
$res = array();
/*
* due to compatibility problems with table we changed the behavior
* of metadata();
* depending on $full, metadata returns the following values:
*
* - full is false (default):
* $result[]:
* [0][table] table name
* [0][name] field name
* [0][type] field type
* [0][len] field length
* [0][flags] field flags (not null, index)
* [0][format] precision and scale of number (eg. 10,2) or empty
* [0][index] name of index (if has one)
* [0][chars] number of chars (if any char-type)
*
* - full is true
* $result[]:
* [num_fields] number of metadata records
* [0][table] table name
* [0][name] field name
* [0][type] field type
* [0][len] field length
* [0][flags] field flags (not null, index)
* [0][format] precision and scale of number (eg. 10,2) or empty
* [0][index] name of index (if has one)
* [0][chars] number of chars (if any char-type)
* [0][php_type] the correspondig php-type
* [0][php_subtype] the subtype of php-type
* [meta][field name] index of field named field name
* this could used, if you have the name, but no index-num - very fast
* test: if (isset($result['meta']['myfield'])) {} ...
*/
$this->connect();
## this is a right outer join: (+), if you want to see, what
## this query results try the following:
## $table = new table; $db = new my_db_sql; # you have to make
## # your own class
## $table->show_results($db->query(see query vvvvvv))
##
$this->query(select t.table_name,t.column_name,t.data_type,.
t.data_length,t.data_precision,t.data_scale,t.nullable,.
t.char_col_decl_length,i.index_name.
from all_tab_columns t,all_ind_columns i.
where t.column_name=i.column_name (+).
and t.table_name=i.table_name (+).
and t.table_name=upper('$table') order by t.column_id);
$i=0;
while ($this->next_record()) {
$res[$i][table] = $this->record[table_name];
$res[$i][name] = strtolower($this->record[column_name]);
$res[$i][type] = $this->record[data_type];
$res[$i][len] = $this->record[data_length];
if ($this->record[index_name]) $res[$i][flags] = index ;
$res[$i][flags] .= ( $this->record[nullable] == 'n') ? '' : 'not null';
$res[$i][format]= (int)$this->record[data_precision].,.
(int)$this->record[data_scale];
if (0,0==$res[$i][format]) $res[$i][format]='';
$res[$i][index] = $this->record[index_name];
$res[$i][chars] = $this->record[char_col_decl_length];
if ($full) {
$j=$res[$i][name];
$res[meta][$j] = $i;
$res[meta][strtoupper($j)] = $i;
switch ($res[$i][type]) {
case varchar2 :
case varchar :
case char :
$res[php_type]=string;
$res[php_subtype]=;
break;
case date :
$res[php_type]=string;
$res[php_subtype]=date;
break;
case blob :
case clob :
case bfile :
case raw :
case long :
case long raw :
$res[php_type]=string;
$res[php_subtype]=blob;
break;
case number :
if ($res[$i][format]) {
$res[php_type]=double;
$res[php_subtype]=;
} else {
$res[php_type]=int;
$res[php_subtype]=;
}
break;
default :
$this->halt(metadata(): type is not a valid value: '$res[$i][type]');
break;
}
}
if ($full) $res[meta][$res[$i][name]] = $i;
$i++;
}
if ($full) $res[num_fields]=$i;
# $this->disconnect();
return $res;
}
## this function is unstested!
function affected_rows() {
if ($this->debug) echo
debug: affected_rows=. ora_numrows($this->query_id).
;
return ora_numrows($this->query_id);
}
## known bugs: it will not work for select distinct and any
## other constructs which are depending on the resulting rows.
## so you *really need* to check every query you make, if it
## will work with it!
##
## also, for a qualified replacement you need to parse the
## selection, cause this will fail: select id, from from ...).
## from is - as far as i know a keyword in oracle, so it can
## only be used in this way. but you have been warned.
function num_rows() {
$curs=ora_open($this->link_id);
## this is the important part and it is also the hack!
if (eregi(^[[:space:]]*select[[:space:]],$this->lastquery) )  
{
# this works for all cases, including select distinct case.
# we just make select count(*) from original sql expression
# and remove order by (if any) for speed
# i like regular expressions too ;-)))  
$q = sprintf(select count(*) from (%s),
@eregi_replace(order[[:space:]]+by[^)]*()*), \1,  
$this->lastquery)  
);
# works also for subselects:
# if (eregi([[:space:]]+from([[:space:]]+.*[[:space:]]+from),$this->lastquery,$r))
# $areplace=$r[1];
# $q=eregi_replace(^[[:space:]]*select[[:space:]]+.
# .*[[:space:]]+from,
# select count(*) from$areplace,
# $this->lastquery);
if ($this->debug) echo
debug: num_rows: $q
;
ora_parse($curs,$q);
ora_exec($curs);
ora_fetch($curs);
$result = ora_getcolumn($curs,0);
ora_close($curs);
if ($this->debug)
{  
echo
debug: id .$this->queryid.
num_rows=. $result .
;
}
return $result;
}  
else  
{
$this->halt(last query was not a select: $this->lastquery);
}
}
function num_fields() {
if ($this->debug) echo
debug: num_fields=. ora_numcols($this->query_id) .
;
return ora_numcols($this->query_id);
}
function nf() {
return $this->num_rows();
}
function np() {
print $this->num_rows();
}
function f($name) {
return $this->record[$name];
}
function p($name) {
print $this->record[$name];
}
/* public: sequence number */
function nextid($seq_name)
{
$this->connect();
/* independent query_id */
$query_id = ora_open($this->link_id);
if(!@ora_parse($query_id,select $seq_name.nextval from dual))  
{
// there is no such sequence yet, then create it
if(!@ora_parse($query_id,create sequence $seq_name)  
||
!@ora_exec($query_id)
)
{
$this->halt(
nextid() function - unable to create sequence);
return 0;
}
@ora_parse($query_id,select $seq_name.nextval from dual);
}  
if (!@ora_exec($query_id)) {
$this->halt(
ora_exec() failed:
nextid function);
}
if (@ora_fetch($query_id) ) {
$next_id = ora_getcolumn($query_id, 0);
}
else {
$next_id = 0;
}
if ( $query_id > 0 ) {
ora_close($query_id);
}
return $next_id;
}
function disconnect() {
if($this->debug) {
echo debug: disconnecting $this->query_id...
n;
}
if ( $this->query_id echo warning: disconnect(): cannot free id $this->query_idn;
# return();
}
ora_close($this->query_id);
$this->query_id=0;
}
/* private: error handling */
function halt($msg) {
if ($this->halt_on_error == no)
return;
$this->haltmsg($msg);
if ($this->halt_on_error != report)
die(session halted.);
}
function haltmsg($msg) {
printf(
database error: %s
n, $msg);
printf(oracle error: %s (%s)
n,
$this->errno,
$this->error);
}
function table_names() {
$this->connect();
$this->query(
select table_name,tablespace_name
from user_tables);
$i=0;
while ($this->next_record())
{
$info[$i][table_name] =$this->record[table_name];
$info[$i][tablespace_name]=$this->record[tablespace_name];
$i++;
}  
return $info;
}
// some transaction support
// methods are used in ct_oracle.inc
function begin_transaction()  
{
$this->connect();
// now, disable autocommit
ora_commitoff($this->link_id);
if ($this->debug)
{
print begin transaction
;
}
}  
function end_transaction()  
{
if ($this->debug)
{
print begin transaction
;
}
$res = 1;
if(!@ora_commit($this->link_id))
{
ora_commiton($this->link_id);
$this->halt(unable to finish transaction);
$res = 0;
}
// enable autocommit again
ora_commiton($this->link_id);
if ($this->debug)
{
print end transaction : $res
;
}
return $res;
}
}
?>  以上就介绍了php的oracle资料库函式库，包括了方面的内容，希望对php教程有兴趣的朋友有所帮助。