prova lab bd

CREATE USER dir_financeiro IDENTIFIED BY diretor
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;

CREATE ROLE SELECT_AUDITORIA;

GRANT CREATE SESSION TO dir_financeiro;
GRANT CREATE TABLE TO dir_financeiro;
GRANT CREATE SEQUENCE TO dir_financeiro;
GRANT CREATE TRIGGER TO dir_financeiro;
GRANT SELECT ON DUAL TO dir_financeiro;

CREATE table "TB_FILIAL" (
    "CD_FILIAL"  NUMBER NOT NULL,
    "NM_FILIAL"  VARCHAR2(100) NOT NULL,
    constraint  "TB_FILIAL_PK" primary key ("CD_FILIAL")
);

GRANT SELECT, INSERT ON dir_financeiro.TB_FILIAL TO dir_financeiro;

INSERT INTO TB_FILIAL VALUES (1, 'FILIAL 1');
INSERT INTO TB_FILIAL VALUES (2, 'FILIAL 2');
INSERT INTO TB_FILIAL VALUES (2, 'FILIAL 3');


CREATE TABLE "TB_TRANSACAO" (
"CD_TRANSACAO" NUMBER NOT NULL,
"CD_FILIAL" NUMBER NOT NULL,
"VL_TRANSACAO" NUMBER(10,2) NOT NULL,
CONSTRAINT "TB_TRANSACAO_PK" PRIMARY KEY ("CD_TRANSACAO"),
CONSTRAINT "TB_TRANSACAO_FILIAL_FK" FOREIGN KEY ("CD_FILIAL") REFERENCES "TB_FILIAL" ("CD_FILIAL")
);

CREATE TABLE "TB_AUDITORIA" (
"CD_AUDITORIA" NUMBER NOT NULL,
"CD_FILIAL" NUMBER NOT NULL,
"VL_ALTERADO" NUMBER(10,2),
"VL_ANTES" NUMBER(10,2) NOT NULL,
"VL_DEPOIS" NUMBER(10,2) NOT NULL,
"DT_AUDITORIA" DATE,
CONSTRAINT "TB_AUDITORIA_PK" PRIMARY KEY ("CD_AUDITORIA"),
CONSTRAINT "TB_AUDITORIA_FILIAL_FK" FOREIGN KEY ("CD_FILIAL") REFERENCES "TB_FILIAL" ("CD_FILIAL")
);

GRANT SELECT ON dir_financeiro.TB_AUDITORIA TO SELECT_AUDITORIA;
GRANT SELECT_AUDITORIA TO dir_financeiro;

CREATE SEQUENCE SEQ_AUDITORIA
START WITH 1
INCREMENT BY 1
NOCYCLE;

CREATE OR REPLACE TRIGGER AUTOINC_AUDITORIA
  BEFORE INSERT ON TB_AUDITORIA
  FOR EACH ROW
BEGIN
  SELECT SEQ_AUDITORIA.nextval
    INTO :new.CD_AUDITORIA
    FROM dual;
END;

CREATE OR REPLACE TRIGGER "AUD_TRANSACAO"
BEFORE UPDATE ON TB_TRANSACAO
FOR EACH ROW
BEGIN
INSERT INTO TB_AUDITORIA
(CD_AUDITORIA, CD_FILIAL, VL_ALTERADO, VL_ANTES, VL_DEPOIS, DT_AUDITORIA)
VALUES ((SELECT SEQ_AUDITORIA.nextval FROM DUAL), OLD.CD_FILIAL, (NEW.VL_TRANSACAO - OLD.VL_TRANSACAO), OLD.VL_TRANSACAO, NEW.VL_TRANSACAO, SYSDATE);
END;

CREATE OR REPLACE PROCEDURE "MAIOR_MOVIMENTACAO" IS
DECLARE
V_CD_FILIAL NUMBER;
V_VL_TRANSACAO NUMBER(10,2);
CURSOR CONTADOR IS
SELECT CD_FILIAL, VL_TRANSACAO FROM TB_TRANSACAO ORDER BY VL_TRANSICAO DESC;
BEGIN
OPEN CONTADOR;
LOOP
FETCH CONTADOR INTO V_CD_FILIAL, V_VL_TRANSACAO;
DBMS_OUTPUT.PUT_LINE(V_CD_FILIAL);
DBMS_OUTPUT.PUT_LINE((SELECT NM_FILIAL FROM TB_FILIAL WHERE CD_FILIAL = V_CD_FILIAL));
DBMS_OUTPUT.PUT_LINE(V_VL_TRANSACAO);
DBMS_OUTPUT.PUT_LINE('----------------');
EXIT WHEN CONTADOR%ROWCOUNT > 3;
END LOOP;
CLOSE CONTADOR;
END;


GRANT SELECT, INSERT ON dir_financeiro.TB_TRANSACAO to dir_financeiro;

INSERT INTO TB_TRANSACAO VALUES (1, 1, 1000.50);
INSERT INTO TB_TRANSACAO VALUES (2, 2, 800.79);
INSERT INTO TB_TRANSACAO VALUES (3, 3, 500);