OIM 10g 完全删除一个帐号

最新推荐文章于 2024-08-16 07:44:14 发布

leoly521

最新推荐文章于 2024-08-16 07:44:14 发布

阅读量729

点赞数

分类专栏： IAM-Oracle IdM 文章标签： login delete user table c

本文链接：https://blog.csdn.net/leoly521/article/details/6138881

版权

IAM-Oracle IdM 专栏收录该内容

18 篇文章 2 订阅

订阅专栏

How to remove all users whose USR_LOGIN beginning with "T11111" from USR table individually?

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

   z_usr_login USR.USR_LOGIN%TYPE;
   z_total NUMBER;
   CURSOR c_user IS SELECT USR_LOGIN from USR where USR_LOGIN like 'T11111%' order by USR_LOGIN;

BEGIN

   select count(*) into z_total from USR where USR_LOGIN like 'T11111%';

   IF z_total != 0 THEN
      DBMS_OUTPUT.PUT_LINE('Starting to delete ' || z_total || ' T11111X Users ... ');
      DBMS_OUTPUT.PUT_LINE('----- ----- ----- ----- -----');

OPEN c_user;

FOR counter in 1..z_total
LOOP

            FETCH c_user INTO z_usr_login;
            delete from oiu where usr_key in (select usr_key from usr where usr_login = z_usr_login);
            delete from oio where orc_key in (select orc_key from orc,usr where orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from osi where orc_key in (select orc_key from orc,usr where orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from rcd where rce_key in (select rce_key from rce,orc,usr where rce.orc_key = orc.orc_key and orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from rch where rce_key in (select rce_key from rce,orc,usr where rce.orc_key = orc.orc_key and orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from rcu where rce_key in (select rce_key from rce,orc,usr where rce.orc_key = orc.orc_key and orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from rcb where rce_key in (select rce_key from rce,orc,usr where rce.orc_key = orc.orc_key and orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from rce where orc_key in (select orc_key from orc,usr where orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from orc where usr_key in (select usr_key from usr where usr_login = z_usr_login);
            delete from upd where upp_key in (select upp_key from upp,usr where upp.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from upp where usr_key in (select usr_key from usr where usr_login = z_usr_login);
            delete from usg where usr_key in (select usr_key from usr where usr_login = z_usr_login);
            delete from uhd where uph_key in (select uph_key from uph,usr where uph.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from uph where usr_key in (select usr_key from usr where usr_login = z_usr_login);
            delete from usr where usr_login = z_usr_login;
            commit;

END LOOP;
CLOSE c_user;

      DBMS_OUTPUT.PUT_LINE('Deletion of T11111X Users is complete. ');

   ELSE
         DBMS_OUTPUT.PUT_LINE('NO T11111X User is found. ');

END IF;

END;
/