--1.创建一个名为‘EMP_DETAILS_VIEW’的只读视图，包含各个员工的员工编号、员工名、职位编号、职位名称、部门编号、国家信息和区域信息：
create view emp_details_view as
select
e.employee_id as employee_id,
e.first_name || ' ' || e.last_name as employee_name,
e.job_id as job_id,
j.job_title as job_title,
e.department_id as department_id,
l.country_id as country_id,
r.region_name as region_name
from
employees e
join
jobs j on e.job_id = j.job_id
join
departments d on e.department_id = d.department_id
join
locations l on d.location_id = l.location_id
join
countries c on c.country_id=l.country_id
join
regions r on c.region_id = r.region_id;
SELECT * FROM EMP_DETAILS_VIEW;
--2.创建一个名为‘DEPT_STAT_VIEW’的视图，包含部门号、部门人数、部门平均工资、部门最高工资、部门最低工资以及部门工资总和：
select * from employees
select * from locations
select * from emp_details_view
create view emp_details_view as
select
e.department_id as department_id,
count(e.employee_id) as peoplenum,
avg(e.salary) as avgsalary,
max(e.salary) as maxs,
min(e.salary) as mins,
sum(e.salary) as sums
from
employees e
group by
e.department_id;
--3.创建名为‘PROC_SHOW_EMP’的存储过程，以部门编号为参数，查询并输出部门平均工资，以及该部门中比该部门平均工资高的员工信息：
create or replace procedure PROC_SHOW_EMP(dpt_id number)
is
avs number;
type tabtype is table of employees%rowtype;
v_employees tabtype;
begin
select avg(salary) into avs from employees where department_id=dpt_id;
dbms_output.put_line('该部门平均工资：'||avs);
select * bulk collect into v_employees from employees where salary>avs;
for i in 1..v_employees.last loop
dbms_output.put_line(v_employees(i).employee_id||','||v_employees(i).first_name||','||v_employees(i).last_name
||','||v_employees(i).email||','||v_employees(i).phone_number||','||v_employees(i).hire_date||','||v_employees(i).job_id
||','||v_employees(i).salary||','||v_employees(i).commission_pct||','||v_employees(i).manager_id||','||v_employees(i).department_id);
end loop;
end;
call proc_secure_dml();
--4.创建名为‘PROC_RETURN_DEPTINFO’的存储过程，以部门编号为参数返回该部门的人数和平均工资：
create or replace procedure PROC_RETURN_DEPTINFO(dpt_id number)
is
avs number;
peos number;
begin
select avg(salary) into avs from employees where department_id=dpt_id;
dbms_output.put_line('该部门平均工资：'||avs);
select count(salary) into peos from employees where department_id=dpt_id;
dbms_output.put_line('人数：'||peos);
end;
--5.创建名为‘PROC_SECURE_DML’的存储过程，检查当前用户操作时间是否为工作时间，非工作时间直接报错：（即周一到周五，时间为08：00~18：00）
08：00~18：00）
create or replace procedure PROC_SECURE_DML
is
begin
if to_char(sysdate,'hh24:mi') not between '08:00' and '18:00'
or to_char(sysdate,'d') in (7,1) then
raise_application_error(-20001,'当前操作时间为非工作时间');
end if;
end;
call PROC_SECURE_DML();
--6.创建名为‘PROC_EMP_INSERT’的存储过程，根据用户输入的参数向EMPLOYEES表插入数据：
create or replace procedure PROC_EMP_INSERT(eid number,fname varchar2,lname varchar2,emai varchar2,hidate date,jbid varchar2)
is
begin
insert into employees(employee_id,first_name,last_name,email,hire_date,job_id) values(eid,fname,lname,emai,hidate,jbid);
end;
call PROC_EMP_INSERT(666,'adfas','sdfa','asfd',sysdate,'AD_PRES');
--7.创建名为‘PROC_JOB_CHANGE’的存储过程，输入员工编号和职位，以实现员工职位的调动。如果输入的职位没有变动，
--则报“the new job title is as same as before!”错误：
select * from employees
create or replace procedure proc_job_change (
p_employee_id in employees.employee_id%type,
p_new_job in employees.job_id%type
) is
v_current_job employees.job_id%type;
begin
select job_id
into v_current_job
from employees
where employee_id = p_employee_id;
if v_current_job = p_new_job then
raise_application_error(-20001, 'The new job title is the same as before!');
else
update employees
set job_id = p_new_job
where employee_id = p_employee_id;
end if;
exception
when no_data_found then
raise_application_error(-20002, 'Employee ID does not exist.');
when others then
rollback;
raise;
end;
call proc_job_change(101,60);
--8.创建名为‘PROC_DEPARTMENT_CHANGE’的存储过程，输入员工编号和部门名称，以实现员工部门的调动。
--如果输入的部门没有变动，则报“the new department name is as same as before!”错误：
create or replace procedure proc_job_change (
p_employee_id in employees.employee_id%type,
p_new_dept in employees.department_id%type
) is
v_current_dept employees.department_id%type;
begin
select department_id
into v_current_dept
from employees
where employee_id = p_employee_id;
if v_current_dept = p_new_dept then
raise_application_error(-20001, 'The new dept title is the same as before!');
else
update employees
set department_id = p_new_dept
where employee_id = p_employee_id;
end if;
exception
when no_data_found then
raise_application_error(-20002, 'Employee ID does not exist.');
when others then
rollback;
raise;
end;
--9.创建名为‘FUNC_EMP_JOB_TITLE’的函数，以员工编号为参数，返回员工的职位名称：
create or replace function func_emp_job_title (
p_employee_id in employees.employee_id%type
) return employees.job_id%type
is
v_job_id employees.job_id%type;
begin
select job_id
into v_job_id
from employees
where employee_id = p_employee_id;
return v_job_id;
exception
when no_data_found then
return null;
when others then
raise;
end;