【存储过程】MySQL存储过程，存储函数

存储例程是存储在数据库服务器中的一组sql语句，通过在查询中调用一个指定的名称来执行这些sql语句命令

Mysql存储例程实际包含了存储过程和存储函数，它们被统称为存储例程。


存储过程主要完成在获取记录或插入记录或更新记录或删除记录，即完成select insert delete update等的工作。
而存储函数只完成查询的工作，可接受输入参数并返回一个结果。

 

show procedure status查看已创建的存储过程

-------------------------

CALL p_draws(@a);
SELECT @a;

 

 

CREATE TABLE `myisam` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`post` text,
PRIMARY KEY (`id`)

 

 

 

 

-------------------------create procedure 存储过程名(参数)
调用存储过程
call 存储过程名();

------取总记录
create procedure select_myisam()
select * from myisam;
在终端运行并调用
#call select_news();


声明参数时规定要使用关键字IN,OUT,INOUT。
其中：
IN：用于输入参数
OUT：用于返回参数
INOUT：用于向存储过程传递参数值，如果该值改变则返回

----------取某条记录
drop procedure if exists select_myisam;
show procedure status;
create procedure select_myisam(IN id int,OUT name varchar(50))
select name from myisam where id=id;
调用#call select_myisam(1,@name);
1是我们要传入的信息编号，title是要返回的对应信息编号标题，由于是OUT所以再调用时要在其前加@



------取总数
create procedure getNum(OUT num int)
select COUNT(*) as num from myisam;
call getNum(@num);



多行存储过程写法：
create procedure sp_name()
begin
………
end



执行 call procedure（）语句
如果有返回值，执行select   @ar，返回执行结果。

---------------------------------create function 存储函数名(参数)

与存储过程返回参数不同的是存储函数在定义时没用直接声明哪个变量是返回参数，而只是使用了returns声明了返回参数所属的数据类型，返

回参数是在函数体中使用return返回要返回的数据变量的形式来表示的。这就需要注意的是：
存储函数只支持输入参数，并且输入参数前没有IN或INOUT.

delimiter //
使用delimiter更改mysql默认使用分号(;)使用新的结束符号来结束当前语句，使用delimiter后原先默认的分号(;)结束语句符号不再起作用，

直到重新恢复声明结束符后。

create function count_news(hits int) returns int
注意存储函数的参数只有输入参数并且前不再声明IN或INOUT，返回只需声明要返回的数据类型


drop function if exists count_myisam;
delimiter //
create function count_myisam(hits int) returns int
comment '根据传入的点击次数统计超过此点击数的信息数目'
begin
declare total_news int;
declare hits_num int default 0;
if hits>=0 then
set hits_num=hits;
select count(id) into total_news from myisam where myisam.id>hits_num;
else
set total_news=0;
end if;
return total_news;
end;
//
delimiter ;

调用select count_myisam(5);






ERROR 1418 (HY000) log_bin_trust_function_creators 解决
今天要写一个函数.但没有办法建提示错误如下:
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
    -> //
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging

is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
 
解决方式:(编缉my.cnf,添加如下)
[mysqld]
log_bin_trust_routine_creators = 1

 

 

 

 

 

 

 

 

 

 

----------------------------------------------存储过程实例

首先创建一个库

mysql>create database mysqlsystems
mysql>use mysqlsystems;

 

创建两个表 - innodb的和myisam的

 

mysql>
CREATE TABLE `myisam` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`post` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `innodb` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`post` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

这样叫创建了两个表，innodb和myisam。
创建可插入数据的 存储过程

 

mysql>
delimiter @
create procedure insert_isam(in item integer)
begin
declare counter int;
set counter = item;
while counter >= 1 do
insert into myisam values(counter,concat('mysqlsystems.com',counter),repeat('bla',10));
set counter = counter - 1;
end while;
end
@

create procedure insert_innodb(in item integer)
begin
declare counter int;
set counter = item;
while counter >= 1 do
insert into innodb values(counter,concat('mysqlsystems.com',counter),repeat('bla',10));
set counter = counter - 1;
end while;
end
@
delimiter ;

 

好了，show procedure status查看已创建的存储过程

运行吧，
你要插入100条到innodb？         执行

mysql>

call insert_innodb(100)
插入1000000到myisam？         

mysql>

call insert_myisam(1000000)
记得再一次插入新的内容时，要清空你的表，delete from ,或者truncate 。
插入10亿条？

 

 

----------------------------------------------------------------------------------基本知识

 

一.创建存储过程

1.基本语法：
create procedure sp_name()
begin
………
end
2.参数传递

二.调用存储过程

1.基本语法：call sp_name()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

三.删除存储过程

1.基本语法：
drop procedure sp_name//
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

四.区块，条件，循环

1.区块定义，常用
begin
……
end;
也可以给区块起别名，如：
lable:begin
………..
end lable;
可以用leave lable;跳出区块，执行区块以后的代码
2.条件语句 if 条件 then
statement
else
statement
end if;
3.循环语句
(1).while循环

[label:] WHILE expression DO

statements

END WHILE [label] ;

(2).loop循环 [label:] LOOP

statements

END LOOP [label];
(3).repeat until循环 [label:] REPEAT

statements

UNTIL expression

END REPEAT [label] ;
五.其他常用命令

1.show procedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
2.show create procedure sp_name
显示某一个存储过程的详细信息