create table vendor_master
(
vencode varchar2(10),
venname varchar2(10),
venadd1 varchar2(10),
venadd2 varchar2(10),
venadd3 varchar2(10),
tel_no varchar2(10)
);select * from vendor_master;create table order_master
(
orderno varchar2(10),
odate date,
ostatus varchar2(10),
vencode varchar2(10),
del_date date
)begin
insert into vendor_master values('111','aaa','a','b','c','123');
insert into vendor_master values('222','bbb','a','b','c','123');
insert into vendor_master values('333','ccc','a','b','c','123');
insert into vendor_master values('444','ddd','a','b','c','123');insert into order_master values('001',(TO_DATE('01/04/2001','DD/MM/YYYY')),'p','111',(TO_DATE('01/04/2001','DD/MM/YYYY')));
insert into order_master values('002',(TO_DATE('01/04/2001','DD/MM/YYYY')),'p','333',(TO_DATE('01/04/2001','DD/MM/YYYY')));
insert into order_master values('003',(TO_DATE('01/04/2001','DD/MM/YYYY')),'c','222',(TO_DATE('01/04/2001','DD/MM/YYYY')));
end;select * from order_master;
DECLARE
a varchar2(10);
BEGIN
FOR e IN (SELECT * FROM vendor_master) LOOP
select order_master.ostatus into a from order_master where order_master.vencode = e.vencode;
if a='p' then
dbms_output.put_line('定单的名字是'||e.venname);
elsif a='c' then
dbms_output.put_line('号码为'||e.vencode||'定单已确认');
else
dbms_output.put_line('号码为'||e.vencode||'的数据不存在');
end if;
END LOOP;
END;上面的怎么做才对呢?
(
vencode varchar2(10),
venname varchar2(10),
venadd1 varchar2(10),
venadd2 varchar2(10),
venadd3 varchar2(10),
tel_no varchar2(10)
);select * from vendor_master;create table order_master
(
orderno varchar2(10),
odate date,
ostatus varchar2(10),
vencode varchar2(10),
del_date date
)begin
insert into vendor_master values('111','aaa','a','b','c','123');
insert into vendor_master values('222','bbb','a','b','c','123');
insert into vendor_master values('333','ccc','a','b','c','123');
insert into vendor_master values('444','ddd','a','b','c','123');insert into order_master values('001',(TO_DATE('01/04/2001','DD/MM/YYYY')),'p','111',(TO_DATE('01/04/2001','DD/MM/YYYY')));
insert into order_master values('002',(TO_DATE('01/04/2001','DD/MM/YYYY')),'p','333',(TO_DATE('01/04/2001','DD/MM/YYYY')));
insert into order_master values('003',(TO_DATE('01/04/2001','DD/MM/YYYY')),'c','222',(TO_DATE('01/04/2001','DD/MM/YYYY')));
end;select * from order_master;
DECLARE
a varchar2(10);
BEGIN
FOR e IN (SELECT * FROM vendor_master) LOOP
select order_master.ostatus into a from order_master where order_master.vencode = e.vencode;
if a='p' then
dbms_output.put_line('定单的名字是'||e.venname);
elsif a='c' then
dbms_output.put_line('号码为'||e.vencode||'定单已确认');
else
dbms_output.put_line('号码为'||e.vencode||'的数据不存在');
end if;
END LOOP;
END;上面的怎么做才对呢?
insert into temp123 values('bbb');
insert into temp123 values('ccc');
declare
b varchar2(100);
CURSOR C_EMP IS SELECT clog from temp123;
begin
open c_emp;
loop
FETCH C_EMP INTO b;
EXIT WHEN c_emp%NOTFOUND;
dbms_output.put_line( b );
end loop;
close c_emp;
end;
v_temp varchar2(100);
CURSOR C_EMP IS select
case ostatus when 'P' then '定单的名字是'||v.venname
when 'c' then '号码为'||o.vencode||'定单已确认'
else '号码为'||o.vencode||'的数据不存在' end temp
from order_master o right join vendor_master v on o.vencode=v.vencode;
BEGIN
open c_emp;
loop
FETCH C_EMP INTO v_temp;
EXIT WHEN c_emp%NOTFOUND;
dbms_output.put_line( v_temp );
end loop;
close c_emp;
END;结果为:
号码为111的数据不存在
号码为333的数据不存在
号码为222定单已确认
号码为的数据不存在