Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Saturday, 28 September 2019 03:01

Session Variables

Written by  http://blog.mclaughlinsoftware.com/2019/09/28/session-variables/
Rate this item
(0 votes)

In MySQL and Oracle, you set a session variable quite differently. That means you should expect there differences between setting a session variable in Postgres. This blog post lets you see

how to set them in all three databases. I’m always curious what people think but I’m willing to bet that MySQL is the simplest approach. Postgres is a bit more complex because you must use a function call, but Oracle is the most complex.

The difference between MySQL and Postgres is an “@” symbol versus a current_setting() function call. Oracle is more complex because it involves the mechanics in Oracle’s sqlplus shell, SQL dialect, and PL/SQL language (required to assign a value to a variable).

MySQL

MySQL lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

  1. You set a session variable on a single line with the following command:

    SET @my_variable_name :='My Value';

    SET @my_variable_name := 'My Value';

  2. You can query a variable from the pseudo table dual or as a comparison value in the SELECT-list

    SELECT @my_variable_name AS"The Value"FROM dual;

    SELECT @my_variable_name AS "The Value" FROM dual;

    or WHERE clause

    SELECT column_nameFROM table_nameWHERE column_name = @my_variable_name;

    SELECT column_name FROM table_name WHERE column_name = @my_variable_name;

Postgres

Postgres lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

  1. You set a session variable in a single line. It iss critical to note that you must use double quotes around the session variable name and single quotes for the value. You raise an error when you use a single quote instead a double quote around the session variable name. The syntax is:

    SETSESSION"videodb.table_name"='new_hire';

    SET SESSION "videodb.table_name" = 'new_hire';

  2. You can query a variable from the pseudo table dual or as a comparison value in the SELECT-list with the current_setting() function call.

    SELECT current_setting('videodb.table_name')AS"The Value";

    SELECT current_setting('videodb.table_name') AS "The Value";

    or WHERE clause

    SELECT column_nameFROM table_nameWHERE column_name = current_setting('videodb.table_name');

    SELECT column_name FROM table_name WHERE column_name = current_setting('videodb.table_name');

Oracle

There are two steps required to declare a session variable in Oracle. First, you need to define the variable in the SQL*Plus session. Oracle lets you define a variable like you would define a variable in the C language, using extern before the variable’s type. Second, you assign a value to the session variable in an anonymous PL/SQL block. There is no single line statement to declare a variable with an initial value.

  1. You set a session variable by using the VARIABLE keyword, a variable name, and data type. The supported data types are: BLOB, BFILE, BINARY_DOUBLE, BINARY_FLOAT, CHAR, CLOB, NCHAR, NCLOB, NVARCHAR2, REFCURSOR, and VARCHAAR2. You define a variable with the following syntax:

    VARIABLE bv_variable_name VARCHAR2(30)

    VARIABLE bv_variable_name VARCHAR2(30)

  2. You assign a value to the bind variable inside an anonymous block by prefacing the variable name with a colon. You assign values inside PL/SQL with the walrus operator (:=) and a string enclosed by single quotes. Anonymous blocks start with a BEGIN and end with an END followed by a semicolon (;) and a forward slash (/) to dispatch the block for execution. The following example shows a full block:

    BEGIN :bv_variable_name :='Some Value';END;/

    BEGIN :bv_variable_name := 'Some Value'; END; /

  3. You can query any declared variable from the pseudo table dual or as a comparison value in the SELECT-list

    SELECT :bv_variable_name FROM dual;

    SELECT :bv_variable_name FROM dual;

    or WHERE clause

    SELECT column_nameFROM table_nameWHERE column_name = :bv_variable_name;

    SELECT column_name FROM table_name WHERE column_name = :bv_variable_name;

Read 20 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.