练手、练手

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;