oracle我以前的资料2.2
程序员文章站
2022-03-28 23:29:17
...
/******************************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;
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;
上一篇: 百度的致命抓取异常?你重视过吗?
下一篇: 判断excel行是否全部为空 excel