WCMC – Måndag 23/11 MySQL Local Variables

Inuti en stored procedure kan du använda dig av vad som kallas för lokala variabler (local variables). En lokal variabel deklareras med kommandot DECLARE inuti ett BEGIN … END block i till exempel CREATE PROCEDURE. En lokal finns endast tillgänglig inuti det BEGIN … END block som den deklarerades i.

Skapa en lokal variabel i en procedure:

-- change delimiter to something other than ;
-- to avoid ending our CREATE PROCEDURE statement too early
DELIMITER //

CREATE PROCEDURE declare_test()
BEGIN
  DECLARE test_variable INT(11); -- create a new local variable called test_variable
  SET test_variable := 2; -- SET the value of test_variable to 2
  SELECT test_variable; -- SELECT the test_variable to see it's value
END// -- end of CREATE PROCEDURE, using our temporary delimiter

-- change delimiter back to ;
-- to make sure that our SQL runs as expected once the procedure has been created
DELIMITER ;

Ändra värdet på en variabel i en procedure:

Din procedures inparametrar är även lokala variabler. De deklareras mellan parenteserna bredvid din procedures namn och används sedan som alla andra lokala variabler.

-- change delimiter to something other than ;
-- to avoid ending our CREATE PROCEDURE statement too early
DELIMITER //

CREATE PROCEDURE declare_test(test_variable INT(11)) -- declare a parameter called test_variable
BEGIN

  SET test_variable := 2; -- SET the value of test_variable to 2
  SELECT test_variable; -- SELECT the test_variable to see it's value
END// -- end of CREATE PROCEDURE, using our temporary delimiter

-- change delimiter back to ;
-- to make sure that our SQL runs as expected once the procedure has been created
DELIMITER ;

Använd lokal variabel för att lagra LAST_INSERT_ID():

-- change delimiter to something other than ;
-- to avoid ending our CREATE PROCEDURE statement too early
DELIMITER //

CREATE PROCEDURE insert_test() -- declare a parameter called test_variable
BEGIN
  DECLARE inserted_id INT(11); -- our local variable

  INSERT INTO
    products
  VALUES
    ('a', 'b');

  SET inserted_id := LAST_INSERT_ID(); -- SET the value of test_variable to the latest inserted ID
  SELECT inserted_id; -- SELECT the inserted_id to see it's value
END// -- end of CREATE PROCEDURE, using our temporary delimiter

-- change delimiter back to ;
-- to make sure that our SQL runs as expected once the procedure has been created
DELIMITER ;
0 votes