MySQL视图索引存储过程

目录

一、准备工作

二、创建一个可以统计表格内记录条数的存储函数 ，函数名为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)