CODEWITHSUNDEEP
plsql
Bernheart
Bernheart

Reputation: 637

Using a variable in PL/SQL

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?

Upvotes: 1

Views: 251

Answers (1)

kara
kara

Reputation: 3455

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

Upvotes: 3

Related Questions