CODEWITHSUNDEEP
oracle-databaseplsql
ggkmath
ggkmath

Reputation: 4246

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

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.

Upvotes: 2

Views: 13923

Answers (3)

Justin Pihony
Justin Pihony

Reputation: 67075

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.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 5

BluesRockAddict
BluesRockAddict

Reputation: 15683

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

Upvotes: 1

Related Questions