oracle基本存储过程

oracle基本存储过程

1.假设有两个表,职员表emp(工号eno,姓名ename,工资esal)和津贴表comm(工号eno,津贴ecomm)
要求用游标完成操作:取出职员表中工资大于1000元的记录,将其工资的30%作为津贴插入
津贴表中.

declare
      --定义游标;
      cursor c is select eno,esal*0.3 from emp where esal>1000;
      vno number;  
      vsal number;
begin
      open c;
      fetch c into vno,vsal;
      while c%found
           loop            
              insert into comm values(vno,vsal);
              fetch c into vno,vsal;
           end loop;
      close c;
end;

2.职员表emp(工号eno,姓名ename,工资esal),用pl/sql匿名块实现输入三个员工的姓名，输出此
3人的平均工资，若平均工资低于800，则所有员工加10%工资，保证此3人平均工资不低于800

declare
  vname1 varchar2(20);
  vname2 varchar2(20);
  vname3 varchar2(20);
  vsal number;
begin
  vname1:='&第1个员工姓名';
  vname2:='&第2个员工姓名';
  vname3:='&第3个员工姓名';

  select avg(esal) into vsal from emp where ename=vname1 or ename=vname2 or ename=vname3;
 
  dbms_output.put_line('此3人平均工资是'||vsal);

  while vsal<800
  loop
     update emp set esal=esal*1.1;
     select avg(esal) into vsal from emp where ename=vname1 or ename=vname2 or ename=vname3;
  end loop;

end;

3.职员表emp(工号eno,姓名ename,出生日期birthday),创建一个函数，实现根据员工编号判断
某员工是否是童工（年龄<18岁）

create or replace function isTonggong(vno varchar2) return boolean
as
  vage number;
begin
  select sysdate-birthday into vage from emp where eno=vno;
  if vage<18 then
    return true;
  else
    return false;
  end if;
end isTonggong;

4.假设有两个表,职员表emp(工号eno,姓名ename,工资esal)和津贴表comm(工号eno,津贴ecomm)，要求
在删除职员表中记录时，自动实现津贴表中对应记录的删除

create or replace trigger delemp
after delete on emp
for each row
begin
  delete from comm where eno=:old.eno;
end;

5.职员表emp(工号eno,姓名ename,出生日期birthday)，
工号 varchar2(10) 主键 由入职年份和编号构成，如'2003000001'
姓名 varchar2(20)
出生日期 date
创建一个存储过程，实现插入一个新员工

create sequence empseq
start with 1
increment by 1
maxvalue 999999
cycle
cache 20;

create or replace procedure
addemp(vno out varchar2,vname varchar2,vbirth date,result out number)
as
begin
  insert into emp
  values(extract(year from sysdate)||lpad(empseq.nextval,6,0),vname,vbirth);
  select eno into vno from emp where ename=vname;
  result:=1;
exception
  when others then
    result:=0;
end addemp;

6.创建包，里面放入第3题的函数和第5题的存储过程，再添加一个存储过程，完成按员工姓名模糊
查询员工信息
type mycur is ref cursor;

create or replace procedure
selemp(vname varchar2,vcur out mycur,result out number)
as
  vcount int;
begin
  select count(*) into vcount from emp where ename like '%'||vname||'%';
  if vcount>0 then
    open vcur for select * from emp where ename like '%'||vname||'%';
    result:=1;
  else
    result:=0;
  end if;
exception
  when others then
    result:=0;
end selemp;

7.创建java项目或web项目，实现对6题包中的存储过程的调用

追加1：
/**********************题目:新建10个用户***************/
declare
    no_max number;
begin
    select max(empno) into no_max from nemp;
    dbms_output.PUT_LINE(no_max);
    for i in no_max..no_max+9
    loop
      insert into nemp values(i,'newEmploee'||i,'养猪',2000);
    end loop;
end;
/****************题目:做个除零异常并处理******************/
declare
  dividend number;
  divisor number;
begin
  dividend:=&被除数;
  divisor:=&除数;
  dbms_output.put_line(dividend/divisor);
exception
  when zero_divide then
  dbms_output.put_line('ERROR:divisor cannot be zero!');
  when others then
  dbms_output.put_line('ERROR:plesse input again or contact the Adminisrator!');
end;



追加2：
/********************1用循环计算工资总和******************************/
declare
  type emp_cur is ref cursor;
  v_cur emp_cur;
  v_type naemp%rowtype;
  v_sum naemp.EMPSAL%type;
begin
  open v_cur for select * from naemp where empdeptno=30;
  fetch v_cur into v_type;
    while v_cur%found
      loop
        v_sum:=v_sum+v_type.empsal;
      end loop; 
  close v_cur;
end;



/***************************2用游标列出所有信息******************************/
declare
  type emp_cur is ref cursor;
  v_cur emp_cur;
  v_type naemp%rowtype;
begin
  open v_cur for select * from naemp;
  fetch v_cur into v_type;
  while v_cur%found
    loop
      dbms_output.PUT_LINE(v_type.empdeptno||' '||v_type.empno);
    end loop;
 
end;
/**********************************3用联合数组打出所有信息**********************/
declare
  type t1 is table of naemp%rowtype index by binary_integer;
  tt t1;
begin
  select empno,empname,empdeptno,empsal,empmanager  bulk collect into tt from naemp;
  for i in 1..tt.count
    loop
      dbms_output.PUT_LINE(tt(i).empno||' '||tt(i).empname||' '||tt(i).empdeptno||' '||tt(i).empsal||' '||tt(i).empmanager);
    end loop; 
end;



追加3

/******************************1.根据部门编号查询部门信息*****************************/
declare
  type empcur is ref cursor;
  v_cur empcur;
  v_type naemp%rowtype;
  v_no int;
begin
  v_no:=&请输入部门编号;
  open v_cur for select * into v_type from naemp where empno=v_no;
  fetch v_cur into v_type; 
  while v_cur%found
    loop
      dbms_output.PUT_LINE(v_type.empno||' '||v_type.empdeptno||' '||v_type.empname||' '||v_type.empsal||' '||v_type.empmanager);
      fetch v_cur into v_type; 
    end loop;
  close v_cur;
end;
/****************************2.根据编号查信息和确定工资等级*************************/
declare
  type emp_cur is ref cursor;
  v_cur emp_cur;
  v_sal naemp.EMPSAL%type;
  v_no naemp.empno%type;
  v1 nanemp%rowtype;
  v_grade varchar2(4);
begin
  /*根据编号查工资*/
  v_no:=&请输入部门编号;
  open v_cur for select empsal  from naemp where empno=v_no;
  fetch v_cur into v_sal;
  dbms_output.PUT_LINE(v_sal);
  close v_cur;
  /*确定工资等级*/
  open v_cur for select * from nanemp;
  fetch v_cur into v1;
  while v_cur%found
    loop
       v_grade:=case
                     when  v1.empsal>=10000 then 'A'
                     when  v1.empsal>=8000 then 'B'
                     when  v1.empsal>=5000 then 'C'
                     when  v1.empsal>=2000 then 'D'
                     else 'E'
                end;
      dbms_output.PUT_LINE(v1.empname||'的工资等级为'||v_grade);
      fetch v_cur into v1;
    end loop;
  close v_cur;
end;
/*****带有异常处理的工资等级查询***********/
declare
  type v_cursor is ref cursor;
  v_cur v_cursor;
  v_row naemp%rowtype;
  v_level varchar2(2);
  v_no number;
begin
  v_no:=&请输入部门编号;
  open v_cur for select empno,empname,empdeptno,empsal,empmanager from naemp where empno=v_no;
  fetch v_cur into v_row;
  if v_cur%notfound then
     raise no_data_found;
  end if;
  dbms_output.PUT_LINE(v_row.empsal);
  v_level:=case
              when v_row.empsal>=10000 then 'A'
              when v_row.empsal>=8000 then 'B'
              when v_row.empsal>=5000 then 'C'
              when v_row.empsal>=2000 then 'D'
              else 'E'
            end;
    dbms_output.PUT_LINE('该雇员目前的工资等级为:'||v_level);
    close v_cur;
exception
   when no_data_found then
     dbms_output.PUT_LINE('对不起！没有这个雇员!');
   when others then
     dbms_output.PUT_LINE('未知错误!');
end;