Banco de Dados II

1. Banco de Dados II 5P/SI – 2011/01 Prof. Carlos Alberto Seixas

2. Agenda • Implementação sobre SGBD MySQL • DateTime, Date, Timestamp • Stored Procedures • Functions

3. O ano de 2038 • O problema do ano 2038 é uma falha na representação de datas em computadores, que pode causar erros em alguns programas de computador no ano de 2038. • O problema afeta os programas que utilizam a representação de tempo POSIX, em que a data é calculada através do número de segundos (ignorando os segundos bissextos) desde 1 de janeiro de 1970.

4. Datetime • O tipo DATETIME é usado quando você precisa de valores que contém informações sobre data e a a hora. • MySQL recupera e mostra valores DATETIME no formato 'YYYY-MM-DD HH:MM:SS'. • A faixa suportada é de '1000-01-01 00:00:00' até '9999-12-31 23:59:59'.

5. DATE • O tipo DATE é usado quando se necessita apenas do valor da data, sem a parte da hora. • MySQL recupera e mostra valores do tipo DATA no formato 'YYYY-MM-DD'. • A faixa suportada é de '1000-01-01' até '9999-12-31'.

6. Timestamp • Numeral de 11 dígitos que representao número de segundos passados desde 01 de janeiro de 1970. • Os valores TIMESTAMP não podem ser anteriores a 1970 ou posteriores a 2037. • Isto significa que datas como '1968-01-01', são permitidas como valores DATETIME ou DATE, mas não são válidas para valores TIMESTAMP e serão convertidas para 0 se atribuídas para tais objetos. • DATETIME, DATE, e TIMESTAMP podem ter o mesmo formato, mas não tem a mesa faixa de valores.

7. Stored Procedures • Conjunto de comandos SQL armazendos no servidor que realizam tarefas específicas e são ativadas como comandos. Podem receber parâmetros de entrada, saída ou entrada e saída.

8. Procedimentos armazenados ou stored routines • São programas armazenados no servidor, pré-compilados, chamados de forma explícita para executar alguma lógica de manipulação de dados, podendo retornar ou não algum valor". • Estão disponíveis exatamente desde a versão 5.0 do SGBD MySQL.

9. Stored Procedures - Localização São inseridos em uma tabela chamada ROUTINES no banco de dados INFORMATION_SCHEMA, que é o dicionário de dados do MySQL.

10. Como listar • Para listarmos todos os stored routines (Stored Procedure e Functions), no mysql client: • mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES;

11. Sintaxe Geral • CREATE PROCEDURE proc_name([parameters, ...])[characteristics][BEGIN]      corpo_da_rotina;[END]

12. CREATE PROCEDURE proc_name([parameters, ...])[characteristics] • proc_name: seu procedimento armazenado deve ter um nome, para quando for chamado, podermos então usá-lo; • parameters: nessa parte do procedimento, informaremos os parâmetros da seguinte forma: [IN | OUT | INOUT] nome_parametro tipo_dado. • Existem 3 tipos de parâmetros em uma Stored Procedure no MySQL: • IN, OUT e INOUT. • characteristics: as características do procedimento pode apresentar. • Questões de segurança, se é determinística ou não, qual a linguagem que estamos utilizando e se nosso procedimento modificará dados na banco de dados,

13. Tipos de Parâmetros • IN => de entrada, ou seja, um parâmetro cujo seu valor será utilizado no interior do procedimento para produzir algum resultado; • OUT => retorna algo de dentro do procedimento para o lado externo, colocando os valores manipulados disponíveis na memória ou no conjunto de resultados; • INOUT => entrada e saída

14. EXEMPLO 1 DELIMITER $$ DROP PROCEDURE IF EXISTS ‘escola’.’proc_mensagem_oi’$$ CREATE PROCEDURE ‘escola’.’proc_mensagem_oi’ ( ) BEGIN SELECT ‘OI, MENSAGEM ENVIADA !’; END $$ DELIMITER;

15. EXEMPLO 1 - chamada • Bem, procedimento compilado basta executarmos o procedimento com o seguinte comando: • CALL proc_mensagem_oi(); • OBS.: Após a versão 5.1.18, não é mais necessário os parênteses caso o procedimento não receba parâmetros. • A resposta do procedimento será: • “OI, MENSAGEM ENVIADA !’”

16. Exemplo 2 • Exemplo: • DELIMITER $$DROP PROCEDURE IF EXISTS escola.pr_total $$CREATE PROCEDURE escola.pr_total ( OUT total INT)BEGINSELECT count(0) INTO totalFROM produtos;END $$

17. Chamada • Executando a procedure via linha de comando • C:\ mysql -u root -p(após teclar ENTER informe a senha do usuário root) • mysql> use loja (Informamos o database que iremos utilizar) mysql> callpr_total(@teste); (Chamamos a procedurepr_total e armazenamos o resultado em teste.) mysql> select @teste; (Exibimos o resultado. No exemplo acima, trazemos a quantidade de produtos cadastrados.)

18. Functions Características: • São definidas pelo usuário, • Promovem agilidade no desenvolvimento de sistemas, • Economia na escrita de código, • Lógica também por conta do banco de dados, • Oferecem mais segurança aos componentes que manipulam dados e produzem informações, • Facilitam a manutenção , • Minimizam o consumo de banda entre o servidor de banco de dados MySQL e a sua aplicação.

19. Exemplo 1 mysql> DELIMITER // mysql> CREATE FUNCTION palavra (texto CHAR(20)) RETURNS CHAR(50) -> RETURN CONCAT('Voce digitou: ',texto,' !!!'); -> // mysql > DELIMITER ;

20. Chamando função • mysql> SELECT palavra('oi'); • Voce digitou: oi !!!

21. Exemplo 2 • Tabela com os nomes dos alunos de uma disciplina com as notas de quatro bimestres, mostrando a média final. • mysql> CREATE TABLE notas(aluno VARCHAR(10), nota1 INT, nota2 INT, nota3 INT, nota4 INT); • Insira valores nesta tabela. • mysql> INSERT INTO notas VALUES('Maria', 10, 9, 10, 10); mysql> INSERT INTO notas VALUES('Pedro', 5, 2, 3, 4);

22. Exemplo - Função mysql> DELIMITER // mysql> CREATE FUNCTION media (nome VARCHAR(10)) -> RETURNS FLOAT -> DETERMINISTIC –> BEGIN -> DECLARE n1,n2,n3,n4 INT; -> DECLARE med FLOAT; -> SELECT nota1,nota2,nota3,nota4 INTO n1,n2,n3,n4 FROM notas WHERE aluno = nome; -> SET med = (n1+n2+n3+n4)/4; -> RETURN med; -> END -> // mysql> DELIMITER ;

23. Chamando • mysql> SELECT media('Maria'); • media('Maria') • 9.75 • mysql> SELECT media('Pedro'); • media('Pedro’) • 3.5

24. Exercício • Prática 1 – Manipulação de objetos no MySQL, via SQLYog ,Verificação do serviço MySQL, conexão com MySQL através do MySQLYog, criação Banco de Dados: Conta • Prática 2 – Exercício 3 – StoredProcedure

25. Bibliografia • Guia de referencia MySql 5.0, disponível em Mysql.com • Wagner Bianchi,Imasters.com, Mysql 5.