PHP调用Oracle,mysql,mssql server 储存过程方法

下面总结了三种流行的数据库教程如何利用php教程 来调用它们的存储过程，我们讲有mysql教程,oracle,mssql server哦。

<?php
function check_login($user, $pass) {
         $conn = ocilogon('user', 'pass', 'database');
         $sql = 'begin :result := test.check_login(:user, :pass); end;';
         $stmt = oci_parse($conn, $sql);
        
         $result = '';
         oci_bind_by_name($stmt, ':user', $user, 32);
         oci_bind_by_name($stmt, ':pass', md5($pass), 32);
         oci_bind_by_name($stmt, ':result', $result, 10);
         oci_execute($stmt);
         ocilogoff($conn);
        
         return $result;
}
?>

调用mysql

存储过程改成：
create procedure in_out(in uid int) begin
set @msg='hello';
select *,@msg from manage_loginhistory where h_uid=uid;
end;
php调用改成：
$sql = "call in_out(39)";
$rs=mysql_query($sql);
$row=mysql_fetch_array($rs);

调用ms sql server

$user_name = '龙之泪';      //声明一个变量，用做存储过程的输入参数
   $password = '123456';       //再声明一个变量，用做存储过程的另一个输入参数
   $info = '';                            //$info，用来接受从存储过程输出的参数值
   $host="192.168.0.1";         //定义数据库服务器
   $user="sa";                        //连接用户名
   $password="123456";        //连接密码
   $db="sample";                    //数据库名称
   $dblink=mssql_connect($host,$user,$password) or die("can't connect to mssql"); //连接数据库服务器
   mssql_select_db($db,$dblink)   or die("can't select sample");//选择数据库

   $sp = mssql_init("test");  //初始化一个存储过程

   //为存储过程添加一个参数，@user_name为参数名，$user_name为参数对应的php变量，sqlvarchar表明该参数类型为sql server的varchar类型，第一个false表示该参数不是输出参数，即该参数是输入参数，第二个false表示该参数不允许为null，最后的30表示该变量的长度为30
   mssql_bind($sp,"@user_name",$user_name,sqlvarchar,false,false,30);
   mssql_bind($sp,"@password",$password,sqlvarchar,false,false,30);
   mssql_bind($sp,"@info",$info,sqlvarchar,true,false,30); //为存储过程添加一个输出参数
   mssql_execute($sp); //执行该存储过程

   echo $info; //打印出从存储过程中返回的输出参数值

测验考试libmysql教程拷入 windows/system32下和libmysql.dll拷贝到apache/bin下。
　　当然，最省事的方法是：
　　在apache中：
　　loadfile d:/php教程/php5.2.13/libmysql.dll
　　只上边这一行，mysql便可成功挪用。
　　我的服务器版本：win7
　　apache 2.2.13
　　php 5.2.13
　　mysql5.1.37
　　phpmyadmin3.3.2
　　下面是我的apache的部门配备布置。
　　phpinidir d:/php/php5.2.13 #不是必须
　　loadfile d:/php/php5.2.13/php5ts.dll
　　loadfile d:/php/php5.2.13/libmysql.dll
　　loadmodule php5_module d:/php/php5.2.13/php5apache2_2.dll
　　addtype application/x-httpd-php .php

php教程连接mssql 2005 1.下载以下两个文件,放入php ext目录及system32

php_sqlsrv_52_ts_vc6.dll   (线程安全)

php_sqlsrv_52_nts_vc6.dll (非线程安全)

vc6用于apache,vc9用于iis

2.修改php.ini

extension=php_sqlsrv_52_ts_vc6.dll

3.下载sqlncli.msi，微软官方可以下

安装的时候提示是sql server 2008的，不过2005也是可以用的。

4.测试代码

<?php

$servername = "127.0.0.1";
$connectioninfo = array("database"=>"testdb","uid"=>"test","pwd"=>"test");
$conn = sqlsrv_connect($servername, $connectioninfo);

if($conn) {
    echo "connection established.<br>";
} else {
    echo "connection could not be established.<br>";
    die(print_r(sqlsrv_errors(), true));
    exit();
}

$sql = "select * from t_employee";
$result = sqlsrv_query($conn,$sql);
$data = array();
while($row=sqlsrv_fetch_array($result)) {
    $data[] = $row;
}

foreach($data as $p) {
    echo $p['name']."<br>";
}

sqlsrv_close($conn);

echo "<br> done <br>";
echo date("y-m-d h:i:s");

?>

mysql教程连接类

class db_handle {
    var $classname = "db_handle";
    var $server;
    var $username;
    var $password;
    var $database;
    var $linkid = 0;
    var $queryresult = "";
    var $lastinsertid = "";
    /* private ignore=>ignore the error and continue, halt=>report the error and halt, report=>report the error and continue */
    var $halt_on_error = "report";
    var $error = "";
    var $errno = 0;
    /**public
     * remark: this is the db_mysql_class's structure
     * function: set the server,username,password,database variable.
     */
    function db_handle($server = "", $username = "", $password = "", $database = "") {
        $this->server = $server;
        $this->username = $username;
        $this->password = $password;
        $this->database = $database;
    }
    /**public
     * function: connect database and select database
     * success: retun 1
     * failed: return 0
     */
    function connect() {
        $this->linkid = @mssql_pconnect ( $this->server, $this->username, $this->password );
        if (! $this->linkid) {
            $this->halt ( "mssql_pconnect($this->server,$this->username,$this->password): failed" );
            return 0;
        }
        if (! @mssql_select_db ( $this->database )) {
            $this->halt ( "mssql_select_db($this->database) failed." );
            return 0;
        }
        return 1;
    }
    /**public
     * function: check the database, if exist then select
     * exist: return 1
     * not exist: return 0
     */
    function selectdatabase() {
        if (@mssql_select_db ( $this->database ))
            return 1;
        else
            return 0;
    }
    /**public
     * function: execute sql instruction
     * success: return sql result.
     * failed: return 0;
     */
    function execquery($sql = "") {
        $this->connect();
        if ($this->linkid == 0) {
            $this->halt ( "execute sql failed: have not valid database connect." );
            return 0;
        }
        ob_start ();
        $this->queryresult = mssql_query ( $sql, $this->linkid );
        $error = ob_get_contents ();
        ob_end_clean ();
        if ($error) {
            $this->halt ( "execute sql: mssql_query($sql,$this->linkid) failed." );
            return 0;
        }
        $reg = "#insert into#";
        if (preg_match ( $reg, $sql )) {
            $sql = "select @@identity as id";
            $res = mssql_query ( $sql, $this->linkid );
            $this->lastinsertid = mssql_result ( $res, 0, id );
        }
        return $this->queryresult;
    }
   
    /**public
     * function: get the query result's row number
     * success: return the row fo the result
     * failed: return 0
     */
    function gettotalrownum($result = "") {
        if ($result != "")
            $this->queryresult = $result;
        $row = @mssql_num_rows ( $this->queryresult );
        if ($row >= 0)
            return $row;
        $this->halt ( "get a row of result failed: result $result is invalid." );
        return 0;
    }
   
    /**public
     * function: get the last insert record's id
     * success: return id
     * failed: return 0
     */
    function lastinsertid() {
        return $this->lastinsertid;
    }
   
    /**public
     * function: get a field's value
     * success: return value of the field
     * failed: return 0
     */
    function getfield($result = "", $row = 0, $field = 0) {
        if ($result != "")
            $this->queryresult = $result;
        $fieldvalue = @mssql_result ( $this->queryresult, $row, $field );
        if ($fieldvalue != "")
            return $fieldvalue;
        $this->halt ( "get field: mssql_result($this->queryresult,$row,$field) failed." );
        return 0;
       
    //here should have error handle
    }
   
    /**public
     * function: get next record
     * success: return a array of the record's value
     * failed: return 0
     */
    function nextrecord($result = "") {
        if ($result != "")
            $this->queryresult = $result;
        $record = @mssql_fetch_array ( $this->queryresult );
        if (is_array ( $record ))
            return $record;
            //$this->halt("get the next record failed: the result $result is invalid.");
        return 0;
    }
   
    /**public
     * function: free the query result
     * success return 1
     * failed: return 0
     */
    function freeresult($result = "") {
        if ($result != "")
            $this->queryresult = $result;
        return @mssql_free_result ( $this->queryresult );
    }
   
    /**public
     * function: set the halt_on_error's state
     * success: return 1
     * failed: return 0
     */
    function sethaltonerror($state = "ignore") {
        if (! ($state == "ignore" || $state == "report" || $state == "halt")) {
            $this->halt ( "set the halt_on_error fail: there is no state value $state" );
            return 0;
        }
        $this->halt_on_error = $state;
        return 1;
    }
   
    /**public
     * function: get the halt_on_error's state
     */
    function gethaltonerror() {
        return $this->halt_on_error;
    }
   
    /**public
     * function: get the class's name
     */
    function tostring() {
        return $this->classname;
    }
   
    /**private
     * function: error handle
     */
    function halt($msg) {
        $this->error = @mysql_error ( $this->linkid );
        $this->errno = @mysql_errno ( $this->linkid );
        if ($this->halt_on_error == "ignore")
            return;
        $this->makemsg ( $msg );
        if ($this->halt_on_error == "halt")
            die ( "session halted" );
    }
   
    /**private
     * function: make error information and print
     */
    function makemsg($msg) {
        printf ( "database error: %sn", $msg );
        printf ( "mysql error: %s (%s)n", $this->errno, $this->error );
    }
}

在 windows 下安装 php教程 时，　为了避免配置文件混乱，将安装目录下的 php.ini 复制到 windows 目录下之后，应该将原来安装目录下的 php.ini 文件重命名或删除，以免造成在不同环境下执行时，查找配置文件不一致的情况发生

fatal error: undefined function mysql_connect()
　　环境: windows 2003, php 5.2.0, mysql 5.0, apache 2.0
　　在 php.ini 中, 已经将 php_mysql.dll 的模块选项给打开了；测试的脚本也很简单，就一个 mysql_conect 函数，内容如下:
复制代码 代码如下:

php.ini:
extension=php_mysql.dll
测试脚本 test.php 内容如下：
text.php
<?
if ( !mysql_connect(dbhost, dbuser,dbpwd) )
{
echo "连接失败!";
exit;
}
echo "连接成功!t";
?>
用 web 方式调用 http://localhost/test.php, 执行正常, 显示"连接成功".
　　但用 dos command 命令行的方式调用 d:/php/php.exe test.php 却显示连接失败, 错误信息为: fatal error: undefined function mysql_connect()
　　很显然, 在 dos command 命令行的环境下，mysql 的模块没有被调用。方法弄尽, 仍无法解决. 百思不得其解. 后来, 用写了个脚本, 看看 php 的配置, 在两个环境下有什么不同:
test.php
复制代码 代码如下:

<?
echo phpinfo();
?>

仔细查看两个环境下 phpinfo() 输入的 php 配置信息，终于发现了问题所在:
web 方式 http://localhost/test.php 方式调用时, 其 configuration file (php.ini) path 显示为 c:windowsphp.ini。
dos command 命令行方式 d:/php/php.exe test.php 调用时, 其 configuration file (php.ini) path 为 d:phpphp.ini。
　　而在 c:windows 和 d:php 目录下，　都存在一个 php.ini 文件，d:php 目录下的 php.ini 没有将 extension=php_mysql.dll 模块打开．于是将 d:phpphp.ini 删除，只保留 c:windowsphp.ini，问题解决．

操作时点击删除连接时就会会获取到当前条目的id然后程序获取到当前id进行删除操作。

<?php教程
       include("conn.php");
      
       $query = "select * from new order by id desc";
       $res = mysql教程_query($query);
       while($rows =mysql_fetch_array($res)){       //将查询到结果拆到$rows数组中
?>
<table>
       <tr>
              <td>标题:<?php echo$rows['title']?></td><td><a href="del.php?<?php $tid = $rows['id'] ?>">[删除]</a></td><td><a href="">[修改]</a></td>
       </tr>
<table>
       <?php
              }
       ?>
<form  action="addnews.php" method="post">
       作者:<input type="text" name="author"/><br/><br/>
       标题:<input type="text" name="title" size="20"><br/><br/>
       内容:<textarea name="content" cols="20" rows="20"></textarea>
       <input type="submit" value="提交" name="submit"/>
</form>

del.php代码

<?php
       $del = "delect from test where id in ($tid)";
       $res  = mysql_query($del);
       if($res){
              echo "删除成功";
       }else{
              echo "删除失败";
       }
?>