创建环境使用的存储过程

create or replace procedure sp_create_env(
tablespace_name in varchar2 DEFAULT 'DEMO',
username in varchar2 DEFAULT 'test',
password in varchar2 DEFAULT 'test',
table_name in varchar2 DEFAULT 'test'
/*
table in format 'tablename1,tablename2,tablename3.... 最大支持16个表
*/
) 
authid current_user  as
 v_tablespace_name varchar2(30);--表空间名
 v_username  varchar2(30);--用户名
 v_tablename varchar2(500);--表名
      v_flag number(10,0);
 v_flag_user number(10,0);
      v_sqlfalg varchar(200);
 v_envflag number(2);
 ex_1 exception;
 
FUNCTION checkosenv RETURN number IS
envflag number(1);
resl varchar2(400);
sqlstr varchar2(400);
BEGIN
sqlstr:='select username from user_users';
execute immediate sqlstr into resl;
if resl='SYS' or resl='SYSTEM'  then
envflag:=1;
RETURN envflag;
else
envflag:=0;
RETURN envflag;
end if;


END;  
 
FUNCTION GET_TBSFLAG(p_tbs_name VARCHAR2) RETURN number IS
    flag number(10,0);
str_sql varchar(200);
BEGIN
flag:=0;
    str_sql:= 'select count(*) from dba_tablespaces where tablespace_name='''||UPPER(p_tbs_name)||'''';
execute immediate str_sql into flag;
    RETURN flag;
END;


FUNCTION GET_USERLAG(p_username VARCHAR2) RETURN number IS
    flag number(10,0);
str_sql varchar(200);
BEGIN
flag:=0;
    str_sql:= 'select count(*) from dba_users where username='''||UPPER(p_username)||'''';
execute immediate str_sql into flag;
    RETURN flag;
END;


FUNCTION GET_OMF RETURN number IS
omfflag number(1);
file_dest varchar2(400);
sqlstr varchar2(400);
BEGIN
sqlstr:='select value from v$parameter where name=''db_create_file_dest'' ';
execute immediate sqlstr into file_dest;
if file_dest is null then
omfflag:=0;
RETURN omfflag;
else
omfflag:=1;
RETURN omfflag;
end if;

END;


FUNCTION FILEPATH(gtbsname varchar2) RETURN varchar2 IS
gfile_dest varchar2(400);
sqlstr varchar2(400);
BEGIN
sqlstr:='select value from v$parameter where name=''db_create_file_dest'' ';
execute immediate sqlstr into gfile_dest;
if gfile_dest is null then
sqlstr:='SELECT SUBSTR(name, 1, INSTR(name, ''/'', -1) - 1)||''/'' AS FILE_NAME from v$datafile where rownum=1';
execute immediate sqlstr into gfile_dest;
gfile_dest:=gfile_dest||gtbsname||'01.dbf';
RETURN gfile_dest;
else
RETURN gfile_dest;
end if;
END;




PROCEDURE newobject(
ntablespace_name varchar2,
nusername varchar2,
npassword varchar2,
ntable_name varchar2
)  is
gomfflag number;
num number;
file_dest varchar2(400);
BEGIN
  /*file_dest:= '+DATA';*/
  /*gomfflag=GET_OMF();*/
  file_dest:=FILEPATH(ntablespace_name);
  
  execute immediate 'create tablespace '||ntablespace_name ||' datafile '''||file_dest||''' ';
  execute immediate 'create user '||nusername||' identified by '||npassword ||' default tablespace '||ntablespace_name ||' ';
  execute immediate 'grant connect,resource to '||nusername ||'  ';
  num:=0;
  for rec_num in
      (
      select regexp_substr(COLUMN1,'[^,]+',1,rownum) COLUMN1
         from
            (
            select ntable_name COLUMN1
               from dual
            )
         connect by rownum<=length(COLUMN1)-length(replace(COLUMN1,','))+1
      )
   loop
num:=num+1;
if num=17 then
dbms_output.put_line('DO not support the 17th table');  
EXIT;
end if;

execute immediate 'create table  '||nusername ||'.'||rec_num.COLUMN1 ||' ( id number CONSTRAINT id_con_'||rec_num.COLUMN1||' NOT NULL,name VARCHAR2(40),PRIMARY KEY (id) )  ';
execute immediate 'insert into '||nusername ||'.'||rec_num.COLUMN1 ||' values (1,''aaa'') ';
execute immediate 'insert into '||nusername ||'.'||rec_num.COLUMN1 ||' values (2,''bbb'') ';
execute immediate 'insert into '||nusername ||'.'||rec_num.COLUMN1 ||' values (3,''ccc'') ';
execute immediate 'insert into '||nusername ||'.'||rec_num.COLUMN1 ||' values (4,''ddd'') ';
execute immediate 'insert into '||nusername ||'.'||rec_num.COLUMN1 ||' values (5,''eee'') ';
execute immediate 'insert into '||nusername ||'.'||rec_num.COLUMN1 ||' values (6,''fff'') ';
execute immediate 'insert into '||nusername ||'.'||rec_num.COLUMN1 ||' values (7,''jjj'') ';
execute immediate 'insert into '||nusername ||'.'||rec_num.COLUMN1 ||' values (8,''hhh'') ';
execute immediate 'insert into '||nusername ||'.'||rec_num.COLUMN1 ||' values (9,''kkk'') ';
commit;
   end loop;
END;


begin
  v_envflag:=checkosenv();
  if v_envflag !=1 then
/*----dbms_output.put_line('当前用户没有创建表空间的权限');  采取这行的话 是不会退出程序的*/
/*RAISE_APPLICATION_ERROR(-20001,'当前用户不是DBA权限，请使用SYS或者SYSTEM用户创建'e'); /* 这样的话 报错之后 是会退出程序的*/
raise ex_1;
  end if;
  v_flag:=0;
  v_flag_user:=0;
      v_tablename:=table_name;
 v_tablespace_name:=UPPER(tablespace_name);
 v_username:=UPPER(username);
 
      v_flag:=GET_TBSFLAG(v_tablespace_name);
 v_flag_user:=GET_USERLAG(v_username);
 if v_flag=1 then  --如果有这个表空间 则去删除,然后再创建
execute immediate 'drop tablespace '||v_tablespace_name ||'  including contents and datafiles';
 if v_flag_user=1 then  --如果有这个用户 则去删除
execute immediate 'drop user '||v_username ||'  cascade';
 end if;
 if v_flag_user=0 then
dbms_output.put_line('表空间中没有目标用户');
 end if;
newobject(v_tablespace_name,v_username,password,v_tablename);
 end if;
 
      if v_flag=0 then  --如果没有这个表空间 则去创建
newobject(v_tablespace_name,v_username,password,v_tablename);
      end if;

exception
when ex_1 then
        /*DBMS_OUTPUT.put_line('当前用户没有创建表空间的权限');/* 这样的话 报错之后 是会退出程序的*/
RAISE_APPLICATION_ERROR(-20001,'当前用户不是DBA权限，请使用SYS或者SYSTEM用户创建');
end;
 
 
/*调用此存储过程*/


begin
  sp_create_env();
end;


begin
  sp_create_env('NEWDAY','junshi','junshi','CCC,DDD,AAA');
end;




drop user &username cascade;
drop tablespace &tbsname including contents and datafiles;
select table_name,owner from dba_tables where owner=UPPER('&username');