在Firebird中，如何在过程中编写多个if语句?

问题描述

我正在Firebird中编写一个SQL脚本进行作业.首先，我必须创建一个2列表并添加一些值(这些值尚不应该做任何事情)

I'm writing an sql script in Firebird for an assignment. First, I have to create a 2 column table and add some values (the values aren't supposed to do anything yet)

--Create the table
create table salary_class (
class varchar(5),
occurrence int ) ;

commit work;

--Add Values to our table
insert into salary_class (class, occurrence)
values ('ELITE', 0);
insert into salary_class (class, occurrence)
values ('HIGH', 0);
insert into salary_class (class, occurrence)
values ('MID', 0);
insert into salary_class (class, occurrence)
values ('LOW', 0);

commit work;

在完成工作后，我创建一个过程.应该根据传递给它的数字返回一个特定的字符串.

After that worked is committed, I create a procedure. It is supposed to return a certain string based on the number that is passed into it.

--Create f_class procedure
set term # ;
create procedure f_class(salary SALARY)

returns (lv VARCHAR(10)) as
begin
    --If statements for each occurrence level
    if (salary <= 39999) then
    lv = 'LOW';
    suspend;

    if (salary <= 67999) then
    lv = 'MID';
    suspend;

    if (salary <= 99999) then
    lv = 'HIGH';
    suspend;

    if (salary >= 100000) then
    lv = 'ELITE';
    suspend;

end#

set term ; #

--Demonstrate that f_class function works correctly
  select lv from f_class(20000);
  select lv from f_class(67999);
  select lv from f_class(68000);
  select lv from f_class(120000);

调用该函数会给我

LV
null
null
null
ELITE

但是应该给我

LV
LOW
MID
HIGH
ELITE

如果有人可以对此有所启发，将不胜感激.在过去2天里，我一直在看《 Firebird》手册上的内容.

If anyone could shine some light on this, it would be much appreciated. Been pulling my hair out over the Firebird manual the past 2 days.

推荐答案

您的代码中有多个问题.第一个问题是您无条件使用suspend，这意味着存储过程的每次调用都返回4行，这取决于条件可能包含null值或先前的值.

You have multiple problems in your code. The first problem is that you use suspend unconditionally, which means that each invocation of the stored procedure returns 4 rows, which depending on the condition may contain a null value, or a previous value.

这似乎与您的期望不符，因为问题的预期输出似乎与每个薪水的预期相符，而实际输出是最后一条语句(select lv from f_class(120000);)的输出.

This doesn't seem what you expect, as the expected output of your question seems to match the expectation for each individual salary, while the actual output is the output of the last statement (select lv from f_class(120000);).

第二个问题是您使用单独的if语句，这导致要评估多个条件(例如，值1将返回low，mid和high(2x)，而值67999将返回null，mid和high(2x)等).

The second problem is that you use individual if statements, which leads to multiple conditions being evaluated (eg value 1 will returnlow, mid and high (2x), and value 67999 will return null, mid and high (2x), etc).

要解决您的问题，有几种解决方法:

To solve your problems there are a few solutions:

1. 使用链接的 if .. then .. else :

if (salary <= 39999) then
    lv = 'LOW';
else if (salary <= 67999) then
    lv = 'MID';
else if (salary <= 99999) then
    lv = 'HIGH';
else if (salary >= 100000) then
    lv = 'ELITE';

suspend;

单个suspend还将阻止输出多行.

The single suspend will also prevent the output of multiple rows.

用搜索到的if语句#fblangref25-commons-conditional-case"rel =" nofollow noreferrer> case :

Replace the if-statements with a searched case:

lv = case
        when salary <= 39999 then 'LOW'
        when salary <= 67999 then 'MID'
        when salary <= 99999 then 'HIGH'
        when salary >= 100000 then 'ELITE'
     end;

suspend;

您也可以用else 'ELITE'

但是，您在问题中将此描述为 function 时，使我认为这可能完全是错误的解决方案.存储过程不是函数，它们是有选择地返回值的过程，或者-使用suspend-生成动态表"(结果集)的过程.如果您需要真正的功能，并且正在使用Firebird 3，请使用存储的函数:

However you describing this as a function in your question makes me think that this might be the wrong solution altogether. Stored procedures are not functions, they are procedures that optionally return values or - with suspend - produce dynamic 'tables' (a result set). If you need a real function, and you are using Firebird 3, then use stored functions:

create function f_class(salary SALARY) returns varchar(10)
as
begin
    return case
        when salary <= 39999 then 'LOW'
        when salary <= 67999 then 'MID'
        when salary <= 99999 then 'HIGH'
        when salary >= 100000 then 'ELITE'
    end;
end

然后您可以像使用它

select f_class(120000) from rdb$database;

这里仅使用rdb$database进行说明，可以在可以使用普通SQL函数的任何地方使用存储函数.

The use of rdb$database is purely for illustration here, a stored function can be used anywhere a normal SQL function can be used.

这篇关于在Firebird中，如何在过程中编写多个if语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！