-- creates


CREATE OR REPLACE TYPE tp_funcionario AS OBJECT(
nome VARCHAR2(20),
tipo number(2),
salario number(9,2),
cpf VARCHAR2(20)
)NOT FINAL NOT INSTANTIABLE; 
/


CREATE OR REPLACE TYPE tp_cliente AS OBJECT(
Nome VARCHAR2(20),
CPF VARCHAR2(20),
Foto BLOB,
Telefone VARCHAR2(10),
Carro1  REF tp_carro,
MEMBER PROCEDURE mostrar_nome_cpf (SELF IN OUT tp_cliente),
MAP MEMBER FUNCTION get_cpf RETURN VARCHAR2
);
/


CREATE OR REPLACE TYPE BODY tp_cliente AS
MEMBER PROCEDURE mostrar_nome_cpf(SELF IN OUT tp_cliente) IS
 BEGIN
  DBMS_OUTPUT.PUT_LINE(Nome || ' ' || CPF);
 END mostrar_nome_CPF;

MAP MEMBER FUNCTION get_cpf RETURN VARCHAR2 IS
 BEGIN
 RETURN CPF;
END;
END;
/


CREATE TYPE tp_carro AS OBJECT(
Chassi VARCHAR2(20),
ReferenciaCliente REF tp_cliente,
Modelo VARCHAR2(20),
Cor VARCHAR2(20)
);
/


CREATE TYPE tp_carro AS OBJECT(
Chassi VARCHAR2(20),
ReferenciaCliente REF tp_cliente,
Modelo VARCHAR2(20),
Cor VARCHAR2(20)
);
/


CREATE OR REPLACE TYPE tp_fone AS OBJECT (
cod_pais VARCHAR2(2),
cod_area VARCHAR2(2),
numero VARCHAR2(8));
/


CREATE OR REPLACE TYPE tp_nt_fone AS TABLE OF tp_fone;
/


CREATE OR REPLACE TYPE tp_va_fone AS VARRAY(5) OF tp_fone;
/


CREATE OR REPLACE TYPE tp_atendente UNDER tp_funcionario ();
/


CREATE OR REPLACE TYPE tp_mecanico UNDER tp_funcionario (
Especializacao VARCHAR2(20));
/


CREATE OR REPLACE TYPE tp_gerente UNDER tp_funcionario (
atendente REF tp_atendente);
/


--tabelas

CREATE TABLE tb_cliente OF tp_cliente
(Nome NOT NULL,
CPF PRIMARY KEY
);
/

CREATE TABLE tb_lista_fone_departamento(
cod_depto NUMBER(5),
lista_fone tp_va_fone);
/


CREATE TABLE tb_lista_fone_contato(
cod_contato NUMBER(5),
lista_fone tp_nt_fone)
 NESTED TABLE lista_fone STORE AS tb_lista_fone;
/


-----------------
-- OK até aqui---
-----------------

CREATE OR REPLACE TYPE tp_funcionario AS OBJECT (
Nome VARCHAR2(80),
Salario NUMBER(9,2),
CPF NUMBER(11),
MEMBER FUNCTION GET_SALARIO () RETURN NUMBER,
MEMBER PROCEDURE SET_SALARIO (novoSal NUMBER),
MEMBER FUNCTION GET_CODIGO RETURN NUMBER
) NOT FINAL;

CREATE OR REPLACE TYPE BODY tp_funcionario AS
MEMBER FUNCTION GET_SALARIO () RETURN NUMBER IS
BEGIN
RETURN SELF.salario;
END GET_SALARIO;
END;

MEMBER PROCEDURE SET_SALARIO (novoSal NUMBER) IS
BEGIN
SELF.salario := novoSal;
END SET_SALARIO;
END;

MEMBER FUNCTION GET_CODIGO RETURN NUMBER IS
BEGIN
RETURN SELF.codigo;
END GET_CODIGO;
END;

CREATE OR REPLACE TYPE tp_gerente UNDER tp_funcionario ();
/

CREATE OR REPLACE TYPE tp_servico AS OBJECT(
Cod_servico NUMBER(3),
Descricao VARCHAR2(20),
Preco NUMBER(4).
MEMBER FUNCTION GET_PRECO() RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY tp_servico AS

MEMBER FUNCTION GET_PRECO () RETURN NUMBER IS
BEGIN
RETURN SELF.Preco;
END GET_PRECO;

END;

CREATE OR REPLACE TYPE tp_produto AS OBJECT(
Qtd_em_estoque NUMBER(2),
Preco NUMBER(4),
Nome VARCHAR2(20),
Codigo NUMBER(2),
MEMBER FUNCTION GET_QTD() RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY tp_servico AS

MEMBER FUNCTION GET_QTD () RETURN NUMBER IS
BEGIN
RETURN SELF.Qtd_em_estoque;
END GET_QTD;

END;



CREATE TABLE tb_produto OF tp_produto(
Qtd_em_estoque NOT NULL,
Preco NOT NULL,
Nome NOT NULL,
Codigo PRIMARY KEY );

-- alters

ALTER TYPE tp_funcionario ADD ATTRIBUTE (email VARCHAR2(50)) CASCADE;