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;