CH05_过程||函数练习

--1：创建一个可以向dept表中插入一行的过程

create or replace procedure p_in_dept(p_no in number,p_name in varchar2,p_loc in varchar2) is
begin
  insert into dept values(p_no,p_name,p_loc);
end;

--2:创建一个过程，传入雇员名，返回该雇员的薪水（薪金+佣金）

create or replace procedure pro_sals(
  p_name in emp.ename%type,p_sal out emp.sal%type) is

begin
  select sal+NVL(comm,0) into p_sal from emp where ename= p_name;
  exception
    when no_data_found then
    dbms_output.put_line('no_data_found');
    when others then
    dbms_output.put_line('数据操作有误！');
end;

declare
  v_sal emp.sal%type;
begin
  pro_sals('&p_name',v_sal);
  dbms_output.put_line('薪资为：'||v_sal);
end;

--3：创建一个过程，传入雇员号，和薪水及增长比例（10%=0.1）。其中薪水为in out参数！
--更新薪水为新薪水用一个PL/SQL程序 块来调用此过程，其传入到过程中的参数都是用户输入得到的

create or replace procedure p_increase_sal(
  p_name in emp.ename%type,
  p_sal in out emp.sal%type,
  increase in emp.sal%type) is
begin
  update emp set sal = p_sal *( 1 + increase) where ename = p_name;
  commit;
  select sal into p_sal from emp where ename = p_name;
exception
  when no_data_found then
    dbms_output.put_line('no_data_found');
  when others then
    dbms_output.put_line('数据操作有误！');
end;

declare
  v_name emp.ename%type := '&p_name';
  v_sal emp.sal%type := &sal;
  v_add emp.sal%type :=&increase;
begin
  p_increase_sal(v_name,v_sal,v_add);
  dbms_output.put_line('增长后的薪资为：'||v_sal);
end;

--4：编写一个函数，以deptno为标准，返回此部门所有雇员的整体薪水

create or replace function p_sals return 
number is
cursor cur_emp is select deptno,sum(sal+NVL(comm,0)) sal from emp group by deptno;
begin
  for emp_record in cur_emp loop 
  dbms_output.put_line(emp_record.deptno||'部门的薪水是：'||emp_record.sal);
  end loop;
return 0;
exception
  when no_data_found then
    dbms_output.put_line('no_data_found');
  when others then
    dbms_output.put_line('数据操作有误！');
end;

declare
 num number;
begin
 num :=  p_sals;
end;

--5：编写一个函数，接收deptno，和name（out），返回此部门的名称和地址

create or replace function p_sals(p_dno dept.deptno%type) return 
dept%rowtype is
  v_dept dept%rowtype;
begin
  select * into v_dept from dept where deptno = p_dno;
return v_dept;
exception
  when no_data_found then
    dbms_output.put_line('no_data_found');
  when others then
    dbms_output.put_line('数据操作有误！');
end;

declare
  v_dept dept%rowtype;
begin
 v_dept := p_sals(10);
 dbms_output.put_line(v_dept.dname||'在'||v_dept.loc);
end;

--6；编写一个过程以显示所指定雇员名的雇员部门名和位置

create or replace procedure p_show(p_name emp.ename%type) is
v_ename dept.dname%type;
v_loc dept.loc%type;
begin
  select dname,loc into v_ename,v_loc from emp,dept where emp.deptno = dept.deptno and ename = p_name;  
  dbms_output.put_line(p_name||'在'||v_loc);
end;

begin
p_show('SMITH');
end;

--7；编写一个给特殊雇员加薪10%的过程，这之后，检查如果已经雇佣该雇员超过了60个月，则给他额外加薪300。

create or replace procedure p_add_sal is
cursor cur_emp is select * from emp for update nowait;
begin
  for emp_record in cur_emp loop
    update emp set sal = sal + sal * 0.1;
    if months_between(sysdate,emp_record.hiredate)>60 then
    update emp set sal = sal + sal * 0.1;
    end if;
  end loop;
end;

begin
  p_add_sal;
end;

8：编写一个函数一检查所指定雇员的薪水是否在有效范围内，不同职位的薪水范围为：
clerk 1500-2500
salesman 2501-2500
analyst 3501-4500
others 4501-n
如果薪水在此范围内，则显示消息“salaly is ok!” ，否则，更新薪水为该范围内的最小值

create or replace function p_check_sal(p_eno emp.ename%type) return char is
  v_sal emp.sal%type;
  v_job emp.job%type;
  v_msg char(50);
begin
  select job,sal into v_job,v_sal from emp where empno = p_eno;
  if v_job = 'CLERK' then
    if v_sal>=1500 and v_sal <=2500 then 
      v_msg := 'salaly is ok!';
    else
      v_sal := 1500;
      v_msg := 'salaly has updated!';
    end if;
  elsif v_job = 'SALESMAN' then
    if v_sal>=2501 and v_sal <=3500 then 
      v_msg := 'salaly is ok!';
    else
      v_sal := 2501;
      v_msg := 'salaly has updated!';
    end if;
  elsif v_job = 'ANALYST' then
    if v_sal>=3501 and v_sal <=4500 then 
      v_msg := 'salaly is ok!';
    else
      v_sal := 3501;
      v_msg := 'salaly has updated!';
    end if;
  else 
    if v_sal>=4501 then 
    v_msg := 'salaly is ok!';
    else 
      v_sal := 4501;
      v_msg := 'salaly has updated!';
    end if;
  end if;
  update emp set sal = v_sal where empno = p_eno;
  return v_msg;
end;

declare
  v_msg char(50);
begin
  v_msg := p_check_sal(7369);
  dbms_output.put_line(v_msg);
end;
`