php程序中调用mysql存储过程实例

CREATE PROCEDURE和CREATE FUNCTION

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

    [characteristic ...] routine_body

    proc_parameter:

    [ IN | OUT | INOUT ] param_name type

    func_parameter:

    param_name type

type:

    Any valid MySQL data type

characteristic:

    LANGUAGE SQL

  | [NOT] DETERMINISTIC

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

routine_body:

    Valid SQL procedure statement or statements

Create procedure sp_Name ([proc_parameter ])
            routine_body

 Create functionsp_Name ([func_parameter ])
           Returns type
    Routine_body

mysql> delimiter //
mysql> create procedure g
    -> begin
    -> select version() i
    -> end
    -> //
Query OK, 0 rows affected

mysql> call getversion(@a
    -> //
Query OK, 0 rows affected

mysql> select @a;
    -> //
+---------------------+
| @a                  |
+---------------------+
| 5.0.45-community-nt |
+---------------------+
1 row in set (0.05 sec)

Drop table if exists user; 
    Create table user( 
        Id int unsigned not null auto_increment, 
        Name varchar(20) not null, 
        Pwd char(32) not null, 
        Primary key(Id) 
    ); 

Delimiter // 
    Create procedure insertuser(in username varchar(20),in userpwd varchar(32)) 
    Begin 
        Insert into welefen.user(Name,Pwd) values (username,md5(userpwd)); 
    End 
    // 

Delimiter // 
    Create procedure validateuser(in username varchar(20),out param1) 
    Begin  
        Select Pwd into param1 from welefen.user where Name=username; 
    End 
    // 

Delimiter // 
    Create procedure modifyPwd(in username varchar(20),in userpwd varchar(32)) 
    Begin 
        Update welefen.user set Pwd=md5(userpwd) where Name=username; 
    End 
    // 

<?php 
     
     if (!mysql_connect("localhost","root","welefen")){  
         echo "连接数据库失败"; 
    } 
     if (!mysql_select_db("welefen")){ 
         echo "选择数据库表失败<br>"; 
    } 
     
    $insert_user=array("welefen","welefen");//这里的welefen分别为用户名、密码 
     if (mysql_query("call insertuser('$insert_user[0]','$insert_user[1]')")){ 
         echo "添加用户$insert_user[0]成功<br>"; 
    }else { 
         echo "添加用户$insert_user[0]失败<br>"; 
    } 
     
    $validate_user=array("welefen","welefen");//这里的welefen分别为用户名、密码 
    mysql_query("call validateuser('$validate_user[0]',@a)"); 
    $Pwd=mysql_query("select @a"); 
    $result=mysql_fetch_array($Pwd); 
     if ($result[0]==md5($validate_user[1])){ 
         echo "用户$validate_user[0]验证正确<br>"; 
    }else { 
         echo "用户$validate_user[0]验证错误<br>"; 
    } 
     
    $modify_Pwd=array("welefen","weilefeng"); //welefen为用户名weilefeng为新密码 
     if (mysql_query("call modifyPwd('$modify_Pwd[0]','$modify_Pwd[1]')")){ 
         echo "用户$modigy_Pwd[0]的密码修改成功<br>"; 
    }else { 
         echo "用户$modigy_Pwd[0]的密码修改失败<br>"; 
    } 
     
    $delete_user=array("welefen");           //welefen为用户名 
     if (mysql_query("call deleteuser('$delete_user[0]')")){ 
         echo "用户$delete_user[0]删除成功<br>"; 
    }else { 
         echo "用户$delete_user[0]删除失败<br>"; 
    } 
    ?>  

<?php
 
/**
 * 主数据库 写 从数据库（可多个）读
 * 实现主从数据库 读写分离 主服务器无法连接 从服务器可切换写功能
 * 从务器无法连接 主服务器可切换读功
 * by lmt
 * */
class DbConnectionMan extends CDbConnection {
 
    public $timeout = 10; //连接超时时间
    public $markDeadSeconds = 600; //如果从数据库连接失败 600秒内不再连接
    //用 cache 作为缓存全局标记
    public $cacheID = 'cache';
 
    /**
     * @var array $slaves.Slave database connection(Read) config array.
     * 配置符合 CDbConnection.
     * @example
     * 'components'=>array(
     *   'db'=>array(
     *    'connectionString'=>'mysql://<master>',
     *    'slaves'=>array(
     *     array('connectionString'=>'mysql://<slave01>'),
     *     array('connectionString'=>'mysql://<slave02>'),
     *    )
     *   )
     * )
     * */
    public $slaves = array();
 
    /**
     *
     * 从数据库状态 false 则只用主数据库
     * @var bool $enableSlave
     * */
    public $enableSlave = true;
 
    /**
     * @var slavesWrite 紧急情况主数据库无法连接 切换从服务器（读写）.
     */
    public $slavesWrite = false;
 
    /**
     * @var masterRead 紧急情况从主数据库无法连接 切换从住服务器（读写）.
     */
    public $masterRead = false;
 
    /**
     * @var _slave
     */
    private $_slave;
 
    /**
     * @var _disableWrite 从服务器（只读）.
     */
    private $_disableWrite = true;
 
    /**
     *
     * 重写 createCommand 方法,1.开启从库 2.存在从库 3.当前不处于一个事务中 4.从库读数据
     * @param string $sql
     * @return CDbCommand
     * */
    public function createCommand($sql = null) {
        if ($this->enableSlave && !empty($this->slaves) && is_string($sql) && !$this->getCurrentTransaction() && self::isReadOperation($sql) && ($slave = $this->getSlave())
        ) {
            return $slave->createCommand($sql);
        } else {
            if (!$this->masterRead) {
                if ($this->_disableWrite && !self::isReadOperation($sql)) {
 
                    throw new CDbException("Master db server is not available now!Disallow write operation on slave server!");
                }
            }
            return parent::createCommand($sql);
        }
    }
 
    /**
     * 获得从服务器连接资源
     * @return CDbConnection
     * */
    public function getSlave() {
 
        if (!isset($this->_slave)) {
 
            shuffle($this->slaves);
            foreach ($this->slaves as $slaveConfig) {
 
                if ($this->_isDeadServer($slaveConfig['connectionString'])) {
                    continue;
                }
                if (!isset($slaveConfig['class']))
                    $slaveConfig['class'] = 'CDbConnection';
 
                $slaveConfig['autoConnect'] = false;
                try {
                    if ($slave = Yii::createComponent($slaveConfig)) {
                        Yii::app()->setComponent('dbslave', $slave);
                        $slave->setAttribute(PDO::ATTR_TIMEOUT, $this->timeout);
                        $slave->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                        $slave->setActive(true);
                        $this->_slave = $slave;
                        break;
                    }
                } catch (Exception $e) {
                    $this->_markDeadServer($slaveConfig['connectionString']);
                    Yii::log("Slave database connection failed!ntConnection string:{$slaveConfig['connectionString']}", 'warning');
 
                    continue;
                }
            }
 
            if (!isset($this->_slave)) {
                $this->_slave = null;
                $this->enableSlave = false;
            }
        }
        return $this->_slave;
    }
 
    public function setActive($value) {
        if ($value != $this->getActive()) {
            if ($value) {
                try {
                    if ($this->_isDeadServer($this->connectionString)) {
                        throw new CDbException('Master db server is already dead!');
                    }
                    //PDO::ATTR_TIMEOUT must set before pdo instance create
                    $this->setAttribute(PDO::ATTR_TIMEOUT, $this->timeout);
                    $this->open();
                } catch (Exception $e) {
                    $this->_markDeadServer($this->connectionString);
                    $slave = $this->getSlave();
                    Yii::log($e->getMessage(), CLogger::LEVEL_ERROR, 'exception.CDbException');
                    if ($slave) {
                        $this->connectionString = $slave->connectionString;
                        $this->username = $slave->username;
                        $this->password = $slave->password;
                        if ($this->slavesWrite) {
                            $this->_disableWrite = false;
                        }
                        $this->open();
                    } else { //Slave also unavailable
                        if ($this->masterRead) {
                            $this->connectionString = $this->connectionString;
                            $this->username = $this->username;
                            $this->password = $this->password;
                            $this->open();
                        } else {
                            throw new CDbException(Yii::t('yii', 'CDbConnection failed to open the DB connection.'), (int) $e->getCode(), $e->errorInfo);
                        }
                    }
                }
            } else {
                $this->close();
            }
        }
    }
 
    /**
     * 检测读操作 sql 语句
     *
     * 关键字： SELECT,DECRIBE,SHOW ...
     * 写操作:UPDATE,INSERT,DELETE ...
     * */
    public static function isReadOperation($sql) {
        $sql = substr(ltrim($sql), 0, 10);
        $sql = str_ireplace(array('SELECT', 'SHOW', 'DESCRIBE', 'PRAGMA'), '^O^', $sql); //^O^,magic smile
        return strpos($sql, '^O^') === 0;
    }
 
    /**
     * 检测从服务器是否被标记 失败.
     */
    private function _isDeadServer($c) {
        $cache = Yii::app()->{$this->cacheID};
        if ($cache && $cache->get('DeadServer::' . $c) == 1) {
            return true;
        }
        return false;
    }
 
    /**
     * 标记失败的slaves.
     */
    private function _markDeadServer($c) {
        $cache = Yii::app()->{$this->cacheID};
        if ($cache) {
            $cache->set('DeadServer::' . $c, 1, $this->markDeadSeconds);
        }
    }
 
}

'db'=>array(
        'class'=>'application.extensions.DbConnectionMan',//扩展路径
        'connectionString' => 'mysql:host=192.168.1.128;dbname=db_xcpt',//主数据库 写
        'emulatePrepare' => true,
        'username' => 'root',
        'password' => 'root',
        'charset' => 'utf8',
        'tablePrefix' => 'xcpt_', //表前缀
        'enableSlave'=>true,//从数据库启用
   'urgencyWrite'=>true,//紧急情况 主数据库无法连接 启用从数据库 写功能
    'masterRead'=>true,//紧急情况 从数据库无法连接 启用主数据库 读功能
        'slaves'=>array(//从数据库
            array(   //slave1
                'connectionString'=>'mysql:host=localhost;dbname=db_xcpt',
                'emulatePrepare' => true,
                'username'=>'root',
                'password'=>'root',
                'charset' => 'utf8',
                'tablePrefix' => 'xcpt_', //表前缀
            ),
   array(   //slave2
                'connectionString'=>'mysql:host=localhost;dbname=db_xcpt',
                'emulatePrepare' => true,
                'username'=>'root',
                'password'=>'root',
                'charset' => 'utf8',
                'tablePrefix' => 'xcpt_', //表前缀
            ),
 
        ),
    ),

"") header("Location:listtable.php");?>
 

$con=mysql_connect('localhost','root','xswlily');
$query="select * from $table ";
//数据库查询
$result=mysql_db_query("embed",$query,$con);
$filestr="<"."?xml version="1.0" encoding="GB2312"?".">";
$filestr.="<".$table."s>";
while ($row=mysql_fetch_array($result))
//列出所有的记录
{$filestr.="<".$table.">";
$fields=mysql_list_fields("embed",$table,$con);
$j=0;
//$num_fields=mysql_field_name($fields,$j);
//echo $num_fields;
while ($j$num_fields=mysql_field_name($fields,$j);
$filestr.="<".$num_fields.">";
$filestr.=$row[$j];
$filestr.="";
$j++;}
$filestr.="";
}
$filestr.="";
echo $filestr;
//以下是文件操作代码
$filename=$table.".xml";
$fp=fopen("$filename","w");
fwrite($fp,$filestr);
fclose($fp);
Echo "数据表".$table."已经备份成功！";?>

<?php
header ( 'http-equiv="Content-Type" content="text/html; charset=utf-8"' );
$DB_host = "localhost"; //数据库主机
$DB_user = "root"; //数据库用户
$DB_psw = "root3306"; //数据库密码
$DB_datebase = "gk_yue39_com"; //数据库名
$DB_charset = "utf8"; //数据库字符集
$dbprefix="yue392_com_";
$new_dbprefix="yue39_com_";
$db = new mysqli ( $DB_host, $DB_user, $DB_psw ); //实例化对象

//检查连接
if (mysqli_connect_errno ()) {
 printf ( "Connect failed: %sn", mysqli_connect_error () );
 exit ();
}

$db->select_db ( $DB_datebase ); //选择操作数据库

$db->set_charset ( $DB_charset ); //设置数据库字符集

//执行一个查询
$sql = 'show tables';
$result = $db->query ( $sql );

echo $result->num_rows . ' 行结果  ' . $result->field_count . ' 列内容<br/>';

//$result->data_seek('5');//从结果集中第5条开始取结果

echo '<table border="1" cellspacing="0" cellpadding="0" align="center" width="90%">';

//循环输出字段名
//$result->field_seek(2);//从字段集中第2条开始取结果
while ( true == ($field = $result->fetch_field ()) ) {
 echo '<th>' . $result->current_field . '_' . $field->name . '(' . $field->length . ')</th>';
}

//循环输出查询结果
while ( true == ($row = $result->fetch_assoc ()) ) {
 echo '<tr>';
 foreach ( $row as $col ) {
$sql="rename table `".$col."` to `".str_replace ( $dbprefix, $new_dbprefix, $col)."`";
    if($db->query ( $sql )){
  echo '<td align="center">' . $sql. '</td><td><font color="blue"> success</font></td>';
  }else{
  echo '<td align="center">' . $sql. '</td><td><font color="red"> failed</font></td>';
  } 
  }
 echo '</tr>';
}

echo '</table>';
$result->free ();//释放结果集
$db->close (); //关闭连接
?>

function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
 
$time_start = microtime_float();
for ( $i=0; $i&lt;30000; $i++){ 
$dbh=new mysqli("XXX.XXX.XXX.XXX", "wubx", "wubxwubx", "userdb", 3308);
$dbh-&gt;close();
}
$time_end= microtime_float();
$time_use= ($time_end - $time_start)/30000;
print "$time_usen";
#php tconn.php
0.00090954260031382

net.ipv4.tcp_max_syn_backlog = 819200
net.core.netdev_max_backlog = 400000
net.core.somaxconn = 4096
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=0
#sysctl -p