1、创建一个可以统计表格内记录条数的存储函数 ,函数名为count_sch()
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `example`.`count_sch()`()
RETURNS INT
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
DECLARE n INT;
SELECT COUNT(*) INTO n FROM t2;
RETURN n;
END$$
DELIMITER ;
#调用:
select count_sch();
2、创建一个存储过程avg_sai,有3个参数,分别是deptno,job,接收平均工资,
功能查询emp表dept为30,job为销售员的平均工资。
DELIMITER $$
USE `example`$$
DROP PROCEDURE IF EXISTS `avg_sai`$$
CREATE DEFINER=`test`@`%` PROCEDURE `avg_sai`(IN dept INT(4),IN job VARCHAR(255),OUT avg_my DOUBLE)
BEGIN
SELECT AVG(sai) INTO avg_my FROM emp
WHERE dept=deptno AND job='销售';
END$$
DELIMITER ;
#调用:
call avg_sai(30,'销售',@a); # @a 将返回结果传入a
select @a;