Share the two most recently written SQL statements

1. Building a basic environment

Inserting test data

Insert into JGDM (JGDM,JGMC) VALUES (' 12300000000 ', ' Henan province ');
Insert into JGDM (JGDM,JGMC) VALUES (' 12300000005 ', ' Henan province Zhengzhou ');
Commit


Insert into JGDM (JGDM,JGMC) VALUES (00300000000, ' Chongqing ');
Insert into JGDM (JGDM,JGMC) VALUES (00300000001, ' Zhongqing Shizhu ');
Insert into JGDM (JGDM,JGMC) VALUES (00300000004, ' Chongqing Qijiang District ');
Commit

2. Sub-Situation Update form

Declare
Begin
For V_JGMC in (select JGMC from JGDM) loop
Dbms_output.put_line (' Update the relevant information of the province ');
If InStr (V_JGMC.JGMC, ' province ') <> 0 and (InStr (V_JGMC.JGMC, ' County ') =0 or InStr (V_JGMC.JGMC, ' district ') =0 and InStr (V_JGMC.JG MC, ' city ') <>0) then

--1. Update the Organization Code
--To "| | v_jgmc.jgmc| | ' Province ' and no ' city ' JGJCDM updated to 00
Execute immediate ' update JGDM set jgjcdm= ' xx ' Where InStr (' ' | | v_jgmc.jgmc| | "', ' province ') <>0 and InStr (" | | v_jgmc.jgmc| | ', ' city ', ' =0 ';
--To "| | v_jgmc.jgmc| | ' Province ' and ' city ' JGJCDM updated to 01
Execute immediate ' update JGDM set jgjcdm= ' Where InStr (' ' | | v_jgmc.jgmc| | "', ' province ') <>0 and InStr (" | | v_jgmc.jgmc| | ', ' city ', ' <>0 ';
--2. Update Parent Agency Code
--To "| | v_jgmc.jgmc| | ' Province ' and no ' city ' SJJGDM update for their JGDM
Execute immediate ' update JGDM set sjjgdm= (select JGDM from JGDM where InStr (' | | v_jgmc.jgmc| | "', ' province ') <>0 and InStr (" | | v_jgmc.jgmc| | "," city ") =0 and InStr (" ' | | v_jgmc.jgmc| | ", JGJC) <>0) ';
--To "| | v_jgmc.jgmc| | ' Province ' and ' city ' SJJGDM updated for their JGDM
--execute immediate ' update JGDM set sjjgdm= (select JGDM from JGDM where InStr (' | | v_jgmc.jgmc| | "', ' province ') <>0 and InStr (" | | v_jgmc.jgmc| | ', ' city ' <>0) ';
--3. Update agency abbreviation
--To "| | v_jgmc.jgmc| | ' Province ' and no ' city ' JGJC update for own ' | | v_jgmc.jgmc| | "
Execute immediate ' update JGDM set jgjc= ' | | v_jgmc.jgmc| | "where InStr (" | | v_jgmc.jgmc| | "', ' province ') <>0 and InStr (" | | v_jgmc.jgmc| | ', ' city ', ' =0 ';
--To "| | v_jgmc.jgmc| | ' Province ' and ' city ' JGJC updated to their own city
Execute immediate ' update JGDM set jgjc= substr (' | | v_jgmc.jgmc| | ", InStr (" | | v_jgmc.jgmc| | "', ' province ') +1,length (" ' | | v_jgmc.jgmc| | ") -instr ("' | | v_jgmc.jgmc| | "," province ")) where InStr (" ' | | v_jgmc.jgmc| | "', ' province ') <>0 and InStr (" | | v_jgmc.jgmc| | ', ' city ', ' <>0 ';
Commit
End If;

Dbms_output.put_line (' Update the relevant information of the municipality ');
If InStr ("' | | | v_jgmc.jgmc| | "', ' province ') =0 and (InStr (" | | v_jgmc.jgmc| | ', ' county ') <>0 or InStr ("' | | v_jgmc.jgmc| | ' ', ' district ') <>0 and InStr ("| | v_jgmc.jgmc| | ", ' city ') <>0) then
--Dbms_output.put_line (v_ "| | v_jgmc.jgmc| | "." v_jgmc.jgmc| | ");
--1. Update the Organization Code
--To "| | v_jgmc.jgmc| | ' There is no ' province, county, district ' and ' City ' JGJCDM updated to 00
Execute immediate ' update JGDM set jgjcdm= ' xx ' Where InStr (' ' | | v_jgmc.jgmc| | "', ' province ') =0 and InStr (" | | v_jgmc.jgmc| | ', ' County ') =0 and InStr ("| | v_jgmc.jgmc| | ' ', ' district ') =0 and InStr ("| | v_jgmc.jgmc| | ', ' city ', ' <>0 ';
--To "| | v_jgmc.jgmc| | ' City, county, district ' and no ' province ' JGJCDM update to 01
Execute immediate ' update JGDM set jgjcdm= ' Where (InStr ("' | | v_jgmc.jgmc| | ', ' county ') <>0 or InStr ("' | | v_jgmc.jgmc| | ' ', ' district ') <>0) and InStr ("' | | v_jgmc.jgmc| | ', ' city ', ' <>0 ';
--2. Update Parent Agency Code
--To "| | v_jgmc.jgmc| | ' city ' and no ' province, county, district ' SJJGDM update for their JGDM
Execute immediate ' update JGDM T2 set sjjgdm= (select JGDM from JGDM t1 where (InStr ("| | v_jgmc.jgmc| | "', ' province ') =0 and InStr (" | | v_jgmc.jgmc| | ', ' County ') =0 and InStr ("| | v_jgmc.jgmc| | ' ', ' district ') =0) and InStr ("' | | v_jgmc.jgmc| | ', ' city ' <>0) ';
--To "| | v_jgmc.jgmc| | ' Province ' and ' city ' JGJC updated to their own city
Execute immediate ' update JGDM set jgjc= substr (' | | v_jgmc.jgmc| | ", InStr (" | | v_jgmc.jgmc| | ', ' city ') +1,length ("' | | v_jgmc.jgmc| | ") -instr ("' | | v_jgmc.jgmc| | "," city ")) Where InStr (" ' | | v_jgmc.jgmc| | "', ' province ') =0 and InStr (" | | v_jgmc.jgmc| | ', ' city ', ' <>0 ';
--3. Update agency abbreviation
--To "| | v_jgmc.jgmc| | ' city ' and no ' county ', ' district ', ' Province ' and JGJC update for own ' | | v_jgmc.jgmc| | "
Execute immediate ' update JGDM set jgjc= ' | | v_jgmc.jgmc| | "where InStr (" | | v_jgmc.jgmc| | "," city ") <>0 and (InStr (" | | v_jgmc.jgmc| | ', ' County ') =0 and InStr ("| | v_jgmc.jgmc| | ' ', ' district ') =0) ';
--To "| | v_jgmc.jgmc| | ' city ' and ' County ' district ' JGJC updated to their own city
Execute immediate ' update JGDM set jgjc= substr (' | | v_jgmc.jgmc| | ", InStr (" | | v_jgmc.jgmc| | ', ' city ') +1,length ("' | | v_jgmc.jgmc| | ") -instr ("' | | v_jgmc.jgmc| | "," city ")) Where InStr (" ' | | v_jgmc.jgmc| | "," city ") <>0 and (InStr (" | | v_jgmc.jgmc| | ', ' county ') <>0 or InStr ("' | | v_jgmc.jgmc| | ' ', ' district ') <>0) ';
Commit

End If;
End Loop;
End

Bad writing, Hope forgive me

Share the two most recently written SQL statements