I used the information oracle 2.2

/****************************** 1. According to department inquiries sector information *********** ID ******************/
declare
type empcur is ref cursor;
v_cur empcur;
v_type naemp% rowtype;
v_no int;
begin
v_no: = & Please enter the sector number;
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. According to the information and determine the number check wage scale *********** **************/
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
/ * The ID check salary * /
v_no: = & Please enter the sector number;
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;
/ * Determine the salary level * /
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 | | 'wage level is' | | v_grade);
fetch v_cur into v1;
end loop;
close v_cur;
end;
/***** With the exception handling of the wage scale inquiry ***********/
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: = & Please enter the sector number;
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 ('the employee's wage level is now:' | | v_level);
close v_cur;
exception
when no_data_found then
dbms_output.PUT_LINE ('Sorry! not the employee!');
when others then
dbms_output.PUT_LINE ('Unknown error!');
end;

分类:Database 时间:2010-02-11 人气:239
分享到:
blog comments powered by Disqus

相关文章

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

Copyright (C) codeweblog.com, All Rights Reserved.

CodeWeblog.com 版权所有 黔ICP备15002463号-1

processed in 0.299 (s). 12 q(s)