ORACLE 12C 生产脚本规范

数据库规范

oracle数据库安装若是 ZHS16GBK,脚本文件编码格式请用相同的ansi格式

1.创建表空间和用户

-------创建kdbase----------
sqlplus / as sysdba;
alter session set container=PDBORCL;  
drop user ktest cascade;
drop tablespace TS_KTEST including contents and datafiles cascade constraints;

--2.创建表空间
create tablespace TS_KTEST datafile 'C:\app\oracle\oradata\orcl\ktest.dbf' size 32M autoextend on next 100M maxsize 15000M extent management local;
--3.重启OracleServiceOrcl服务
exit
lsnrctl stop
sqlplus / as sysdba
alter session set container=PDBORCL; 
shu immediate
startup
exit
lsnrctl start

sqlplus / as sysdba
alter session set container=PDBORCL; 
--4.创建数据库用户
create user ktest identified by ktest default tablespace TS_KTEST;
GRANT 
　　CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
　　ALTER ANY TABLE, ALTER ANY PROCEDURE,
　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
　　TO ktest;
GRANT connect,resource TO ktest;
alter user ktest quota unlimited on TS_KTEST;
--解决PLSQL无法连接的问题
grant dba to ktest;  

2.创建对象

2.1创建表

--存在就删除函数（函数中使用execute immediate不能执行DDL语句删除表结构。）
declare tb_num number :=0;
BEGIN
  select count(1) into tb_num from user_tables where table_name = upper('TABLE_NAME') ;
    if tb_num > 0 then
        execute immediate 'drop table TABLE_NAME cascade constraints';
    end if;
END;
--创建表
create table TABLE_NAME 
(
   RECORD_SNO           INTEGER     default SEQ_TABLE_NAME.NEXTVAL ,
   CUST_CODE            VARCHAR2(32),
   YY_CODE             VARCHAR2(32),
   UP_DATE              VARCHAR2(10),
   CREATE_DATE          VARCHAR2(10),
   CREATE_TIME          VARCHAR2(10),
   CREATE_STA           VARCHAR2(1),
   REMARK               VARCHAR2(256),
   constraint PK_TABLE_NAME primary key (RECORD_SNO)
);

comment on table TABLE_NAME is
'一码通任务流水表';

comment on column TABLE_NAME.RECORD_SNO is
'记录号';

comment on column TABLE_NAME.CUST_CODE is
'客户代码';

comment on column TABLE_NAME.YY_CODE is
'测试代码';

comment on column TABLE_NAME.UP_DATE is
'更新日期';

comment on column TABLE_NAME.CREATE_DATE is
'生成日期';

comment on column TABLE_NAME.CREATE_TIME is
'生成时间';

comment on column TABLE_NAME.CREATE_STA is
'生成状态';

comment on column TABLE_NAME.REMARK is
'备注';

--生成删除表的语句(注意结果在notepad++中将换行换成\r\n ， ~换成')
select 'declare tb_num'||rownum||' number :=0;换行BEGIN换行  select count(1) into tb_num'||rownum||' from user_tables where table_name = upper(~' ||
       table_name ||
       '~) ;换行    if tb_num'||rownum||' > 0 then换行        execute immediate ~drop table ' ||
       table_name || ' cascade constraints~;换行    end if;换行END;换行/换行' as del_tb_sql
  from user_tables;

2.2创建序列器

--BEGIN END之间用execute immediate 执行DDL语句。
declare sq_num number :=0;
BEGIN
    select count(1) into sq_num from user_sequences where sequence_name = upper('SEQ_NAME') ;
    if sq_num > 0 then
        execute immediate 'drop sequence SEQ_NAME';
    end if;
END;
/
create sequence SEQ_NAME
minvalue 1
maxvalue 9999999999999999999999999999
start with 100000
increment by 1
cache 20;

--生成删除序列器的语句(注意结果在notepad++中将换行换成\r\n ， ~换成')
  select 'declare sq_num'||rownum||' number :=0;换行BEGIN 换行 select count(1) into sq_num'||rownum||' from user_sequences where sequence_name = upper(~' ||
       sequence_name ||
       '~) ;换行 if sq_num'||rownum||' > 0 then 换行    execute immediate  ~drop sequence ' ||
       sequence_name || '~; 换行 end if;换行END;换行/换行' as del_sq_sql
  from user_sequences;