oracle资料库函式库

<?
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("<br>connect()ing to $this->database...<br>n");
}
if($this->remote) {
if($this->debug) {
printf("<br>connect() $this->user/******@$this->database<br>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("<br>connect() $this->user, $this->password <br>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("<br>connect() link_id: $this->link_id<br>n");
}
if (!$this->link_id) {
$this->halt("connect() link-id == false " .
"($this->link_id), ora_plogon failed");
} else {
//echo "commit on<p>";
ora_commiton($this->link_id);
}
if($this->debug) {
printf("<br>connect() obtained the link_id: $this->link_id<br>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<br>n", $query_string);
printf("<br>debug: query_id: %d<br>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("<br>ora_parse() failed:<br>$query_string<br><small>snap & paste this to sqlplus!</small>");
} elseif (!@ora_exec($this->query_id)) {
$this->errno=ora_errorcode($this->query_id);
$this->error=ora_error($this->query_id);
$this->halt("<br>n$query_stringn<br><small>snap & paste this to sqlplus!</small>");
}

$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("<br>next_record(): id: %d row: %d<br>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("<br>%d error: %s",
$this->errno,
$this->error);
}
$stat=0;
}
} else {
$this->ora_no_next_fetch=false;
for($ix=0;$ix<ora_numcols($this->query_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"<b>[$col]</b>: $value <br>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.<br>".
"only a seek to the last element is allowed in this version<br>".
"difference too big. wanted: $pos current pos: $this->row");
}
if ($this->debug) echo "<br>debug: seek = $pos<br>";
$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 "<br>debug: affected_rows=". ora_numrows($this->query_id)."<br>";
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 "<br>debug: num_rows: $q<br>";

ora_parse($curs,$q);
ora_exec($curs);
ora_fetch($curs);
$result = ora_getcolumn($curs,0);
ora_close($curs);
if ($this->debug)
{  
echo "<br>debug: id ".$this->queryid.
" num_rows=". $result ."<br>";
}
return $result;
}  
else  
{
$this->halt("last query was not a select: $this->lastquery");
}
}

function num_fields() {
if ($this->debug) echo "<br>debug: num_fields=". ora_numcols($this->query_id) . "<br>";
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("<br> 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("<br>ora_exec() failed:<br>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...<br>n";
}
if ( $this->query_id < 1 ) {
echo "<b>warning</b>: 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("</td></tr></table><br><b>database error:</b> %s<br>n", $msg);
printf("<b>oracle error</b>: %s (%s)<br>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<br>";
}
}  
function end_transaction()  
{
if ($this->debug)
{
print "begin transaction<br>";
}

$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<br>";
}
return $res;
}


}
?>