select into 在存储过程中的运用

运用 select -- into 时可能会导致的异常：

一，no_data_found

二，too_many_rows

在存储过程中，如果有用到select into ，要捕获他可能抛出的异常，比如：

------
begin
select pos_type into v_postype from pos_type_tbl where 1=0;
dbms_output.put_line(v_postype);
exception
when no_data_found then
dbms_output.put_line('没找到数据');
when too_many_rows then
dbms_output.put_line('找到多条要赋给参数的记录');
end;
------

这样，才能保证存储过程在各种可能的情况下都能正常的走完流程，提高了其健壮性；

================================
下面再附上一个运行在真实环境下的实例：

=================================
CREATE OR REPLACE PROCEDURE "HUAXIA_PRO"
/*
Copyright(c) John.
Remark : First Creation (xxx 2009)
*/
(
cd_in IN TBL_EW_MT_SRC.cd%TYPE,
dscp_in IN TBL_EW_MT_SRC.dscp%TYPE,
alt_dscp_in IN TBL_EW_MT_SRC.alt_dscp%TYPE,
is_default_in IN TBL_EW_MT_SRC.is_default%TYPE,
is_other_in IN TBL_EW_MT_SRC.is_other%TYPE,
is_deactivated_in IN TBL_EW_MT_SRC.is_deactivated%TYPE,
is_del_in IN TBL_EW_MT_SRC.is_del%TYPE,
is_sys_in IN TBL_EW_MT_SRC.is_sys%TYPE,
mt_maint_sts_cd_in IN TBL_EW_MT_SRC.mt_maint_sts_cd%TYPE,
created_by_in IN TBL_EW_MT_SRC.created_by%TYPE,
updated_by_in IN TBL_EW_MT_SRC.updated_by%TYPE,
dt_created_in IN TBL_EW_MT_SRC.dt_created%TYPE,
dt_updated_in IN TBL_EW_MT_SRC.dt_updated%TYPE,
version_in IN TBL_EW_MT_SRC.version%TYPE,
result_cursor OUT TYPES.cursor_type
)
AS
v_pk TBL_EW_MT_SRC.cd%TYPE;

BEGIN
SELECT TBL_EW_MT_SRC.cd INTO v_pk
FROM TBL_EW_MT_SRC
WHERE TBL_EW_MT_SRC.cd = cd_in
AND TBL_EW_MT_SRC.version = version_in
AND TBL_EW_MT_SRC.is_sme='Y'
FOR UPDATE NOWAIT;

UPDATE TBL_EW_MT_SRC
SET
TBL_EW_MT_SRC.dscp = dscp_in,
TBL_EW_MT_SRC.alt_dscp = alt_dscp_in,
TBL_EW_MT_SRC.is_default = is_default_in,
TBL_EW_MT_SRC.is_other = is_other_in,
TBL_EW_MT_SRC.is_deactivated = is_deactivated_in,
TBL_EW_MT_SRC.is_del = is_del_in,
TBL_EW_MT_SRC.is_sys = is_sys_in,
TBL_EW_MT_SRC.mt_maint_sts_cd = mt_maint_sts_cd_in,
TBL_EW_MT_SRC.updated_by = updated_by_in,
TBL_EW_MT_SRC.dt_updated = dt_updated_in,
TBL_EW_MT_SRC.version = USF_NEXT_VERSION(version_in)
WHERE TBL_EW_MT_SRC.cd = cd_in
AND TBL_EW_MT_SRC.version = version_in
AND TBL_EW_MT_SRC.is_sme='Y';

OPEN result_cursor FOR
SELECT 'success' AS status FROM dual;

EXCEPTION

WHEN NO_DATA_FOUND THEN
BEGIN
SELECT TBL_EW_MT_SRC.cd INTO v_pk
FROM TBL_EW_MT_SRC
WHERE TBL_EW_MT_SRC.cd = cd_in
AND TBL_EW_MT_SRC.is_sme='Y';
ERR.RAISE_ERR(ERR.UPDATED_BY_OTHER_USER);

EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR.RAISE_ERR(ERR.DELETED_BY_OTHER_USER);
END;

WHEN TOO_MANY_ROWS THEN
ERR.RAISE_ERR(ERR.UNIQUE_CONSTRAIT);

WHEN ERR.RECORD_IS_LOCKED THEN
ERR.RAISE_ERR(ERR.UPDATED_BY_OTHER_USER);

WHEN OTHERS THEN
RAISE;

END;