目录
二、创建一个可以统计表格内记录条数的存储函数 ,函数名为count_sch()
三、创建一个存储过程avg_svi,有3个参数,分别是deptno,job,接收平均工资, 功能查询emp表dept为30,job为销售员的平均工资
一、准备工作
创建表并插入数据
mysql> create table student(
-> id int(10) primary key unique,
-> name varchar(50) not null,
-> glass varchar(50) not null);
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> insert into student values(1,'xiaoming','glass 1'), (2,'xiaojunn','glass 2');
表记录如下:
mysql> select * from student;
+----+----------+---------+
| id | name | glass |
+----+----------+---------+
| 1 | xiaoming | glass 1 |
| 2 | xiaojun | glass 2 |
+----+----------+---------+
2 rows in set (0.00 sec)
二、创建一个可以统计表格内记录条数的存储函数 ,函数名为count_sch()
mysql> create function count_sch()
-> returns int
-> begin
-> declare c int default 0;
-> select count(1) into c from studet;
-> return c;
-> end $
如果报错:
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
解决方法:
mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
查看:
mysql> \d ;
mysql> select count_sch();
+-------------+
| count_sch() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
三、创建一个存储过程avg_svi,有3个参数,分别是deptno,job,接收平均工资, 功能查询emp表dept为30,job为销售员的平均工资
mysql> \d $
mysql> create procedure avg_svi(in p1 int,in p2 varchar(30),out avg float)
-> begin
-> select avg(sal) into avg from emp
-> where deptno=p1 and job=p2;
-> end $
mysql> call avg_svi(30,'销售员',@a) /
mysql> select @a/
+--------------------+
| @a |
+--------------------+
| 3222.22249609375 |
+--------------------|
1 row in set (0.00 sec)