Re: [SQL ] PL/SQL CASE語法

[fantasyj (如夢似幻)]

※ 引述《cling05 (cling(b))》之銘言：
: 請問一下pl/sql
: 要像c語言一樣做簡單的case判斷
: 然後輸出欄位要怎麼做呢
: 以下是我的想法
: 但是最後跑不出來
: 請問問題何在?
: declare
:     carN1 varchar2(5);
:     motoN1 varchar2(5);
:     rewdN1 varchar2(5);
: begin
:     select car_type from table01 a
:         where license_yy='001' and license_no1='002';
:     case a.car_type
:         when '1' then carN1:=a.car_num1;
:         when '2' then motoN1:=a.car_num1;
:         when '3' then rewdN1:=a.car_num1;
:     end case;
: end;
: /
: ERROR msg如下
: ORA-06550: line 8, column 5:
: PLS-00428: an INTO clause is expected in this SELECT statement
: ORA-06550: line 10, column 10:
: PLS-00201: identifier 'A.CAR_TYPE' must be declared
: ORA-06550: line 10, column 5:
: PL/SQL: Statement ignored

您是要輸出到那.......只是簡單的條件用Oracle自有的DECODE函數就可以了

DECALRE
  CARN1  VARCHAR2(5);
  MOTON1 VARCHAR2(5);
  REWDN1 VARCHAR2(5);
BEGIN
  SELECT DECDOE(A.CAR_TYPE, '1', A.CAR_TYPE, NULL)
        ,DECODE(A.CAR_TYPE, '2', A.CAR_TYPE, NULL)
        ,DECODE(A.CAR_TYPE, '3', A.CAR_TYPE, NULL)
    INTO CARN1
        ,MOTON1
        ,REWDN1
    FROM TABLE01 A
   WHERE A.LICENSE_YY = '001'
     AND A.LICENSE_NO1 = '002';
END;

PS:Oracle的PL/SQL變數的宣告沒有大小寫之分....

--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 59.117.117.50
※ 編輯: fantasyj        來自: 59.117.117.50        (01/04 20:40)
推 cling05:thx