oracle练习2024.08.15

--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;