CREATE OR REPLACE PROCEDURE restoreorg_disable_FK(ownerx  in varchar2, tblname in varchar2, n_enable in number)IS--类型定义cursor c_constraintisselect y.CONSTRAINT_NAME, y.TABLE_NAME from dba_constraints y where y.R_CONSTRAINT_NAME =(select x.CONSTRAINT_NAME from dba_constraints x where x.TABLE_NAME = upper(tblname) and x.CONSTRAINT_TYPE = 'P'  and x.owner = upper(ownerx)) ;--定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型c_row c_constraint%rowtype;cursor d_constraintisselect x.CONSTRAINT_NAME, x.TABLE_NAME from dba_constraints x where x.TABLE_NAME = upper(tblname) and x.CONSTRAINT_TYPE = 'P'  and x.owner = upper(ownerx);--定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型d_row d_constraint%rowtype;v_sql varchar2(3999);BEGINDBMS_OUTPUT.ENABLE (buffer_size=>null);for c_row in c_constraint loopdbms_output.put_line(c_row.CONSTRAINT_NAME||'-'||c_row.TABLE_NAME);if n_enable = 1 thenv_sql:= ' alter table '||ownerx||'.'||c_row.TABLE_NAME||' enable constraint '|| c_row.CONSTRAINT_NAME ;--打开外键dbms_output.put_line('打开外键'|| v_sql);execute immediate (v_sql);end if;if n_enable = 0 thenv_sql:= ' alter table '||ownerx||'.'||c_row.TABLE_NAME||' drop  constraint '|| c_row.CONSTRAINT_NAME ;--关掉外键dbms_output.put_line('关掉外键'|| v_sql);execute immediate (v_sql);end if;end loop;for d_row in d_constraint loopdbms_output.put_line(d_row.CONSTRAINT_NAME||'-'||d_row.TABLE_NAME);if n_enable = 1 thenv_sql:= ' alter table '||ownerx||'.'||d_row.TABLE_NAME||' enable constraint '|| d_row.CONSTRAINT_NAME ;--打开外键dbms_output.put_line('打开外键'|| v_sql);execute immediate (v_sql);end if;if n_enable = 0 thenv_sql:= ' alter table '||ownerx||'.'||d_row.TABLE_NAME||' drop  constraint '|| d_row.CONSTRAINT_NAME ;--关掉外键dbms_output.put_line('关掉外键'|| v_sql);execute immediate (v_sql);end if;end loop;END restoreorg_disable_FK; CREATE OR REPLACE PROCEDURE dropOrgTbl(ownerx in varchar2)IS-- declare fixed arrayTYPE arry_var IS VARRAY(30) OF VARCHAR2(32);tblnames arry_var;v_sql varchar2(3999);tblname varchar2(32);tblexist number;--tblexist1 number;--set serveroutput on size 10000000000;BEGINDBMS_OUTPUT.ENABLE (buffer_size=>null);-- init arraytblnames := arry_var('org_department','org_user','org_group','org_duty','org_job','org_department_principal','org_dept_principal','org_department_role','org_department_user_job','org_user_job','org_user_leader','org_user_privilege','org_user_role','org_job_leader','org_user_property','org_user_login_log','org_job_function','org_group_nested_group','org_employee','org_employee_job','org_duty_agent','org_department_function','org_department_employee_job','org_group_user','org_group_member','org_group_job','org_group_duty','org_group_department','org_duty_role','org_job_role');--dbms_output.put_line(tblname(1));--dbms_output.put_line(tblname(2));for i in 1..tblnames.countlooptblname := tblnames(i);select count(1) into tblexist from all_tables t where t.TABLE_NAME = upper(tblname) and  t.OWNER = upper(ownerx);if tblexist = 1 then  restoreorg_disable_FK(upper(ownerx), tblname, 0);  v_sql:= ' drop table '||upper(ownerx)||'.'||tblname;  dbms_output.put_line(v_sql);  execute immediate (v_sql);end if;if tblexist=0 thendbms_output.put_line(upper(ownerx)||'.'||tblname ||' 不存在');end if;-- || ' create table '||tblname(i)||'_bak as select * from '||tblname(i)||';';end loop;--select * from all_tables where owner = 'ADM_JYJ'EXCEPTION  WHEN OTHERS THEN    dbms_output.put_line(sqlerrm);--显示异常信息    dbms_output.put_line(dbms_utility.format_error_backtrace);END dropOrgTbl;        create or replace procedure createTenantDbUser iscursor c_tenant is  select t.jdbc_username, t.JDBC_PASSWORD from ADM_OA_03.MT_TENANT t where t.status = 1;c_row c_tenant%rowtype;userexist number;v_sql varchar2(3999);beginDBMS_OUTPUT.ENABLE (buffer_size=>null);dbms_output.put_line('-----------------');for c_row in c_tenant loopselect count(1) into userexist from dba_users t where t .username = upper(c_row.jdbc_username);dbms_output.put_line('--------create---------' || c_row.jdbc_username);if userexist < 1 then   v_sql:='create user '||  c_row.jdbc_username              || ' identified by '||  c_row.JDBC_PASSWORD               ||' default tablespace OA_CLOUD '               ||' temporary tablespace TEMP '               ||'  profile DEFAULT '               ||' quota unlimited on oa_cloud ';   execute immediate (v_sql);                             v_sql:=' grant connect to  '         ||c_row.jdbc_username;    execute immediate (v_sql);    v_sql:=           ' grant exp_full_database to   ' || c_row.jdbc_username;     execute immediate (v_sql);    v_sql:=          ' grant imp_full_database to '  ||c_row.jdbc_username;     execute immediate (v_sql);     v_sql:=          ' grant resource to  ' ||c_row.jdbc_username;      execute immediate (v_sql);     v_sql:=          ' grant debug connect session to ' ||c_row.jdbc_username;      execute immediate (v_sql);     v_sql:=        ' grant unlimited tablespace to '   ||c_row.jdbc_username;  execute immediate (v_sql);             dbms_output.put_line('----create user ok----' || c_row.jdbc_username);  end if;if userexist > 0 then   dbms_output.put_line('----------' || c_row.jdbc_username || 'has exist');     end if;end loop;EXCEPTION  WHEN OTHERS THEN    dbms_output.put_line(sqlerrm);--显示异常信息    dbms_output.put_line(dbms_utility.format_error_backtrace);end createTenantDbUser;     create or replace procedure  dropAllorgtbl iscursor c_tenant is  select t.jdbc_username, t.JDBC_PASSWORD from ADM_OA_03.MT_TENANT t where t.status = 1 and  t.id not  in ('T024', 'T001', 'T008');c_row c_tenant%rowtype;begin  for c_row in c_tenant loop    droporgtbl(c_row.jdbc_username);   end loop;end  dropAllorgtbl;  create or replace procedure dropAllUser iscursor c_tenant is  select t.jdbc_username, t.JDBC_PASSWORD from ADM_OA_03.MT_TENANT t where t.status = 1 and  t.id not  in ('T024', 'T001', 'T008');c_row c_tenant%rowtype;v_sql varchar2(3999);begin  for c_row in c_tenant loop    --droporgtbl(c_row.jdbc_username);    v_sql :='drop user '||c_row.jdbc_username||' cascade';     execute immediate (v_sql);     dbms_output.put_line('----drop user ok----' || c_row.jdbc_username);   end loop;end dropAllUser;   create or replace procedure AssignWfPrivilege iscursor c_tenant is  select t.jdbc_username, t.JDBC_PASSWORD from ADM_OA_03.MT_TENANT t where t.status = 1;c_row c_tenant%rowtype;v_sql varchar2(3999);begin  for c_row in c_tenant loop      v_sql:='grant select on ADM_OA_03.wf_gz_data_sync TO '|| c_row.jdbc_username;       execute immediate (v_sql);       v_sql:='grant update on ADM_OA_03.wf_gz_data_sync TO '|| c_row.jdbc_username;        execute immediate (v_sql);        v_sql:='grant delete on ADM_OA_03.wf_gz_data_sync TO '|| c_row.jdbc_username;         execute immediate (v_sql);         v_sql:='grant insert on ADM_OA_03.wf_gz_data_sync TO '|| c_row.jdbc_username;          execute immediate (v_sql);                    v_sql:='grant select on ADM_OA_03.wf_gz_data_sync_his TO '|| c_row.jdbc_username;       execute immediate (v_sql);       v_sql:='grant update on ADM_OA_03.wf_gz_data_sync_his TO '|| c_row.jdbc_username;        execute immediate (v_sql);        v_sql:='grant delete on ADM_OA_03.wf_gz_data_sync_his TO '|| c_row.jdbc_username;         execute immediate (v_sql);         v_sql:='grant insert on ADM_OA_03.wf_gz_data_sync_his TO '|| c_row.jdbc_username;          execute immediate (v_sql);end loop;end AssignWfPrivilege;