Tutorial MySQL - Stored Procedures

MYSQL

STORAGE PROCEDURES

Curso Profissional Técnico de Gestão e Programação de Sistemas Informáticos Programação e Sistemas de Informação 12.º Ano

–

Turma K

MySQL Stored Procedure

–

Temas abordados

1 - Introdução ao SQL Stored Procedures Vais aprender o que é o um stored procedure em MySQL, também podes compreender as vantagens e desvantagens do Stored Procedures.

2 - “Getting started” MySQL Stored Procedures Neste tutorial, você vai aprender a escrever uma stored procedure e chamá-lo na linha de comando do MySQL.

3 - Variáveis em Stored Procedures Vais aprender como declarar uma variável e usar SET e SELECT INTO para atribuir outro valor a uma variável.

4 - Parâmetros dos Stored Procedures Neste tutorial, vais aprender a escrever Stored Procedures com parâmetros. Vamos também analisar um par de exemplos para ajudar a entender melhor os parâmetros em Stored Procedures.

5 – Controle condicional Neste tutorial, aprenderás como usar o controle condicional, IF e instruções CASE em Stored Procedures.

6 - Loops em Stored Procedures Aprenda a usar várias instruções de loop no Stored Procedures.


–

Turma K

1 - Introdução ao SQL Stored Procedures Um stored procedure, por definição, é um segmento de código SQL que é armazenado na base de dados e pode ser chamado mais tarde por um programa, um trigger ou mesmo outro stored procedure. Um stored procedure, que se chama a si mesmo, é recursivo. Quase todas as RDMBS suportam stored procedure recursivos, mas o MySQL não os suporta muito bem. Verifique a sua versão do MySQL antes de utilizar stored procedures recursivos. MySQL é certamente o RDBMS cuja fonte é mais aberta, sendo amplamente utilizado por comunidades e empresas, mas durante a primeira década de sua existência, ele não suportava os stored procedure, triggers, event... Desde o MySQL versão 5.0, esses recursos foram adicionados à base de dados MySQL para permitir ser mais flexível e poderoso. Portanto, antes de iniciar este tutorial sobre o stored procedure, é necessário ter a versão 5.x do MySQL instalado no seu computador. Vantagens dos Stores Procedures

■ Os Stored procedures aumentam o desempenho da aplicação. Uma vez criado, o Stored

procedure é compilado e armazenado no catálogo da base de dados. ■ Os Stored Procedure reduz o tráfego entre o servidor de aplicação e a base de dados porque

em vez de enviar vários comandos não compilados com longas instruções SQL, a aplicação só tem de enviar o nome do Stored procedure e obter os dados de volta para manipulá-los ainda mais. ■ Os Stored Procedure são reutilizáveis e transparentes para qualquer aplicação que pretenda

utilizá-lo. Os Stored Procedure expõem a interface da base de dados para todos os aplicativos para que os programadores não tenham que programar as funções que já estão suportadas num Stored procedure. ■Os Stored procedure é seguro. O administrador de bases de dados pode conceder o direito

de acesso às aplicações que queiram aceder aos Stored procedure armazenados no catálogo da base de dados sem conceder qualquer permissão a tabelas de dados subjacentes.

Desvantagens dos Stores Procedures

■Os Stored procedures fazem sobrecarga no servidor de base de dados tanto na memória

como nos processadores. ■Os Stored procedure contém apenas SQL declarativo, por isso é muito difícil escrever um

procedimento com a complexidadede outras línguas na camada de aplicação, tais como em Java, C #, C + + ...


–

Turma K

■ Não podemos depurar um stored procedure em quase todas as RDMBSs incluindo o MySQL.

Existem algumas soluções sobre este problema, mas ainda não são suficientes. ■ Escrever e manter um stored procedure geralmente exige habilidades especializadas que

nem todos os programadores possuem. Isto apresenta um problema no desenvolvimento das aplicações e na fase de manutensão. Os Stored Procedure têm as suas vantagens e desvantagens, como mencionado acima. Portanto, quando se desenvolve uma aplicação, deve haver equilíbrio para escolher se deseja utilizar o Stored Procedure ou não.

2 - “Getting started “ MySQL Stored Procedures Neste tutorial, vamos escrever o primeiro Stored Procedure simples, e chamá-lo de linha de comando do MySQL. Escrevendo o primeiro Stored Procedure

O primeiro stored procedure é muito simples. Ele recupera todos os produtos da tabela produtos. Primeiro vamos dar uma olhada no código fonte do stored procedure abaixo: DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT *

FROM products;

END // DELIMITER ;

O primeiro comando que você vê é DELIMITER / /. Este comando não está relacionado com o stored procedure. DELIMITER no MySQL é usado para alterar o delimitador padrão (ponto e vírgula) para outro. Neste caso, o delimitador é alterado de ponto e vírgula (;) para / /, assim podes ter várias instruções SQL dentro do stored procedure, que podem ser separados por ponto e vírgula. Após a palavra-chave END usamos delimitador / / para mostrar o fim do stored procedure. O último comando muda o delimitador de volta ao padrão (ponto e vírgula). A fim de criar um stored procedure usamos CREATE PROCEDURE. Após a instrução CREATE PROCEDURE você pode especificar o nome do stored procedure, neste caso, é GetAllProducts. A parte do corpo do stored procedure começou entre o bloco BEGIN eo bloco END. Você pode escrever código declarativo SQL aqui. Posteriormente iremos analisar isto melhor. Criamos um novo stored procedure, mas também precisamos saber invocá-lo no programa ou na linha de comando do MySQL.


–

Turma K

Chamar o stored procedure A fim de chamar um stored procedure, usamos o seguinte comando SQL:

Para chamar o stored procedure criado anteriormente basta escrever: CALL GetAllProducts(); Recebemos assim todos os produtos da tabela produtos.

3 - Variáveis em Stored Procedures Declarando variáveis

As variáveis são usadas no stored procedure para armazenar o resultado imediato. Você pode declarar uma variável com a seguinte sintaxe:

A seguir à instrução DECLARE está o nome da variável. O nome da variável deve obedecer a critérios de produção de nomes para variáveis e não deve ter o mesmo nome da tabela ou coluna de uma base de dados. Em seguida, você pode especificar o tipo de dados da variável, pode ser qualquer tipo que o MySQL suporte como INT, VARCHAR, DATETIME ... juntamente com o tipo de dados definimos o tamanho da variável. Quando você declarar uma variável, o seu valor inicial é NULL. Você também pode atribuir um valor padrão para a variável usando o comando DEFAULT. Por exemplo, podemos definir a variável total_sale com o tipo de dados INT eo valor padrão é 0 como se segue: DECLARE Total_sale INT DEFAULT 0 Para declarar duas ou mais variáveis com o mesmo tipo de dados podemos usar apenas um DECLARE, tais como: DECLARE x,y INT DEFAULT 0 Nós declaramos duas variáveis X e Y com o mesmo tipo de dados INT e seu valor padrão é zero.


–

Turma K

Atribuindo variáveis

Uma vez que você declarou uma variável, você pode começar a usá-la. Para atribuir outro valor a uma variável que você pode usar o comando SET, por exemplo: DECLARE TOTAL_COUNT INT DEFAULT 0 SET TOTAL_COUNT = 10; O valor da variável TOTAL_COUNT agora é de dez (10). Ao lado de instrução SET, podemos usar SELECT … INTO p ara atribuir um resultado de consulta

a uma variável. DECLARE total_products INT DEFAULT 0 SELECT COUNT(*) INTO total_products FROM products; No exemplo acima, vamos declarar a variável total_products e iniciar seu valor em zero. Então nós usamos instrução SELECT … INTO para atribuir o total de produtos à variável total_products.

Variáveis scope (locais ou globais) Uma variável tem o seu próprio escopo. Se você declarar uma variável dentro de um stored procedure, ela estará fora de alcance quando chegar ao fim do stored procedure. Se você definiu uma variável dentro do bloco BEGIN / END num stored procedure esta estará fora de alcance quando o stored procedure atingir o END. Você pode declarar duas ou mais variáveis com o mesmo nome em escopos diferentes, a única variável é eficaz no seu procedimento. Uma variável com o "@" no início é variável de sessão. Ela existe até ao final da sessão.

4 - Parâmetros dos Stored Procedures Quase todos os stored procedure a desenvolver requerem parâmetros. Parâmetros fazem o stored procedure mais flexível e útil. No MySQL, um parâmetro adquire um dos seguintes três modos - IN, OUT e INOUT. IN é o modo padrão( declarado por defeito).


–

Turma K

■ IN indica que um parâmetro pode ser passado para um stored procedure, mas qualquer

alteração dentro do stored procedure não altera o parâmetro. Suponha que você quer passar o parâmetro ID, que é igual a 10, para o stored procedure getAll (Id), após executar o stored procedure o valor do Id ainda é 10 mesmo que o stored procedure getAll alterar o valo r do mesmo. ■ OUT este modo indica que o stored procedure pode alterar o parâmetro e devolve-lo ao

programa de chamada. ■ INOUT, obviamente, este modo é a combinação do modo IN e OUT, você pode passar

parâmetros para o stored procedure e recuperá-los com um novo valor a partir do programa de chamada. A sintaxe da definição de um parâmetro no stored procedure é o seguinte: MODE param_name param_type(param_size) MODE pode ser IN, OUT ou INOUT dependendo da finalidade do parâmetro especificado. param_name é o nome do parâmetro. O nome não deve ser o mesmo que o nome da coluna. Por fim temos o tipo de parâmetro e seu tamanho. Cada parâmetro é separado por uma vírgula, se o stored procedure tiver mais de um parâmetro. Vamos praticar com os exemplos abaixo para entender melhor. O primeiro exemplo é um stored procedure para obter todos os escritórios dum dado país. Aqui está o código fonte do SQL: DELIMITER // CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255)) BEGIN SELECT city, phone FROM offices WHERE country = countryName; END // DELIMITER ;

Como você pode ver que nós usamos countryName como parâmetro IN com seu tipo é varchar e seu tamanho é de 255. No corpo de stored procedure, nós mostramos todos os escritórios onde country é o countryName. Suponha que queremos mostrar todos os escritórios nos EUA, basta passar o valor para os stored procedures deste modo:


–

Turma K

CALL GetOfficeByCountry('USA') Ele retorna todos os escritórios nos EUA. Para obter todos os escritórios na França, basta substituir a França pelos USA no stored procedures: CALL GetOfficeByCountry( ‘France’ ) Ele retorna todos os escritórios em França. No segundo exemplo, vamos escrever um stored procedure para contar o status de uma encomenda, esse status pode tomar valores tais como expedido, resolvido, cancelado, em espera, contestado ou em andamento. Aqui está o stored procedure: DELIMITER $$ CREATE PROCEDURE CountOrderByStatus( IN orderStatus VARCHAR(25), OUT total INT) BEGIN SELECT count(orderNumber) INTO total FROM orders WHERE status = orderStatus; END$$ DELIMITER;

O stored procedure CountOrderByStatus tem dois parâmetros: ■ orderStatus é parâmetro IN; passamos o status do pedido, tal como, enviado ou em espera

para obter o número do mesmo ■total é o parâmetro OUT que usamos para obter o total de um status específico.

Assim, para obter o número de pedidos enviados efectuamos a s seguintes instruções: CALL CountOrderByStatus('enviados',@total); SELECT @total AS total_enviados;

Para obtermos o numero de encomendas ‘em espera’, efectuamos a s seguintes instruções: CALL CountOrderByStatus('em espera ',@total); SELECT @total AS

total_em_espera;


–

Turma K

No terceiro procedimento, vamos demonstrar o parâmetro INOUT. O stored procedure ‘Capitalize’ recebe todas as palavras em uma string e retorna-a para o programa de chamada. O código-fonte do stored procedure é a seguinte: DELIMITER $$ CREATE PROCEDURE `Capitalize`(INOUT str VARCHAR(1024)) BEGIN DECLARE i INT DEFAULT 1; DECLARE myc, pc CHAR(1); DECLARE outstr VARCHAR(1000) DEFAULT str; WHILE i <= CHAR_LENGTH(str) DO SET myc = SUBSTRING(str, i, 1); SET pc = CASE WHEN i = 1 THEN ' ' ELSE SUBSTRING(str, i - 1, 1) END; IF pc IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN SET outstr = INSERT(outstr, i, 1, UPPER(myc)); END IF; SET i = i + 1; END WHILE; SET str = outstr; END$$ DELIMITER ;

Aqui está o uso do stored procedure Capitalize: SET @str = 'mysql stored procedure tutorial'; CALL Capitalize(@str); SELECT @str;

E o valor de @str é 'Mysql Stored Procedure Tutorial'

5 – Controle condicional O MySQL suporta duas declarações de controle condicional, o IF e o CASE.


–

Turma K

A instruçãoIF IF expression THEN commands [ELSEIF expression THEN commands] [ELSE commands] END IF;

Os comandos associados com IF ou ELSE ou ELSEIF só são ex ecutados quando a expressão é avaliada como TRUE. Uma das armadilhas comuns de instrução SE é o valor NULL; Quando a expressão é avaliada como NULL, ela não é nem verdadeira nem falsa. Aqui estão várias combinações IF: IF expression THEN commands END IF;

IF expression THEN commands ELSE commands END IF;

IF expression THEN commands ELSEIF expression THEN commands ELSE commands END IF;

Você pode ter instrução IF aninhada com outra IF. A instrução CASE Quando várias condições são usadas com o IF (IF’s encadeados), o código não é fácil de ler. Neste momento, o CASE pode ser usado para tornar o código mais claro. A sintaxe da instrução CASE é a seguinte:

CASE WHEN expression THEN commands …

WHEN expression THEN commands ELSE commands END CASE;


–

Turma K

6 - Loops em Stored Procedures O MySQL suporta loops que permitem processar os comandos de forma iterativa. Os loops padrão são os seguintes: WHILE loop

A sintaxe do loop WHILE é a seguinte: WHILE expression DO Statements END WHILE

Primeiro, o loop verifica a expressão, se é verdade que irá executa instrução até que a expressão se tornar falsa. Porque o while loop verifica a expressão antes das instruções executadas, é conhecido como pré-teste loop. Aqui está um exemplo de como usar while num stored procedure: DELIMITER $$ DROP PROCEDURE IF EXISTS WhileLoopProc$$ CREATE PROCEDURE WhileLoopProc() BEGIN DECLARE x

INT;

DECLARE str

VARCHAR(255);

SET x = 1; SET str = ''; WHILE x <= 5 DO SET

str = CONCAT(str,x,',');

SET

x = x + 1;

END WHILE; SELECT str; END$$ DELIMITER ;

No stored procedure acima, construimos uma string repetidamente até que a variável x seja superior a 5, depois enviamos o resultado da string para a consola usando o comando SELECT. Uma armadilha comum de quase todos os programadores encontram é que a variável x não é inicializada, seu valor padrão é NULL e a condição de while é sempre verdadeiro, o bloco de código dentro do laço é executado indifinidamente até que o seu servidor de base de dados tem um ‘crash’ .


–

Turma K

REPEAT loop

A sintaxe do repeat loop é a seguinte: REPEAT Statements; UNTIL expression END REPEAT

Primeiro as declarações são executadas e, em seguida a expressão é avaliada. Se a expressão for avaliada como verdadeira as declarações são executadas várias vezes até que seu valor seja falso. Because the repeat loop checks the expression after the execution of statements so it is also known as post-test loop. Porque o loop de repetição verifica a expressão após a execução de instruções por isso é também conhecido como post-teste loop. We can rewrite the stored procedure above by using repeat loop as follows: Podemos reescrever o stored procedure anterior usando o REPEAT da seguinte forma: DELIMITER $$ DROP PROCEDURE IF EXISTS RepeatLoopProc$$ CREATE PROCEDURE RepeatLoopProc() BEGIN DECLARE x

INT;

DECLARE str

VARCHAR(255);

SET x = 1; SET str = ''; REPEAT SET


SET

x = x + 1;

UNTIL x > 5 END REPEAT; SELECT str; END$$ DELIMITER ;

LOOP loop, LEAVE e ITERATE


–

Turma K

A instrução LEAVE permite que você saia do loop. É um pouco como o break de outras linguagens como Java, C # ... A instrução ITERATE permite iniciar o ciclo novamente. É como o continue em Java ou C #. O MySQL também suporta um LOOP loop que lhe permite executar comandos repetidamente e mais flexível. Aqui está um exemplo do uso LOOP loop. DELIMITER $$ DROP PROCEDURE IF EXISTS LOOPLoopProc$$ CREATE PROCEDURE LOOPLoopProc() BEGIN DECLARE x

INT;

DECLARE str

VARCHAR(255);

SET x = 1; SET str = ''; loop_label: LOOP IF

x > 10 THEN LEAVE loop_label;

END

IF;

SET

x = x + 1;

IF

(x mod 2) THEN ITERATE loop_label;

ELSE SET


END IF; END LOOP; SELECT str; END$$ DELIMITER ;

O stored procedure apenas constrói string com números pares. Primeiro vamos definir um loop_label, se uma variável x é maior que 10, o ciclo é encerrado por causa da declaração LEAVE. Caso contrário, se a variável x é ímpar, o ITERATE ignora tudo abaixo e continua, se a variável x é par, o bloco após o ELSE constroi uma string com números pares.

Tutorial MySQL - Stored Procedures

Recommend Documents