oracle复习二

1：给 scott.dept创建公有同义词syn_dept，使用同义词查询部门编码、部门名称和地点
     创建公有同义词:create public synonym syn_dept for scott.dept
     查询:select deptno,dname,loc from syn_dept

2：创建序列seq_student,初始值是1000.增量为5，最大值为2000，循环，缓存为10个序列号
    create sequence seq_student
      start with 1000
      increment by 5
      maxvalue 2000
      cache 10

3:创建student表，字段名为sid,number(4),sname,sid的值通过序列seq_student获得
    create table student(sid number(4),sname varchar(10))
    insert into student values(seq_student.nextval,'瑞马')

4:基于emp表创建视图含20号部门的员工编号、姓名、年薪 （view20_emp）。
    create or replace view View20_emp as
      select empno,ename,sal from emp
      where deptno=20
      with check option constraint chk_View20

5：采用PL/SQL编程，在 emp表中，如果部门号 （deptno）为30，且工作(job)为’MANAGER’ 的员工，则工资(sal)提高50%，如果部门号（deptno）为30，且工资job不为‘MANAGER ‘,则工资(sal) 提高 10%,其它员工工资提高20%.
declare
   v_deptno   emp.deptno%type;
   v_sal   emp.sal%type;
   v_job  emp.job%type;
   v_empno emp.empno%type:=&empno;
begin
  select deptno,sal,job into v_deptno,v_sal,v_job from emp where empno=&empno;
  if v_job='MANAGER' and v_deptno='30' then
    update emp set sal=sal*1.5;
  elsif v_deptno='30' and v_job!='MANAGER'  then
    update emp set sal=sal*1.1;
  else
    update emp set sal=sal*1.2;
  end if;
  end;

6.使用显式游标，根据员工工资（sal）参数，查询员工表emp中工资大于等于该参数的员工信息，显示内容包括员工编码(empno)、姓名(ename)、工资(sal)。
declare 
  cursor emp_cursor(sl number) is select empno,ename,sal from emp where sl>sal;
    v_empno emp.empno%type;
    v_ename emp.ename%type;
    v_sal   emp.sal%type;
  v_ss emp.sal%type;
  begin
     v_ss:=2000;
     open emp_cursor(v_ss);
   
    loop
     fetch emp_cursor into v_empno,v_ename,v_sal;
     exit when emp_cursor%notfound;
      dbms_output.put_line(v_empno||':'||v_ename||':'||v_sal);
    end loop;
     close emp_cursor;
  end;

for循环：
declare 
  cursor s(sl number) is select empno,ename,sal from emp where sal>sl;
    empno emp.empno%type;
    ename emp.ename%type;
    sal emp.sal%type;
    begin
      sal:=&sal;
      for rec in s(sal)loop
        dbms_output.put_line('员工编码:'||rec.empno);
        dbms_output.put_line('姓名:'||rec.ename);
        dbms_output.put_line('工资:'||rec.sal);
        end loop;
      end;

7.使用for循环游标查询并打印薪水大于2000并且职位是’manager’ 的员工编号(empno)、员工姓名(ename)、员工职位 （job）、员工薪水(sal)。
declare 
  cursor emp_cursor is select empno,ename,sal,job from emp where sal>2000 and job='MANAGER';
begin
for emp_record in emp_cursor
  loop
    dbms_output.put_line('姓名是:'||emp_record.ename||'的，职位是：'||emp_record.job||'的，工资是:'||emp_record.sal);
  end loop;
end;

8：创建程序包，包含一个过程和一个函数。
过程：计算每个员工的年薪并输出。
函数：以部门编号为参数，返回该部门的平均工资。
 
函数:
create or replace function sss(ndeptno number) return number  is
 v_sal emp.sal%type;
begin
  select avg(sal) into v_sal  from emp where deptno=ndeptno;
  return v_sal;
end sss;
过程:
create or replace procedure ssss is
cursor cursor_salchange is select ename,sal*12 from emp;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  open cursor_salchange;
  loop
    fetch cursor_salchange into v_ename,v_sal;
    exit when cursor_salchange%NOTFOUND;
    dbms_output.put_line(v_ename||':'||v_sal);
  end loop;
  close cursor_salchange;
end ssss;
包规范：
create or replace package h is
function sss(ndeptno number) return number;
procedure ssss;
end h;
包主体：create or replace package body h is
function sss(ndeptno number) return number  is
 v_sal emp.sal%type;
begin
  select avg(sal) into v_sal  from emp where deptno=ndeptno;
  return v_sal;
end sss;
 procedure ssss is
cursor cursor_salchange is select ename,sal*12 from emp;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  open cursor_salchange;
  loop
    fetch cursor_salchange into v_ename,v_sal;
    exit when cursor_salchange%NOTFOUND;
    dbms_output.put_line(v_ename||':'||v_sal);
  end loop;
  close cursor_salchange;
end ssss;
end h;

 

9：在EMP表的sal列上创建触发器，当修改sal列的值时自动触发触发器，显示是加薪还是减薪
create or replace trigger emp_tri
after update of sal on emp
for each row
when (new.sal <> old.sal)
declare
    sal_diff number;
begin
    sal_diff := :new.sal-:old.sal;
    if sal_diff > 0 then
       dbms_output.put_line(:new.ename||'加薪了');
    else
       dbms_output.put_line(:new.ename||'减薪了');
    end if;
End  emp_trigger;