如 create or replace procedure sp_update(A in table.pkid%type) is --定义变量和游标 rs table.b%type; cursor cur is select b from table where pkid=A for update; begin open cur; loop fetch cur into rs; exit when cur%notfound; if rs.b =1 then Update table Set B=2 where current of cur; end if; end loop; close cur; end sp_update;
--你是要根据参数A查询出的字段B全部是1才将其更新为2还是什么哦?需求有点模糊 -- create or replace procedure sp_update(A in table.pkid%type) is --定义变量 cnt number; begin select count(*) into cnt from table where pkid=A and b!=1; if cnt=0 THEN --cnt=0说明字段B全部是1 update table Set B=2 ; --这里看你是全表更新还是只更新where pkid=A end; end sp_update;
CREATE OR REPLACE PROCEDURE P_RE(A IN table1.pkid%TYPE) IS TYPE T IS TABLE OF table1.pkid%TYPE; T1 T; BEGIN SELECT B BULK COLLECT INTO T1 FROM TABLE1 WHERE PKID = A; FOR I IN 1 .. T1.COUNT LOOP IF T1(I) = 1 THEN UPDATE TABLE1 SET B = 2; END IF; COMMIT; END LOOP;END;
根据参数A查询出的字段B全部是1才执行更新语句 更新语句 是更新另外一张表的字段 ,我没写清楚update 表2 Set 字段=2 where 字段2=参数A
create or replace procedure sp_update(A in table.pkid%type) is --定义变量 cnt number; begin select count(*) into cnt from table where pkid=A and b!=1; if cnt=0 then --cnt=0说明字段B全部是1 update 表2 set 字段=2 where 字段2=A ; end if; end sp_update;
CREATE OR REPLACE PROCEDURE P_RE(A IN table1.pkid%TYPE) IS TYPE T IS TABLE OF table1.pkid%TYPE; T1 T; BEGIN SELECT B BULK COLLECT INTO T1 FROM TABLE1 WHERE PKID = A; FOR I IN 1 .. T1.COUNT LOOP IF T1(I) = 1 THEN UPDATE TABLE1 SET B = 2 WHERE PKID=A;--添加更新条件 END IF; COMMIT; END LOOP;END;
如
create or replace procedure sp_update(A in table.pkid%type)
is
--定义变量和游标
rs table.b%type;
cursor cur is select b from table where pkid=A for update;
begin
open cur;
loop
fetch cur into rs;
exit when cur%notfound;
if rs.b =1 then
Update table Set B=2 where current of cur;
end if;
end loop;
close cur;
end sp_update;
--你是要根据参数A查询出的字段B全部是1才将其更新为2还是什么哦?需求有点模糊
--
create or replace procedure sp_update(A in table.pkid%type)
is
--定义变量
cnt number;
begin
select count(*) into cnt from table where pkid=A and b!=1;
if cnt=0 THEN --cnt=0说明字段B全部是1
update table Set B=2 ; --这里看你是全表更新还是只更新where pkid=A
end;
end sp_update;
TYPE T IS TABLE OF table1.pkid%TYPE;
T1 T;
BEGIN
SELECT B BULK COLLECT
INTO T1
FROM TABLE1
WHERE PKID = A;
FOR I IN 1 .. T1.COUNT
LOOP
IF T1(I) = 1 THEN
UPDATE TABLE1
SET B = 2;
END IF;
COMMIT;
END LOOP;END;
create or replace procedure sp_update(A in table.pkid%type)
is
--定义变量
cnt number;
begin
select count(*) into cnt from table where pkid=A and b!=1;
if cnt=0 then --cnt=0说明字段B全部是1
update 表2 set 字段=2 where 字段2=A ;
end if;
end sp_update;
TYPE T IS TABLE OF table1.pkid%TYPE;
T1 T;
BEGIN
SELECT B BULK COLLECT
INTO T1
FROM TABLE1
WHERE PKID = A;
FOR I IN 1 .. T1.COUNT
LOOP
IF T1(I) = 1 THEN
UPDATE TABLE1
SET B = 2
WHERE PKID=A;--添加更新条件
END IF;
COMMIT;
END LOOP;END;