Oracle - Using variables in SELECT statement

Question

My background in SQL is SQL Server, so forgive me for using Oracle in a similar way to it. I need to use a variable so I can use the value stored in it to perform a separate SELECT query. My aim is to find a percentage value and the variable will hold the total value of a table.

DECLARE
  v_Count INT;

BEGIN

--get total rows
SELECT COUNT(OrderID)
INTO v_Count
FROM OrderedEquipment;

--find percentage of equipment ordered
SELECT a.Equip_Name, COUNT(b.EquipmentID), ((COUNT(b.EquipmentID)*1.0)/(v_Count*1.0)*100)
FROM Equipment a
LEFT OUTER JOIN OrderedEquipment b ON a.EquipmentID = b.EquipmentID
GROUP BY a.Equip_Name;

END;

SQL Developer will then throw this error:

Error report -
ORA-06550: line 10, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

I tried looking for solutions to this, and stumbled on to this post:

How do I use variables in Oracle SQL Developer?

But none of the answers really solved my problem. I tried using the bind variables example but that didn't work.

In Oracle 11g one has *always* to use `select into`. There is no exceptions and no automatic result set printing. The good starting point is [PL/SQL Static SQL](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS006). — user272735, Feb 21 '15 at 19:13

Ponder Stibbons · Accepted Answer · 2015-02-21T19:48:08.227

The simplest way is not using variable at all

SELECT a.Equip_Name, COUNT(b.EquipmentID), 
    (COUNT(b.EquipmentID)*1.0)/((SELECT COUNT(OrderID) cnt FROM OrderedEquipment)*1.0)*100
  FROM Equipment a
  LEFT OUTER JOIN OrderedEquipment b ON a.EquipmentID = b.EquipmentID
  GROUP BY a.Equip_Name;

You can also in your block select data into 3 variables

... a.Equip_Name into v1, COUNT(b.EquipmentID) into v2,
  ((COUNT(b.EquipmentID)*1.0)/(v_Count*1.0)*100) into v3 ...

and list them with

dbms_output.put_line(v1||' '||v2||' '||v3);

Edit - this query should be faster:

with counter as (select count(OrderID) cnt from OrderedEquipment)
select a.Equip_Name, count(b.EquipmentID), 
    (count(b.EquipmentID)*1.0)/(max(counter.cnt)*1.0)*100
  from Equipment a
    left join OrderedEquipment b ON a.EquipmentID = b.EquipmentID
    cross join counter
  group by a.Equip_Name;

Oracle - Using variables in SELECT statement

1 Answers1