create procedure procedure_name([params,.....])
begin
    SQL语句
end;
复制代码

call procedure_name();
复制代码

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lastName` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `departmentID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
INSERT INTO `employee` VALUES ('1', '亚索', '123qq.com', '201');
INSERT INTO `employee` VALUES ('2', '李青', '12345@qq.com', '201');
INSERT INTO `employee` VALUES ('3', '艾欧尼亚', '774855@qq.com', '203');
INSERT INTO `employee` VALUES ('4', '德玛西亚', '6565@qq.com', '203'
复制代码

create procedure demo1()
begin
   select * from employee
end$
复制代码

call demo1()$
复制代码

查询指定数据库的所有的存储过程
如果不指明type 则默认shi存储过程
select name from mysql.proc where db='数据库名字 and type = 'PROCEDURE'
复制代码

select name from mysql.proc where db='jdbc'$
复制代码

show create procedure [数据库名].存储过程名 \G$
复制代码

show procedure status \G$
复制代码

drop procedure 存储过程名称$
复制代码

create function function_name([params type,.....])
returns type
begin
   .....
end;
复制代码

create function numofemployee(department_ID int)
returns int
begin 
  declare count int;
  select count(*) into count from employee where departmentID=department_ID ;
  return count;
 end$
复制代码

select function_name(params)$
复制代码

select name from mysql.proc where db = '数据库名字' and type= 'FUNCTION'
复制代码

show function status \G$
复制代码

drop function fun_name$
复制代码