--table
create table student(stu_id number(8),stu_name varchar2(128));
create table place(p_id number(5),contain_num number(3)); create table stu_place(stu_id number(8),p_id number(3));--pl/sql script
set serveroutput on
declare
cursor cur_place is select p_id,contain_num from place order by p_id;
cursor cur_student is select stu_id from student order by stu_id;
place_rec cur_place%rowtype;
stuid number(8);
num number(3):=0;
place_id number(5);
flag number(1):= 0;
begin
open cur_student;
open cur_place;
loop
fetch cur_student into stuid;
flag := 1;
exit when cur_student%notfound;
if(num =0) then
fetch cur_place into place_rec;
flag := 2;
exit when cur_place%notfound;
num := place_rec.contain_num;
place_id := place_rec.p_id;
end if;
insert into stu_place(stu_id,p_id) values(stuid,place_id);
num := num - 1;
end loop;
close cur_place;
close cur_student;
if(flag = 1) then
dbms_output.put_line('分配成功');
else
dbms_output.put_line('座位不够');
end if;
end;
自己插入数据试一下.
create table student(stu_id number(8),stu_name varchar2(128));
create table place(p_id number(5),contain_num number(3)); create table stu_place(stu_id number(8),p_id number(3));--pl/sql script
set serveroutput on
declare
cursor cur_place is select p_id,contain_num from place order by p_id;
cursor cur_student is select stu_id from student order by stu_id;
place_rec cur_place%rowtype;
stuid number(8);
num number(3):=0;
place_id number(5);
flag number(1):= 0;
begin
open cur_student;
open cur_place;
loop
fetch cur_student into stuid;
flag := 1;
exit when cur_student%notfound;
if(num =0) then
fetch cur_place into place_rec;
flag := 2;
exit when cur_place%notfound;
num := place_rec.contain_num;
place_id := place_rec.p_id;
end if;
insert into stu_place(stu_id,p_id) values(stuid,place_id);
num := num - 1;
end loop;
close cur_place;
close cur_student;
if(flag = 1) then
dbms_output.put_line('分配成功');
else
dbms_output.put_line('座位不够');
end if;
end;
自己插入数据试一下.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货