如果a1为null时更新a1字段为v,如果不为空值更新为a1+1 create or replace procedure sp_test(v int) is begin update ttt set a1=decode(a1,null,v,a1+1); end sp_test;
连个退出条件都不给,不是让人写死循环嘛。其实没太明白楼主的意思,记录存在时更新不?要是记录存在不更新,那不就是插入新记录了?不管了,做了一个函数,不断去更新,除非记录没查到。create function test( al in varchar2) return number as v_temp varchar2(100) begin while 1<>2 loop begin select column into v_temp from table where column = al; exception when NO_DATA_FOUND then return 1; end; update table set column = XX; al := al + 1; end; 大概就这样吧。 好久没写了。
最基本的语句: update table1 set col1=(a1或者a1+1) where col2='xx'
粗写了一个,没有试,楼主可以自己试试CREATE OR REPLACE PROCEDURE P_CHANGE IS V_SQL VARCHAR2(600); BEGIN V_SQL:='alter table1 add(flag VARCHAR2(1))'; EXECUTE IMMEDIATE V_SQL; V_SQL:='update table1 set col1=a1+1,flag=''1'' where col2=''xx'' and col1=a1'; EXECUTE IMMEDIATE V_SQL; COMMIT; V_SQL:='update table1 set col1=a1 where col2=''xx'' and col1<>a1' and flag=''1''; EXECUTE IMMEDIATE V_SQL; COMMIT; V_SQL:='alter table1 drop column flag'; EXECUTE IMMEDIATE V_SQL; END; / CALL P_CHANGE(); / drop procedure P_CHANGE /
create or replace procedure pro_t1 IS cursor cur_t1 IS select * from t1 where col2='aa'; begin for get_cur_t1 in cur_t1 loop if get_cur_t1=='aa' then update t1 set col1:='xx'; elsif update t1 set aa:=aa||1; end if; end loop; end; /这是我做的一个不成熟的程序 大家看看 给修改一下吧
create or replace procedure sp_test(v int) is
begin
update ttt set a1=decode(a1,null,v,a1+1);
end sp_test;
al in varchar2)
return number
as
v_temp varchar2(100)
begin
while 1<>2
loop
begin
select column into v_temp from table where column = al;
exception
when NO_DATA_FOUND
then
return 1;
end;
update table set column = XX;
al := al + 1;
end; 大概就这样吧。 好久没写了。
一张表:table1,其中有两个字段:col1,col2
有一个值a1根据条件col2='xx'查询出来一条记录在table1中
如果col1中不存在a1这个值,直接更新这条记录
如果col1中已经存在a1这个值,则将a1+1,然后更新这条记录不知道各位明白了吗?
update table1 set col1=(a1或者a1+1) where col2='xx'
IS
V_SQL VARCHAR2(600);
BEGIN
V_SQL:='alter table1 add(flag VARCHAR2(1))';
EXECUTE IMMEDIATE V_SQL; V_SQL:='update table1 set col1=a1+1,flag=''1'' where col2=''xx'' and col1=a1';
EXECUTE IMMEDIATE V_SQL;
COMMIT; V_SQL:='update table1 set col1=a1 where col2=''xx'' and col1<>a1' and flag=''1'';
EXECUTE IMMEDIATE V_SQL;
COMMIT; V_SQL:='alter table1 drop column flag';
EXECUTE IMMEDIATE V_SQL;
END;
/ CALL P_CHANGE();
/ drop procedure P_CHANGE
/
IS
cursor cur_t1 IS
select * from t1 where col2='aa';
begin
for get_cur_t1 in cur_t1 loop
if get_cur_t1=='aa' then
update t1 set col1:='xx';
elsif
update t1 set aa:=aa||1;
end if;
end loop;
end;
/这是我做的一个不成熟的程序 大家看看 给修改一下吧