MySQL存储过程

mysql> select substring('abcd',0,2);  
+-----------------------+  
| substring('abcd',0,2) |  
+-----------------------+  
|                       |  
+-----------------------+  
1 row in set (0.00 sec)  
 
mysql> select substring('abcd',1,2);  
+-----------------------+  
| substring('abcd',1,2) |  
+-----------------------+  
|     ab                |  
+-----------------------+  
1 row in set (0.02 sec)  

mysql> select round(1.23);  
+-------------+  
| round(1.23) |  
+-------------+  
|           1 |  
+-------------+  
1 row in set (0.00 sec)  
 
mysql> select round(1.56);  
+-------------+  
| round(1.56) |  
+-------------+  
|           2 |  
+-------------+  
1 row in set (0.00 sec) 

mysql> select round(1.567,2);  
+----------------+  
| round(1.567,2) |  
+----------------+  
|           1.57 |  
+----------------+  
1 row in set (0.00 sec) 

create TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  userName varchar(50) DEFAULT NULL,
  userAge int(11) DEFAULT NULL,
  userAddress varchar(200) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
insert into user values(1, 'summer', 100, 'Shanghai Pudong');
 
/*
  创建存储过程
  in 参数例子
*/
DROP PROCEDURE IF EXISTS proc_user_demo_in; /*删除存储过程（如果存在）*/
CREATE PROCEDURE proc_user_demo_in(IN p_in INT)
 BEGIN
  SELECT p_in; 
  SET p_in=2; 
  SELECT p_in; 
 END
/*调用*/
/*定义客户端变量（与连接有关，当客户端退出时，变量会被释放）*/
SET @p_in=1; 
CALL proc_user_demo_in(@p_in); /*1*/ /*2*/
SELECT @p_in; /*1*/
SHOW GLOBAL VARIABLES like '%';
/*p_in虽然在存储过程中被修改，但并不影响@p_id的值*/
 
/*
 创建存储过程
 out 例子
*/
drop procedure if exists proc_user_demo_out;
create procedure proc_user_demo_out(out p_out int)
 BEGIN
  select p_out; 
  SET p_out=2; 
  select p_out; 
 END;
SET @p_out=1; 
call proc_user_demo_out(@p_out); /*null*/ /*2*/
SELECT @p_out; /*2*/
/*p_out在存储过程中被修改，影响@p_out的值*/
 
/*
 创建存储过程
 inout 例子
*/
drop procedure if exists proc_user_demo_inout;
create procedure proc_user_demo_inout(INOUT p_inout int)
 BEGIN
  select p_inout;
  set p_inout=2;
  select p_inout;
 END;
SET @p_inout=1;
CALL proc_user_demo_inout(@p_inout); /*1*/ /*2*/
SELECT @p_inout; /*2*/
 

/*在存储过程中使用用户变量*/
drop procedure if exists proc_user_demo_user_variable;
create procedure proc_user_demo_user_variable()
 BEGIN
  select CONCAT(@greeting, ' world!') as 'greeting';
 END;
set @greeting='hello';
call proc_user_demo_user_variable();
 
/*在存储间传递全局范围的用户变量*/
drop procedure if exists proc_user_demo_variable_transport1;
drop procedure if exists proc_user_demo_variable_transport2;
create procedure proc_user_demo_variable_transport1()
 BEGIN
  set @last_variable_transport='p1';
 END;
create procedure proc_user_demo_variable_transport2()
 BEGIN
  select CONCAT('the last_variable_transport is ', @last_variable_transport) as 'last_variable_transport';
 END;
CALL proc_user_demo_variable_transport1();
CALL proc_user_demo_variable_transport2();
/*
注意:
①用户变量名一般以@开头
②滥用用户变量会导致程序难以理解及管理
*/
 

/*在存数过程中使用内部变量*/
drop procedure if exists proc_user_demo_use_inner_variable;
create procedure proc_user_demo_use_inner_variable(IN param INTEGER)
 BEGIN
  DECLARE variable1 char(10) default 'nothing';
  IF param=1 THEN
   SET variable1='birds';
  ELSEIF param=2 THEN
   SET  variable1='beasts';
  END IF;
  INSERT INTO user(userName) values(variable1);
 END;
CALL proc_user_demo_use_inner_variable(3);
 
 
/*查看数据库中的表和存储过程*/
show tables; /*查看数据库的所有表*/
show create TABLE mybatis.user; /*查看某张表的创建详情*/
select * from mysql.proc where db='mybatis'; /*查看数据库存储过列表*/
show create procedure mybatis.proc_user_demo_in; /*查看某个存储过程的创建详情*/
 

/*
存储过程：变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储
过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派
给会话变量来保存其值。
*/
drop procedure if exists proc_user_demo_variable_scope;
create procedure proc_user_demo_variable_scope()
 BEGIN
  declare x1 varchar(5) default 'outer';
  BEGIN
   declare x1 varchar(5) default 'inner';
   select x1; /*inner*/
  END;
  select x1; /*outer*/
 END;
call proc_user_demo_variable_scope();
 
/*存储过程：条件语句*/
drop procedure if exists proc_user_demo_if_then_else;
create procedure proc_user_demo_if_then_else(IN param INT)
 BEGIN
  declare var int default 0;
  declare maxid int;
  set var=param;
  select max(id) into maxid from user;
  IF var=0 THEN
   insert into user(userName, userAge) values('nothing', var);
  END IF;
  IF var=1 THEN
   update user set userAge=1 where id=maxid;
  ELSE
   update user set userAge=100 where id=maxid;
  END IF;
 END;
call proc_user_demo_if_then_else(0);
 
 
/*存储过程：case语句*/
drop procedure if exists proc_user_demo_case_when;
create procedure proc_user_demo_case_when(in param int)
 BEGIN
  declare var int;
  set var=param;
  CASE var
   WHEN 0 THEN
    insert into user(userName, userAge) values('nothing', 0);
   WHEN 1 THEN
    insert into user(userName, userAge) values('nothing', 1);
   ELSE
    insert into user(userName, userAge) values('nothing', 100);
  END CASE;
 END;
CALL proc_user_demo_case_when(15);
 

/*存储过程：循环语句 while*/
drop procedure if exists proc_user_demo_while;
create procedure proc_user_demo_while()
 BEGIN
  declare var int default 0;
  while var<6 DO
   insert into user(userName, userAge) values(CONCAT('name',var), var);
   set var=var+1;
  END WHILE;
 END;
CALL proc_user_demo_while();
 
/*存储过程：循环语句 repeat*/
/*它在执行操作后检查结果，而while则是执行前进行检查*/
drop procedure if exists proc_user_demo_repeat;
create procedure proc_user_demo_repeat(in repeat_times int)
 BEGIN
  DECLARE var int default 1;
  REPEAT
   insert into user(userName, userAge) values(CONCAT('name',var), var);
   set var=var+1;
  UNTIL var>repeat_times END REPEAT;
 END;
CALL proc_user_demo_repeat(3);
 
/*存储过程：循环语句 loop*/
drop procedure if exists proc_user_demo_loop;
create procedure proc_user_demo_loop(in loop_times int)
 BEGIN
  declare var int default 1;
  LOOP_LABLE:loop
   insert into user(userName, userAge) values(CONCAT('name',var), var);
   set var=var+1;
   IF var>loop_times THEN
    LEAVE LOOP_LABLE;
   END IF;
  END LOOP;
 END;
CALL proc_user_demo_loop(2);

/*定义函数*/
DROP FUNCTION IF EXISTS fun_user_demo_sum;
CREATE FUNCTION fun_user_demo_sum(a INT(2),b INT(2)) RETURNS INT(4)
 BEGIN
  DECLARE sum_ INT(2) DEFAULT 0;
  SET sum_ = a + b;
  RETURN sum_;
 END;
/*使用函数*/
select fun_user_demo_sum(1,2) as 'sum_';
 
/*定义存储过程*/
drop procedure if exists proc_user_demo_sum;
CREATE PROCEDURE proc_user_demo_sum(IN a INT(2), IN b INT(2), OUT sum INT(4))
 BEGIN
  SET sum=a+b;
 END;
/*使用存储过程*/
call proc_user_demo_sum(1, 2, @c);
select @c;