Using a variable in PL/SQL

Question

I am using PL/SQL in Toad for Oracle. I would like to use define a variable in my code and then using this variable multiple times in the query.

Please note that I'm not asking for the pop-up window in which input the value of the variable; I need something like this:

DEFINE min_salary = 100

SELECT Surname FROM employees
WHERE salary < min_salary

I.e. min_salary in the WHERE statement assumes the value defined above.

Surfing the net, someone suggests to add an & before the variable in the where statement, i.e.

DEFINE min_salary = 100

SELECT Surname FROM employees
WHERE salary < &min_salary

But this is not useful in my case, since the & calls the pop-up window. Instead, I would insert the value directly in the code.

Anyone could help?

Already seen, but all suggested procedures cause the pop-up window in Toad, while I want the query to be executed without the users interaction — Bernheart, Apr 22 '18 at 15:38
@Bernheart : you may use the techniques described in the link shared by jchevali. But, remember.. in Toad, you must click on "execute it as script" option (IInd green arrow from left ). — Kaushik Nayak, Apr 22 '18 at 16:18

score 3 · Accepted Answer · edited Apr 24 '18 at 14:59

A Select-Statement is not PL/SQL it's SQL. You need to create PL/SQL-Code:

DECLARE
   min_salary   employees.salary%TYPE := 100;
BEGIN
    FOR i IN (SELECT Surname
                FROM employees
               WHERE salary < min_salary)
    LOOP
        DBMS_OUTPUT.put_line ('Surname: ' || i.Surname);
    END LOOP;
END;

I don't know what you want to do, but you have to choose where to get the output. A PL/SQL-Script doesn't output the data-grid. You only run it.

You also could build a function to validate. Example:

CREATE OR REPLACE FUNCTION IsMinSalary (salary NUMBER)
    RETURN NUMBER
IS
    defaultMinSalary   employees.salary%TYPE := 100;
BEGIN
    IF (defaultMinSalary < salary)
    THEN
        RETURN 0;
    ELSE
        RETURN 1;
    END IF;
END IsMinSalary;
/

SELECT surname
  FROM (SELECT 10 AS Salary, 'ten' AS Surname FROM DUAL
        UNION ALL
        SELECT 100 AS Salary, 'hundred' AS Surname FROM DUAL
        UNION ALL
        SELECT 200 AS Salary, 'two-hundred' AS Surname FROM DUAL) t -- fake-table
 WHERE IsMinSalary (t.salary) = 1

Hi @kara and thank you for your answer. So should I suppose that it is impossible to use a parameter in a SQL query? All I want to do is to retrieve data using a parameter in the SQL code. Why? Because in this way I can automatize the query process simply by changing a parameter and not replacing all the occurrences of the query (it is a more efficient approach, if possible...of course) — Bernheart, Apr 23 '18 at 14:54
The question is: Replacing how? If you run the query manually you can go with the `:myvarname`. How do you want to execute the query? — kara, Apr 24 '18 at 15:24

Using a variable in PL/SQL

1 Answers1