注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

@fc_lamp

关注Web应用解决方案MySql/PHP/Python一盏名为"飞川"的灯~

 
 
 

日志

 
 

简单说说MYSQL的存储过程(存储程序和函数 MySQL随机产生整数)  

2010-09-30 17:52:00|  分类: Web技术-Mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

简单说说MYSQL的存储过程

我们在开始(MYSQL的存储过程)之前,先看看一段代码。(“哦,我的天!!我看不懂呀。”等等,请暂停你的这样想法,你尽管去看,其他的你不必多虑,稍后就明白的。)

#While循环

1 delimiter //
2 create procedure cur<out var int>
3 begin
4 declare a int;
5 declare b int;
6 declare c_1 cursor for select t from t; #定义游标
7 declare continue handler for NOT FOUND #没有找到数据时,错误处理机制被触发,就将 B 设置为1
8   set b = 1;
9 open c_1; #打开游标
10 while b !=1 do #while循环 
11   fetch c_1 into a; #获得一行从SELECT产生的结果集中检索出来的值,
#然而表T中有多行,因此此句会被多次执行。
12 end while;
13 close c_1; #关闭游标
14 set var = a; 
15 end //

实际例子:

DROP PROCEDURE IF EXISTS `createPre`;
DELIMITER ;;
CREATE PROCEDURE `createPre`(IN `loops` int,IN `a` int,IN `b` int,IN `c` int,IN `d` int)
BEGIN
DECLARE count INT;
DECLARE p_id INT;
DECLARE t_id INT;
DECLARE expire_time INT;
DECLARE create_time INT;
set p_id = a;
set t_id=b;
set expire_time=c;
set create_time = d;
#不自动提交数据,加快插入(如不关闭,插入速度将会慢10倍)
SET autocommit=0;
SET count =loops;
WHILE count > 0 DO
insert into `test`.`test_table` (`p_id`,`t_id`,`secret`,`expire_time`,`create_time`) values(p_id,t_id,('test',expire_time,create_time);
SET count = count - 1;
END WHILE;
#提交更新
COMMIT;

END
;;
DELIMITER ;

PHP调用时:

"call createPre($loops,$a,$b,$c,$d)"


DROP PROCEDURE IF EXISTS `createPreCode`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `createCode`(IN `loops` int,IN `iid` int,IN `oid` int)
BEGIN

DECLARE count INT;
DECLARE i_id INT;
DECLARE o_id INT;
DECLARE code VARCHAR(16);
set i_id = itemid;
set o_id = operid;
SET count =loops;

WHILE count > 0 DO

#set code= CONCAT(FLOOR(RAND()*1000),unix_timestamp(now()),FLOOR(RAND()*1000));
#16位唯一数
set code= CONCAT(right(UUID_SHORT(),14),FLOOR(10+RAND()*99));

insert into `xxx` (`code`,`itemid`,`operid`) values(code,iid,oid);

SET count = count - 1;

END WHILE;

END
;;
DELIMITER ;


#repeat循环

delimiter //
create procedure cur<out var int>
begin
declare a int;
declare b int;
declare c_1 cursor for select t from t; #定义游标
declare continue handler for NOT FOUND#没有找到数据时,错误处理机制被触发,就将 B 设置为1
set b = 1;
open c_1; #打开游标
repeat   #repeat循环
fetch c_1 into a; #获得一行从SELECT产生的结果集中检索出来的值,
#然而表T中有多行,因此此句会被多次执行。
until b= 1
end repeat;
close c_1; #关闭游标
set var = a; 
end //

调用此procedure:

CALL cur(@var);//


存储函数

下面我们来一个更实际的例子,mysql随机产生字符串(mysql随机生成整数)。

DROP FUNCTION IF EXISTS `mychar`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `mychar`(`len` int) RETURNS char(255) CHARSET latin1
BEGIN
#定义变量
DECLARE s_len INT DEFAULT 0;
DECLARE str VARCHAR(255) DEFAULT '';
DECLARE char_num INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

#设置变量值
SET s_len = len;

WHILE i< s_len DO
#随机产生小字的字母
set char_num = FLOOR(97 + RAND()*(26));

#拼接字符
set str = concat(char(char_num),str);
set char_num =0;

SET i = i +1;
END WHILE;

#返回值
RETURN str;
END
;;
DELIMITER ;

调用时:

select mychar(6);

以上代码需要注意的是:1) 存储函数与过程不同,传入的参数前没有关键字'IN'了。

                                      2) 存储函数是必须要有返回值的,而且必须定义返回值类型'RETURNS char(255) CHARSET'。

                                      3)  关于随机产生小字的字母: 我们知道在ASCII码中97~122号为26个小写英文字母,因此我们只需要在97~122范围内随机产生一个整数就OK了。mysql在某范围内随机产生整数有一个公式:在i ≤ N ≤ j 这个范围得到一个随机整数N,则用到 FLOOR(i + RAND() * (j – i + 1))。                             


好了,看了以上的代码后(涉及游标),我们再说说他们是什么意思。

以上代码的简单解释:

delimiter //
分隔符:用于你通知MYSQL客户端你已经完成输入一个
SQL语句的字符或者字符串符号。
说明:一般SQL都用分号";"作为分隔符,但是在存储过程中
有许多语句都需要一个分号。所以,不能使用";"作为分隔符。
你可以根据自己的喜好来选择,但建议一般用“//”

create procedure name(out[in] var_name data-type)

定义存储过程名称

begin.....end  
用于存放多条存储过程的SQL语句

declare
用于定义变量,游标,错误处理机制等。
变量:
定义:declare a,b int default 5;
设置值: set a = 6;
游标:
declare cursor-name cursor for select ....;
open cursor-name;
fetch cursor-name into variable[,variable];
close cursor-name;

说明:一个游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)。游标每个时间点只能指向一行,但是可以根据需要指向结果集中其他的行。

错误处理:
DECLAREhandler_type HANDLER FORcondition_value[,...] statement
handler_type:
CONTINUE
| EXIT
| UNDO
condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
这段语句就是错误处理的用法,也就是当一段程序出错后自动触发的代码。MYSQL允许两种处理器,
一种是exit处理:执行后,原主程序不再运行。
一种是continue处理:执行后,原主程序仍然继续运行。
错误处理的三个预声明条件:
create procedure p9()
being
declare a int;
declare exit handler for NOT FOUND set a = 9; #NOT FOUND 找不到行
declare exit handler for SQLEXCEPTION set a = 9; #SQLEXCEPTION 错误
declare exit handler for SQLWARNING set a = 9; #SQLWARNING 警告
end;//
以上代码为:若出现了上述的错误,就把 变量a 设置为 9

循环:
WHILE ... END WHILE
LOOP ... END LOOP
REPEAT ... END REPEAT
GOTO
调用:
CALL P9(2); #对 create procedure p9(in var int)而言
CALL p9(@var);# create procedure p9(out var int)而言

补充:Labels 标号
CREATE PROCEDURE p17 () 
label_1: BEGIN 
label_2: WHILE 0 = 1 DO LEAVE label_2; END 
WHILE; 
label_3: REPEAT LEAVE label_3; UNTIL 0 =0 
END REPEAT; 
label_4: LOOP LEAVE label_4; END LOOP; 
END label_1; // 

最后一个循环例子中我使用了语句标号。现在这里有一个包含4个语句标号的过程的例子。我
们可以在BEGIN、WHILE、REPEAT或者LOOP语句前使用语句标号,语句标号只能在合法的
语句前面使用。因此"LEAVE label_3"意味着离开语句标号名定义为label_3的语句或复合语句。

另注:以下信息来源于:

MySql InnoDB 外键约束动作说明 

CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在两个表之间,你不应定义若干在父表或子表中的同一列采取动作的ON UPDATE CASCADE子句。

SET NULL: 从父表删除或更新行,并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词,这就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。

NO ACTION: 在ANSI SQL-92标准中,NO ACTION意味这不采取动作,就是如果有一个相关的外键值在被参考的表里,删除或更新主要键值的企图不被允许进行(Gruber, 掌握SQL, 2000:181)。 InnoDB拒绝对父表的删除或更新操作。

RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样,删除ON DELETE或ON UPDATE子句。(一些数据库系统有延期检查,并且NO ACTION是一个延期检查。在MySQL中,外键约束是被立即检查的,所以NO ACTION和RESTRICT是同样的)。

SET DEFAULT: 这个动作被解析程序识别,但InnoDB拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定义。

其他文章:《MYSQL触发器》

符:1  MySQL 5.1参考手册  http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html#create-procedure

       2 关于MySQL权限问题: http://fc-lamp.blog.163.com/blog/static/174566687201341391642583/


另:PHP操作MYSQL存储过程

实例一:无参的存储过程
$conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!");
mysql_select_db('test',$conn);
$sql = "
create procedure myproce()
begin
INSERT INTO user (id, username, sex) VALUES (NULL, 's', '0');
end;
";
mysql_query($sql);//创建一个myproce的存储过程

$sql = "call test.myproce();";
mysql_query($sql);//调用myproce的存储过程,则数据库中将增加一条新记录。

实例二:传入参数的存储过程
$sql = "
create procedure myproce2(in score int)
begin
if score >= 60 then
select 'pass';
else
select 'no';
end if;
end;
";
mysql_query($sql);//创建一个myproce2的存储过程
$sql = "call test.myproce2(70);";
mysql_query($sql);//调用myproce2的存储过程,看不到效果,可以在cmd下看到结果。

实例三:传出参数的存储过程
$sql = "
create procedure myproce3(out score int)
begin
set score=100;
end;
";
mysql_query($sql);//创建一个myproce3的存储过程
$sql = "call test.myproce3(@score);";
mysql_query($sql);//调用myproce3的存储过程
$result = mysql_query('select @score;');
$array = mysql_fetch_array($result);
echo '<pre>';print_r($array);

实例四:传出参数的inout存储过程
$sql = "
create procedure myproce4(inout sexflag int)
begin
SELECT * FROM user WHERE sex = sexflag;
end;
";
mysql_query($sql);//创建一个myproce4的存储过程
$sql = "set @sexflag = 1";
mysql_query($sql);//设置性别参数为1
$sql = "call test.myproce4(@sexflag);";
mysql_query($sql);//调用myproce4的存储过程,在cmd下面看效果


实例五:使用变量的存储过程
$sql = "
create procedure myproce5(in a int,in b int)
begin
declare s int default 0;
set s=a+b;
select s;
end;
";
mysql_query($sql);//创建一个myproce5的存储过程
$sql = "call test.myproce5(4,6);";
mysql_query($sql);//调用myproce5的存储过程,在cmd下面看效果

来至:http://hi.baidu.com/tj_110


  评论这张
 
阅读(1762)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017