PL/SQL 我做錯了什麼？ (PL/SQL What am I doing wrong?)

問題描述

PL/SQL 我做錯了什麼？ (PL/SQL What am I doing wrong?)

DECLARE
updated_commission float;
cur_var a%rowtype;
BEGIN
dbms_output.put_line('EMPLOYEE_NO     FNAME     LNAME     COMMISSION');
dbms_output.put_line('‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑');
WHILE cur_var%found AND COMMISSION IS NOT NULL
LOOP
CASE BRANCH_NO
  WHEN 100 THEN updated_commission := COMMISSION * 1.10
  WHEN 101 THEN updated_commission := COMMISSION * 1.15
  WHEN 104 THEN updated_commission := COMMISSION * 1.20
  dbms_output.put_line(cur_var.EMPLOYEE_NO||'       '||cur_var.FNAME||'       '||cur_var.LNAME||'     
'||cur_var.COMMISSION||'      '||cur_var.updated_commission);
END LOOP;
END;

（一直給我一個關於我的 CASE 語句的錯誤，我無法找出問題所在，請幫忙） PS（必須是一個 while 循環來分配）

參考解法

方法 1:

How about this? I fixed only the CASE, didn't look at other stuff as we don't know anything about CUR_VAR ‑ how it gets its value, what COMMISION is etc..

DECLARE
  updated_commission float;
  cur_var a%rowtype;
BEGIN
  dbms_output.put_line('EMPLOYEE_NO     FNAME     LNAME     COMMISSION');
  dbms_output.put_line('‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑');
  WHILE cur_var%found AND COMMISSION IS NOT NULL
  LOOP
    updated_commision := commision * 
      case when branch_no = 100 then 1.10
           when branch_no = 101 then 1.15
           when branch_no = 104 then 1.20
      end;
  dbms_output.put_line(cur_var.EMPLOYEE_NO||'       '||cur_var.FNAME||'       '||cur_var.LNAME||'     
    '||cur_var.COMMISSION||'      '||cur_var.updated_commission);
  END LOOP;
END;

方法 2:

As another way to fix your CASE statement you might consider:

DECLARE
  updated_commission  NUMBER;
  cur_var             a%rowtype;
BEGIN
  dbms_output.put_line('EMPLOYEE_NO     FNAME     LNAME     COMMISSION');
  dbms_output.put_line('‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑');

  WHILE cur_var%found AND COMMISSION IS NOT NULL
  LOOP
    CASE BRANCH_NO
      WHEN 100 THEN updated_commission := COMMISSION * 1.10
      WHEN 101 THEN updated_commission := COMMISSION * 1.15
      WHEN 104 THEN updated_commission := COMMISSION * 1.20
    END CASE;

    dbms_output.put_line(cur_var.EMPLOYEE_NO || '       ' ||
                         cur_var.FNAME       || '       ' ||
                         cur_var.LNAME       || '         ' ||
                         cur_var.COMMISSION  || '      ' ||
                         updated_commission);
  END LOOP;
END;

(by mjboesche、Littlefoot、Bob Jarvis ‑ Слава Україні)

參考文件

1. PL/SQL What am I doing wrong? (CC BY‑SA 2.5/3.0/4.0)