储存过程--创建用户账号和密码

Create or Replace procedure zxqynwpt3."pro_zjtxxjr_cjjgzh" is
  num int;
  bmid varchar2(32);
  yhid varchar2(32);

begin
	/**对到期的认定进行失效处理，并发送一条消息。*/
	for cur in (select * from a_xzqh) loop
		--建市机构部门用户
		if(cur.szs <>'—' and cur.szs<>'-' and (cur.szq ='—' or cur.szq='-')) then
  			select count(1) into num from t_xt_unit  where unitcode='1'||cur.s;
  			if num=0 then--没有机构
  				if cur.szs in('市辖区','省直辖','县') then
  					insert into t_xt_unit ("UNITCODE","UNITNAME","UNITREGION","UNITROOTCODE","UNITPARENTCODE","UNITISROOT","UNITLEVELCODE",
  					"UNITISVALIDE","UNITNOTE","UNITHAVECHILD","UNITORDER","GXSJ","BBH","JHZT","UNITJC","UNITWSJG",
  					"UNITDZ","UNITYB","UNITJGLX","UNITCODELINK","UNITOWNER","UNITSHOW") 
  					values ('1'||cur.s,cur.szs,cur.s,null,'1'||cur.sf,'0', '0503', '0', null, null, 3,sysdate, null, null, null, null, null, null, '1','100000000'||'1'||cur.sf||'1'||cur.s, null, null);
  					commit;
  				else
  					insert into t_xt_unit ("UNITCODE","UNITNAME","UNITREGION","UNITROOTCODE","UNITPARENTCODE","UNITISROOT","UNITLEVELCODE",
  					"UNITISVALIDE","UNITNOTE","UNITHAVECHILD","UNITORDER","GXSJ","BBH","JHZT","UNITJC","UNITWSJG",
  					"UNITDZ","UNITYB","UNITJGLX","UNITCODELINK","UNITOWNER","UNITSHOW") 
  					values ('1'||cur.s,cur.szs,cur.s,null,'1'||cur.sf,'0', '0503', '0', null, null, 3,sysdate, null, null, null, null, null, null, '3','100000000'||'1'||cur.sf||'1'||cur.s, null, null);
  					select to_char(sys_guid()) into bmid from dual;
  					insert into t_xt_dept(jg_id,jg_dm,jg_mc,yx_bj,sc_bj,jgbh,gxsj)
  					values(bmid,'1'||cur.s||'0001','业务部门','Y','N','1'||cur.s,sysdate);
  					select to_char(sys_guid()) into yhid from dual;
  					insert into t_xt_yh(yh_id,yh_dm,yh_mc,mm,xb,yx_bj,sc_bj,userjgbh,gxsj)
  					values(yhid,cur.yhdm,cur.szs||'_审核人','gxb@12381','0','Y','N','1'||cur.s,sysdate);
  					insert into T_XT_DEPT_YH(jg_id,yh_id,gxsj)
  					values(bmid,yhid,sysdate);
  					insert into T_XT_YH_GNJS(js_dm,yh_id,gxsj)
  					values('032',yhid,sysdate);
  					commit;
  				end if;
  			end if;
  		elsif cur.szq <>'—' and cur.szq<>'-' then
  			--建区县机构部门用户
  			select count(1) into num from t_xt_unit  where unitcode='1'||cur.q;
  			if num=0 then--没有机构
				insert into t_xt_unit ("UNITCODE","UNITNAME","UNITREGION","UNITROOTCODE","UNITPARENTCODE","UNITISROOT","UNITLEVELCODE",
				"UNITISVALIDE","UNITNOTE","UNITHAVECHILD","UNITORDER","GXSJ","BBH","JHZT","UNITJC","UNITWSJG",
				"UNITDZ","UNITYB","UNITJGLX","UNITCODELINK","UNITOWNER","UNITSHOW") 
				values ('1'||cur.q,cur.szq,cur.q,null,'1'||cur.s,'0', '0504', '0', null, null, 3,sysdate, null, null, null, null, null, null, '3','100000000'||'1'||cur.sf||'1'||cur.s||'1'||cur.q, null, null);
				select to_char(sys_guid()) into bmid from dual;
				insert into t_xt_dept(jg_id,jg_dm,jg_mc,yx_bj,sc_bj,jgbh,gxsj)
				values(bmid,'1'||cur.q||'0001','业务部门','Y','N','1'||cur.q,sysdate);
				select to_char(sys_guid()) into yhid from dual;
				insert into t_xt_yh(yh_id,yh_dm,yh_mc,mm,xb,yx_bj,sc_bj,userjgbh,gxsj)
				values(yhid,cur.yhdm,cur.szq||'_审核人','gxb@12381','0','Y','N','1'||cur.q,sysdate);
				insert into T_XT_DEPT_YH(jg_id,yh_id,gxsj)
				values(bmid,yhid,sysdate);
  				insert into T_XT_YH_GNJS(js_dm,yh_id,gxsj)
  				values('032',yhid,sysdate);
				commit;
  			end if;
  		end if;
  	end loop;

end;