作者fantasyj (如梦似幻)
看板Database
标题Re: [SQL ] PL/SQL CASE语法
时间Tue Jan 4 20:34:37 2011
※ 引述《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)
1F：推 cling05:thx 01/05 15:27