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
– Bernheart, Commented Apr 22, 2018 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
– Kaushik Nayak, Commented Apr 22, 2018 at 16:18

Adi · Accepted Answer · 2018-04-24 14:59:04Z

3

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

edited Apr 24, 2018 at 14:59

Adi

3091 gold badge5 silver badges19 bronze badges

answered Apr 23, 2018 at 6:52

kara

3,4854 gold badges23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bernheart Over a year ago

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)

kara Over a year ago

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?

Collectives™ on Stack Overflow

Using a variable in PL/SQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related