oracle复习二

1：给 scott.dept创建公有同义词syn_dept，使用同义词-查询部门编码、部门名称和地点
 CREATE public SYNONYM dept_syn FOR SCOTT.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),saname varchar(20)/
insert into student(sid,sname) values(seq_student.nextval,'张')

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

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:=7788;

begin
  select deptno,sal,job into v_deptno,v_sal,v_job from emp where empno=7788 ;
  if v_job<'MANAGER'  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)。
(for循环)
declare
  cursor cursor_emp(inputsal number) is
  select empno,ename,sal from emp where sal>inputsal;
  empno emp.empno%type;
  ename emp.ename%type;
  sal emp.sal%type;
  begin
    sal:=&sal;
    for rec in cursor_emp(sal) loop
      dbms_output.put_line('编号：'||rec.empno||'姓名：'||rec.ename||'工资：'||rec.sal);
      end loop;
      end;
显示游标
declare
  cursor cursor_emp(inputsal number) is
  select empno,ename,sal from emp where sal>inputsal;
  empno emp.empno%type;
  ename emp.ename%type;
  sal emp.sal%type;
  begin
    sal:=&inputsal;
    open cursor_emp(sal);
    loop
    fetch cursor_emp into empno,ename,sal;
    exit when cursor_emp%NOTFOUND;
    dbms_output.put_line('编号：'||empno||'姓名：'||ename||'工资：'||sal);
    end loop;
    close cursor_emp;
    end;

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

8创建程序包，包含一个过程和一个函数。
过程：计算每个员工的年薪并输出。
create or replace procedure SALCHANGE 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 SALCHANGE;
函数：以部门编号为参数，返回该部门的平均工资。
create or replace function avgsal(deptno in number) return integer
 As
  v_sal emp.sal%type;
  v_ename  emp.ename%type;
begin
  select ename  into v_ename  from emp where sal=(select avg(sal) from emp where deptno=deptno);
    dbms_output.put_line(v_ename||'的平均工资为：'||v_sal); 
  return v_ename;
end avgsal;