Views e Stored Procedures

View,
Stored Procedure e
Function
Prof. André Luiz Montevecchi
[email protected]
View
• Representa logicamente subconjuntos de dados de uma
ou mais tabelas, ou, até mesmo, de outras views.
• Não contém dados próprios, mas é como uma janela
através da qual os dados das tabelas podem ser vistos
ou alterados.
• É armazenada como uma instrução SELECT no dicionário
de dados
View
• As views permitem que usuários façam consultas simples
para recuperar resultados de consultas complexas.
• Pode ser usada para recuperar os dados de várias
tabelas.
• As views fornecem acesso aos dados a grupos de
usuários de acordo com seus critérios em particular.
View
• Uma consulta pode ser embutida na instrução CREATE
VIEW.
• CREATE VIEW nome-da-visão
[(nome-de-coluna-simples [,nome-de-colunasimples]*)]
AS Consulta
• Os dados da view são recuperados da mesma forma que
os dados de uma tabela. Podem ser exibidos o conteúdo
de toda a view ou somente ver linhas e colunas
específicas. Ex: Select * from nome_view
View Simples e Complexa
• A diferença básica está relacionada às operações DML
(insert, update, delete).
• View simples:
– Apresenta os dados a partir de uma única tabela
– Não contém funções ou grupos de dados
– Pode executar a DML através da view
• View complexa:
– Apresenta os dados a partir do join de várias tabelas
– Contém funções ou dados agrupados
– Nem sempre executa a DML através da view
View Simples e Complexa
•
View simples:
CREATE VIEW SAMP.PROJ_COMBO (NUM_PROJ, PROJ_DATA_FIM,
PROJ_EQUIPE, PROJ_CHEFE) AS
SELECT NUM_PROJ, PROJ_DATA_FIM, PROJ_EQUIPE,
PROJ_CHEFE FROM SAMP.PROJETO
UNION ALL
SELECT NUM_PROJ, EMSTDATE, EMPTIME, NUM_EMP FROM
SAMP.ATIV_EMP WHERE NUM_EMP IS NOT NULL
,
•
View complexas:
CREATE VIEW SAMP.DEPT_SUM_VU (NAME, MINSAL, MAXSAL,
AVGSAL) AS
SELECT D.DNAME, MIN(E.SAL), MAX(E.SAL), AVG(E.SAL) FROM
SAMP.EMP E, SAMP.DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME;
View
• Não poderá ser removido uma linha da View se ela contiver:
– Funções de grupo
– Cláusula GROUP BY
– A palavra-chave DISTINCT
– A palavra-chave da pseudocoluna ROWNUM
• Não poderá ser modificado dados em uma View se ela contiver:
– Uma das condições acima
– Colunas definidas por expressões (Ex: SAL * 12)
• Não poderá ser adicionado dados em uma View se ela contiver:
– Uma das condições acima
– Houver colunas NOT NULL na tabela base que não foi levada
para a view.
View
• É possível executar verificações de integridade
referencial pelas views.
• Essas restrições podem ser impostas no nível de banco
de dados.
• A view poderá ser usada para proteger a integridade dos
dados, mas o uso é muito restrito.
• A cláusula WITH CHECK OPTION especifica que
INSERTS e UPDATES executados pela view não tem
permissão de criar linhas que a view não possa
selecionar. É garantido que a DML na view continue no
domínio da view.
View
CREATE VIEW EMPVU20
AS
SELECT *
FROM SAMP.EMP E
WHERE E.DEPTNO = 20
WITH CHECK OPTION ;
• Qualquer tentativa de alteração do número do departamento para
qualquer linha na view falhará porque ela violará a restrição WITH
CHECK OPTION.
UPDATE EMPVU20
SET DEPTNO = 10
WHERE EMPNO = 7788
ERRO: Violação na cláusula WITH
CHECK OPTION na View
View
• Para remover uma view basta usar a instrução:
DROP VIEW NOME_VIEW;
Ex: DROP VIEW EMPVU10;
• A instrução remove a definição da view no banco de
dados, sem eliminar outras views ou tabelas bases que
ela referencia.
• As views ou outras aplicações baseadas em views
deletadas tornam-se inválidas.
Exercício
• Escreva uma VIEW para visualizar o
nome do dentista e o nome do convênio o
qual ele está relacionado.
Procedimentos (Procedures)
•
Os procedimentos e funções assemelham-se muito
com os procedimentos e funções em linguagens como
C e Pascal.
•
Podem ser chamados a partir de outro bloco SQL.
•
São criados, compilados e depois armazenados como
objetos no banco de dados.
•
Promovem a reutilização do código.
Procedimentos (Procedures)
• Somente poderão executar o comando CREATE STORED
PROCEDURE, usuários que são membros da role de
servidor sysadmin ou das roles de Banco de Dados
db_owner e db_ddladmin;
• Em um Stored Procedure, podemos incluir qualquer
comando T-SQL, com exceção dos seguintes: CREATE
PROCEDURE, CREATE RULE, CREATE TRIGGER E
CREATE VIEW;
• Em um Stored Procedure podemos referenciar tabelas,
Views, outras Stored Procedures e tabelas temporárias.
Procedimentos (Procedures)
A estrutura da instrução p/ criação de uma procedure é:
CREATE PROCEDURE procedure_name
[(parameter1 datatype1,
parameter2 datatype2, ...)]
AS
[local_variable_declarations; …]
-- SQL statements
Procedimentos (Procedures)
• Maneiras de chamar um procedimento:
• EXEC SP_DENTISTA ‘nome’;
• EXECUTE SP_DENTISTA ‘nome’;
• Eliminando um procedimento:
• DROP PROCEDURE nome_do_procedimento;
Funções (Functions)
• São blocos nomeados que retornam um valor;
• Uma diferença básica entre um procedimento e uma
função é que o procedimento é uma instrução T-SQL por
si própria, enquanto uma chamada de função é chamada
como parte de uma expressão.
Funções (Functions)
• A estrutura da instrução para criação de uma função é:
• CREATE FUNCTION function_name
( [ { @parameter_name [AS]
scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
• RETURNS scalar_return_data_type
• [ WITH < function_option> [ [,] ...n] ]
• [ AS ]
• BEGIN
function_body
RETURN scalar_expression
Funções (Functions)
• Maneiras de chamar uma função:
• SELECT F_SOMA (2,3);
• Eliminando uma função:
• DROP FUNCTION nome_da_função;
Exercício
• Escreva uma função para somar dois
números inteiros.
Exercício 2
• Escreva uma função que receba um
número inteiro e retorne o número ao
quadrado!