2024年7月11日 练习2

--1.编写一个程序块，接受一个雇员编号，从emp表中显示该雇员的工作岗位与薪水，
--若输入的雇员编号不存在，则显示信息‘该雇员不存在！’：
declare
v emp%rowtype;
begin
  select * into v from emp where empno=&empno;
  dbms_outputput_line(v.job||' '||v.sal);
  exception
    when no_data_found then
      dbms_output.put_line('no exist');  
--2.接受两个数相除并且显示结果，如果第二个数为0，则显示消息‘除数不能为0’：
declare
    n number(10);
    z number(10);
    result number;
begin
    n := &被除数;
    z := &除数;
    -- perform division
    begin
        result := n / z;
        dbms_output.put_line('结果：' || result);
    exception
        when zero_divide then
            dbms_output.put_line('除数不能为0');
    end;
end;
--6.查询姓名为‘Smith’的员工工资，如果该员工不存在，则输出“There is not such an employee!”；
--如果存在多个同名的员工，则输出其员工号、姓名和工资：  
declare
    type emp_type is table of emp%rowtype; 
    v_emp emp_type;
begin
    execute immediate 'select * from emp where ename = :1 'bulk collect into v_emp using '&NAME' ;
        for i in 1..v_emp.count loop
            dbms_output.put_line(v_emp(i).empno || ' ' || v_emp(i).ename || ' ' || v_emp(i).sal);
        end loop;
exception
    when no_data_found then
        dbms_output.put_line('There is not such an employee!');
end;
--------------------------------
--15.输出菱形
begin
  for i in -3..3 loop
    for j in -3..3 loop
     if abs(i)+abs(j)<=3 then
       dbms_output.put('*');
     else
       dbms_output.put(' ');
     end if;
     end loop;
     dbms_output.put_line('');
  end loop;
end;
---------------------------------
--5.用键盘输入的方式给部门表插入一条记录，如果出现主键冲突的异常
--请显示‘部门号已被占用！’的字样：
declare
  str varchar2(100) := 'insert into dept values(:1,:2,:3)';
begin
  execute immediate str using &部门号,'&部门名称','&部门地址';
exception
  when DUP_VAL_ON_INDEX then
    dbms_output.put_line('部门号已被占用！');
end;

--------------------------------------
--3.输入一个数字，如果等于5，显示 ‘不能是5’ 否则就输出100：
declare
  myexception exception;
  pragma exception_init(myexception, -00001);
  n number;
begin
  n := &n;
  
  if n = 5 then
    raise myexception;
  else
    dbms_output.put_line('100');
  end if;
exception
  when myexception then
    dbms_output.put_line('not 5!');
end;

--4.编写一个PL/SQL程序，修改7369的员工工资为8000元，保证修改后的工资在工资等级允许的工资范围之内，否则取消操作（使用用户定义异常）：
--4.编写一个PL/SQL程序，修改7369的员工工资为8000元，
--保证修改后的工资在工资等级允许的工资范围之内，否则取消操作（使用用户定义异常）：
declare
  v_grade1 number;
  v_grade2 number;
  x number := 8000;
  e exception;
begin
  select max(hisal),min(losal) into v_grade1,v_grade2 from salgrade;
  if x between v_grade1 and v_grade2 then
    update emp set sal=x where empno=7369;
  else 
    raise e;
  end if;
exception
  when e then
    dbms_output.put_line('工资不在允许范围内，操作已取消！');
  when others then
    dbms_output.put_line(sqlcode||sqlerrm);
end;
---------------------------------------------------
--1.JOBS中新增加一职位。值如下：JOB_ID=99,JOB TITLE=HR,MIN_SALARY=100,MAX_SALARY=1000：
insert into jobs values ('99', 'hr', 100, 1000);
--2.市场营销岗（JOB_TITLE为‘市场营销岗’，JOB_ID为‘88’）的岗位撤销后，需要删除该岗位，并将原来属于该岗位的的员工调转到‘业务发展岗’：
declare
    v_business_job_id varchar2(10) := '89';
begin

    update employees
    set job_id = v_business_job_id
    where job_id = '88';
        delete from jobs
    where job_id ='88';
    dbms_output.put_line('success');
exception
    when no_data_found then
        dbms_output.put_line('fail');
end;
--3.创建视图 V_TEST 查询各个职位名称、平均工资、最低工资、以及最高工资和人数，按照职位名称降序排列：
create view v_test as 
select jobs.job_title,avg(e.sal),min(j.min_salary),max(j.max_salary),count(1)
from emp e join jobs no e.job_id=jobs.job_id
group by jobs.job_title
order by jobs.job_title;
--4.查询薪金比‘张三’多的所有员工信息：
select * from employees e
where sal>(select sal from employees e where employee_id='张三');
--5.哪些部门的人数比DEPARTMENT_ID为9000的人数多：
select department_id,count(employee_id)
from employees
group by department_id
having count(employee_id)>(select count(employee_id)where department_id='9000')
--6.查询哪些员工名称跟‘张三’不在同一个部门：
select * from employees
where department_id not in
(select department_id from employees where employee_name='张三')；

--3.输入一个数字，如果等于5，显示 ‘不能是5’ 否则就输出100：
declare
  n number:=&输入一个数字;                  
begin
  if n=5 then
    dbms_output.put_line('不能是5');
  else
    dbms_output.put_line(100);
  end if;
end;

--4.编写一个PL/SQL程序，修改7369的员工工资为8000元，保证修改后的工资在工资等级允许的工资范围之内，否则取消操作（使用用户定义异常）：
declare
  myexcp exception;
  grade1 number;
  grade2 number;
  se varchar2(100):='select s.grade from emp e join salgrade s on e.sal between s.losal and hisal where
  e.empno=7369';
  up varchar2(100):='update emp set sal=8000 where empno=7369';
begin
  execute immediate se into grade1;
  execute immediate up;
  execute immediate se into grade2;
  if grade1!=grade2 then
    raise myexcp;
  end if;
exception
  when myexcp then
    dbms_output.put_line('修改后的工资在工资等级不在允许的工资范围,撤回修改');
    rollback;
end;