LOCAL

MySQL

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.

PostgreSQL

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.

Oracle

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.