PL/SQL: re-write SELECT statement using IN parameter in stored procedure

Question

Suppose I have an Oracle 11.2 database containing the following table:

TABLE: SURVEY

PARAMETER    MALE   FEMALE 
--------------------------
SAMPLE_SIZE  102       95
AVG_WEIGHT   170      120
AVG_HEIGHT   5.9      5.4

Here's an example minimal PL/SQL stored procedure that selects the average weight of males and places it (e.g. 170) into variable v_stat.

PROCEDURE get_stat (gender IN VARCHAR2) 
AS
    v_stat number;
BEGIN
    SELECT male INTO v_stat FROM survey WHERE parameter = 'avg_weight';
END get_stat;

Notice the IN parameter gender doesn't do anything here. What I want is to pass in variable gender, which may equal either 'male' or 'female', and use gender somehow in the SELECT statement (instead of 'male'). The goal is to pass in gender as a variable that may be used to return, for example, the average weight of, either male or female, as defined by gender.

I know I can probably use an IF/THEN/ELSE statement with two separate SELECT statements, but I wondered if there was an elegant way to use just one SELECT statement by changing 'male' in the above SELECT statement to something else?

Note that this is a re-do of my previous question here

How to programmatically set table name in PL/SQL?

that was (rightly) criticized for not being a realistic question.

Justin Cave · Accepted Answer · 2012-03-31 21:35:29Z

5

You'd need to use the same dynamic SQL approach that was suggested in your other question

PROCEDURE get_stat (gender IN VARCHAR2) 
AS
    v_sql   varchar2(1000);
    v_param varchar2(100) := 'AVG_WEIGHT';
    v_stat  number;
BEGIN
    v_sql := 'SELECT ' || gender || ' FROM survey WHERE parameter = :1';
    EXECUTE IMMEDIATE v_sql
                 INTO v_stat
                USING v_param;
END get_stat;

But you'd have the same general objections that were raised in your earlier question-- the data model is fundamentally flawed. You would be much better served having a separate row for MALE and FEMALE survey results rather than having separate columns for male and female results.

answered Mar 31, 2012 at 21:35

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

2 Comments

ggkmath Over a year ago

Thanks Justin, why is it better to have separate rows than columns? Is this a general rule of thumb that sometimes does not apply, or is it safe to say it is ALWAYS better to have additional rows if one can eliminate columns? The data for male and female will always be present.

Justin Cave Over a year ago

@ggkmath - It's essentially always best to at least start with a properly normalized data model. And a properly normalized data model would have a table with columns like parameter, 'gender, and result` rather than parameter, male, and female. In general, if you find yourself needing to pick a different table or a different column at runtime, your data model is not properly normalized.

BluesRockAddict · Accepted Answer · 2012-03-31 21:30:16Z

1

You'd need to compose the SELECT statement as string and then use EXECUTE IMMEDIATELY on it.

answered Mar 31, 2012 at 21:30

BluesRockAddict

15.6k3 gold badges40 silver badges35 bronze badges

Comments

Justin Pihony · Accepted Answer · 2012-03-31 21:36:02Z

1

I have to see how to transform your example, but I know that in SQL Server you can perform dynamic SQL. And, it appears that you can do this in Oracle, also.

It would go something like this (I am only going by examples here as Oracle is not my normal langauge)

avg_weight_str := avg_weight;
stmt_str := 'SELECT :gender INTO v_stat FROM survey WHERE parameter = :avg_weight_str';
EXECUTE IMMEDIATE stmt_str;

The problem with dynamic SQL is that it is not as performant. While not elegant, if you only have a two way if, then I would go with the if block so that the code can be more performant.

edited Mar 31, 2012 at 21:36

answered Mar 31, 2012 at 21:30

Justin Pihony

67.2k20 gold badges154 silver badges185 bronze badges

Collectives™ on Stack Overflow

PL/SQL: re-write SELECT statement using IN parameter in stored procedure

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related