------------------------------------------------------
-- Export file for user SYSTEM                      --
-- Created by Administrador on 14/12/2014, 18:58:52 --
------------------------------------------------------

spool BKP-Aula06-Objects2.log

prompt
prompt Creating table CADASTRO
prompt =======================
prompt
create table SYSTEM.CADASTRO
(
  id         NUMBER not null,
  first_name VARCHAR2(20),
  last_name  VARCHAR2(25)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table PRO_PAIS
prompt =======================
prompt
create table SYSTEM.PRO_PAIS
(
  cdpais NUMBER not null,
  nmpais VARCHAR2(40) not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.PRO_PAIS
  add constraint PRO_PAIS_PK primary key (CDPAIS)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.PRO_PAIS
  add constraint NM_PAIS_UK unique (NMPAIS)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table FABRICA
prompt ======================
prompt
create table SYSTEM.FABRICA
(
  cd_fab NUMBER not null,
  nm_fab VARCHAR2(40),
  cdpais NUMBER not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.FABRICA
  add constraint FABRICA_PK primary key (CD_FAB)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.FABRICA
  add constraint FABRICA_PRO_PAIS_FK foreign key (CDPAIS)
  references SYSTEM.PRO_PAIS (CDPAIS);

prompt
prompt Creating table CONCES
prompt =====================
prompt
create table SYSTEM.CONCES
(
  cd_con    NUMBER not null,
  nm_conces VARCHAR2(40),
  cd_fab    NUMBER not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.CONCES
  add constraint CONCES_PK primary key (CD_CON)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.CONCES
  add constraint CONCES_FABRICA_FK foreign key (CD_FAB)
  references SYSTEM.FABRICA (CD_FAB);

prompt
prompt Creating table EMPRESA_TESTE
prompt ============================
prompt
create table SYSTEM.EMPRESA_TESTE
(
  codigo        NUMBER(20) not null,
  nome          VARCHAR2(2000),
  nome_fantasia VARCHAR2(2000),
  cnpj          VARCHAR2(100)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table MOTOR
prompt ====================
prompt
create table SYSTEM.MOTOR
(
  cd_motor      NUMBER not null,
  tipo_motor    NUMBER(10,2),
  veloc_inicial NUMBER,
  veloc_final   NUMBER
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.MOTOR
  add constraint MOTOR_PK primary key (CD_MOTOR)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table TIPO_COMB
prompt ========================
prompt
create table SYSTEM.TIPO_COMB
(
  cd_comb NUMBER not null,
  nm_comb VARCHAR2(40) not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.TIPO_COMB
  add constraint TIPO_COMB_PK primary key (CD_COMB)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table VEICULO
prompt ======================
prompt
create table SYSTEM.VEICULO
(
  cd_veic  NUMBER not null,
  nm_veic  VARCHAR2(40) not null,
  dat_fab  DATE,
  dat_mod  DATE,
  cd_motor NUMBER not null,
  cd_fab   NUMBER not null,
  cd_comb  NUMBER not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.VEICULO
  add constraint VEICULO_PK primary key (CD_VEIC)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.VEICULO
  add constraint VEICULO_UN unique (CD_FAB, NM_VEIC, CD_MOTOR, CD_COMB)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.VEICULO
  add constraint VEICULO_FABRICA foreign key (CD_FAB)
  references SYSTEM.FABRICA (CD_FAB);
alter table SYSTEM.VEICULO
  add constraint VEICULO_MOTOR_FK foreign key (CD_MOTOR)
  references SYSTEM.MOTOR (CD_MOTOR);
alter table SYSTEM.VEICULO
  add constraint VEICULO_TIPO_COMB_FK foreign key (CD_COMB)
  references SYSTEM.TIPO_COMB (CD_COMB);

prompt
prompt Creating table ESTOQ
prompt ====================
prompt
create table SYSTEM.ESTOQ
(
  cd_est   NUMBER not null,
  qt_veic  NUMBER,
  val_veic NUMBER(10,2),
  cd_con   NUMBER not null,
  cd_veic  NUMBER not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.ESTOQ
  add constraint ESTOQ_PK primary key (CD_EST)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table SYSTEM.ESTOQ
  add constraint ESTOQ_CONCES_FK foreign key (CD_CON)
  references SYSTEM.CONCES (CD_CON);
alter table SYSTEM.ESTOQ
  add constraint ESTOQ_VEICULO_FK foreign key (CD_VEIC)
  references SYSTEM.VEICULO (CD_VEIC);

prompt
prompt Creating sequence CONCES_SEQ
prompt ============================
prompt
create sequence SYSTEM.CONCES_SEQ
minvalue 0
maxvalue 999999
start with 6
increment by 1
nocache;

prompt
prompt Creating sequence ESTOQ_SEQ
prompt ===========================
prompt
create sequence SYSTEM.ESTOQ_SEQ
minvalue 0
maxvalue 999999
start with 8
increment by 1
nocache;

prompt
prompt Creating sequence FABRICA_SEQ
prompt =============================
prompt
create sequence SYSTEM.FABRICA_SEQ
minvalue 0
maxvalue 999999
start with 7
increment by 1
nocache;

prompt
prompt Creating sequence MOTOR_SEQ
prompt ===========================
prompt
create sequence SYSTEM.MOTOR_SEQ
minvalue 0
maxvalue 999999
start with 6
increment by 1
nocache;

prompt
prompt Creating sequence PRO_PAIS_SEQ
prompt ==============================
prompt
create sequence SYSTEM.PRO_PAIS_SEQ
minvalue 1
maxvalue 999999
start with 20
increment by 1
nocache;

prompt
prompt Creating sequence SEQUENCE_CADASTRO
prompt ===================================
prompt
create sequence SYSTEM.SEQUENCE_CADASTRO
minvalue 1
maxvalue 999999
start with 6
increment by 1
nocache;

prompt
prompt Creating sequence TIPO_COMB_SEQ
prompt ===============================
prompt
create sequence SYSTEM.TIPO_COMB_SEQ
minvalue 0
maxvalue 999999
start with 5
increment by 1
nocache;

prompt
prompt Creating sequence VEICULO_SEQ
prompt =============================
prompt
create sequence SYSTEM.VEICULO_SEQ
minvalue 0
maxvalue 999999
start with 10
increment by 1
nocache;

prompt
prompt Creating function CADASTRO_F
prompt ============================
prompt
create or replace function system.Cadastro_F
       (mensagem in varchar2) 
  return number 
  is
  V_ID number;
begin
  select sequence_cadastro.nextval
         into V_ID
         from dual;
  return(V_ID);
end;
/

prompt
prompt Creating procedure AULA_6
prompt =========================
prompt
create or replace procedure system.aula_6
       (p_aula in number,
        p_saida out varchar2)
is
  v_entrada number(10);

begin
  null;
end;
/

prompt
prompt Creating procedure CADASTRO_P
prompt =============================
prompt
CREATE OR REPLACE PROCEDURE SYSTEM.CADASTRO_P
       (MENSAGEM IN VARCHAR2,
       P_ID      OUT NUMBER)
IS
BEGIN
  select sequence_cadastro.nextval
         into P_ID
         from dual;
END;
/

prompt
prompt Creating procedure P_PAIS
prompt =========================
prompt
create or replace procedure system.P_Pais
       (p_nmpais in varchar2,
        p_saida out varchar2)
is
  v_entrada number(10);

begin
  begin
    insert into pro_pais
       (nmpais)
    values
       (upper(p_nmpais));
    p_saida:='País Incluido com sucesso!';
    commit;
  exception
    when dup_val_on_index then
      p_saida:='País já cadastrado!  '||sqlerrm;
      rollback;
    when others then
      p_saida:='Erro ao cadastrar o País!  '|| sqlerrm;
      rollback;
    end;
end;
/

prompt
prompt Creating trigger CADASTRO_INSERT
prompt ================================
prompt
create or replace trigger SYSTEM.Cadastro_insert
  before insert on cadastro  
  for each row
declare
  -- local variables here
begin
 -- :NEW.id:=Cadastro_F('Ok');
 CADASTRO_P('Ok', :New.id);
end Cadastro_insert;
/

prompt
prompt Creating trigger CONCES_TR_INSERT
prompt =================================
prompt
create or replace trigger SYSTEM.CONCES_TR_insert
  before insert on CONCES  
  for each row
declare
  -- local variables here
begin
  select CONCES_seq.nextval into :new.CD_CON from dual;
end CONCES_TR_insert;
/

prompt
prompt Creating trigger ESTOQ_TR_INSERT
prompt ================================
prompt
create or replace trigger SYSTEM.ESTOQ_TR_insert
  before insert on ESTOQ
  for each row
declare
  -- local variables here
begin
  select ESTOQ_seq.nextval into :new.CD_EST from dual;
end ESTOQ_TR_insert;
/

prompt
prompt Creating trigger FABRICA_TR_INSERT
prompt ==================================
prompt
create or replace trigger SYSTEM.FABRICA_TR_insert
  before insert on FABRICA  
  for each row
declare
  -- local variables here
begin
  select FABRICA_seq.nextval into :new.CD_FAB from dual;
end FABRICA_TR_insert;
/

prompt
prompt Creating trigger MOTOR_TR_INSERT
prompt ================================
prompt
create or replace trigger SYSTEM.MOTOR_TR_insert
  before insert on MOTOR  
  for each row
declare
  -- local variables here
begin
  select MOTOR_seq.nextval into :new.CD_MOTOR from dual;
end MOTOR_TR_insert;
/

prompt
prompt Creating trigger PRO_PAIS_TR_INSERT
prompt ===================================
prompt
create or replace trigger SYSTEM.pro_pais_TR_insert
  before insert on pro_pais  
  for each row
declare
  -- local variables here
begin
  select pro_pais_seq.nextval into :new.cdpais from dual;
end pro_pais_TR_insert;
/

prompt
prompt Creating trigger TIPO_COMB_TR_INSERT
prompt ====================================
prompt
create or replace trigger SYSTEM.TIPO_COMB_TR_insert
  before insert on TIPO_COMB  
  for each row
declare
  -- local variables here
begin
  select TIPO_COMB_seq.nextval into :new.CD_COMB from dual;
end TIPO_COMB_TR_insert;
/

prompt
prompt Creating trigger VEICULO_TR_INSERT
prompt ==================================
prompt
create or replace trigger SYSTEM.VEICULO_TR_insert
  before insert on VEICULO  
  for each row
declare
  -- local variables here
begin
  select VEICULO_seq.nextval into :new.CD_VEIC from dual;
end VEICULO_TR_insert;
/


spool off