--关于SELECT INTO 一直以来的错误认知: --如果查出来多条数据,不仅会抛出异常,而且会把第一行数据赋值上去,直接上例子
CREATE TABLE SELECT_INTO_TEST (T_ID NUMBER,T_NAME VARCHAR2(10),T_LOCATION VARCHAR2(50));
INSERT INTO SELECT_INTO_TEST
SELECT 1,'RICK','AMERICAN' FROM DUAL
UNION ALL
SELECT 2,'SON','AMERICAN' FROM DUAL;
COMMIT;
SELECT * FROM SELECT_INTO_TEST;
查询出结果为:
--例子一:查出多条数据会自动填充第一行,所以输出为: ** V_ID=1V_NAME=RICKV_LOACTION=AMERICAN**
DECLARE
V_ID NUMBER;
V_NAME VARCHAR2(10);
V_LOCATION VARCHAR2(50);
BEGIN
BEGIN
SELECT T_ID,T_NAME,T_LOCATION
INTO V_ID,V_NAME,V_LOCATION FROM SELECT_INTO_TEST;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
DBMS_OUTPUT.PUT_LINE('V_ID='||V_ID||'V_NAME='||V_NAME||'V_LOACTION='||V_LOCATION);
END;
--这样写才比较正确 :如果查出多条数据,抛出异常,清空表数据
DECLARE
V_ID NUMBER;
V_NAME VARCHAR2(10);
V_LOCATION VARCHAR2(50);
BEGIN
BEGIN
SELECT T_ID,T_NAME,T_LOCATION
INTO V_ID,V_NAME,V_LOCATION FROM SELECT_INTO_TEST;
EXCEPTION WHEN OTHERS THEN
V_ID := NULL;
V_NAME := NULL;
V_LOCATION := NULL;
END;
DBMS_OUTPUT.PUT_LINE('V_ID='||V_ID||'V_NAME='||V_NAME||'V_LOACTION='||V_LOCATION);
END;
总结: 之前一直认为如果查出多条 INTO 到单行上,会直接报错,赋值为空,这一直是一个认知错误,如果多条INTO到单行,会把第一行数据赋值上。 --所以需要再 EXCEPTION 中清空数据。