create or replace procedure p_lx is
begin
UPDATE AB01_QHD1 t set t.w =1 where t.name in (select t.name from AB01_QHD1 t,AA01_QHD2 t2 where t.name=t2.name);
commit;end p_lx;怎么用游标的方式实现上面的过程 把sql语句换成游标的形式
begin
UPDATE AB01_QHD1 t set t.w =1 where t.name in (select t.name from AB01_QHD1 t,AA01_QHD2 t2 where t.name=t2.name);
commit;end p_lx;怎么用游标的方式实现上面的过程 把sql语句换成游标的形式
不到万不得已,别用游标,很影响性能。
你这个根本不需要要用到游标。--采用update语句
create or replace procedure p_lx
is
begin update AB01_QHD1 t
set t.w = 1
where exists(select null from AA01_QHD2 t2 where t2.name = t1.name);
commit;end p_lx;
--2.采用for循环
create or replace procedure p_lx
is
begin for res in (select name from AA01_QHD2) loop
update AB01_QHD1 set w = 1 where name = res.name;
end loop;
commit;
end p_lx;
--1.采用update语句
create or replace procedure p_lx
is
begin update AB01_QHD1 t
set t.w = 1
where exists(select null from AA01_QHD2 t2 where t2.name = t.name);
commit;end p_lx;
CURSOR C_EMP IS --声明显式游标
SELECT name FROM AA01_QHD2;
C_ROW C_EMP%ROWTYPE; --定义游标变量,该变量的类型为基于游标C_EMP的记录
begin --For 循环
FOR C_ROW IN C_EMP LOOP
UPDATE AB01_QHD1 t set t.w = 1 where t.name = C_ROW.NAME;
commit;
END LOOP; --Fetch 循环
OPEN C_EMP; --必须要明确的打开和关闭游标
LOOP
FETCH C_EMP
INTO C_ROW;
EXIT WHEN C_EMP%NOTFOUND;
UPDATE AB01_QHD1 t set t.w = 1 where t.name = C_ROW.NAME;
commit;
END LOOP;
CLOSE C_EMP; --While 循环
OPEN C_EMP; --必须要明确的打开和关闭游标
FETCH C_EMP
INTO C_ROW;
WHILE C_EMP%FOUND LOOP
UPDATE AB01_QHD1 t set t.w = 1 where t.name = C_ROW.NAME;
commit;
FETCH C_EMP
INTO C_ROW;
END LOOP;
CLOSE C_EMP;end p_lx;