存储过程的问题（急，在线等）

Question

下面这段存储过程，在plsql develop中的Command窗口，执行，提示执行成功。。
可是数据库里的数据没有发生变化。。是为什么呢？？？CREATE OR REPLACE PROCEDURE SP_IMPORTSTAFF(
    outERR_CD     OUT    NUMBER,        
    outERR_MSG    OUT    VARCHAR2)      
IS   
    wREV_USER    VARCHAR2(10)   := 'SYSTEM';        
    wREV_DATE    DATE           := SYSDATE;         
                                 wCNT         NUMBER         := 0;               
    CURSOR CUR_STAFF IS
    SELECT RTRIM(D.STAFF_CD) STAFF_CD,
           RTRIM(D.STAFF_NAME) STAFF_NAME,
           RTRIM(D.STAFF_NAME_KANA) STAFF_NAME_KANA,
           TO_CHAR(D.NYUSHA,'YYYY/MM/DD') HIRE_DATE,
           TO_CHAR(D.TAISHOKU,'YYYY/MM/DD') RETIRE_DATE,
           '00' QUEST_MISSION,
           '0' QUEST_GRADE,
           NULL BIRTHDAY,
           RTRIM(D.MAIL_ADDRESS) MAIL_ADDRESS,
           CASE WHEN RTRIM(D.SECTION_CD1) IS NOT NULL THEN RTRIM(D.SECTION_CD1)
                ELSE '000000'
           END SECTION_CD
    FROM DIRECTOR_MST D
    UNION ALL
    SELECT T.STAFF_CD STAFF_CD,   
           T.STAFF_NAME STAFF_NAME,   
           T.STAFF_NAME_KANA STAFF_NAME_KANA,  
           T.HIRE_DATE HIRE_DATE,  
           T.RETIRE_DATE RETIRE_DATE,  
           T.QUEST_MISSION QUEST_MISSION,  
           T.QUEST_GRADE QUEST_GRADE,  
           T.BIRTHDAY BIRTHDAY,  
           T.MAIL_ADDRESS MAIL_ADDRESS,  
           T.SECTION_CD SECTION_CD   
     FROM (    
    SELECT RTRIM(K.SHAINCD) STAFF_CD,
           RTRIM(K.KANNAME) STAFF_NAME,
           RTRIM(K.KANANAME) STAFF_NAME_KANA,
           TO_CHAR(N.SEINYUYMD,'YYYY/MM/DD') HIRE_DATE,
           DECODE(TO_CHAR(N.TAIYMD,'YYYY/MM/DD'),'1901/01/01','9999/12/31', TO_CHAR(N.TAIYMD,'YYYY/MM/DD')) RETIRE_DATE,
           RTRIM(H.KBN14) QUEST_MISSION,
           RTRIM(H.KBN15) QUEST_GRADE,
           TO_CHAR(K.BIRTHDAY,'YYYY/MM/DD')  BIRTHDAY,
           RTRIM(I.MAIL_ADDRESS) MAIL_ADDRESS,
           RTRIM(G.BUMONCD) SECTION_CD
      FROM PED_KIHON K,PED_GENSHOKU G,PED_NYUTAI N,KYM_SHAIN H,INTRANET_STAFF_MST I
      WHERE K.SHAINCD = G.SHAINCD
       AND K.SHAINCD = N.SHAINCD
       AND K.SHAINCD = H.SHAINCD(+)
       AND K.SHAINCD = I.STAFF_CD(+)
       AND (K.SHAINCD LIKE '0%' OR K.SHAINCD LIKE '1%' OR K.SHAINCD LIKE '4%') 
    ) T   
    WHERE (SELECT COUNT(D.STAFF_CD) FROM DIRECTOR_MST D WHERE D.STAFF_CD = T.STAFF_CD) = 0  
    ORDER BY STAFF_CD;BEGIN    FOR rec IN CUR_STAFF LOOP     -- 是否登录
        SELECT COUNT(STAFF_CD) INTO wCNT FROM STAFF_MST WHERE STAFF_CD = rec.STAFF_CD;     -- 新登录
        IF wCNT = 0 THEN
            INSERT INTO STAFF_MST(
                            STAFF_CD
                           ,STAFF_NAME
                           ,STAFF_NAME_KANA
                           ,HIREDATE
                           ,RETIREDATE
                           ,QUEST_MISSION
                           ,QUEST_GRADE
                           ,BIRTHDAY
                           ,MAIL_ADDRESS
                           ,SECTION_CD
                           ,REV_USER
                           ,REV_DATE)
                   VALUES(
                            rec.STAFF_CD
                           ,rec.STAFF_NAME
                           ,FP_CONVERT(rec.STAFF_NAME_KANA)
                           ,TO_DATE(rec.HIRE_DATE,'YYYY/MM/DD')
                           ,TO_DATE(rec.RETIRE_DATE,'YYYY/MM/DD')
                           ,rec.QUEST_MISSION
                           ,rec.QUEST_GRADE
                           ,TO_DATE(rec.BIRTHDAY,'YYYY/MM/DD')
                           ,rec.MAIL_ADDRESS
                           ,rec.SECTION_CD
                           ,wREV_USER
                           ,wREV_DATE
                         );
            INSERT INTO SYSTEM_AUTH(
                            STAFF_CD
                           ,ITSS_PASSWORD
                           ,AUTH
                           ,FLG_1
                           ,FLG_2
                           ,FLG_3
                           ,REV_USER
                           ,REV_DATE)
                   VALUES(
                           rec.STAFF_CD
                          ,'quest1'
                          ,NULL
                          ,NULL
                          ,1
                          ,NULL
                          ,wREV_USER
                          ,wREV_DATE
                         );
            INSERT INTO NETRI_T_ROLE_MST(
                            STAFF_CD
                           ,ROLE_KIND
                           ,ROLE_CD
                           ,REV_USER
                           ,REV_DATE
                           ,REV_COUNT)
                   VALUES(
                           rec.STAFF_CD
                          ,'class'
                          ,(CASE WHEN SUBSTRB(rec.STAFF_CD,1,1) IN ('2','3','5','6') THEN 'other_company'
                                 ELSE 'quitss_member'
                            END)
                          ,wREV_USER
                          ,wREV_DATE
                          ,1);            INSERT INTO NTLIC_T_LICENSE_PAY_MANAGE(
                           STAFF_CD
                          ,RETIRED_FLG
                          ,PAY_KIND
                          ,PROMOTION_FLG
                          ,LAST_MON_ALLOWANCE
                          ,CUR_MON_ALLOWANCE
                          ,LAST_MON_LUMP_SUM
                          ,CUR_MON_LUMP_SUM
                          ,REV_USER
                          ,REV_DATE
                          ,REV_COUNT)
                   VALUES(
                           rec.STAFF_CD
                          ,0
                          ,(CASE WHEN rec.QUEST_MISSION > '09' THEN 'MONTHLY' ELSE 'ONCE' END)
                          ,0
                          ,0
                          ,0
                          ,0
                          ,0
                          ,wREV_USER
                          ,wREV_DATE
                          ,1);           DBMS_OUTPUT.PUT_LINE(rec.STAFF_CD||'  登录);
    -- 更新
        ELSE            UPDATE STAFF_MST SET
                    STAFF_NAME      = rec.STAFF_NAME
                   ,STAFF_NAME_KANA = FP_CONVERT(rec.STAFF_NAME_KANA)
                   ,HIREDATE        = TO_DATE(rec.HIRE_DATE,'YYYY/MM/DD')
                   ,RETIREDATE      = TO_DATE(rec.RETIRE_DATE,'YYYY/MM/DD')
                   ,QUEST_MISSION   = rec.QUEST_MISSION
                   ,QUEST_GRADE     = rec.QUEST_GRADE
                   ,BIRTHDAY        = TO_DATE(rec.BIRTHDAY,'YYYY/MM/DD')
                   ,MAIL_ADDRESS    = rec.MAIL_ADDRESS
                   ,SECTION_CD      = rec.SECTION_CD
                   ,REV_USER        = wREV_USER
                   ,REV_DATE        = wREV_DATE
             WHERE STAFF_CD = rec.STAFF_CD;       -- 退职
            IF rec.SECTION_CD = 'ZZZZZZZZZZ' THEN
                UPDATE SYSTEM_AUTH SET
                    ITSS_PASSWORD = 'ZZZZZZZZZZZZZZZZZZZZ'
                   ,FLG_1         = ''
                   ,FLG_2         = ''
                   ,REV_USER      = wREV_USER
                   ,REV_DATE      = wREV_DATE
                 WHERE STAFF_CD = rec.STAFF_CD;
                DELETE FROM NETRI_T_ROLE_MST
                 WHERE STAFF_CD = rec.STAFF_CD;
                DELETE FROM NETRI_T_FNCAUTH_MST
                 WHERE ROLE_KIND = 'staff'
                   AND ROLE_CD = rec.STAFF_CD;                DBMS_OUTPUT.PUT_LINE(rec.STAFF_CD ||'  退职);            ELSE
                DBMS_OUTPUT.PUT_LINE(rec.STAFF_CD ||'  更新);
            END IF;        END IF;    END LOOP; 
    COMMIT;EXCEPTION
    WHEN OTHERS THEN
        outERR_CD     := SQLCODE;
        outERR_MSG    := SUBSTRB(SQLERRM,1,400);
        UTL_FILE.FCLOSE_ALL;
        ROLLBACK;
END;

Answer 1

不好意思，我头一回做存储过程，这个异常捕捉到哪里看到？？
在Command窗口中，只有这面的信息提示
SQL> exec SP_IMPORTSTAFF;
PL/SQL procedure successfully completed

Answer 2

show error看看,还有你的存储过程编译的成功的?有错误?查看是否编译成功
select T. status from dba_objects T where T.OBJECT_NAME='存储过程名称' ;

Answer 3

TO:java3344520
编译没有问题。。通过我用你提供的SQL，status没有显示的内容。。

Answer 4

我的存储过程中有COMMIT;语句

Answer 5

insert update 最好都commit一下。可以看到结果的。

Answer 6

我每个SQL之后都加commit了。数据库结果还是不变既不登录，也不更新。
郁闷。。

Answer 7

确认下你传入的参数有没有导致cursor的记录数为0

Answer 8

e，没入参，直接看这个有没有数据
SELECT RTRIM(D.STAFF_CD) STAFF_CD,
  RTRIM(D.STAFF_NAME) STAFF_NAME,
  RTRIM(D.STAFF_NAME_KANA) STAFF_NAME_KANA,
  TO_CHAR(D.NYUSHA,'YYYY/MM/DD') HIRE_DATE,
  TO_CHAR(D.TAISHOKU,'YYYY/MM/DD') RETIRE_DATE,
  '00' QUEST_MISSION,
  '0' QUEST_GRADE,
  NULL BIRTHDAY,
  RTRIM(D.MAIL_ADDRESS) MAIL_ADDRESS,
  CASE WHEN RTRIM(D.SECTION_CD1) IS NOT NULL THEN RTRIM(D.SECTION_CD1)
  ELSE '000000'
  END SECTION_CD
  FROM DIRECTOR_MST D
  UNION ALL
  SELECT T.STAFF_CD STAFF_CD,   
  T.STAFF_NAME STAFF_NAME,   
  T.STAFF_NAME_KANA STAFF_NAME_KANA,   
  T.HIRE_DATE HIRE_DATE,   
  T.RETIRE_DATE RETIRE_DATE,   
  T.QUEST_MISSION QUEST_MISSION,   
  T.QUEST_GRADE QUEST_GRADE,   
  T.BIRTHDAY BIRTHDAY,   
  T.MAIL_ADDRESS MAIL_ADDRESS,   
  T.SECTION_CD SECTION_CD   
  FROM (   
  SELECT RTRIM(K.SHAINCD) STAFF_CD,
  RTRIM(K.KANNAME) STAFF_NAME,
  RTRIM(K.KANANAME) STAFF_NAME_KANA,
  TO_CHAR(N.SEINYUYMD,'YYYY/MM/DD') HIRE_DATE,
  DECODE(TO_CHAR(N.TAIYMD,'YYYY/MM/DD'),'1901/01/01','9999/12/31', TO_CHAR(N.TAIYMD,'YYYY/MM/DD')) RETIRE_DATE,
  RTRIM(H.KBN14) QUEST_MISSION,
  RTRIM(H.KBN15) QUEST_GRADE,
  TO_CHAR(K.BIRTHDAY,'YYYY/MM/DD') BIRTHDAY,
  RTRIM(I.MAIL_ADDRESS) MAIL_ADDRESS,
  RTRIM(G.BUMONCD) SECTION_CD
  FROM PED_KIHON K,PED_GENSHOKU G,PED_NYUTAI N,KYM_SHAIN H,INTRANET_STAFF_MST I
  WHERE K.SHAINCD = G.SHAINCD
  AND K.SHAINCD = N.SHAINCD
  AND K.SHAINCD = H.SHAINCD(+)
  AND K.SHAINCD = I.STAFF_CD(+)
  AND (K.SHAINCD LIKE '0%' OR K.SHAINCD LIKE '1%' OR K.SHAINCD LIKE '4%')  
  ) T   
  WHERE (SELECT COUNT(D.STAFF_CD) FROM DIRECTOR_MST D WHERE D.STAFF_CD = T.STAFF_CD) = 0   
  ORDER BY STAFF_CD;

Answer 9

TO minitoy
 这个是有数据的

Answer 10

有plsqldev等工具的话，test跟一下