Em meu artigo anterior, falamos sobre como utilizar o recurso de stored procedures no MySQL 5. Assim como em algumas linguagens de programação, as procedures do MySQL também podem retornar valores que, por sua vez, podem ser tratados em scripts PHP.

Vamos, então, entender como obter esses valores gerados em uma SP executada a partir de um script PHP. Antes, porém, quero novamente avisar que se trata de exemplos genéricos com a intenção de servir como referência e ilustrar o artigo. Os leitores devem modificar os scripts aqui apresentados de acordo com suas necessidades e aplicações. Agora, ao que interessa!

Inicialmente, vamos considerar o banco de dados genérico “banco_dados” que irá possuir três tabelas denominadas, respectivamente, tbl_1, tbl_2 e tbl_3 e diferentemente do artigo anterior, neste daremos mais ênfase aos dados fictícios dessas tabelas. Os campos das tabelas e seus conteúdos podem ser vistos a seguir. Por convenção, o campo marcado com um asteristico (*) indica a chave primária da tabela.

Tabela tbl_1:

tbl1_cod_vendedor*	tbl1_data_alteracao	tbl1_regiao
1	08/11/2006	SP
2	21/10/2006	RS
3	07/11/2006	BA

Tabela tbl_2:

tbl2_reg*	tbl2_cod_cliente	tbl2_cod_vend	tbl2_valor_venda
1	1000	2	140.00
2	1345	3	235.78
3	1213	2	1345.89
4	1112	1	3456.89

Tabela tbl_3:

tbl3_cod_cliente*	tbl3_nome_cliente	tbl3_cidade	tbl3_uf
1000	José da Silva	Porto Alegre	RS
1112	Carlos Magnólio	Salvador	BA
1213	Eduardo Schunker	Canoas	RS
1345	Antonio Carlos	São Paulo	SP
4350	Fábio Campos	Jundiaí	SP

Consideremos agora a necessidade de incluirmos um registro na tbl_3 que, como podemos perceber, trata-se de uma tabela contendo dados de clientes. Percebe-se também que o campo chave, tbl3_cod_cliente, não é do tipo autoincrement como nos campos tbl1_cod_vendedor e tbl2_reg (que o valor aumenta automaticamente à medida que acrescemos registros a essas tabelas) e, portanto, o usuário deverá entrar com um código para esse novo cliente. Como se trata de uma chave primária, o campo tbl3_cod_cliente deve ser necessariamente único. Por isso, devemos verificar se o código digitado pelo usuário já existe na tabela; se existir, o script de inclusão deverá ser informado e assim gerar um aviso ao usuário.

Para esse fim, vamos criar uma SP para verificar a existência ou não do código informado pelo usuário e três funções em PHP: uma para executar querys, uma para chamar a SP e processar o resultado retornado por ela e, finalmente, uma para executar ou não a inclusão do registro em tbl_3, dependendo do resultado retornado pela SP. Essa seqüência de códigos é importante para a compreensão do mecanismo de recuperação de valores retornados por uma stored procedure. Os códigos serão simples, com intuito apenas de ilustrar o que o artigo propõe demonstrar.

01. Stored procedure valida_cod_cliente (armazenada no banco de dados):

01) DELIMITER $$

02) DROP PROCEDURE IF EXISTS `banco_dados`.`valida_cod_cliente` $$
03) CREATE DEFINER=`root`@`localhost` PROCEDURE
      `valida_cod_cliente`(codigo INTEGER(6), OUT verificacao CHAR(1))
       COMMENT 'Rotina para verificar se cliente já está registrado.'
04) BEGIN
05)   DECLARE total INTEGER(6);
06)   SELECT COUNT(*) INTO total
07)     FROM tbl_1
08)     WHERE tbl1_cod_cliente = codigo;
09)   IF total = 0 THEN
10)     SET verificacao = "N";
11)   ELSE
12)     SET verificacao = "S";
13)   END IF;
14) END $$

15) DELIMITER ;

Comentários:

Como já vimos no artigo anterior como funciona a estrutura da PL para desenvolvimento de stored procedures, vamos nos ater principalmente no trecho da linha 03) a linha 14). Na linha 03), estamos criando efetivamente a SP denominada valida_cod_cliente. Diferentemente da SP apresentada em meu artigo anetior, temos agora dois tipos de parâmetros: um de entrada (codigo) e a novidade, um de saída (verificacao). Porém, a SP continua sendo do tipo padrão not-deterministic (apenas relembrando o conceito, uma SP será do tipo deterministic caso um de seus parâmetros seja de entrada e saída – INOUT – simultaneamente). O parâmetro codigo tem como função trazer para a SP o valor obtido através de um form (por exemplo) o código de um novo cliente digitado pelo usuário. Já o parâmetro verificacao irá retornar o resultado de todo o procedimento descrito entre as linhas 04) e 14) do código acima, bem simples por sinal e que só visa ilustrar o artigo.

A proposta inicial é termos uma SP que verifique se o código digitado para um novo cliente já existe em nossa tabela tbl_3. Podemos fazer isso de várias maneiras, mas optamos simplesmente por contar todas as ocorrências do novo código dentro de tbl_3, que é traduzido pelas linhas 05) e 06), onde na linha 05) declaramos uma variável local denominada total, cuja função é armazenar o total de registros encontrados pelo comando SQL em 06). Feito isso, verificamos se há ou não registros com o novo código; se o total de registros for zero, então não há registros com esse código e, portanto, pode ser incluído. Caso contrário, tem pelo menos um registro igual ao novo código e, portanto, ele não poderá ser incluído em tbl_3. Novamente, isso é traduzido pelas linhas de 09) a 13). A linha 14) encerra a SP e a linha 15) limpa o delimitador de blocos, voltando ao padrão “;”.

Uma dúvida pode ser levantada aqui. Eu não poderia usar a variável total para mandar o resultado da SP? A resposta é não, uma vez que ela é uma variável interna da SP. Assim, tão logo a SP encerre sua execução, a variável total torna-se inacessível. Isso não ocorre com a variável verificacao pois como é um parâmetro de saída, assume o status de variável global e então poderá ter seu conteúdo acessado mesmo após a execução da SP ter se encerrado. É um detalhe simples, mas importante para a compreensão do mecanismo de retorno de valores através de uma SP. Vamos agora aos scripts em PHP.

02. Função PHP execSQL(), utilizando as funções do grupo mysqli, do PHP 5:

function execSQL($sql) {
      $conn = mysqli_connect("host", "user", "senha", "banco_dados");
      if(mysqli_multi_query($conn, $sql)) {
            do {
                  if ($resultado = mysqli_store_result($conn)) {
                        while ($row = mysqli_fetch_row($resultado)) {
                            $retorno = $row[0];
                        }
                        mysqli_free_result($resultado);
                  }
            } while (mysqli_next_result($conn));
            mysqli_close($conn);
            return $retorno;
      } else {
            echo "<p>Não foi possível executar a seguinte instrução
SQL:</p><p><strong>$sql</strong></p>\n"."<p>Erro MySQL: ".
mysqli_error($conn)."</p>";
            exit();
            mysqli_close($conn);
      }
}

Comentários:

Iremos exemplificar as funções mysqli envolvidas neste script de forma detalhada, através de um exemplo prático, mais adiante. Por ora, basta compreender que a função mysqli_multi_query(), por executar querys múltiplas, retorna vários resultados, um para cada query executada. Esses resultados, que estão em arrays multidimensionais, são recuperados através da função msqli_store_result() e armazenados, linha a linha, em um array simples através da função mysqli_fetch_row(). Através da função mysqli_next_result() conseguimos navegar através dos vários resultados das várias querys executadas e uma vez recuperada cada linha de resultados, liberamos a memória utilizada por essa linha através da função mysqli_free_result(). Como execSQL() visa exclusivamente executar uma SP e recuperar um valor retornado pela SP, pegamos somente o primeiro registro da última linha de resultados das querys executadas ($retorno = $row[0]). Feito isso, retornamos o resultado e fechamos a conexão com o banco de dados.

Os scripts a seguir são mais simples e visam, respectivamente, chamar a SP e obter a validação do código do cliente e executar ou não a inclusão do novo código.

03. Função PHP validaCodigo():

function validaCodigo($novo_codigo) {
      $sql = "CALL valida_ID('$novo_codigo',@retorno);";
$sql .= "SELECT @retorno";

$rsValidacao=execSQL($sql);
      return $rsValidacao;
}

Comentários:

Apesar de ser um código bem simples, cabe aqui chamar atenção ao detalhe de como efetuamos a chamada da SP que criamos anteriormente. Percebam que no parâmetro de saída não utilizamos uma variável PHP, mas sim uma variável SQL (@retorno). Como disse, a variável de saída é uma variável global, só que uma variável SQL. Se o leitor, inadvertidamente, utilizar uma variável PHP como, por exemplo, ‘$retorno’, o MySQL retornará um erro informando que $retorno não é uma variável reconhecida, justamente porque não é uma variável SQL. Percebam também que a variável PHP $sql é a concatenação de duas querys. A primeira executa a SP e a segunda recupera a variável SQL @retorno, que contém o conteúdo gerado na SP.

A execução de várias querys em um único comando PHP só é possível porque usaremos a função mysqli_multi_query(). Se estivéssemos usando mysql_query() ou mysqli_query() isso não seria permitido e acabaria gerando um erro.

04. Script PHP para incluir novos clientes:

<?php
$codigo_digitado = $_POST['novo_cliente'];
$nome_cliente = $_POST['nome_cliente'];
$cidade = $_POST['cidade_cliente'];
$uf = $_POST['uf_cliente'];

if (validaCodigo($codigo_digitado) == "S") {
      echo "Este codigo já está sendo utilizado para outro cliente.";
} else {
      $sql = "INSERT INTO `tbl_3` () VALUES
             ('$codigo_digitado', '$nome_cliente', '$cidade', '$uf')";
$inclusao = execSQL($sql);
}
?>

Comentários:

O script listado em 4 recupera os dados digitados pelo usuário em um form e, através da utilização da função validaCodigo(), verifica se o código recuperado já está cadastrado em tbl_3. Se validaCodigo() retornar “S” significa que $codigo_digitado já está cadastrado em tbl_3. Então, é exibida uma mensagem informativa ao usuário. Senão, isso significa que o valor retornado foi “N” (que $codigo_digitado não está em tbl_3) e então é feita a inclusão do novo cliente. A inclusão, por sua vez, utiliza a mesma função utilizada para executar a SP. Isso não importa, porque a query será executada normalmente e o que for gerado por essa execução ficará armazenado em $inclusao e também não nos interessa aqui. Com isso demonstramos, de forma simples e objetiva, como retornar valores com uma stored procedure e como utilizá-los em scripts PHP. Vamos agora conhecer o funcionamento da funções mysqli utilizadas no script da função execSQL().

Com essa finalidade, faremos um rápido exemplo prático que deverá ilustrar bem o seu funcionamento. Considerando as tabelas e os seus conteúdos apresentados no início deste artigo, ao executarmos o script PHP:

$i = 0;
$conn = mysqli_connect("host", "user", "senha", "banco_dados");

$sql = "SELECT * tbl_1;";
$sql .= "SELECT * tbl_2;";
$sql .= "SELECT * tbl_3";

if (mysqli_multi_query($conn, $sql)) {
      do {
            echo "Query ".$i.":<br />";
            echo "======<br />";
            if ($resultado = mysqli_store_result($conn)) {
                  while ($row = mysqli_fetch_row($resultado)) {
                        $exemplo[$i] = $row;
                        foreach ($exemplo[$i] as $teste) {
                             echo " :: ".$teste." :: ";
                        }
                        echo "<br />";
                  }
                  mysqli_free_result($resultado);
            }
            if (mysqli_more_results($conn)) {
                  echo "<br />--------------------------<br /><br />";
            }
            $i++;
      } while (mysqli_next_result($conn));
      mysqli_close($conn);
} else {
echo mysqli_error($conn);
      exit();
      $this->closeConnMySQL();
}

Obteremos a seguinte saída:

Query 0:
======
:: 1 :: :: 08/11/2006 :: :: SP :: ::
:: 2 :: :: 21/10/2006 :: :: RS :: ::
:: 3 :: :: 07/11/2006 :: :: BA :: ::
 
----------------------------------------------------------------------

Query 1:
======
:: 1 :: :: 1000 :: :: 2 :: :: 140.00 :: ::
:: 2 :: :: 1345 :: :: 3 :: :: 235.78 :: ::
:: 3 :: :: 1213 :: :: 2 :: :: 1345.89 :: ::
:: 4 :: :: 1112 :: :: 1 :: :: 3456.89 :: ::

----------------------------------------------------------------------

Query 2:
======
:: 1000 :: :: José da Silva :: :: Porto Alegre :: :: RS :: ::
:: 1112 :: :: Carlos Magnólio :: :: Salvador :: :: BA :: ::
:: 1213 :: :: Eduardo Schunker :: :: Canoas :: :: RS :: ::
:: 1345 :: :: Antonio Carlos :: :: São Paulo :: :: SP :: ::
:: 4350 :: :: Fábio Campos :: :: Jundiaí :: :: SP :: ::

Com este exemplo, mostramos a questão de como são tratados os resultados obtidos através de múltiplas querys. Assim, $exemplo será um array multidimensional do tipo $exemplo[i][j], onde i será o número de querys executadas por mysqli_multi_query() e j será o número do registro em cada linha resultante da query executada, com i e j começando em 0. Portanto, se pensarmos na execução da SP e a recuperação do valor retornado por ela, verão que o valor de @retorno estaria em $exemplo[1][0].

Entretanto, vale registrar que a posição i refere-se apenas ao posicionamento no array de armazenamento ($exemplo) e, portanto, pode ser qualquer valor. Diferentemente, j deve iniciar sempre em 0.

É importante lembrar mais uma vez que os exemplos aqui empregados visam ilustrar o artigo, dando ao leitor uma visão concreta do que foi dito e servindo como referência para utilizações futuras. O retorno de valores através de SPs pode ser interessante. No exemplo que usamos no artigo, poderíamos estar calculando a média de vendas por região ou por vendedor ou obtendo outras estatísticas ou informações quaisquer, utilizando as funções internas do MySQL e retornando quantos valores desejados. Caberá ao leitor decidir como, quando e aonde aplicar as informações aqui apresentadas.

Finalizando, quero agradecer a todos os comentários, mails, críticas e sugestões que tenho recebido pelo meu artigo anterior, que foi o meu primeiro no iMasters. Espero continuar interagindo com os leitores, para que possa estar sempre melhorando nas próximas matérias. E quanto as sugestões, prometo atendê-las na medida do possível, pode deixar. Portando, continuem opinando e enviando seus mails.

Um forte abraço e sucesso a todos! Até breve!