SQL Reference

LOCAL

LOCAL is a qualifier used in SQL within SET TRANSACTION command. It establishes that the scope of the transaction is confined to the current session only. Transactions confined by LOCAL do not impose locks and do not conflict with other transactions. It ensures that the changes made in the session do not interfere with operations outside of the existing session.

Example

CREATE DATABASE test;
USE test;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    location VARCHAR(20)
);

INSERT INTO employees (id, name, location) VALUES
(1, 'John', 'New York'),
(2, 'Peter', 'Los Angeles'),
(3, 'Suse', 'Boston');

SET @location := 'Los Angeles';
SELECT name, location FROM employees WHERE location = @location;

Output

+-------+-------------+
| name  | location    |
+-------+-------------+
| Peter | Los Angeles |
+-------+-------------+

Explanation

In the example, SET @location := 'Los Angeles'; is a declaration of a local variable @location in MySQL. The variable takes the string value ‘Los Angeles’. A SELECT statement is then written to retrieve the names and locations of the employees whose location matches the value of the @location variable. This variable is local to the session in which it is being executed.

Example

BEGIN;
SET LOCAL timezone TO 'America/New_York';
SHOW timezone;
COMMIT;

Output

timezone
------------------
 America/New_York
(1 row)

Explanation

In the example, a new transaction is initiated with BEGIN. Inside this transaction, the SET LOCAL command is used to modify the timezone parameter to ‘America/New_York’ only for the duration of the current transaction. The SHOW timezone command is executed to display the current value of timezone, which verifies that the change has been made. The transaction is finalized using COMMIT.

Example

    DECLARE
        -- Local variables
        var1 NUMBER := 30;
    BEGIN
        var1 := var1 + 10;
        dbms_output.put_line('Var1 is : ' || var1);
    END;

Output

Var1 is : 40

Explanation

In the given example, a local variable var1 was declared and initialized with a value of 30 within a PL/SQL block. This variable was used in an operation to increment its value by 10, and the resultant value of var1, which is 40, was printed out.