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

Question

How can I do something like this in oracle SQL developer?

DECLARE
    p_name products.product_name%TYPE;
BEGIN
    p_name := 'Strawberry';
   SELECT * FROM products WHERE product_name = p_name;
END;

score 1 · Answer 1 · answered Mar 28 '21 at 21:20

1

You need to give the SELECT statement somewhere to put the results of the query:

DECLARE
  p_name VARCHAR2(100);
  aProducts_row  PRODUCTS%ROWTYPE;
BEGIN
  p_name := 'Strawberry';

  SELECT *
    INTO aProducts_row
    FROM products
    where product_name=p_name;

  -- Add code to manipulate data in aProducts_row here, as in...

  DBMS_OUTPUT.PUT_LINE('PRODUCT_NAME = ''' ||
                          aProducts_row.PRODUCT_NAME || '''');
END;

Or if you're expecting more than one row to be returned you can use a cursor:

DECLARE
  p_name VARCHAR2(100);
BEGIN
  p_name := 'Strawberry';

  FOR aProducts_row IN (SELECT *
                          FROM products
                          where product_name=p_name)
  LOOP
    -- Add code to manipulate data in aProducts_row here, as in...

    DBMS_OUTPUT.PUT_LINE('PRODUCT_NAME = ''' ||
                           aProducts_row.PRODUCT_NAME || '''');
  END LOOP;
END;

answered Mar 28 '21 at 21:20

Bob Jarvis - Reinstate Monica

45,294
8
73
102

Why is this so easy in T SQL and so complicated in PL/SQL? – developer747 Mar 28 '21 at 21:26
T-SQL and PL/SQL use different models to accomplish the same goal. `SELECT...INTO` is present in both languages, although the syntax is slightly different. What are you actually trying to accomplish? – Bob Jarvis - Reinstate Monica Mar 28 '21 at 21:29
@developer747 SQL Server allows multiple statements to be run in a single command (which opens up SQL injection vulnerabilities); Oracle only allows a single statement in each command (which still allows some SQL injection vulnerabilities but shuts down many of them - [this xkcd](https://xkcd.com/327/) would not work in Oracle). That restriction and other differences mean they treat what you are doing slightly differently (they are different languages after all). – MT0 Mar 28 '21 at 21:31
@BobJarvis-ReinstateMonica I think developer747 wants to return a result set somehow, such that the block acts similarly to a query/view/table macro in Oracle. From my limited understanding of SQL Server, T-SQL can do this because it's more of a scripting language than PL/SQL. – William Robertson Mar 28 '21 at 22:12
@developer747: I guess this is a perfect example of what you learn first you learn best. To me T-SQL seems like you just throw something up and hope for the best. Difficult and totally without structure. – Belayer Mar 28 '21 at 23:11
@BobJarvis-ReinstateMonica Basically I have this query where I need to plug in a value. I want to share that query with others. I can either ask them to change the plugged in value in the query, or I can just declare a variable and ask them to set the variable and run the query, without the need to modify the query. – developer747 Mar 28 '21 at 23:19

score 1 · Accepted Answer · answered Mar 28 '21 at 21:21

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

score 1 · Answer 3 · answered Mar 28 '21 at 22:05

If the question is not about setting variables but is actually about output from a PL/SQL anonymous block then it is already answered here.

declare
    rc sys_refcursor;
begin
    open rc for select 'Hello' as test from dual;
    dbms_sql.return_result(rc);
end;

You can set any variable you want using the normal PL/SQL assignment syntax. If there is some part of the documentation that is not clear then please provide an example of what you are trying to do.

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

3 Answers3