oracle PL/SQL：在存储过程中使用 IN 参数重写 SELECT 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9959799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL: re-write SELECT statement using IN parameter in stored procedure
提问by ggkmath
Suppose I have an Oracle 11.2 database containing the following table:
假设我有一个包含下表的 Oracle 11.2 数据库：
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.
这是一个示例最小 PL/SQL 存储过程，它选择男性的平均体重并将其（例如 170）放入变量中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 genderdoesn't do anything here. What I want is to pass in variable gender, which may equal either 'male' or 'female', and use gendersomehow in the SELECT statement (instead of 'male'). The goal is to pass in genderas a variable that may be used to return, for example, the average weight of, either male or female, as defined by gender.
注意 IN 参数gender在这里没有任何作用。我想要的是传入变量gender，它可能等于“男性”或“女性”，并gender在 SELECT 语句中以某种方式使用（而不是“男性”）。目标是gender作为变量传入，该变量可用于返回例如由 定义的男性或女性的平均体重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?
我知道我可能可以将 IF/THEN/ELSE 语句与两个单独的 SELECT 语句一起使用，但我想知道是否有一种优雅的方法可以通过将上述 SELECT 语句中的“男性”更改为其他语句来仅使用一个 SELECT 语句？
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.
这被（正确地）批评为不是一个现实的问题。
回答by Justin Cave
You'd need to use the same dynamic SQL approach that was suggested in your other question
您需要使用其他问题中建议的相同动态 SQL 方法
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 MALEand FEMALEsurvey results rather than having separate columns for maleand femaleresults.
但是您会遇到与您之前的问题相同的普遍反对意见——数据模型存在根本性缺陷。为MALE和FEMALE调查结果设置单独的行比为male和female结果设置单独的列会更好。
回答by BluesRockAddict
You'd need to compose the SELECT statement as string and then use EXECUTE IMMEDIATELYon it.
回答by Justin Pihony
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.
我必须看看如何转换您的示例，但我知道在 SQL Server 中您可以执行动态 SQL。而且，您似乎也可以在 Oracle 中执行此操作。
It would go something like this (I am only going by examples hereas Oracle is not my normal langauge)
它会是这样的（我只是在这里举个例子，因为 Oracle 不是我的正常语言）
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.
动态 SQL 的问题在于它的性能不高。虽然不优雅，但如果您只有两种方式 if，那么我会使用 if 块，以便代码可以提高性能。
