007_Aula_12_Mai_2016 PrgBD

Programação em PL/SQL

Stored Procedures

• Stored procedures são programas que ficam armazenados no banco de dados e podem ser escritos em PL/SQL, C++ ou Java.

• PL/SQL (acrônimo para a expressão inglesa Procedural

Language/Structured Query Language) é uma extensão da linguagem padrão SQL para o SGBD Oracle da Oracle Corporation. É uma linguagem procedural da Oracle que estende a linguagem SQL.

• Utilizaremos a criação do cenário de hoje para conhecermos os conceitos de criação de constraints.

Programação em PL/SQL

Constraints

• Constraints que pode ser traduzido como restrições são utilizadas como “restrição de integridade”, que são regras que restringem os valores no banco de dados com a finalidade de garantir integridade dos dados.

• Falamos a respeito da constrint NOT NULL e veremos como criar as constraints PRIMARY KEY (chave primária) e FOREIGN KEY (chave estrangeira)

Programação em PL/SQL

Criação do Cenário

• Crie o usuário AULA1205 e conceda a ele o grant de DBA.

• Conecte-se a esse usuário utilizando a ferramenta SQL Developer

• Antes de criarmos alguma coisa consulte as views USER_CONSTRAINTS e USER_OBJECTS utilizando os comandos abaixo:

SELECT * FROM user_constraints; SELECT * FROM user_objects;

Você poderá constatar que não existem nem constraints nem objetos criados ainda para esse usuário e é assim que deveria ser pois acabamos de criá-lo.

Programação em PL/SQL

Criação do Cenário

• Criar a tabela EMPREGADOS com os atributos abaixo:

NOME ORIGINAL ALIAS

EMPLOYEE_ID EMPREGADO_ID FIRST_NAME NOME

LAST_NAME SOBRENOME

SALARY SALARIO

DEPARTMENT_ID DEPARTAMENTO_ID

Programação em PL/SQL

Criação do Cenário

AS

SELECT EMPLOYEE_ID EMPREGADO_ID, FIRST_NAME NOME,

LAST_NAME SOBRENOME, SALARY SALARIO,

DEPARTMENT_ID DEPARTAMENTO_ID FROM HR.EMPLOYEES ;

Programação em PL/SQL

Criação do Cenário

• Consulte novamente a view USER_CONSTRAINTS, você verá que existe uma constraint para a tabela EMPREGADOS em que a search_condition é “SOBRENOME” IS NOT NULL.

Isso acontece pois quando a tabela HR.EMPLOYEES foi criada foi utilizada a constraint NOT NULL para o atributo LAST_NAME e como criamos nossa tabela a partir dessa a constraint veio junto.

• Consulte também a view USER_OBJECTS e poderá observar que o usuário tem agora um objeto do tipo tabela.

Programação em PL/SQL

Criação do Cenário

• Criar a tabela DEPARTAMENTOS com os atributos abaixo:

NOME ORIGINAL ALIAS

DEPARTMENT_ID DEPARTAMENTO_ID DEPARTMENT_NAME DEPARTAMENTO_NOME

A tabela deve ser criada baseada na tabela HR.DEPARTMENTS. • CREATE TABLE DEPARTAMENTOS

AS

SELECT DEPARTMENT_ID DEPARTAMENTO_ID,

DEPARTAMENT_NAME DEPARTAMENTO_NOME FROM HR.DEPARTMENTS ;

Programação em PL/SQL

Criação do Cenário

• Consultando a view USER_CONSTRAINTS você verá que agora são duas constraints e a view USER_OBJECTS tem também dois objetos.

Programação em PL/SQL

Criando uma Constraint Primary Key

• Antes de criarmos uma PK vamos ver o que acontece se incluirmos na

tabela empregados um empregado com um EMPREGADO_ID que já existe. Para isso utilize o comando abaixo:

• INSERT INTO empregados VALUES

(100, 'Mario', 'Assis', 1000, 210) ;

• Após o comando se você selecionar os dados da tabele EMPREGADOS verá que existem dois empregados com o código 100, o Steven e o Mario.

Programação em PL/SQL

Criando uma Constraint Primary Key

• Em uma situação real isso não seria aceitável, pois não pode haver dois empregados com o mesmo código.

• Vamos desfazer o insert que efetuamos, execute o comando ROLLBACK para isso.

• Agora para criar a constraint PRIMARY KEY digite o comando abaixo: ALTER TABLE EMPREGADOS

Programação em PL/SQL

Criando uma Constraint Primary Key

• Observe que “pk_empregados” é o nome da constraint, você pode atribuir qualquer nome, mas é importante que o nome da constraint identifique qual o tipo de constraint e a qual tabela ela pertence.

• Consulte a view USER_CONSTRAINTS e verá que a constraint criada aparece lá é é do tipo P (de Primary).

• Consulte também a view USER_OBJECTS, lá você verá que apareceu um objeto do tipo INDEX e tem o nome de PK_EMPREGADOS, isso acontece pois o Oracle utiliza um índice para garantir de que não haverá valores repetidos para o atributo que foi utilizado para a criação da PK.

Programação em PL/SQL

Criando uma Constraint Primary Key

INSERT INTO empregados VALUES

(100, 'Mario', 'Assis', 1000, 210) ;

• Você receberá a seguinte mensagem de erro:

Erro de SQL: ORA-00001: restrição exclusiva (AUL1205.PK_EMPREGADOS) violada

• Após a criação da constraint PRIMARY KEY o SGBD garante de que não haverá duplicidade para o atributo EMPREGADO_ID na tabela

Programação em PL/SQL

Criando uma Constraint Primary Key

• Criar a constraint de primary key também para a tabela DEPARTAMENTOS. • ALTER TABLE DEPARTAMENTOS

ADD CONSTRAINT pk_departamentos PRIMARY KEY (DEPARTAMENTO_ID);

Programação em PL/SQL

Criando uma Foreign Key

• Vamos fazer agora um teste de incluirmos um empregado em que o departamento cadastrado para ele não exista.

• INSERT INTO empregados VALUES

(300, 'Mario', 'Assis', 1000, 280) ;

• Observe que o departamento 280 não existe na tabela DEPARTAMENTOS mas mesmo assim o insert foi aceito.

Programação em PL/SQL

Criando uma Foreign Key

• Vamos repetir o JOIN efetuado na aula anterior para ver o que acontece. • SELECT *

FROM empregados

JOIN departamentos

USING (departamento_id) ORDER BY 1;

• Você pode observar que o comando acima não mostra o usuário 300

inserido a pouco. Isso deve-se ao fato de o departamento 280 no qual ele foi cadastrado não existe.

Programação em PL/SQL

Criando uma Foreign Key

• ALTER TABLE empregados

ADD CONSTRAINT fk_empregados_departamentos FOREIGN KEY (departamento_id)

REFERENCES departamentos(departamento_id);

• FK_EMPREGADOS_DEPARTAMENTOS é o nome da constraint tipo FOREIGN KEY, que está sendo criada para a tabela EMPREGADOS e referenciando a tabela DEPARTAMENTOS.

Programação em PL/SQL

Criando uma Foreign Key

INSERT INTO empregados VALUES

(300, 'Mario', 'Assis', 1000, 280) ;

• restrição de integridade (AUL1205.FK_EMPREGADOS_DEPARTAMENTOS) violada - chave mãe não localizada

• Após a criação da constraint FOREIGN KEY o SGBD garante de que não

haverá valores no atributo DEPARTMENT_ID da tabela EMPREGADOS, sem que haja correspondência no atributo DEPARTAMENTO_ID na tabela

Programação em PL/SQL

Criando uma Procedure

• Antes de criarmos a procedure é necessário que seja incluído um novo atributo na tabela EMPREGADOS, faça isso executando o comando abaixo. • ALTER TABLE EMPREGADOS

ADD SALARIO_REAJUSTADO NUMBER (8,2) ;

• Para criar a procedure execute os comandos que estão no arquivo 008_Stored_Procedure_Comentada.sql.

• A criação do procedimento e seus comandos foram explicados em sala de aula, no arquivo acima citado você encontrará comentários sobre o