ORACLE存储过程习题及答案，题目是网上找的，答案是本人自己写的，仅供参考

来源：互联网发布：owncloud nginx php 编辑：程序博客网时间：2024/04/28 20:31

建表语句：

员工表emp_test

create table emp_test(

Idnumber(5)notnullprimarykey,

Emp_id number(10)notnull,

Namevarchar2(200)notnull,

Pay number(10,2) default0,

Dept_id varchar2(20)notnull,

entryDate datedefaultsysdate

)

部门信息表dept_test

Create table dept_test(

Id number(5) not null primarykey,

Name varchar2(200) not null,

Loc varchar2(500)

)

insertinto emp_testvalues(3,10003,'李三',3500,5,to_date('20170302','yyyy/mm/dd'));

insertinto emp_testvalues(4,10004,'李四',4500,3,to_date('20170201','yyyy/mm/dd'));

insertinto emp_testvalues(5,10005,'李五',2500,4,to_date('20170102','yyyy/mm/dd'));

insertinto emp_testvalues(6,10006,'张一',5500,3,to_date('20170402','yyyy/mm/dd'));

insertinto emp_testvalues(7,10007,'张二',7500,7,to_date('20170205','yyyy/mm/dd'));

insertinto emp_testvalues(8,10008,'张三',7500,6,to_date('20170209','yyyy/mm/dd'));

insertinto emp_testvalues(9,10009,'张四',2500,8,to_date('20170112','yyyy/mm/dd'));

insertinto emp_testvalues(10,10010,'张五',12500,9,to_date('20170312','yyyy/mm/dd'));

insertinto emp_testvalues(11,10011,'白一',3500,10,to_date('20170209','yyyy/mm/dd'));

insertinto emp_testvalues(12,10012,'白二',6500,4,to_date('20170112','yyyy/mm/dd'));

insertinto emp_testvalues(13,10013,'白三',5500,9,to_date('20170310','yyyy/mm/dd'));

insertinto emp_testvalues(14,10014,'白四',8500,7,to_date('20170412','yyyy/mm/dd'));

insertinto emp_testvalues(15,10015,'白五',4500,10,to_date('20170212','yyyy/mm/dd'));

（1）创建一个存储过程，以员工号为参数，输出该员工的工资

create or replace procedure EMP_NUM_PAY(

e_id in emp_test.emp_id%type,

a_pay outemp_test.pay%type

)

e_pay integer :=0;

begin

select pay intoe_pay from emp_test where emp_id = e_id;

end;

--（1）执行

declare

emp_idemp_test.emp_id%type := 10001;

begin

a_pay := e_pay;

dbms_output.put_line(e_id || ' 员工的工资为：' ||e_pay);

end;

end EMP_NUM_PAY;

（2）创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，

--则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；

--若属于其他部门，则增加300。

createorreplaceprocedureupdate_emp_pay(e_idininteger)as

E_dept_id integer;

E_pay varchar2(200);

begin

Select dept_id

Into e_dept_id

From emp_testwhere emp_id=e_id;

Select payinto e_pay fromemp_test where emp_id=e_id;

dbms_output.put_line(e_id || '员工的工资调整前为：' || e_pay);

end;

begin

If e_dept_id =1 then

Update emp_testset pay=(pay+150)where emp_id=e_idand dept_id=E_dept_id;

Commit;

Elsif e_dept_id =2then

Update emp_testset pay=(pay+200)where emp_id=e_idand dept_id=E_dept_id;

Commit;

Elsif e_dept_id =3then

Update emp_testset pay=(pay+250)where emp_id=e_idand dept_id=E_dept_id;

Commit;

Else

Update emp_testset pay=(pay+300)where emp_id=e_idand dept_id=E_dept_id;

Commit;

Endif;

End;

Begin

Select payinto e_pay fromemp_test where emp_id=e_id;

dbms_output.put_line(e_id || '员工的工资调整后为：' || e_pay);

End;

End update_emp_pay;

（3）创建一个存储过程，以员工号为参数，返回该员工的工作年限（以参数形式返回）。

create or replace procedureemp_work_year(e_id in integer,work_year out integer)is

Begin

Select round((sysdate - entrydate),1) into work_year fromemp_test where emp_id=e_id;

end;

End emp_work_year;

Sql界面执行：

declare

e_idemp_test.emp_id%type :=10003;

v_year number;

begin

emp_work_year(e_id,v_year);

dbms_output.put_line(e_id || '工作年限为 ' || v_year ||'天');

end;

（4）创建一个存储过程，以部门号为参数，输出入职日期最早的10个员工信息。

Create or replace emp_first(d_id in integer, cur_arg out sys_refcursor) is

Begin

open cur_arg for select *from emp_test whererownum<11 and dept_id=d_idorderby paydesc;

End;

End emp_first;

declare

cur_calling sys_refcursor;

rec_next emp_test%rowtype;

begin

emp_first(3,cur_calling);--这样这个游标就有值了

loop

fetch cur_calling

into rec_next;

exitwhen cur_calling%notfound;

dbms_output.put_line('----------------:'|| rec_next.name ||'的工资为：'||rec_next.pay);

endloop;

closecur_calling;

end;

（5）创建一个函数，以员工号为参数，返回该员工的工资。

createorreplacefunctionemp_pay(e_idinnumber)Returnnumber is

E_pay number;

Begin

begin

Select payinto E_pay from emp_testwhere emp_id=e_id;

return E_pay;

end;

End emp_pay;

（6）创建一个函数，以部门号为参数，返回该部门的平均工资。

Create orreplace function emp_dept_pay(d_id in number) return number is

Result number;

Begin

Select dept_id,avg(nvl(pay,0)) intoresult from emp_test where dept_id=d_id;

Endemp_dept_pay;

（7）创建一个函数，以员工号为参数，返回该员工所在的部门的平均工资。

create orreplace function emp_dept_pay(d_id in number) return number is

Result number;

Begin

Select avg(nvl(pay,0)) into result from emp_test where dept_id=d_id;

return result;

Endemp_dept_pay;

（8）创建一个存储过程，以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号。

--如果修改成功，则显示“员工由……号部门调入……号部门”；

--如果不存在该员工，则显示“员工号不存在，请输入正确的员工号。”；

--如果不存在该部门，则显示“该部门不存在，请输入正确的部门号。”。

createorreplaceprocedureemp_change_dept(e_idinteger,d_id integer) is

D_num integer;

Dd_id integer;

Begin

Select dept_idinto dd_id fromemp_test where emp_id=e_id groupby dept_id;

EXCEPTION

WHEN NO_DATA_FOUNDTHEN

dbms_output.put_line(e_id||'员工号不存在，请输入正确的员工号');

Return;

End;

begin

Select idinto d_num fromdept_test where id=d_id;

EXCEPTION

WHEN NO_DATA_FOUNDTHEN

Dbms_output.put_line(d_id||'该部门不存在，请输入正确的部门号');

Return;

End;

Begin

Update emp_testset dept_id=d_id whereemp_id=e_id;

Ifsql%rowcount =0then

Dbms_output.put_line('部门变更失败');

Else

Dbms_output.put_line('员工由'||dd_id||'号部门调入'||d_id||'号部门');

Endif;

End;

End emp_change_dept;

（9）创建一个存储过程，以一个整数为参数，输入工资最高的前几个（参数值）员工的信息。

create or replace procedure emp_max_pay(numinteger) as

Begin

For Iin (select * from (select * from emp_test order by pay desc) whererownum<=num)

Loop

Dbms_output.put_line(i.name||'的薪资为'||i.pay);

Endloop;

End emp_max_pay;

Create or replace procedure emp_max_pay(numinteger, cur_arg outsys_refcursor) as

Begin

Opencur_arg for select * from (select * fromemp_test order by pay desc) where rownum<=num;

End emp_max_pay;

declare

cur_calling sys_refcursor;

rec_next emp_test%rowtype;

begin

emp_max_pay(3,cur_calling);--这样这个游标就有值了

loop

fetch cur_calling

into rec_next;

exitwhen cur_calling%notfound;

dbms_output.put_line('----------------:'|| rec_next.name ||'的工资为：'||rec_next.pay);

endloop;

closecur_calling;

end;

（10）创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。

createor replace procedure emp_range_pay(max_num integer,min_num integer)as

Begin

For Iin (select * from emp_test where pay between min_num and max_num order by paydesc)

Loop

Dbms_output.put_line(i.name||'的薪资为'||i.pay);

Endloop;

End emp_range_pay;

Create or replace procedure emp_range_pay(max_numinteger,min_num integer, cur_arg out sys_refcursor) as

Begin

Opencur_arg for select * from emp_test wherepay between min_num and max_num order by pay desc;

End emp_range_pay;

declare

cur_calling sys_refcursor;

rec_next emp_test%rowtype;

begin

emp_max_pay(3,cur_calling); --这样这个游标就有值了

loop

fetch cur_calling

into rec_next;

exitwhen cur_calling%notfound;

dbms_output.put_line('----------------:'|| rec_next.name ||'的工资为：'||rec_next.pay);

end loop;

close cur_calling;

end;

0 0