Matrículas, Papéis e XML em Oracle: Guia Completo

1) Modelagem de Matrículas: Tabelas, Sequences, Triggers e Procedure

• ALUNO: codigo INT PRIMARY KEY NOT NULL, nome VARCHAR2(50), email VARCHAR2(50), curso INT, chave estrangeira referenciando a tabela CURSO.
• CURSO: codigo INT PRIMARY KEY NOT NULL, descricao VARCHAR2(50).
• RESUMO: curso INT, chave estrangeira referenciando a tabela CURSO, quantidade INT.

Solução

CREATE TABLE CURSO (
  CODIGO INT NOT NULL PRIMARY KEY,
  DESCRICAO VARCHAR2(50)
);

CREATE TABLE ALUNO (
  CODIGO INT NOT NULL PRIMARY KEY,
  NOME VARCHAR2(50),
  EMAIL VARCHAR2(50),
  CURSO INT,
  CONSTRAINT fk_CURSOALUNO FOREIGN KEY (CURSO) REFERENCES CURSO (CODIGO)
);

CREATE TABLE RESUMO (
  CURSO INT,
  QUANTIDADE INT,
  CONSTRAINT fk_CURSO FOREIGN KEY (CURSO) REFERENCES CURSO (CODIGO)
);

CREATE SEQUENCE SEQ_CURSO MINVALUE 1 MAXVALUE 999999999 START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE SEQ_ALUNO MINVALUE 1 MAXVALUE 999999999 START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER TG_CURSO
BEFORE INSERT ON CURSO
FOR EACH ROW
BEGIN
  SELECT SEQ_CURSO.NEXTVAL INTO :NEW.CODIGO FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER TG_ALUNO
BEFORE INSERT ON ALUNO
FOR EACH ROW
BEGIN
  SELECT SEQ_ALUNO.NEXTVAL INTO :NEW.CODIGO FROM DUAL;
END;
/

INSERT INTO CURSO (DESCRICAO) VALUES ('BANCO DE DADOS');

SELECT * FROM CURSO;
SELECT * FROM ALUNO;

INSERT INTO ALUNO (NOME, EMAIL, CURSO) VALUES ('LORA', '[email protected]', 3);

CREATE OR REPLACE PROCEDURE SP_RESUMO AS
BEGIN
  DELETE FROM RESUMO WHERE 1 = 1;
  INSERT INTO RESUMO (
    SELECT CURSO.CODIGO,
           (SELECT COUNT(*) FROM ALUNO WHERE ALUNO.CURSO = CURSO.CODIGO) AS QUANTIDADE
    FROM CURSO
  );
END;
/

EXEC SP_RESUMO;

SELECT * FROM RESUMO;

2) Papéis (Roles) e Privilégios

• Administradores: podem ter qualquer privilégio de sistema e de objeto.
• Gerentes: podem ter apenas privilégios de objetos e conceder privilégio de Gerente a outros usuários.
• Operadores: podem ter apenas privilégios de consulta, alteração e inclusão.

CREATE ROLE Administradores;
CREATE ROLE Gerentes;
CREATE ROLE Operadores;

GRANT
  CREATE SESSION,
  CREATE SEQUENCE,
  CREATE TABLE,
  CREATE PROCEDURE,
  CREATE VIEW,
  CREATE USER,
  DROP TABLE,
  DROP USER,
  EXECUTE ANY PROCEDURE,
  CREATE TRIGGER
TO Administradores;

SELECT 'GRANT ALL ON ' || object_name || ' TO GERENTES;'
FROM user_objects;

SELECT 'GRANT SELECT, UPDATE, INSERT ON ' || object_name || ' TO OPERADORES;'
FROM user_objects;

3) Exportação em XML das tabelas ALUNO e CURSO

-- ALUNO com elementos aninhados
SELECT XMLELEMENT(
  "ALUNO",
  XMLELEMENT("CODIGO", CODIGO),
  XMLELEMENT("NOME", NOME),
  XMLELEMENT("EMAIL", EMAIL),
  XMLELEMENT("CURSO", CURSO)
) AS XML_ALUNO
FROM ALUNO;

-- ALUNO com atributos
SELECT XMLELEMENT(
  "ALUNO",
  XMLATTRIBUTES(CODIGO AS "CODIGO", NOME AS "NOME", EMAIL AS "EMAIL", CURSO AS "CURSO")
) AS XML_ALUNO
FROM ALUNO;

-- ALUNO com XMLFOREST
SELECT XMLELEMENT(
  "ALUNO",
  XMLFOREST(CODIGO AS "CODIGO", NOME AS "NOME", EMAIL AS "EMAIL", CURSO AS "CURSO")
) AS XML_ALUNO
FROM ALUNO;

-- CURSO com elementos aninhados
SELECT XMLELEMENT(
  "CURSO",
  XMLELEMENT("CODIGO", CODIGO),
  XMLELEMENT("DESCRICAO", DESCRICAO)
) AS XML_CURSO
FROM CURSO;

-- CURSO com atributos
SELECT XMLELEMENT(
  "CURSO",
  XMLATTRIBUTES(CODIGO AS "CODIGO", DESCRICAO AS "DESCRICAO")
) AS XML_CURSO
FROM CURSO;

-- CURSO com XMLFOREST
SELECT XMLELEMENT(
  "CURSO",
  XMLFOREST(CODIGO AS "CODIGO", DESCRICAO AS "DESCRICAO")
) AS XML_CURSO
FROM CURSO;