CODEWITHSUNDEEP
oracle-databaseplsql
Bobby Heyer
Bobby Heyer

Reputation: 601

How to declare and set variables in PL/SQL

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

Upvotes: 4

Views: 25054

Answers (1)

MT0
MT0

Reputation: 167972

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;
/

Upvotes: 7

Related Questions