当前位置: 动力学知识库 > 问答 > 编程问答 >

plsql - Correct way of writing PL SQL conditions

问题描述:

Below is the condition for my table Request.

level of till 300$ 301-500$ 501-3400$

credit card

usage in

3 month

0% 0% 0% 0%

1-30% 30% 0% 0%

31-50% 50% 0% 0%

51-60% 50% 15% 0%

61-70% 100% 15% 0%

70%~ 100% 30% 30%

My task is to retrieve all information I mentioned above in one table using PL SQL. I have table Request which consists of 3 columns as client_id, level_3m and credit_limit So the output(for example) should look like this using above information:

level_3m credit_limit($) new_limit(%)

0 50 0

45 400 0

45 250 50

65 350 15

80 1500 30

What have I done so far? Here is my own script:

DECLARE

v_level VARCHAR2(100);

v_credit_limit VARCHAR2(100);

v_id VARCHAR2(100);

new_limit VARCHAR2(100);

BEGIN

SELECT level_3m,

credit_limit

INTO v_level, v_credit_limit

FROM request a

WHERE v_id = a.client_id;

--this is for "till 300$" condition

IF v_level = 0

AND v_credit_limit =< 300 THEN

new_limit := 0;

ELSIF v_level >= 1

AND v_level <= 30

AND v_credit_limit =< 300 THEN

new_limit := 30;

ELSIF v_level >= 31

AND v_level <= 50

AND v_credit_limit =< 300 THEN

new_limit := 50;

ELSIF v_level >= 51

AND v_level <= 60

AND v_credit_limit =< 300 THEN

new_limit := 50;

ELSIF v_level >= 61

AND v_level <= 70

AND v_credit_limit =< 300 THEN

new_limit := 100;

ELSIF v_level >= 70

AND v_credit_limit =< 300 THEN

new_limit := 100;

END IF;

END;

/

--the other conditions were written same manner as the above one.

I am new to PL/SQL so please tell is my condition right? Or is there another more simple way to write those conditions?

网友答案:

You're doing If statements right.

Another option is using CASE. It's basically the same but sometimes looks a little neater, especially if you're writing out many ELSIF clauses.

    CASE
      WHEN v_level=0  and v_credit_limit=<300 then new_limit:=0
      WHEN v_level>=1 and v_level <=30 and v_credit_limit =<300 then new_limit:=30
      WHEN v_level>=31 and v_level<=50 and v_credit_limit=<300 then new_limit:=50
      WHEN v_level>=51 and v_level<=60 and v_credit_limit=<300 then new_limit:=50
      WHEN v_level>=61 and v_level<=70 and v_credit_limit=<300 then new_limit:=100
      WHEN v_level>=70 and v_credit_limit=<300 then new_limit:=100
    END CASE

It doesn't really matter all that much whether you use IF or CASE in my opinion.

网友答案:

Personally I would have a table with the v_level and credit limit in it and join to that to get new_limit out.

That's the relational way, and therefore the "proper way" in this context.

分享给朋友:
您可能感兴趣的文章:
随机阅读: