表a:字段a_id 、a_company
表b:字段b_id 、b_values
字段都是varchar2类型,oracle 9i
目的:从表a中取出a_company的值,做一个判断,然后根据判断结果在表b中插入相应的数据。CREATE OR REPLACE Procedure aaa as
x_company varchar2(12);
x_id varchar2(6);
cursor csor_aaa is select t.a_id,t.a_company from 表a t where t.a_company like '001%';
begin
open csor_aaa;
fetch csor_aaa into x_id,x_company;
while csor_company%found loop
if substr(x_company,7,2)=10 then
insert into 表b y (b_id,b_values) values ('&x_id','000001');
elseif substr(x_company,7,2)>10 and substr(x_company,7,2)<30 then
insert into 表b y (b_id,b_values) values ('&x_id','000002');
elseif substr(x_company,7,2)=30 then
insert into 表b (b_id,b_values) values ('&x_id','000003');
end if
fetch csor_aaa into x_id,x_company;
end loop;
close csor_aaa;
commit;
end aaa;
表b:字段b_id 、b_values
字段都是varchar2类型,oracle 9i
目的:从表a中取出a_company的值,做一个判断,然后根据判断结果在表b中插入相应的数据。CREATE OR REPLACE Procedure aaa as
x_company varchar2(12);
x_id varchar2(6);
cursor csor_aaa is select t.a_id,t.a_company from 表a t where t.a_company like '001%';
begin
open csor_aaa;
fetch csor_aaa into x_id,x_company;
while csor_company%found loop
if substr(x_company,7,2)=10 then
insert into 表b y (b_id,b_values) values ('&x_id','000001');
elseif substr(x_company,7,2)>10 and substr(x_company,7,2)<30 then
insert into 表b y (b_id,b_values) values ('&x_id','000002');
elseif substr(x_company,7,2)=30 then
insert into 表b (b_id,b_values) values ('&x_id','000003');
end if
fetch csor_aaa into x_id,x_company;
end loop;
close csor_aaa;
commit;
end aaa;
select t.a_id,
case when substr(t.a_company,7,2) =10 then '000001'
when substr(t.a_company,7,2)>10 and substr(t.a_company,7,2)<30 then '000002'
when substr(t.a_company,7,2)=30 then '000003' end
from 表a t where t.a_company like '001%';
我看到你前面的帖子好像是想用变量的
when then
...
end
也可以判断很多阿
我不太明白你的意思
就用游标好了你也可以两种方法都用一下
正好可以比较一下
那个效率更高就用哪个了
x_company varchar2(12);
x_user_id char(6);
x_user_zwjb char(2);
x_1 char(2);
x_2 char(2);
x_description varchar2(80);
cursor csor_company is select t.user_id,t.company,t.zwjb,t.description from ry_jbxx t where t.company like '3601%';
begin
open csor_company;
fetch csor_company into x_user_id,x_company,x_user_zwjb,x_description;
while csor_company%found loop
x_1:=substr(x_company,7,2);
x_2:=substr(x_company,9,2);
x_description:=trim(description);
if x_1='00' then
insert into yhglb y (yhbid,yhgljsid) values (x_user_id,'000008');
insert into yhglb y (yhbid,yhgljsid) values (x_user_id,'000405');
----------------------------------------------------------------------------
elseif to_number(x_1)>'50' and zwjb='40' then
insert into yhglb y (yhbid,yhgljsid) values (x_user_id,'000004');
elseif to_number(x_1)>'50' and x_2='00' and zwjb='50' then
insert into yhglb y (yhbid,yhgljsid) values (x_user_id,'000003');
elseif to_number(x_1)>'50' and x_2='01' and zwjb='50' then
insert into yhglb y (yhbid,yhgljsid) values (x_user_id,'000050');
insert into yhglb y (yhbid,yhgljsid) values (x_user_id,'000002');
elseif to_number(x_1)>'50' and x_2='02' and zwjb='50' then
insert into yhglb y (yhbid,yhgljsid) values (x_user_id,'000050');
insert into yhglb y (yhbid,yhgljsid) values (x_user_id,'000001');
end if; fetch csor_rybh_hh into x_user_id,x_company,x_user_zwjb,x_description;
end loop;
close csor_company;
commit;
end User_JueSe;
if x_description='电脑' then
begin
aa:='电脑';
if aa='电脑' then
dbms_output.put_line('电脑' );
end if;
end;