oralce PLSQL生成更新数据表语句

在工作中遇到更新多表的情况。数据表涉及TZZ_TZ、TZZ_OUT。

 

 通过数据之间的关系，更新数据表中SELF_NO字段信息。

 通过编写PLSQL语句：

set   serveroutput   on   size   1000000   ;

DECLARE
   selfno   NVARCHAR2 (20);
BEGIN
   FOR i IN (select zztz_id from TZZ_TZ where self_no in  (select self_no from tzz_out group by self_no having count(*)>1  ) and SPLIT_NO = '-1')
   LOOP
   dbms_output.put_line('---------begain '||i.zztz_id||'--------');
     -- selfno :=
         SELECT SUBSTR (CAST (DBMS_RANDOM.VALUE AS VARCHAR (38)), 3, 10)     GUID   into selfno        FROM DUAL;
      for j in(SELECT * FROM TZZ_TZ WHERE 1=1 Start With ZZTZ_ID = i.ZZTZ_ID Connect By Prior ZZTZ_ID = SPLIT_NO) loop


    dbms_output.put_line('UPDATE TZZ_TZ SET SELF_NO='''||selfno||''' WHERE ZZTZ_ID='||j.ZZTZ_ID||';');
    if j.STATUS_ID=2 then
     dbms_output.put_line('UPDATE TZZ_OUT SET SELF_NO='''||selfno||''' WHERE ZZTZ_ID='||j.ZZTZ_ID||';');
    end if;
    
   
   end loop;
     dbms_output.put_line('---------end '||i.zztz_id||'--------');
   
   END LOOP;
END;

输出生成的SQL语句：