How to declare and set variables in PL/SQL

Question

PROBLEM

I am struggling to define variables in PL/SQL

in T-SQL it is easy to declare & set variables and reference them in Select query (as below). But how would I do something similar in PL/SQL?

WHAT I"VE TRIED

This seems to be a common question asked multiple times on Stack, but there is no clear answer! the closest I got was This link here, but even still I couldn't get it working for me.

MY VARIOUS PL/SQL ATTEMPTS

DECLARE
   myname varchar2(20);
BEGIN
     myname := 'Tom';
     dbms_output.print_line(myname);
END;

variable v_date datetime;
exec :v_date := to_date('01/01/2014','mm/dd/yyyy');

var myname varchar2(20);
exec :myname := 'Tom';

A T-SQL EXAMPLE OF WHAT I AM TRYING TO DO

DECLARE 
    @String VARCHAR(10),
    @Date DATE,
    @Integer As INT

SET @String = 'Hello'
SET @Date = '20200413'
SET @Integer = Year(@Date)

SELECT *
FROM TABLE
WHERE 1=1
AND col_a = @String
AND col_b >= @Year

`var` isn't PL/SQL, it's a [SQL*Plus](https://docs.oracle.com/database/121/SQPUG/ch_twelve050.htm#SQPUG133) command and can only be used in SQL\*Plus (maybe in Oracle SQL Developer as well, I don't know). Unlike all other databases T-SQL mixes procedural code and the query language SQL. But SQL as a query language doesn't have a variables — , Apr 13 '20 at 10:09
another post https://stackoverflow.com/questions/7553901/declaring-a-variable-and-setting-its-value-from-a-select-query-in-oracle — Rohim Chou, Jul 27 '21 at 02:16

MT0 · Accepted Answer · 2020-04-13T10:29:09.010

SQL/Plus & SQL Developer

If you are using a user interface that supports the VARIABLE command then you can use that to declare bind variables:

VARIABLE string VARCHAR2(10);
VARIABLE dt     DATE;
VARIABLE year   NUMBER;

BEGIN
  :string := 'Hello';
  :dt := DATE '2020-04-13';
  :year := EXTRACT( YEAR FROM :dt );
END;
/

SELECT *
FROM   table_name
WHERE  col_a = :string
AND    col_b = :year;

However, here you aren't using PL/SQL except for the middle BEGIN ... END section.

Other user interfaces:

You can declare a single PL/SQL block and use PL/SQL variables:

DECLARE
  v_string VARCHAR2(10) := 'Hello';
  v_dt     DATE         := DATE '2020-04-13';
  v_year   NUMBER(4,0)  := EXTRACT( YEAR FROM v_dt );
  c_cur    SYS_REFCURSOR;
BEGIN
  OPEN c_cur FOR
    SELECT *
    FROM   table_name
    WHERE  col_a = v_string
    AND    col_b = v_year;

  -- Do stuff in PL/SQL with the cursor.
END;
/

How to declare and set variables in PL/SQL

1 Answers1

Linked

Related