鸥鹏数据库作业4

创建表

 create table sch ( id int primary key, name varchar(50) not null, glass varchar (50) not null);insert into sch values (1, 'xiaommg' ,'glass 1'), (2, 'xiaojun', 'glass2') ;

 set global log_bin_trust_function_creators=1;

1、创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()

\d @

create function count_sch()
    -> returns int
    -> begin
    -> declare c int default 0;
    -> select count(*) into c from sch;
    -> return c;
    -> end@
Query OK, 0 ro

2、创建一个存储过程avg_sal，有3个参数，分别是deptno，job，接收平均工资(out);
\d // 

create procedure avg_sal(in deptno int,in job varchar(50),out s double)
    -> begin
    -> select avg(salary) into s from employees where department_id=deptno and job;
    -> select s;
    -> end//

功能查询employees表的平均薪资满足条件department_id为deptno，job_id为job的平均工资。

call avg_sal(50,'SH_CLERK',@s)//