CODEWITHSUNDEEP
oracle-databaseparametersplsqlprocedure
Lynn
Lynn

Reputation: 31

Passing Value from Procedure to Select Statement

I would like to create a procedure which returns the MAX Date from the employee table and then allows me to call the max date again in select statements.

Thank you.

NOTE: I will be using the max_date_PARAM in many select statements for reporting. 

CREATE OR REPLACE PROCEDURE max_date_proc (max_date_PARAM out DATE) 
IS
BEGIN
    SELECT  max(start_date)  INTO max_date_PARAM
     FROM employee 
   END max_date_proc;
/
select * from benefits where claim > = max_date_PARAM;

Upvotes: 2

Views: 2918

Answers (3)

Alex Poole
Alex Poole

Reputation: 191570

If you're running your statements in SQL*Plus (or SQL Developer I think, not sure about others) you can use a bind variable:

variable l_max_date date;
exec max_date_proc(:l_max_date);
select * from benefits where claim >= :l_max_date;

Same with a function, which is more suitable here; using Justin's:

variable l_max_date date;
exec :l_max_date := get_max_start_date();
select * from benefits where claim >= :l_max_date;

Of course, there isn't much point making this a function or procedure if you're only using the result once, but I'm assuming you want to reuse the same value multiple times and not recalculate it each time.

Yet another option, using SQL*Plus' column handling to define a variable:

column max_date new_value l_max_date noprint;
select max(start_date) max_date from employee;
select * from benefits where claim >= &l_max_date;

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231851

If you created a function

CREATE OR REPLACE FUNCTION get_max_start_date
  RETURN DATE
IS
  l_max_date DATE;
BEGIN
  SELECT MAX(start_date)
    INTO l_max_date
    FROM employee;
  RETURN l_max_date;
END get_max_start_date;

Then you could query

SELECT *
  FROM benefits
 WHERE claim >= get_max_start_date();

Of course, you could do this without a function as well

SELECT * 
  FROM benefits
 WHERE claim >= (SELECT MAX(start_date)
                   FROM employee)

Upvotes: 4

TTT
TTT

Reputation: 2375

You should use a function instead of a procedure. http://psoug.org/reference/functions.html

Upvotes: 3

Related Questions