1. 创建表并插入数据:
CREATE TABLE `PR`.`sch` (
`id` int,
`name` varchar(50) NOT NULL,
`glass` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
);
insert sch (id,name,glass) values(1,'xiaommg','glass1'),(2,'xiaojun','glass2');
2.创建存储函数 :
delimiter $
CREATE FUNCTION count_sch()
RETURNS int
BEGIN
DECLARE n INT DEFAULT 0;
SELECT COUNT(id) INTO n FROM sch;
RETURN n;
END $
3.创建存储过程 :
delimiter $$
CREATE PROCEDURE avg_sai (IN deptno int(10),IN partment VARCHAR(20),OUT avg_total float(20))
begin select avg(incoming) into avg_total from emp where dept=deptno AND job=partment;
end $$