Oracle PL SQL DECLARE and USE variable in subquery

Question

In Oracle PL SQL, is there a way to use the variable in a subquery that was "declared"? Since all select statement requires "INTO" clause, I cant do any subqueries using the variable. All i see are people using dbms_output to print a line.

Ex:

DECLARE
name_variable varchar(20);

BEGIN
  SELECT name
  INTO name_variable 
  FROM employee
  WHERE ID = 1;

  Select * 
  FROM employee 
  WHERE name = name_variable;

END;

I have tried something like the above query and got something like this.

Error message when doing query like above

Also can not use the "DEFINE" as the PL SQL I am using doesnt support it.

Answer 1

If you want to use a SELECT query in PL/SQL then you cannot just use SELECT ... FROM ... and need to either:

1. Use SELECT ... INTO ... FROM ...
2. Use a cursor.

The simplest solution is to not use PL/SQL for this, just use a sub-query:

Select * 
FROM   employee 
WHERE  name = (
         SELECT name
         FROM   employee
         WHERE  ID = 1
       );

or:

SELECT id, name
FROM   (
  SELECT e.*,
         COUNT(CASE WHEN id = 1 THEN 1 END) OVER (PARTITION BY name)
           AS count_id_matches
  FROM   employee e
)
WHERE  count_id_matches > 0;

Which, for the sample data:

CREATE TABLE employee (id, name) AS
  SELECT 1, 'Alice' FROM DUAL UNION ALL
  SELECT 2, 'Betty' FROM DUAL UNION ALL
  SELECT 3, 'Carol' FROM DUAL UNION ALL
  SELECT 4, 'Alice' FROM DUAL;

Both output:

ID	NAME
1	Alice
4	Alice

If you do want to use PL/SQL then use a FOR loop with an implicit cursor:

DECLARE
  name_variable varchar(20);
BEGIN
  SELECT name
  INTO name_variable 
  FROM employee
  WHERE ID = 1;

  FOR r IN (
    SELECT * 
    FROM employee 
    WHERE name = name_variable
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE( r.id || ' ' || r.name );
  END LOOP;
END;
/

Which outputs:

1 Alice
4 Alice

fiddle

---

I want to use the variable is because the query to grab the data I am trying to assign to my variable is long and I need to use this value multiple times in my query.

Use a sub-query factoring (WITH) clause:

WITH names (name) AS (
  SELECT name
  FROM   employee
  WHERE  ID = 1
)
SELECT * 
FROM   employee 
WHERE  name = (SELECT name FROM names)
UNION ALL
SELECT * 
FROM   employee
WHERE  name = (SELECT name FROM names);