练手、练手

最新推荐文章于 2024-05-22 19:07:29 发布

最新推荐文章于 2024-05-22 19:07:29 发布 · 84 阅读

本文介绍了一个使用PL/SQL实现的员工招聘过程自动化程序。该程序通过两个游标分别获取部门需求和申请者信息，并根据申请者的志愿及部门需求进行匹配，最终更新招聘结果到数据库中。当第一志愿部门名额已满时，程序会尝试将申请者匹配到第二志愿。

create or replace procedure p_employee_hire(resultinfo out testpackage.test_cursor)
is
d_department_name varchar2(20);
d_requirement_num number;
d_re number:=0;
sd_re number:=0;
u_user_id varchar2(20);
u_user_point number(3,1);
u_first_wish varchar2(20);
u_second_wish varchar2(20);
--r_department_name varchar2(20);
--r_hire_user_in varchar2(20);
cursor department is select requirement_num,department_name from t_department_requirements for update;
cursor userinfo is select user_id,user_point,first_wish,second_wish from t_user_apply_info order by user_point desc;
begin

open department;
open userinfo;
loop
fetch userinfo into u_user_id,u_user_point,u_first_wish,u_second_wish;
exit when userinfo%notfound;
--判断志愿并插入数据：
select requirement_num,department_name into d_requirement_num,d_department_name from t_department_requirements where department_name=u_first_wish;
if d_requirement_num>0 then
insert into t_result_info values(u_first_wish,u_user_id,u_user_point);
update t_department_requirements set requirement_num=requirement_num-1 where department_name=u_first_wish;
end if;
if d_requirement_num<=0 then
select requirement_num,department_name into d_requirement_num,d_department_name from t_department_requirements where department_name=u_second_wish;
if d_requirement_num>0 then
insert into t_result_info values(u_second_wish,u_user_id,u_user_point);
update t_department_requirements set requirement_num=requirement_num-1 where department_name=u_second_wish;
end if;
end if;
end loop;
Exception
when No_data_found then
DBMS_OUTPUT.PUT_LINE('没有找到数据');
when others then
DBMS_OUTPUT.PUT_LINE('操作异常');
open resultinfo for select * from t_result_info;
close department;
close userinfo;
end;