class09_2020_04_30_view.sql

VIEW    -- semelhante a uma tabela, geralmente é utilizada para disponibilizar dados somente para leitura ou cópia de tabela envolvendo dados de mais de uma tabela.

-- Criação de view
CREATE [OR REPLACE] VIEW "view_name"
AS SELECT ... [WITH READ ONLY]

-- Exemplo:
-- View da tabela orcamento
CREATE OR REPLACE VIEW VORCAMENTO 
AS SELECT * FROM ORCAMENTO;


DESC VORCAMENTO;
SELECT * FROM VORCAMENTO;


INSERT INTO VORCAMENTO VALUES
(26, SYSDATE, 'orçamento teste');

DELETE VORCAMENTO WHERE id_orcamento = 26;


INSERT INTO ORCAMENTO VALUES(
    SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE, 'orçamento teste'
);

COMMIT;


-- View da tabela categoria somente leitura
CREATE OR REPLACE VIEW VCATEGORIA 
AS SELECT * FROM CATEGORIA WITH READ ONLY;

-- INSERT INTO VCATEGORIA VALUES(
--     20, 'Categoria X'
-- );

INSERT INTO VCATEGORIA VALUES(
    SEQ_ID_CATEGORIA.NEXTVAL, 'Categoria X'
);


-- View da tabela produto com o nome da categoria
CREATE OR REPLACE VIEW "VPRODUTO"
AS SELECT P.ID_PRODUTO, P.NOM_PRODUTO,
C.NOM_CATEGORIA, P.VLR_PRODUTO 
FROM PRODUTO P, CATEGORIA C 
WHERE P.ID_CATEGORIA = C.ID_CATEGORIA;

SELECT * FROM VPRODUTO;


-- View dos orçamentos de 2020
CREATE OR REPLACE VIEW "VORCAMENTO2020"
AS SELECT * FROM ORCAMENTO
WHERE TO_CHAR(DTA_ORCAMENTO, 'YYYY') = '2020';

SELECT * FROM VORCAMENTO2020;


-- Exluir VIEW
DROP VIEW "VORCAMENTO2020";



-- ====================================================================================================
-- ========================================== EXAMPLE TABLES ==========================================
-- ====================================================================================================

-- CLEAN
DROP SEQUENCE seq_id_categoria;
DROP SEQUENCE seq_id_produto;
DROP SEQUENCE seq_num_orcamento;


DELETE item_orcamento;
DELETE orcamento;
DELETE produto;
DELETE categoria;


-- CREATE TABLES
CREATE TABLE CATEGORIA(
    id_categoria NUMERIC(2),
    nom_categoria VARCHAR2(20),
    CONSTRAINT pk_categoria PRIMARY KEY(id_categoria)
);

CREATE TABLE PRODUTO(
    id_produto NUMERIC(4),
    nom_produto VARCHAR2(30),
    id_categoria NUMERIC(2),
    vlr_produto NUMERIC(7,2),
    CONSTRAINT pk_produto PRIMARY KEY(id_produto),
    CONSTRAINT fk_produto_categoria FOREIGN KEY(id_categoria) REFERENCES categoria
);


CREATE SEQUENCE SEQ_ID_CATEGORIA
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99
NOCYCLE;

CREATE SEQUENCE SEQ_ID_PRODUTO
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
NOCYCLE;


CREATE TABLE ORCAMENTO(
    id_orcamento NUMERIC(6),
    dta_orcamento DATE,
    obs_orcamento VARCHAR(50),
    CONSTRAINT pk_orcamento PRIMARY KEY(id_orcamento)
);

CREATE TABLE ITEM_ORCAMENTO(
    id_produto NUMERIC(4),
    id_orcamento NUMERIC(6),
    qtd_orcamento NUMERIC(4),
    vlr_item_orcamento NUMERIC(7,2),
    CONSTRAINT pk_item_orcamento PRIMARY KEY(id_orcamento, id_produto),
    CONSTRAINT fk_produto FOREIGN KEY(id_produto) REFERENCES produto,
    CONSTRAINT fk_orcamento FOREIGN KEY(id_orcamento) REFERENCES orcamento
);


CREATE SEQUENCE SEQ_NUM_ORCAMENTO 
START WITH 1 
INCREMENT BY 1;


-- ====================================================================================================
-- ========================================= POPULATE TABLES ==========================================
-- ====================================================================================================
DECLARE 
i NUMBER(2);
BEGIN
i := 1;
LOOP
    EXIT WHEN I > 10;
    INSERT INTO CATEGORIA (ID_CATEGORIA, NOM_CATEGORIA) VALUES(SEQ_ID_CATEGORIA.NEXTVAL, 'CATEGORIA ' || i);
i:=i+1;
END LOOP;
END;
/

DECLARE 
i NUMBER(2);
vlr NUMBER(4,2);
BEGIN
i := 1;
vlr := 10.5;
LOOP
    EXIT WHEN I > 10;
    IF I <= 5 THEN 
         INSERT INTO PRODUTO (ID_PRODUTO, NOM_PRODUTO, ID_CATEGORIA, VLR_PRODUTO) VALUES(SEQ_ID_PRODUTO.NEXTVAL, 'PRODUTO ' || i, 1, vlr);
    ELSE
         INSERT INTO PRODUTO (ID_PRODUTO, NOM_PRODUTO, ID_CATEGORIA, VLR_PRODUTO) VALUES(SEQ_ID_PRODUTO.NEXTVAL, 'PRODUTO ' || i, 2, vlr);
      END IF;
i:=i+1;
vlr := vlr + 1;
END LOOP;
END;
/


INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-200);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-200);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-200);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-200);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-200);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-100);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-50);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-50);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-50);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-50);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-50);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-50);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE-50);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE);
INSERT INTO ORCAMENTO (ID_ORCAMENTO, DTA_ORCAMENTO) VALUES (SEQ_NUM_ORCAMENTO.NEXTVAL, SYSDATE);

INSERT INTO ITEM_ORCAMENTO VALUES (1, 1, 1, 10.5);
-- INSERT INTO ITEM_ORCAMENTO VALUES (1, 1, 1, 10.5);
INSERT INTO ITEM_ORCAMENTO VALUES (2, 1, 1, 23);
INSERT INTO ITEM_ORCAMENTO VALUES (1, 2, 1, 10.5);
INSERT INTO ITEM_ORCAMENTO VALUES (1, 3, 2, 21);
INSERT INTO ITEM_ORCAMENTO VALUES (10, 4, 1, 19.5);
INSERT INTO ITEM_ORCAMENTO VALUES (10, 5, 1, 19.5);
INSERT INTO ITEM_ORCAMENTO VALUES (9, 5, 1, 18.5);
INSERT INTO ITEM_ORCAMENTO VALUES (3, 6, 10, 125);

COMMIT;