/ 中存储网

MySQL存储过程及调用方法

2014-07-13 15:54:08 来源:中存储网

        写存储过程方法很多:

下面我写的是比较传统的命令下操作:

进入mysql bin目录

输入:delimiter //

           create procedure two()

begin

select * from lw_test order by id desc;

end

//

创建成功

查看全部存储过程是:

方法一:

       select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'

方法二:

         show procedure status;

查看具体存储过程的内容是:

show create procedure proc_name;
show create function func_name;

删除存储过程是:

DROP PROCEDURE IF EXISTS proc_name
drop procedure proc_name

修改存储过程是

alter procedure proc_name

注意:不过修改存储过程只能修改名称和定义,不能修改具体内容所有,还是删除在创建。我也就没记他了。

调用:

 define('CLIENT_MULTI_RESULTS', 131072);
    $link = mysql_connect("127.0.0.1", "root", "root",1,CLIENT_MULTI_RESULTS) or die("Could not connect: ".mysql_error());
    mysql_select_db("mysqlrock") or die("Could not select database");
    $result = mysql_query("call two()") or die("Query failed:" .mysql_error());
//加参数MYSQL_ASSOC为关联数组,不加为数组数组。而print_r只能读取数字数组
    $row = mysql_fetch_array($result, MYSQL_ASSOC);
    // print_r($row);  
     while($row = mysql_fetch_array($result, MYSQL_ASSOC))
        {
$line = $row["id"].'qqqqqqqqq'.$row["name"];
echo $line.'<br/>';
        }

    mysql_free_result($result);
    mysql_close($link);

注意:CLIENT_MULTI_RESULTS此参数只名是读取存储过程及语句,在MYSQL,官方翻译知道的的

下面是引用别人的文章:绝对的好东西,提升

建立存储过程

   Create procedure、Create function

   下面是它们的格式:
   Create proceduresp_Name ([proc_parameter ])
        routine_body

   这里的参数类型可以是 IN OUT INOUTT      ,意思和单词的意思是一样的,IN  表示是传进来的参数,
OUT  是表示传出去的参数,INOUT  是表示传进来但最终传回的参数。
   Create functionsp_Name ([func_parameter ])
      Returns type

Routine_body
    Returns type 指定了返回的类型,这里给定的类型与返回值的类型要是一样的,否则会报错。

下面给出两个简单的例子来说明:

    1、 显示  Mysql      当前版本

    执行结果  
    mysql> use welefen;  
    Database changed  
    mysql> delimiter //         #定义//作为结束标记符号  
    mysql> create procedure getversion(out param1 varchar(50)) #param1为传出参数  
        -> begin  
        -> select version() into param1;  #将版本的信息赋值给 param1  
        -> end  
        -> //  
    Query OK, 0 rows affected (0.00 sec)  
    mysql> call getversion(@a);           #调用getversion()这个存储过程  
        -> //  
    Query OK, 0 rows affected (0.00 sec)  
    mysql> select @a;  
        -> //  
    +--------------------------+  
    | @a                         |  
    +--------------------------+  
    | 5.1.14-beta-community-nt |  
    +--------------------------+  
    1 row in set (0.00 sec)  

    2、 显示”hello world”

    执行结果  
    mysql> delimiter //  
    mysql> create function display(w varchar(20)) returns varchar(50)  
        -> begin  
        -> return concat('hello ‘,w);         -> end  
        -> //  
    Query OK, 0 rows affected (0.05 sec)  
    mysql> select display("world");  
        -> //  
    +------------------+  
    | display("world") |  
    +------------------+  
    | hello world      |  
    +------------------+  
    1 row in set (0.02 sec)  

其他操作存储过程的语句

   前面我们已经知道了怎么创建存储过程,下面看看其他常用的用于操作存储过程的语句。

   Alter {procedure | function} sp_Name []

   Alter 语法是用来改变一个过程或函数的特征,当你想改变存储过程或者函数的结构时可以使
用它。当然你也可以先 drop 它再 create。

   Drop {procedure | function}  [if exists] sp_Name

   Drop 语法即用来删除一个存储程序或者函数,当你创建的一个存储过程或者函数的名字已经存
在时,你想把以前的给覆盖掉,那么此时你就可以使用 drop ,然后在创建。

   Show create {procedure | function } sp_Name

   Show 语法用来显示创建的存储过程或者函数的信息。这里的 show 用法跟数据表中的 show 用
法是很相似的。

   Show {procedure | function} status [like 'partten']

   它返回子程序的特征,如数据库,名字,类型,创建者及创建和修改日期。如果没有指定样式,
根据你使用的语句,所有存储程序和存储函数的信息都被列出。

   看了以上的几个语法,你是不是感觉跟对表的操作很相象,那你就想对了,他们确实是很相似
的。带着一份激动心情我们继续往下看,你会发现很简单。

   Begin ... End  语句

   通过 begin end 可以来包含多个语句,每个语句以“;”结尾。

   Declare

   用Declare 来声明局部变量
   Declarevar_Name type defaulevaule

   Delare 条件

   Declarecondition_Name CONDITION FOR condition_value

    调用存储过程

   Call

   格式:
   Callsp_Name [parameter ]

   这里的  sp_Name  必须是由 create procedure 创建的名称。它可以通过声明的参数来传回值,
它也返回受影响的行数,在  MySQL          中可以通过 mysql_affected_rows() 来获得。

流程控制语句

    IF  语句

    IFsearch_condition THENstatement_list
    [ELSEIFsearch_condition THENstatement_list]
    [ELSEstatement_list]
    END IF  
    CASE  语句

    CASE case_value
    WHEN when_value THENstatement_list
        WHEN when_value THENstatement_list]
        ELSEstatement_list]
    END CASE  

    LOOP  语句

[begin_label:] LOOP
    statement_list
    END LOOP [end_label]
    LOOP 实现了一个简单的循环,通过 LEAVE 来退出

    LEAVE  语句

    LEAVE lable
退出语句,一般可以用在循环中。

    ITERATE  语句

    ITERATE lable
    ITERATE 一般出现在 LOOP、REPEATE、WHILE 里,意思是再次循环。

    REPEATE  语句

[begin_label:] REPEAT
    statement_list
    UNTILsearch_condition
    END REPEAT [end_label]
    REPEAT 语句内的语句或语句群被重复,直至 search_condition     为真。

    WHILE  语句

[begin_label:] WHILEsearch_condition DO
    statement_list
    END WHILE [end_label]
    WHILE 语句内的语句或语句群被重复,直至 search_condition      为真。
运用实例

   下面通过几个例子来讲述他们的应用:

   对网站用户的操作

   为了简单,用户表只有用户名和密码的信息.在服务端,我们建立如下的表:

代码片段  
   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  
   //  

   删除用户的存储过程:

代码片段  
   Delimiter //  
   Create procedure deleteuser(in username varchar(20))
Begin  
        delete from welefen.user where Name=username;  
   End  
   //  

    在客户端,我们给出如下的程序:

代码片段  
   文件名:ProcedureUser.php  
   <?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>";    }  
   ?  

    程序运行的结果:

执行结果  
添加用户welefen 成功  
   用户welefen 验证正确  
   用户welefen 的密码修改成功  
   用户welefen 删除成功  

    以上的这个程序简单的说明了Mysql 中的存储过程结合PHP 的应用,当然在实际应用要比这个
复杂的多。

   验证角谷猜想

   角谷猜想:给定一个整数x,若x%2=1,则x=3*x+1,否则x=x/2,如此循环下去,经过有限步骤必
能得到1。
   例        如        :        初       始        整        数        为       9       ,        则
 9->28->14->7->22->11->34->17->52->26->13->40->20->10->5->16->8->4->2->1

   为了说明存储过程中一些语法的应用,我们通过存储过程来实现它:

执行结果  
   mysql> delimiter //  
   mysql> create procedure jgguess(in number int)        -> begin  
       -> declare param1 int default 1;        -> set @a=concat(number);  
       -> jiaogu:loop                 #循环开始  
       -> set param1=number%2;            -> if param1=1 then set number=number*3+1; #number 为奇数,将它乘3加 1  
       -> else set number=number/2;        -> end if;  
       -> set @a=concat(@a,'->',number);        -> if number>1 then iterate jiaogu; #number 不为 1,继续循环  
       -> end if;  
       -> leave jiaogu; #退出循环  
       -> end loop jiaogu;  
       -> end  
       -> //  
   Query OK, 0 rows affected (0.00 sec)  
   mysql> call jgguess(11);  
       -> //  
   Query OK, 0 rows affected (0.00 sec)  
   mysql> select @a//  
 +-------------------------------------------------------+     | @a                     |  
 +-------------------------------------------------------+     | 11->34->17->52->26->13->40->20->10->5->16->8->4->2->1 |  
 +-------------------------------------------------------+     1 row in set (0.02 sec)  

   在这个存储过程中,你传入的参数不能超过int 型数据的范围,否则就会报错。

触发器

   触发器是与表有关的命名数据库对象,当表上出现特定事件时,将激活该对象。例如当我们向
某个表插入一行数据时发生一个事件或者删除某个记录时触发某个事件。
   语法:
   CREATE TRIGGER  trigger_Name trigger_time trigger_event
      ON  tbl_Name FOR EACHROW  trigger_stmt
   trigger_time 是触发器的动作时间。它可以是 BEFORE 或 AFTER ,以指明触发器是在激活它的
语句之前或之后触发。
   trigger_event 指明了激活触发器的语句的类型。trigger_event        可以是下述值之一:
   INSERT:将新行插入表时激活触发器,例如,通过 INSERT、LOADDATA 和 REPLACE 语句;
   UPDATE:更改某一行时激活触发器,例如,通过UPDATE语句;
   DELETE:从表中删除某一行时激活触发器,例如,通过 DELETE 和 REPLACE 语句。

   例如当我们向上面的user 表中增加一个用户名为“welefen ”时,我们把记录用户数的表的值增
加 1;

代码片段  
   Create table numuser(  
      Num int not null default 0  
   );  
   Delimiter //  
   Create trigger testnum after insert on welefen.user  for each row  
   Begin  
      Update welefen.numuser set Num=Num+1;    End  
   //  

    视图

   当我们想得到数据表中某些字段的信息,并想把他们保存时我们就可以用视图。

   语法:
   CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
      VIEW view_Name [(column_list)]
      AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

   例如我们想对上面的用户表使用视图,可以这样:
   Create viewwelefen.userview as select * fromwelefen.user;
   查看视图的信息可以使用:
   Select * fromwelfen.userview;