这个查询可以select b.sclientid , b.sL2OrgID ,
(case when b.saddr=' ' or b.saddr is null then a.sCheckAddr else b.saddr end),
(case when b.stel=' ' or b.stel is null then a.sCheckTel else b.stel end)
from BTC_ClientInfo a,btc_updateaddr b
where a.sclientid=b.sclientid and a.sL2OrgID =b.sL2OrgID;
但是加上into就提示需要where关键字了
declare
-- Local variables here
tmp_sclientid varchar2(50);
tmp_sL2OrgiD varchar2(50);
tmp_addr varchar2(200);
tmp_tel varchar2(30);
beginselect b.sclientid into tmp_sclientid, b.sL2OrgID into tmp_sL2OrgiD,
(case when b.saddr=' ' or b.saddr is null then a.sCheckAddr else b.saddr end) into tmp_addr,
(case when b.stel=' ' or b.stel is null then a.sCheckTel else b.stel end) into tmp_tel
from BTC_ClientInfo a,btc_updateaddr b
where a.sclientid=b.sclientid and a.sL2OrgID =b.sL2OrgID;end;
我是像把记录值赋给变量,之前用子查询在外面用聚合函数的时候也试过提示解决方法用不上在这里,groub by也试过
这里应该是怕我返回的值不是唯一的所以这样提示的吧?不知道怎么解决?请高人指点
(case when b.saddr=' ' or b.saddr is null then a.sCheckAddr else b.saddr end),
(case when b.stel=' ' or b.stel is null then a.sCheckTel else b.stel end)
from BTC_ClientInfo a,btc_updateaddr b
where a.sclientid=b.sclientid and a.sL2OrgID =b.sL2OrgID;
但是加上into就提示需要where关键字了
declare
-- Local variables here
tmp_sclientid varchar2(50);
tmp_sL2OrgiD varchar2(50);
tmp_addr varchar2(200);
tmp_tel varchar2(30);
beginselect b.sclientid into tmp_sclientid, b.sL2OrgID into tmp_sL2OrgiD,
(case when b.saddr=' ' or b.saddr is null then a.sCheckAddr else b.saddr end) into tmp_addr,
(case when b.stel=' ' or b.stel is null then a.sCheckTel else b.stel end) into tmp_tel
from BTC_ClientInfo a,btc_updateaddr b
where a.sclientid=b.sclientid and a.sL2OrgID =b.sL2OrgID;end;
我是像把记录值赋给变量,之前用子查询在外面用聚合函数的时候也试过提示解决方法用不上在这里,groub by也试过
这里应该是怕我返回的值不是唯一的所以这样提示的吧?不知道怎么解决?请高人指点
要如下使用into才行:
DECLARE
-- Local variables here
TMP_SCLIENTID VARCHAR2(50);
TMP_SL2ORGID VARCHAR2(50);
TMP_ADDR VARCHAR2(200);
TMP_TEL VARCHAR2(30);
BEGIN
SELECT B.SCLIENTID,
B.SL2ORGID,
(CASE
WHEN B.SADDR = ' ' OR B.SADDR IS NULL THEN
A.SCHECKADDR
ELSE
B.SADDR
END),
(CASE
WHEN B.STEL = ' ' OR B.STEL IS NULL THEN
A.SCHECKTEL
ELSE
B.STEL
END)
INTO TMP_SCLIENTID, TMP_SL2ORGID, TMP_ADDR, TMP_TEL
FROM BTC_CLIENTINFO A, BTC_UPDATEADDR B
WHERE A.SCLIENTID = B.SCLIENTID
AND A.SL2ORGID = B.SL2ORGID;
END;
/
多于一条记录的处理方法:
DECLARE
-- Local variables here
TMP_SCLIENTID VARCHAR2(50);
TMP_SL2ORGID VARCHAR2(50);
TMP_ADDR VARCHAR2(200);
TMP_TEL VARCHAR2(30);
CURSOR c IS SELECT B.SCLIENTID,
B.SL2ORGID,
(CASE
WHEN B.SADDR = ' ' OR B.SADDR IS NULL THEN
A.SCHECKADDR
ELSE
B.SADDR
END),
(CASE
WHEN B.STEL = ' ' OR B.STEL IS NULL THEN
A.SCHECKTEL
ELSE
B.STEL
END)
FROM BTC_CLIENTINFO A, BTC_UPDATEADDR B
WHERE A.SCLIENTID = B.SCLIENTID
AND A.SL2ORGID = B.SL2ORGID;
BEGIN
OPEN c;
LOOP
FETCH c INTO TMP_SCLIENTID, TMP_SL2ORGID, TMP_ADDR, TMP_TEL;
EXIT WHEN c%NOTFOUND;
--other operate
END LOOP;
END;
/