窥探mysql存储过程细节

create table proced(
     id int(5) primary key auto_increment,
     name varchar(50),
     type varchar(50)
);

mysql> delimiter //
mysql> create procedure adddata()
     -->begin
     -->declare n int default 0;
     -->while n<100000
     -->do
     -->insert into proced(name,type) values(‘迹忆博客','onmpw');
     -->set n = n+1;
     -->end while;
     -->end
     -->//
mysql> delimiter ;
mysql> call adddata();

CREATE PROCEDURE procedure_name(IN/OUT/INOUT parameter TYPE)
BEGIN
     procedure_body
END

mysql> delimiter //;  改变界定符为 //
mysql> delimiter ; 重新改变界定符为分号

mysql>delimiter //
mysql> create procedure in_proced(IN param VARCHAR(100))
      -->begin
      -->insert into proced(name,type) values(param,'onmpw');
      -->end
      -->//
mysql>delimiter ;
mysql> call in_proced(‘onmpw.com');

mysql> delimiter //
mysql> create procedure out_proced(OUT param INT)
     -->begin
     -->select count(*) into param from proced;
     -->end
     -->//
mysql>delimiter ;
mysql> call out_proced(@a);
mysql>select @a;
+------+
| @a |
+------+
| 3   |
+------+

mysql> delimiter //
mysql> create procedure inout_proced(INOUT param INT)
     --> begin
     --> select count(*) into param from proced where id>param;
     --> end
     -->//
mysql>delimiter ;
mysql>set @a = 3;
mysql>call inout_proced(@a);
mysql>select @a; 查看变量的值是否改变

ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
  COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

mysql>delimiter //
mysql>create procedure A(IN pa1 INT,OUT pa2 INT)
     -->begin
     -->select count(*) into pa2 from proced where id>pa1;
     -->end
     -->//
mysql>create procedure B(INOUT pa INT)
     -->begin
     -->declare v int;
     -->call A(pa,v);
     -->set pa = v;
     -->end
     -->//
mysql>delimiter ;
mysql>drop procedure A;
mysql>set @a=5;
mysql>call B(@a);
ERROR 1305 (42000): PROCEDURE test.A does not exists