oracle 分批提取数据,Oracle创建关系分批抽取测试数据
1、创建表、字段、约束、主键、外键
create table T_STU (
STU_ID char(5) not null,
STU_NAME varchar2(8) not null,
constraint PK_T_STU primary key (STU_ID)
)
create table T_SCORE (
EXAM_SCORE number(5,2),
EXAM_DATE date,
AUTOID number(10) not null,
STU_ID char(5),
SUB_ID char(3),
constraint PK_T_SCORE primary key (AUTOID),
constraint FK_T_SCORE_REFE foreign key (STU_ID) references T_STU (STU_ID)
)
2、创建同义词：
CREATE SYNONYM SYN_T_STU FOR T_STU;
CREATE SYNONYM SYN_T_SCORE FOR T_SCORE;
3、创建唯一性索引：
CREATE UNIQUE INDEX schema.INDEX1 ON T_STU(STU_NAME);
CREATE UNIQUE INDEX schema.INDEX2 ON T_SCORE(EXAM_SCORE);
4、创建视图：
create view V_T_STU as select * from T_STU;
create view V_T_SCORE as select * from T_SCORE;
5、创建序列和触发器：
create sequence T_STU_seq;
create trigger T_STU_trigger before insert on T_STU for each row
begin
select T_STU_seq.nextval into :new.id from dual;(这句移出去也可以试试)
end;
insert into T_STU(STU_ID,STU_NAME) values(5,'Chir');
6、创建存储过程：
create or replace procedure getdefault1 is
begin
execute immediate 'create global temporary table deftemp(pid varchar2(5))on commit delete rows'
end;
7、查询数据库全局名称：
select * from global_name;
oracle 分批提取数据,Oracle创建关系分批抽取测试数据
1、创建表、字段、约束、主键、外键
create table T_STU (
STU_ID char(5) not null,
STU_NAME varchar2(8) not null,
constraint PK_T_STU primary key (STU_ID)
)
create table T_SCORE (
EXAM_SCORE number(5,2),
EXAM_DATE date,
AUTOID number(10) not null,
STU_ID char(5),
SUB_ID char(3),
constraint PK_T_SCORE primary key (AUTOID),
constraint FK_T_SCORE_REFE foreign key (STU_ID) references T_STU (STU_ID)
)
2、创建同义词：
CREATE SYNONYM SYN_T_STU FOR T_STU;
CREATE SYNONYM SYN_T_SCORE FOR T_SCORE;
3、创建唯一性索引：
CREATE UNIQUE INDEX schema.INDEX1 ON T_STU(STU_NAME);
CREATE UNIQUE INDEX schema.INDEX2 ON T_SCORE(EXAM_SCORE);
4、创建视图：
create view V_T_STU as select * from T_STU;
create view V_T_SCORE as select * from T_SCORE;
5、创建序列和触发器：
create sequence T_STU_seq;
create trigger T_STU_trigger before insert on T_STU for each row
begin
select T_STU_seq.nextval into :new.id from dual;(这句移出去也可以试试)
end;
insert into T_STU(STU_ID,STU_NAME) values(5,'Chir');
6、创建存储过程：
create or replace procedure getdefault1 is
begin
execute immediate 'create global temporary table deftemp(pid varchar2(5))on commit delete rows'
end;
7、查询数据库全局名称：
select * from global_name;