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;