MySQL存储过程和函数存储过程就是一条或多条SQL语句的集合
创建存储过程和函数
创建存储过程：create procedure
创建函数：create function
调用存储过程：CALL
创建存储过程
create procedure sp_name([proc_parameter])
[characteristics...] routine_body
[in|out|inout] param_name type
mysql> delimiter $
mysql> create procedureavgfruitprice()
-> begin
-> select avg(f_price) as avgprice
-> from fruits;
-> end$
创建存储函数
create function func_name([func_parameter])
returns type
[characteristic...] routine_body
[in|out|inout]param_name type
mysql> delimiter //
mysql> create function namebyzip()
-> returns char(50)//RETURN子句只能对FUNCTION做指定
-> return (select s_name from suppliers where s_call='48075');
-> //
Query OK, 0 rows affected (0.05sec)
mysql> delimiter ;
变量的使用：
范围：在BEGIN....END程序中起做用
定义变量：
declare var_name[,varname].... date_type[default value];
eg: declare myparam int default100;
为变量赋值：
set var_name=expr[,var_name=expr]...;
declare var1,var2,var3 int;
set var1=10,var2=20
set var3=var1+var2;
为多个值赋值:
select col_name[...] intovar_name[,...] table_expr;
定义条件和处理程序:
定义条件：
declare condition_name conditionfor [condition_type]
[condition_type]:
sqlstate[value]sqlstate_value | mysql_error_code
eg:
declare command_not_allowedcondition for sqlstate '42000';
declare command_not_allowedcondition for 1148
定义处理程序：
declare handler_type handler forcondition_value[,...] sp_statement
handler_type:
continue|exit|undo
condition_value:
sqlstate[value] sqlstate_value
|condition_name
|sqlwarning
|not found
|sqlexeception
|mysql_error_code
eg:
declare continue handler forsqlstate '42s02' set @info='no_such_table';
declare continue handler for 1146set @info='no_such_table';
declare no_such_table condition for1146;
declare continue handler forno_such_table set @info='no_such_table';
declare exit handler for sqlwarningset @info='error';
declare exit handler for not foundset @info='no_such_table';
declare exit handler forsqlexception set @info='error';
定义条件和处理程序:
mysql> create table test.t(s1int,primary key(s1));
Query OK, 0 rows affected (0.03sec)
mysql> delimiter //
mysql> create procedurehandlerdemo()
-> begin
-> declare continue handler for sqlstate '23000' set @x2=1; // @var_name 表示用户变量
-> set @x=1;
-> insert into test.tvalues(1);
-> set @x=2;
-> insert into test.t values(1);
-> set @x=3;
-> end;
-> //
Query OK, 0 rows affected (0.00sec)
mysql> delimiter ;
mysql> callhandlerdemo(); //调用存储过程
mysql> insert into t(s1)values(1);
ERROR 1062 (23000):Duplicate entry '1' for key 'PRIMARY'
光标的使用：
光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明
声明：
declare cursor_name cursor forselect_statement
eg:declare cursor_fruit cursorfor select f_name,f_price from fruits;
打开光标：
open cursor_name{光标名称}
eg:open cursor_fruit;
使用光标：
fetch cursor_name into var_name[,var_name]...{参数名称}
eg:fetch cursor_fruit intofruit_name,fruit_price //fruit_name,fruit_price 必须在前面已定义
关闭光标;
close cursor_name{光标名称}
eg:close cursor_fruit;
光标只能在存储过程和函数中使用
流程控制的使用
语句：
IF CASE LOOP WHILE LEAVE ITERATE REPEAT WHILE
IF语句：
if expr_condition then statement_list
[elseifexpr_condition then statement_list]...
[elsestatement_list]
end if
eg:
if val is null
thenselect 'val is null';
elseselect 'val is not null';
end if;
CASE语句：类型1
case case_expr
whenwhen_value then statement_list
[whenwhen_value then statement_list]...
[elsestatement_list]
end case
eg:
case val
when1 then select 'val is 1';
when2 then select 'val is 2';
elseselect 'val is not 1 or 2';
end case;
CASE语句：类型2
case
whenexpr_condition then statement_list
[whenexpr_condition then statement_list]...
[elsestatement_list]
end case
eg:
case
whenval is null then select 'val is null';
whenval<0 then select 'val is less than 0';
whenval>0 then select 'val is greater than 0';
elseselect 'val is 0';
end case;
Loop语句：
Loop循环语句可以用来重复执行某些语句,leave子句跳出循环
[loop_label:] LOOP
statement_list
end loop[loop_label]
eg:
declare id int default 0;
add_loop:LOOP
SETid=id+1;
ifid>=10 then leave add_loop;
endif;
end loop add_loop;
LEAVE语句:
LEAVE label
eg:
add_num:LOOP
SET@count=@count+1;
IF@count=50 THEN LEAVE add_num;
END LOOP add_num;
ITERATE语句：
将执行顺序转到语句开头处，（意思为再次循环）只能出现在LOOP,REPEAT和WHILE语句内
ITERATE label
eg:
mysql> create proceduredoiterate()
-> begin
-> declare p1 int default 0;
-> my_loop:LOOP
-> set p1=p1+1;
-> if p1<10 then iterate my_loop;
-> elseif p1>20 then leave my_loop;
-> end if;
-> SELECT 'p1 is between 10 and 20';
-> END LOOP my_loop;
-> END//
REPEAT语句：
REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式式的真，则循环结束，否则重复执行循环中的语句
[repeat_label:]REPEAT
statement_list
UNTIL expr_condition
END REPEAT [repeat_label]
eg:
declare id int default 0;
REPEAT
SETid=id+1;
UNTILid>=10;
END REPEAT;
WHILE语句：
[while_label:] WHILEexpr_condition DO
statement_list
END WHILE [while_label]
eg:
declare i int default 0;
WHILE i<10 DO
SETi=i+1;
END WHILE;
调用存储过程和函数
存储过程使用：CALL来调用,如果是不同的数据库，要用CALL dbname.procename
调用存储过程：
CALL sp_name([parameter[,...]])
mysql> create procedure rsc(insid int,out num int)
-> begin
-> select count(*) into num fromfruits where s_id=sid;
-> end//
Query OK, 0 rows affected (0.04sec)
mysql> delimiter ;
mysql> call rsc(101,@num);
Query OK, 0 rows affected (0.07sec)
mysql> select @num;
+------+
| @num |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql> select * from fruitswhere s_id=101
-> ;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101| apple | 5.20 |
| b1 | 101| blackberry | 10.20 |
| c0 | 101| cherry | 3.20 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
调用存储函数
mysql> delimiter //
mysql> create function rsc(sidint)
-> returns int
-> begin
-> return(select count(*) from fruitswhere s_id=sid);
-> end//
Query OK, 0 rows affected (0.05sec)
mysql> delimiter ;
mysql> select rsc(101);
+----------+
| rsc(101) |
+----------+
| 3 |
+----------+
1 row in set (0.01 sec)
查看存储过程和函数
1.SHOW STATUS 语句查看存储过程和函数的状态
show {procedure|function} status[like 'pattern']
eg:
mysql> show procedure statuslike 'r%'\G;
*************************** 1.row ***************************
Db: test
Name: rsc
Type: PROCEDURE
Definer: root@localhost
Modified: 2014-09-16 22:14:29
Created: 2014-09-16 22:14:29
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection:utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.05 sec)
ERROR:
No query specified
SHOW CREATE 语句查看存储过程和函数的定义
show create {procedure | function} sp_name
mysql> show create function test.rsc\G;
*************************** 1.row ***************************
Funcdtion: rsc
sql_mode:
Create Function: CREATEDEFINER=`root`@`localhost` FUNCTION `rsc`(sid int) RETURNS int(11)
begin
return(select count(*) from fruitswhere s_id=sid);
end
character_set_client: utf8
collation_connection:utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
ERROR:
No query specified
从information_schema.Routines表中查看存储器过程和函数的信息(表中存储所有的存储过程和函数的定义)
select * frominformation_schema.Routines where routine_name='rsc' androutine_type='function'\G;
*************************** 8.row ***************************
SPECIFIC_NAME: rsc
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: rsc
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: begin
select count(*) into num fromfruits where s_id=sid;
end
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2014-09-16 22:14:29
LAST_ALTERED: 2014-09-16 22:14:29
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION:utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
8 rows in set (0.00 sec)
修改存储过程和函数
alter {procedure|function}sp_name [characteristic...]
characteristic:
containssql
nosql
readssql data
modifiessql data
sqlsecurity{definer|invoker}
eg:
mysql> alter function rsc
-> reads sql data comment 'findname';
Query OK, 0 rows affected (0.00sec)
mysql> show create functionrsc\G;
*************************** 1.row ***************************
Function: rsc
sql_mode:
Create Function: CREATEDEFINER=`root`@`localhost` FUNCTION `rsc`(sid int) RETURNS int(11)
READS SQL DATA
COMMENT 'find name'
begin
return(select count(*) fromfruits where s_id=sid);
end
character_set_client: utf8
collation_connection:utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
删除存储过程和函数
DROP{procedure|function} [if exists] sp_name
eg:
mysql> drop procedure rsc;
Query OK, 0 rows affected (0.05sec)
综合实列：
mysql> delimiter //
mysql> create procedureadd_id(out count int)
-> begin
-> declare itmp int;
-> declare cur_id cursor for select idfrom sch;
-> declare exit handler for not foundclose cur_id;
-> select count_sch() into count;
-> set @sum=0;
-> open cur_id;
-> repeat
-> fetch cur_id into itmp;
-> if itmp<10
-> then set @sun= @sum+itmp;
-> end if;
-> until 0 end repeat;
-> close cur_id;
-> end//
Query OK, 0 rows affected (0.00sec)
mysql> select @a,@sum//
+------+------+
| @a | @sum |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.01 sec)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
存储：procedure (相当于函数,段码代)
1.修改语句定界符：
mysql>\c //
2.创建存储
mysql> create procedure t1()
-> begin
-> set @i=3;
-> while @i<=100 do
-> insert into t1(name)values(concat("user",@i));
-> set @i=@i+1;
-> end while;
-> end//
3.查看存储：
mysql> show procedurestatus\G;
mysql> show create proceduret1\G; 查看具体创建时的语句
4.执行存储：
mysql>call t1;
查询存储引擎
show engines;
查看当前支持那些存储引擎：
mysql> showengines;
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as ofMySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful fortemporary tables |
| InnoDB | YES | Supports 
