ORACLE DDL测试脚本

测试脚本

1、简单测试脚本

#!/bin/bash

username='test_caolh'
passwd='1qaz!QAZ'
tnsname='PROD'

sql='sqlplus '$username'/'$passwd'@'$tnsname''

$sql << EOF

#创建表
create table zcbus_oracle_test (test_1 number primary key,test_2 binary_float,test_3 char(255),test_4 varchar2(4000),test_5 blob,test_6 clob,test_7 long,test_8 raw(2000),test_9 timestamp);

#插入数据
insert into zcbus_oracle_test values (17294,18153961256,'xuxiaogang','beijing',rawtohex('tinatongyuan16#4-1203'),'beijingshichangpinquhuilonghuandajie','idcard:1CE4A7C465B6F27EF688BAEA5C8F3CD0','10101101010101011111010101001',sysdate);
insert into zcbus_oracle_test values (17295,18153961257,'wanghaoyu','shanghai',rawtohex('guiyuandnanlijia6#3-201'),'shanghaishiminhangqulianhuaqiaolu','idcard:38F3AD145986D4DFB310B504D9E5C1F0','10101010101011110110101001',sysdate);
insert into zcbus_oracle_test values (17296,18153971255,'yuboyang','tianjin',rawtohex('huilingguan3#1-1402'),'tinajinshidongliquwaihuandonglu','idcard:6305D39723FE465F4DFA458CA656CC8C','10101010101110101010111001',sysdate);
insert into zcbus_oracle_test values (17297,18923908888,'songtao','hunan',rawtohex('wanshuyuan1qu3#-601'),'hunanshengchangshashifuronqunongdalu','idcard:468259A84D736055C2456B197F63F671','101010111010101110010101001',sysdate);
insert into zcbus_oracle_test values (17298,18923908888,'songtao','hubei',rawtohex('haoxinjianyuan3qu5#602'),'hubeishengshaoyangshibeitaquxingzhilu','idcard:349385BC397C45420DE8A06D3F692A0C','1010110101110010111101001',sysdate);

#更新数据
update zcbus_oracle_test set test_4='qwertyuiop' where test_1=1;
update zcbus_oracle_test set test_2='11.22' where test_1=2;
update zcbus_oracle_test set test_9=sysdate where test_1=3;

#删除数据
delete zcbus_oracle_test where test_1=2;
delete zcbus_oracle_test where test_1=3;
delete zcbus_oracle_test where test_1=1;

#增加字段
alter table zcbus_oracle_test add test_10 date;
#修改字段属性
alter table zcbus_oracle_test modify test_10 float default 10000;
#修改字段名
alter table zcbus_oracle_test rename column test_10 to test_11;
#删除字段
alter table zcbus_oracle_test drop column test_11;

#创建唯一索引
create unique index ind_zcbus_test_2  on zcbus_oracle_test(test_2);
#删除唯一索引
drop index ind_zcbus_test_2;
#创建唯一约束
alter table zcbus_oracle_test add constraint unicons_zcbus_test_2 unique(test_2);
#删除唯一约束
alter table zcbus_oracle_test drop constraint unicons_zcbus_test_2;
#删除主键
alter table zcbus_oracle_test drop primary key;


EOF

2、DDL测试脚本

#建表语句
        create table oracle_tab1 (test_1 number(38) primary key,test_2 number(38,29),test_3 integer,test_4 float(126),test_5 binary_float,test_6 binary_double);
        create table oracle_tab2 (test_1 number(38) primary key,test_2 char(255),test_3 varchar2(4000),test_4 nvarchar2(2000),test_5 varchar(30));
        create table oracle_tab3 (test_1 number(38) primary key,test_2 date,test_3 timestamp,test_4 TIMESTAMP WITH LOCAL TIME ZONE,test_5 TIMESTAMP WITH TIME ZONE);
        create table oracle_tab4 (test_1 number(38) primary key,test_2 blob,test_3 clob,test_4 long,test_5 raw(2000));
        create table oracle_tab5 (test_1 number(38,27),test_2 char(255),test_3 date,test_4 varchar2(4000),constraint pk_test12 primary key (test_1,test_2));
        create table oracle_tab6 (test_1 number(38,27),test_2 char(255),test_3 integer,test_4 float(126),test_5 timestamp,constraint pk_test1 primary key (test_1,test_2),constraint fk_test foreign key(test_1,test_2) references oracle_tab5(test_1,test_2));
        create table oracle_tab7 (test_1 nchar(200),test_2 real primary key);
        create table "oracle_tab8" (test_1 nclob,test_2 long raw,test_3 number primary key);
        create table "ORACLE_TAB9" (test_1 number primary key);

#DDL语句
       ALTER TABLE ORACLE_TAB7 ADD LONG_RAW LONG RAW;
       alter table oracle_tab7 add tab7_var varchar2(4000);
       alter table oracle_tab7 add tab7_lob blob;
       alter table oracle_tab7 add tab7_null number(38) null;
       alter table oracle_tab7 add tab7_not_null number(38) not null;
       alter table oracle_tab7 add tab7_defa number(38) default 10;
       alter table oracle_tab7 add tab7_defa1 number(38) default 10  not null;
       alter table oracle_tab7 add (tab7_var2 varchar2(300),tab7_num2 number(38));

       alter table oracle_tab7 modify TAB7_NUM2 number;
       alter table oracle_tab7 modify tab7_var2 varchar(30);
       alter table oracle_tab7 modify tab7_num2 number default 10;
       alter table ORACLE_TAB7 modify tab7_num2 number default 10 not null;

       alter table oracle_tab7  rename column tab7_defa to tab7_defa2;

       ALTER TABLE "oracle_tab8" ADD TEST_4 NUMBER(12);
       alter table "oracle_tab8" add test_5 varchar(10) null;
       create index index_tab8_test_4 on "oracle_tab8"(test_4);
       alter table "oracle_tab8" drop COLUMN TEST_5;

       ALTER TABLE "ORACLE_TAB9" ADD TEST_2 NUMBER(18);
       alter table     "ORACLE_TAB9" add test_3 varchar2(30) defualt 'abc';
       create index INDEX_ORACLE_TAB9_TEST_5 ON "ORACLE_TAB9"(TEST_3);
       ALTER TABLE "ORACLE_TAB9" DROP COLUMN TEST_3; 



       create unique index index_oracle_tab7 on oracle_tab7(tab7_defa2);
       alter table oracle_tab7 add constraint uni_cons_tab7_defa1 unique(tab7_defa1);
       create index index_tab1_test_2  on oracle_tab1(test_2);
#      alter table oracle_tab7 add check(tab7_defa2 not like '%2%');  ##该DDL语句大部分数据库不支持
       drop index index_oracle_tab7;
       alter table oracle_tab7 drop constraint uni_cons_tab7_defa1;
       comment on table oracle_tab7 is 'this is a test table';
       comment on column oracle_tab7.test_2  is  'this is a test filed';

       alter table oracle_tab7 drop column tab7_null;


       alter table oracle_tab7 drop primary key;
       alter table oracle_tab7 add tab7_pk number(38) primary key;
       alter table oracle_tab7 drop primary key;
       alter table oracle_tab7 add constraint tab7_pk1 primary key (tab7_pk);