PL SQL Parameter List Empty

Question

If I have a PL SQL procedure like this:

Create Or Replace Procedure get_age (first_name varchar(40), last_name varchar(50))

Begin

Select age
From Person
Where first = first_name AND last = last_name;

End;

It is not guaranteed that the user will pass in a value for the first_name and last_name variable.

How do I account for this in the Procedure above since I do not want the first_name or last_name in the Where clause of my query if either one of those variables do not have a value.

I assume that this is not the actual code that you have. Parameters do not have lengths and a select in a procedure would have to have an into or it has to be used to open a cursor. If you are just retrieving a value, you'd want a function not a procedure. If either parameter is optional, you could get multiple rows so what would you want to happen? — Justin Cave
– Justin Cave, Commented May 29, 2016 at 4:35

EoinS · Accepted Answer · 2016-05-29 04:17:56Z

2

Your query is good as it is now, you can accept null values in your WHERE clause:

Where (first = first_name OR first_name is NULL) AND (last = last_name OR last_name in NULL);

This way the user can enter first and last names, first or last names only or neither and results will be selected as expected.

edited May 29, 2016 at 4:17

answered May 29, 2016 at 4:03

EoinS

5,4901 gold badge21 silver badges33 bronze badges

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

2 Comments

DF768 Over a year ago

Thanks for replying. When you say first_name is NULL and last_name in NULL in the where clause is that setting the parameter first_name and last_name to NULL? So what will be the output of this where clause if the user doesn't pass in any first_name or last_name as parameters? Since you set the parameters to NULL in the where but how does that affect the where clause?

EoinS Over a year ago

Hey so this just takes input from the user, first_name last_name. So Basically when they enter a first and last name it selects only rows with matches for first AND last as entered. If they omit an argument then the test is automatically passed. So if they enter no first_name, the where clause is (TRUE) AND (last = last_name), this case enables the user to search by last name only . They they enter no arguments (TRUE) AND (TRUE) all rows are returned. It's a pretty dynamic function

j2EEdevelopper · Accepted Answer · 2016-05-30 09:27:50Z

0

Create Or Replace Procedure get_age (first_name varchar(40) default null, last_name varchar(50) default null)

Begin

Select age From Person Where first = nvl(first_name,first) AND last = nvl(last_name,last);

End;

answered May 30, 2016 at 9:27

j2EEdevelopper

314 bronze badges

Collectives™ on Stack Overflow

PL SQL Parameter List Empty

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related