Oracle练习题（九）

（1） 创建一个存储过程，以员工号为参数，输出该员工的工资

--创建存储过程

create or replace procedure print_sal(n number) as

  val_sal emp.sal%type;

begin 

  select emp.sal into val_sal from emp where empno = n;

  dbms_output.put_line(n||'号员工的工资为：'||val_sal);

end;

--执行

exec print_sal(7499);

（2） 创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；若属于其他部门，则增加300。

--创建存储过程

create or replace procedure pro_update_sal(n number) as

  val_deptno emp.deptno%type;

begin  

  select deptno into val_deptno from emp where empno=n;

  case val_deptno

    when 10 then 

      update emp set sal=sal+150 where empno = n;

    when 20 then 

      update emp set sal=sal+200 where empno = n;

    when 30 then 

      update emp set sal=sal+250 where empno = n;

    else

      update emp set sal=sal+300 where empno = n;

  end case;

end;

--执行

 exec pro_update_sal(7499);

（3） 创建一个程，以员工号为参数，返回该员工的工作年限（以参数形式返回）。

--创建存储过程

create or replace procedure pro_return_year(n number,yeartime out varchar2) as

begin

  select to_char(sysdate,'YYYY')-to_char(emp.hiredate,'YYYY') into yeartime from emp where empno = n;

end;

--声明变量，接受工作年限的值

var a varchar2;

--执行

exec pro_return_year(7499,:a);

（4） 使用无参游标cursor，查询所有员工的姓名和工资

--创建游标。1创建游标2打开游标3存取游标4关闭游标

create or replace procedure pro_select_name_and_sal as

 cursor cur_emp is select ename,sal from emp;

 val_name emp.ename%type;

 val_sal emp.sal%type;

begin 

  open cur_emp;

  fetch cur_emp into val_name,val_sal;

  while cur_emp%found loop

    dbms_output.put_line('姓名:'||val_name||'工资:'||val_sal);

    fetch cur_emp into val_name,val_sal;

  end loop;

  close cur_emp;

end;

--执行

exec pro_select_name_and_sal;

（5） 创建语句级触发器，当对emp表进行delete操作后，显示"world hello"

--创建触发器

create or replace trigger tri_delete after delete on emp 

begin

  dbms_output.put_line('world hello');

end; 



--删除时触发

delete from emp where deptno = 10; 

（6） 周一到周五，且9-17点能向数据库插入数据，否则显示异常提示

--创建触发器

create or replace trigger tri_check before insert or delete or update on emp

begin

  if to_char(sysdate,'d') in ('1','7') then

    raise_application_error(-20000,'不能在非工作日更改员工信息，上班时间为周一到周五9-17点');

  elsif to_char(sysdate,'hh24:mi:ss') not  between '9:00:00' and '17:00:00' then

    raise_application_error(-20000,'不能在非上班时间更改员工信息，上班时间为周一到周五9-17点');

  end if;

end;



--删除时触发

delete from emp where deptno = 10; 

（7） 创建行级触发器，涨后工资这一列，确保大于涨前工资

--创建行级触发器

create or replace trigger tri_test before update on emp for each row 

begin 

  if :old.sal>:new.sal then 

    raise_application_error(-20001,'更新失败，涨后工资小于涨前工资!');

  end if;

end;


--触发

update emp set sal = sal-100 where empno = 7499;