网站地图
【www.gdgbn.com--mysql教程】
mysql教程 创建存储过程
“pr_add” 是个简单的 mysql 存储过程，这个存储过程有两个 int 类型的输入参数 “a”、“b”，返回这两个参数的和。
drop procedure if exists pr_add;
-- 计算两个数之和
create procedure pr_add
(
a int,
b int
)
begin
declare c int;
if a is null then
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a b;
select c as sum;
/*
return c; -- 不能在 mysql 存储过程中使用。return 只能出现在函数中。
*/
end;
二、调用 mysql 存储过程
call pr_add(10, 20);
执行 mysql 存储过程，存储过程参数为 mysql 用户变量。
set @a = 10;
set @b = 20;
call pr_add(@a, @b);
三、mysql 存储过程特点
创建 mysql 存储过程的简单语法为：
create procedure 存储过程名字()
(
[in|out|inout] 参数 datatype
)
begin
mysql 语句;
end;
mysql 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。
1. mysql 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”
2. mysql 存储过程参数，不能在参数名称前加“@”，如：“@a int”。下面的创建存储过程语法在 mysql 中是错误的（在 sql server 中是正确的）。 mysql 存储过程中的变量，不需要在变量名字前加“@”，虽然 mysql 客户端用户变量要加个“@”。
create procedure pr_add
(
@a int, -- 错误
b int -- 正确
)
3. mysql 存储过程的参数不能指定默认值。
4. mysql 存储过程不需要在 procedure body 前面加 “as”。而 sql server 存储过程必须加 “as” 关键字。
create procedure pr_add
(
a int,
b int
)
as -- 错误，mysql 不需要 “as”
begin
mysql statement ...;
end;
5. 如果 mysql 存储过程中包含多条 mysql 语句，则需要 begin end 关键字。
create procedure pr_add
(
a int,
b int
)
begin
mysql statement 1 ...;
mysql statement 2 ...;
end;
6. mysql 存储过程中的每条语句的末尾，都要加上分号 “;”
...
declare c int;
if a is null then
set a = 0;
end if;
...
end;
7. mysql 存储过程中的注释。
/*
这是个
多行 mysql 注释。
*/
declare c int; -- 这是单行 mysql 注释 （注意 -- 后至少要有一个空格）
if a is null then # 这也是个单行 mysql 注释
set a = 0;
end if;
...
end;
8. 不能在 mysql 存储过程中使用 “return” 关键字。
set c = a b;
select c as sum;
/*
return c; -- 不能在 mysql 存储过程中使用。return 只能出现在函数中。
*/
end;
9. 调用 mysql 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()”
call pr_no_param();
10. 因为 mysql 存储过程参数没有默认值，所以在调用 mysql 存储过程时候，不能省略参数。可以用 null 来替代。
call pr_add(10, null);
方法二
1 用mysql客户端登入
2 选择数据库教程
mysql>use test
3 查询当前数据库有哪些存储过程
mysql>show procedure status where db="test"
4 创建一个简单的存储过程
mysql>create procedure hi() select "hello";
5 存储过程创建完毕,看怎么调用它
mysql>call hi();
显示结果 mysql> call hi();
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)
query ok, 0 rows affected (0.01 sec)
6 一个简单的储存过程就成功了,这只是一个演示,存储过程可以一次执行多个sql语句,所以php教程只连接数据库一次就能查询多个语句;不过要返回多个结果集就必须用mysqli扩展来查询,否则会提示错误can"t return a result set in the given context
所以要使用这些新功能,最好适应使用mysqli的php扩展库。
其它方法
建立存储过程
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
if (!mysql_connect("localhost","root","welefen")){
echo "连接数据库失败";
}
if (!mysql_select_db("welefen")){
echo "选择数据库表失败
";
}
$insert_user=array("welefen","welefen");//这里的welefen分别为用户名、密码
if (mysql_query("call insertuser("$insert_user[0]","$insert_user[1]")")){
echo "添加用户$insert_user[0]成功
";
}else {
echo "添加用户$insert_user[0]失败
";
}
$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]验证正确
";
}else {
echo "用户$validate_user[0]验证错误
";
}
$modify_pwd=array("welefen","weilefeng"); //welefen为用户名weilefeng为新密码
if (mysql_query("call modifypwd("$modify_pwd[0]","$modify_pwd[1]")")){
echo "用户$modigy_pwd[0]的密码修改成功
";
}else {
echo "用户$modigy_pwd[0]的密码修改失败
";
}
$delete_user=array("welefen"); //welefen为用户名
if (mysql_query("call deleteuser("$delete_user[0]")")){
echo "用户$delete_user[0]删除成功
";
}else {
echo "用户$delete_user[0]删除失败
";
}
?
程序运行的结果：
执行结果
添加用户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;