Using Variables in Plsql Select Statement

How to use variables in a select statement in Oracle PL-SQL

PLSQL is different than SQL SERVER. It has its own syntax. See how you can do it as below:

DECLARE
   var    NUMBER := 1;
   var2   my_table%ROWTYPE;
BEGIN
   SELECT *
     INTO var2
     FROM my_table
    WHERE my_id = var;

  --To display result you need to add dbsm_output.put_line function.

  dbms_output.put_line(var2.<columnname>);

Exception
When others then
 Null;       
END;

Note: Assumption is that the query wil return a single row only.

How to set a variable and use it in a select query in Oracle PL/SQL?

Use a cursor:

DECLARE
    p_name VARCHAR2(100);
    p_cur  SYS_REFCURSOR;
BEGIN
    p_name := 'Strawberry';
    OPEN p_cur FOR
    SELECT * FROM products where product_name=p_name;

    -- do something with the cursor.
END;
/

Or use a SQL/Plus-style bind variable declaration:

VARIABLE p_name VARCHAR2;

BEGIN
  :p_name := 'Strawberry';
END;
/

SELECT * FROM products where product_name=:p_name;

If only one row will ever be returned from your query (i.e. product_name is UNIQUE) then you can use SELECT ... INTO ...:

DECLARE
    p_name   VARCHAR2(100);
    p_value1 products.value1%TYPE;
    p_value2 products.value2%TYPE;
    p_value3 products.value3%TYPE;
BEGIN
    p_name := 'Strawberry';

    SELECT value1, value2, value3
    INTO   p_value1, p_value2, p_value3
    FROM   products
    WHERE  product_name=p_name;

    -- do something with the values.
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
    -- Handle the exception
  WHEN TOO_MANY_ROWS THEN
    NULL;
    -- Handle the exception
END;
/

How to declare variable and use it in the same Oracle SQL script?

There are a several ways of declaring variables in SQL*Plus scripts.

The first is to use VAR, to declare a bind variable. The mechanism for assigning values to a VAR is with an EXEC call:

SQL> var name varchar2(20)
SQL> exec :name := 'SALES'

PL/SQL procedure successfully completed.

SQL> select * from dept
  2  where dname = :name
  3  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL>

A VAR is particularly useful when we want to call a stored procedure which has OUT parameters or a function.

Alternatively we can use substitution variables. These are good for interactive mode:

SQL> accept p_dno prompt "Please enter Department number: " default 10
Please enter Department number: 20
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 20

ENAME             SAL
---------- ----------
CLARKE            800
ROBERTSON        2975
RIGBY            3000
KULASH           1100
GASPAROTTO       3000

SQL>

When we're writing a script which calls other scripts it can be useful to DEFine the variables upfront. This snippet runs without prompting me to enter a value:

SQL> def p_dno = 40
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 40

no rows selected

SQL>

Finally there's the anonymous PL/SQL block. As you see, we can still assign values to declared variables interactively:

SQL> set serveroutput on size unlimited
SQL> declare
  2      n pls_integer;
  3      l_sal number := 3500;
  4      l_dno number := &dno;
  5  begin
  6      select count(*)
  7      into n
  8      from emp
  9      where sal > l_sal
 10      and deptno = l_dno;
 11      dbms_output.put_line('top earners = '||to_char(n));
 12  end;
 13  /
Enter value for dno: 10
old   4:     l_dno number := &dno;
new   4:     l_dno number := 10;
top earners = 1

PL/SQL procedure successfully completed.

SQL>

Declaring & Setting Variables in a Select Statement

From the searching I've done it appears you can not declare and set variables like this in Select statements. Is this right or am I missing something?

Within Oracle PL/SQL and SQL are two separate languages with two separate engines. You can embed SQL DML within PL/SQL, and that will get you variables. Such as the following anonymous PL/SQL block. Note the / at the end is not part of PL/SQL, but tells SQL*Plus to send the preceding block.

declare 
    v_Date1 date := to_date('03-AUG-2010', 'DD-Mon-YYYY');
    v_Count number;
begin
    select count(*) into v_Count
    from Usage
    where UseTime > v_Date1;
    dbms_output.put_line(v_Count);
end;
/

The problem is that a block that is equivalent to your T-SQL code will not work:

SQL> declare 
  2      v_Date1 date := to_date('03-AUG-2010', 'DD-Mon-YYYY');
  3  begin
  4      select VisualId
  5      from Usage
  6      where UseTime > v_Date1;
  7  end;
  8  /
    select VisualId
    *
ERROR at line 4:
ORA-06550: line 4, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement

To pass the results of a query out of an PL/SQL, either an anonymous block, stored procedure or stored function, a cursor must be declared, opened and then returned to the calling program. (Beyond the scope of answering this question. EDIT: see Get resultset from oracle stored procedure)

The client tool that connects to the database may have it's own bind variables. In SQL*Plus:

SQL> -- SQL*Plus does not all date type in this context
SQL> -- So using varchar2 to hold text
SQL> variable v_Date1 varchar2(20)
SQL>
SQL> -- use PL/SQL to set the value of the bind variable
SQL> exec :v_Date1 := '02-Aug-2010';

PL/SQL procedure successfully completed.

SQL> -- Converting to a date, since the variable is not yet a date.
SQL> -- Note the use of colon, this tells SQL*Plus that v_Date1
SQL> -- is a bind variable.
SQL> select VisualId
  2  from Usage
  3  where UseTime > to_char(:v_Date1, 'DD-Mon-YYYY');

no rows selected

Note the above is in SQLPlus, may not (probably won't) work in Toad PL/SQL developer, etc. The lines starting with variable and exec are SQLPlus commands. They are not SQL or PL/SQL commands. No rows selected because the table is empty.

Oracle - Using variables in SELECT statement

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;

Use a declared variable in a SELECT statement

The correct syntax is:

DECLARE
  v_blah NUMBER := 13;
  v_people_rec PEOPLE%ROWTYPE;
BEGIN
  SELECT * INTO v_people_rec FROM PEOPLE p WHERE p.LuckyNumber = v_blah;
END;

The select statement in PL/SQL requires a place where store the query result. In this example the place is v_people_rec variable.

The example above expects exactly one row to be returned. In other cases it will throw exceptions NO_DATA_FOUND or TOO_MANY_ROWS.

PLSQL - IN clause using variable with multiple values

Declare the type globally:

CREATE TYPE number_list IS TABLE OF NUMBER;

Then you can initialise the list and then, in each iteration of the loop, EXTEND the list and assign the value and, finally, use the MEMBER OF operator or IN with a subquery and table collection expression:

DECLARE
  l_id_list NUMBER_LIST := NUMBER_LIST();
BEGIN
  FOR i IN 1..l_row_count
  LOOP
    l_id_list.EXTEND;
    l_id_list(l_id_list.COUNT) := to_number(
                                    apex_json.get_varchar2(
                                      p_path   => 'rows[%d].id', 
                                      p0       => i,
                                      p_values => l_values
                                    )
                                  );
  END LOOP;

  SELECT *
  -- BULK COLLECT INTO ...
  FROM   mytable
  WHERE  someid MEMBER OF l_id_list;

  -- or

  SELECT *
  -- BULK COLLECT INTO ...
  FROM   mytable
  WHERE  someid IN (SELECT COLUMN_VALUE FROM TABLE(l_id_list));
END;

---

Related Topics