How to use declare in a database in Mysql?

Question

I am creating an Insert database in Mysql, but when trying to declare a variable an error appears saying:

"statement incomplete Waiting : ;"

This is my code

CREATE PROCEDURE `inserirAgente` (
  IN `nome` VARCHAR(60),
  IN `rg` VARCHAR(150), 
  IN `cpf` VARCHAR(150), 
  IN `sexo` VARCHAR(20), 
  IN `escolaridade` VARCHAR(50), 
  IN `dt_nasc` VARCHAR(10),
  IN `dt_admissao` VARCHAR(10), 
  IN `funcao` VARCHAR(30),
  IN `email` VARCHAR(150),
  IN `senha` VARCHAR(150),
  IN `foto` VARCHAR(150),
  IN `estado` CHAR(2), 
  IN `cidade` VARCHAR(30),
  IN `cep` VARCHAR(9), 
  IN `bairro` VARCHAR(30),
  IN `rua` VARCHAR(30),
  IN `numero` INT,
  IN `complemento` VARCHAR(30), 
  IN `tel` VARCHAR(20)
  )
  BEGIN
    declare cod int;
    cod=select max(cod_agente) from agentes;

    INSERT INTO `Agentes`(nome, dt_nasc, rg, cpf, sexo, escolaridade, dt_admissao, funcao, email, senha, foto) 
    VALUES(nome, dt_nasc, rg, cpf, sexo, escolaridade, dt_admissao, funcao, email, senha, foto);

    INSERT INTO `Enderecos`(estado, cidade, cep, bairro, rua, numero, complemento, cod_agente_end)
    VALUES(estado, cidade, cep, bairro, rua, numero, complemento, cod);

    INSERT INTO `Telefones`(cod_agente_tel, tel)
    VALUES(cod, tel);
END  

I started using Mysql a few days ago and I don’t know what’s wrong.

Answer 1

The clause DECLARE does not initialize the variable. When you declare it, you declare the variable name, the type and a default value, which can be including an expression.

Already the clause SET, is used to initialize the variable you declared earlier.

Antony would be:

SET cod = (select max(cod_agente) from agentes);

---

Below is a detailed explanation of variables.
Omesh original: https://stackoverflow.com/a/11754790/6378641

There are basically three types of variables in Mysql:

1. Variables defined by the user (prefixed with @):

You can access any user-defined variable without declaring or initializing it. If you refer to a variable that has not been initialized, it will have a value NULL and a string type.

SELECT @var_any_var_name

You can initialize a variable using the instruction SET or SELECT:

SET @start = 1, @finish = 10;

or

SELECT @start := 1, @finish := 10;

SELECT * FROM places WHERE place BETWEEN @start AND @finish;

User variables can receive a value from a limited set of data types: integer, decimal, floating-point, Binary or nonbinary string, or NULL value.

User-defined variables are session-specific, i.e., a client-defined user variable cannot be viewed or used by other clients.

They can be used in queries SELECT using Advanced Mysql user variable Techniques.

2. Local variables (prefixed):

Local variables need to be declared using DECLARE before accessing it.

They can be used as local variables and input parameters within a stored procedure:

DELIMITER //

CREATE PROCEDURE sp_test(var1 INT) 
BEGIN   
    DECLARE start  INT unsigned DEFAULT 1;  
    DECLARE finish INT unsigned DEFAULT 10;

    SELECT  var1, start, finish;

    SELECT * FROM places WHERE place BETWEEN start AND finish; 
END; //

DELIMITER ;

CALL sp_test(5);

If the clause DEFAULT is missing, the initial value will be NULL.

The scope of a local variable is the block BEGIN ... END within which it is declared.

3. System variables of the server (prefixed with @@):

The Mysql server maintains many system variables set to a default value. They can be of the type GLOBAL, SESSION or BOTH.

Global variables affect the overall operation of the server, while session variables affect its operation for individual client connections.

To see the current values used by a running server, use the instruction SHOW VARIABLES or SELECT @@var_name.

SHOW VARIABLES LIKE '%wait_timeout%';

SELECT @@sort_buffer_size;

They can be set on server startup using options on the command line or in an options file. Most of them can be changed dynamically while the server is running using SET GLOBAL or SET SESSION:

-- Sintaxe para definir valor para uma variável global:
SET GLOBAL sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000;

-- Sintaxe para definir valor para uma variável de sessão:
SET sort_buffer_size=1000000;
SET SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@local.sort_buffer_size=10000;