Oracle classic uses cursor stored procedures

Create or replace procedure createmanageruser as v_certificatenumber nvarchar2 (100); -- ID card number (ID card) v_accessionstate integer; -- active state -- cursor field value (annotation field not used) v_userid varchar2 (40); v_loginname nvarchar2 (40); v_password nvarchar2 (40); -- v_userdesc nvarchar2 (100); v_usertype integer; v_personid varchar2 (40); v_builddate limit (23 ); v_startdate nvarchar2 (23); -- v_enddate nvarchar2 (23); v_islock integer; v_neverused integer; v_lastchanger nvarchar2 (40); -- v_useritem1 nvarchar2 (40); 1_nvarchar2 (23 ); -- v_useritem2 nvarchar2 (40); -- v_useritem3 nvarchar2 (40); -- v_useritem4 nvarchar2 (40); -- v_useritem5 nvarchar2 (40); -- v_useritem6 nvarchar2 (40 ); v_tmp_loginname nvarchar2 (40); -- temporary variable v_no integer for an existing login name; -- assembly line v_count integer; -- I integer; -- rule: the supervisor cursor manager_to_create_cur is select sys_guid (), loginname, password, usertype, personid, builddate, startdate, islock, neverused, lastchanger, lastchangedate, certificatenumber, accessionstate from (select distinct Replace (lower (lastname | '. '| firstname), '','') as loginname, nvl (substr (PSN. certificatenumber, length (PSN. certificatenumber)-5), '000000') as password, '0' as usertype, PSN. personid, to_char (sysdate, 'yyyy-mm-dd') as builddate, to_char (sysdate, 'yyyy-mm-dd') as startdate, '0' as islock, '1' as neverused, 'sa 'As lastchanger, to_char (sysdate, 'yyyy-mm-dd') as lastchangedate, PSN. certificatenumber, PSN. accessionstate from psnaccount PSN -- where PSN. personid = '083435d45ef749d1be0c9d6121b95a1e '); -- (used for testing, replace the following conditions) Inner join orststdstruct ORG on PSN. personid = org. director and org. label like '200' where 1 = 1 and PSN. lastname is not null and PSN. firstname is not null and PSN. accessionstate in ('1', '2') and PSN. personid not in (select personid from secuser inner join (select distinct director from orstdstruct) dir on personid = Director); begin -- set serveroutput on; -- open the cursor if manager_to_create_cur % isopen = false then open manager_to_create_cur; end if; -- I: = 1; loop v_loginname: = ''; -- I: = I + 1; -- merge (I); fetch into v_userid, v_loginname, v_password, v_usertype, v_personid, v_builddate, v_startdate, v_islock, v_neverused, v_lastchanger, begin, complete, v_accessionstate; exit when manager_to_create_cur % notfound; -- if the same logon name already exists select count (*) into v_count from secuser left join psnaccount on psnaccount. personid = secuser. personid where loginname = v_loginname; If (v_count> 0) Then v_no: = 1; v_tmp_loginname: = v_loginname | v_no; select count (*) into v_count from secuser left join psnaccount on psnaccount. personid = secuser. personid where loginname = v_tmp_loginname; -- cyclically determines whether the pipeline number exists. If so, it increments while v_count> 0 loop v_no: = v_no + 1; v_tmp_loginname: = v_loginname | v_no; select count (*) into v_count from secuser left join psnaccount on psnaccount. personid = secuser. personid where loginname = v_tmp_loginname; end loop; v_loginname: = v_tmp_loginname; end if; insert into login (userid, loginname, password, usertype, personid, builddate, startdate, islock, neverused, lastchanger, lastchangedate) values (v_userid, v_loginname, v_password, v_usertype, v_personid, v_builddate, v_startdate, v_islock, iterator, v_lastchanger, iterator); End loop; commit; -- close cursor; exception when no_data_found then dbms_output.put_line ('error code: '| to_char (sqlcode); dbms_output.put_line ('error message:' | sqlerrm); rollback; when too_many_rows then dbms_output.put_line ('error code: '| to_char (sqlcode); dbms_output.put_line ('error message:' | sqlerrm); rollback; when others then dbms_output.put_line ('error code: '| to_char (sqlcode); dbms_output.put_line ('error message:' | sqlerrm); rollback; end;