关于存储过程的一个不解的问题？

Question

06550, 00000, "line %s, column %s:\n%s"
// *Cause:  Usually a PL/SQL compilation error.
// *Action:我觉得还是过程有问题，你单步跟踪试试

Answer 1

我跟过了，但是test的时候根本没有问题的

Answer 2

我想应该是你的过程没有编译过去. 你可以用pl/sql dev看看,没编译过去的话,前面会带一个红色的叉号.  右键编译.

Answer 3

错误信息：ORA-06550  line2 Column3
PL/SQL: Statement Ignoed我找了错误的位置，但是这个位置是一段注释信息呀，怎么会出错呢？

Answer 4

我的存储过程的全部代码CREATE OR REPLACE PROCEDURE pp_insert_total_score
/*
  写入总表*/
(
   p_result          OUT      VARCHAR2,
   p_msg             OUT      VARCHAR2,
   m_casenum         IN       VARCHAR2,                      
   m_grant_unit      IN       VARCHAR2,                     
   m_formal_arch     IN       VARCHAR2,                       
   m_license_num     IN       VARCHAR2,                            
   m_name            IN       VARCHAR2,                      
   m_sex             IN       VARCHAR2,                
   m_addr            IN       VARCHAR2,         
   m_punish_date     IN       DATE,                            
   m_score           IN       NUMBER,                         
   m_fine            IN       NUMBER,
   m_hang_months     IN       NUMBER,                       
   m_is_notify       IN       VARCHAR2,      
   m_license_level   IN       VARCHAR2,                      
   m_type            IN       VARCHAR2,                                
   m_istemp          IN       VARCHAR2            
)
IS
   v_total_score       NUMBER        := 0;                        
   v_temp_score        NUMBER        := 0;                         
   v_over_date         DATE;                                
   temp_days           NUMBER        := 0;     
   v_status            CHAR (1);     
   v_notify_date       DATE;                                   
   v_score_to          DATE;                                     
   v_score_start       DATE;                           
   v_hagn_to           DATE;
   v_duty_count        NUMBER (3, 0) := 0;                     
   v_violation_count   NUMBER (3, 0) := 0;                        
   v_last_score        NUMBER (3, 0) := 0;          
   v_time              DATE;
--M_ADDR                      VARCHAR2(50);
/*CURSOR CUR_SCORE(V_PUNISH_DATE   DATE ) 
           IS SELECT SCORE
           FROM  PECCANCY
          WHERE SCORE_CANCEL='0'
            and IS_DEL='0'
            AND CASENUM = M_CASENUM;*/
BEGIN
   SELECT TO_DATE (info_value, 'YYYY.MM.DD')
     INTO v_score_start
     FROM ws_sys_info
    WHERE info_name = 'SCORE_START_DATE';   SELECT ADD_MONTHS (v_score_start,
                        12
                      * (  FLOOR (MONTHS_BETWEEN (SYSDATE, v_score_start) / 12)
                         + 1
                        )
                     )
     INTO v_score_to
     FROM DUAL;   IF m_istemp ='1'
   THEN
      BEGIN
         UPDATE w_total_score
            SET temp_score = temp_score + m_score
          WHERE TRIM (formal_arch) = TRIM (m_formal_arch);         IF SQL%NOTFOUND
         THEN
            INSERT INTO w_total_score
                        (grant_unit, formal_arch, license_num,
                         NAME, sex, addr, total_score, temp_score,
                         violation_count, duty_count, status, notify_date,
                         score_to, over_date, license_level, hangto_date
                        )
                 VALUES (m_grant_unit, m_formal_arch, TRIM (m_license_num),
                         m_name, m_sex, m_addr, 0, m_score,
                         0, 0, 'A', NULL,
                         v_score_to, NULL, m_license_level, NULL
                        );
         END IF;         p_result := '0';
 p_msg := '更新成功！';
    
         GOTO label_end;
      EXCEPTION
         WHEN OTHERS
         THEN
            p_result := '255';
            p_msg := '更新出错！';
            GOTO label_end;
      END;
   END IF;   v_hagn_to := NULL;
   v_time := SYSDATE;   -- SELECT NVL(SUM(SCORE),0), NVL(SUM(FINE),0) INTO TEMP_SCORE, M_FINE FROM W_DETAIL WHERE CASENUM = M_CASENUM;
   -- select score,fine into temp_score,m_fine from peccancy where casenum = m_casenum;
    --TEMP_SCORE:=M_SCORE;
   -- SELECT NVL(MAX(HANGMONTHS), 0) INTO TEMP_MONTHS FROM W_DETAIL WHERE CASENUM = M_CASENUM;
   -- SELECT HANG_TO INTO V_HAGN_TO FROM PECCANCY WHERE CASENUM = M_CASENUM;
    --TEMP_MONTHS:=M_HANG_MONTHS;
   IF m_hang_months > 0
   THEN
      v_status := 'F';                                             
   ELSE
     BEGIN 
      SELECT NVL (hang_to, SYSDATE) - SYSDATE
        INTO temp_days
        FROM peccancy
       WHERE casenum = m_casenum;                    --GRANT_UNIT=M_GRANT_UNIT
     EXCEPTION
      WHEN no_data_found THEN
        p_result := '255';
        p_msg := '无此记录！';
        GOTO label_end;
     END;
--       AND LICENSE_NUM=M_LICENSE_NUM;
      IF temp_days > 0
      THEN
         v_status := 'F';                                          
      ELSE
         v_status := 'A';                                          
      END IF;
   END IF;   IF m_type = '1'
   THEN
      v_duty_count := 0;
      v_violation_count := 1;
   ELSE
      v_duty_count := 1;
      v_violation_count := 0;
   END IF;/*
  OPEN CUR_TOTAL_SCORE;
  FETCH CUR_TOTAL_SCORE INTO REC_TOTAL_SCORE;
  IF CUR_TOTAL_SCORE%NOTFOUND THEN
    SELECT TO_DATE(INFO_VALUE,'YYYY.MM.DD') INTO V_SCORE_START
      FROM WS_SYS_INFO
     WHERE INFO_NAME='SCORE_START_DATE';
    SELECT ADD_MONTHS(V_SCORE_START,12*(FLOOR(MONTHS_BETWEEN(SYSDATE,V_SCORE_START)/12)+1))
      INTO V_SCORE_TO
      FROM DUAL;*/--   v_total_score := m_score;
   BEGIN
   SELECT NVL (total_score, 0), NVL (temp_score, 0), over_date, score_to,
          NVL (duty_count, 0) + v_duty_count,
          NVL (violation_count, 0) + v_violation_count
     INTO v_total_score, v_temp_score, v_over_date, v_score_to,
          v_duty_count,
          v_violation_count
     FROM w_total_score
    WHERE TRIM (license_num) = TRIM (m_license_num);--    v_zsscore := m_score + v_zsscore;
   IF (v_total_score<12) AND (v_total_score + m_score >= 12)
   THEN
      v_over_date := m_punish_date;
   ELSIF v_total_score+m_score <12 THEN
      v_over_date := NULL;
   END IF;

Answer 5

IF     v_score_to >= v_time /*M_PUNISH_DATE*/
      AND v_total_score + v_temp_score < 12
   THEN
      v_score_to := ADD_MONTHS (v_score_to, 12);
      v_over_date := NULL;
      v_total_score := m_score;
--      V_DUTY_COUNT:=V_DUTY_COUNT+,0);
--      V_VIOLATION_COUNT:=V_VIOLATION_COUNT+NVL(REC_TOTAL_SCORE.VIOLATION_COUNT,0);
       
   ELSE
      v_total_score := v_total_score + m_score;
   -- V_DUTY_COUNT:=V_DUTY_COUNT+NVL(REC_TOTAL_SCORE.DUTY_COUNT,0);
   -- V_VIOLATION_COUNT:=V_VIOLATION_COUNT+NVL(REC_TOTAL_SCORE.VIOLATION_COUNT,0);
   END IF;   IF v_total_score >= 12
   THEN
      IF v_over_date IS NULL
      THEN
         v_over_date := v_time;         
         --V_OVER_DATE:=M_PUNISH_DATE;
         /*SELECT MAX(PECCANCYTIME) INTO V_OVER_DATE
           FROM PECCANCY T
          WHERE casenum=M_CASENUM;--GRANTUNIT=M_GRANT_UNIT
            --AND TRIM(LICENSENUM)=TRIM(M_LICENSE_NUM)
            --AND SCORE_CANCEL='0'
            --AND IS_DEL='0';*/
         IF v_over_date < m_punish_date
         THEN
            v_over_date := m_punish_date;
         END IF;
      -- ELSE
      --   IF M_PUNISH_DATE<V_OVER_DATE THEN
      --     FOR REC IN CUR_SCORE(V_OVER_DATE) LOOP
      --       V_LAST_SCORE:=V_LAST_SCORE+REC.SCORE;
      --     END LOOP;
      --     IF M_SCORE>=V_LAST_SCORE THEN
      --       V_OVER_DATE:=M_PUNISH_DATE;
      --     END IF;
        -- END IF;
      END IF;
   END IF;   IF v_temp_score - m_score > 0
   THEN
      v_temp_score := v_temp_score - m_score;
   ELSE
      v_temp_score := 0;
   END IF;   UPDATE w_total_score
      SET total_score = v_total_score,                                
          temp_score = v_temp_score,                                
          addr = m_addr,                                          
          formal_arch = m_formal_arch,                       
          duty_count =  v_duty_count,          
          violation_count = v_violation_count,
                                                                
          total_fine = total_fine + m_fine,
--            NOTIFY_DATE=V_NOTIFY_DATE,
          over_date = v_over_date,                                
          license_level = m_license_level,
--DECODE(M_LICENSE_LEVEL,NULL,LICENSE_LEVEL,M_LICENSE_LEVEL,M_LICENSE_LEVEL          hangto_date = v_hagn_to                                   
    WHERE grant_unit = m_grant_unit
      AND TRIM (license_num) = TRIM (m_license_num);  -- IF SQL%NOTFOUND
    EXCEPTION
      WHEN no_data_found THEN
        INSERT INTO w_total_score
                  (grant_unit, formal_arch, license_num,
                   NAME, sex, addr, total_score, temp_score,
                   violation_count, duty_count, total_fine, status,
                   notify_date, score_to, over_date, license_level,
                   hangto_date
                  )
           VALUES (m_grant_unit, m_formal_arch, TRIM (m_license_num),
                   m_name, m_sex, m_addr, m_score, 0,
                   v_violation_count, v_duty_count, m_fine,
                                                           --   M_FINE,
                                                           v_status,
                   NULL,                                    --  V_NOTIFY_DATE,
                        v_score_to, NULL, m_license_level,
                   v_hagn_to
                  );
   END;   p_result := '0';
   p_msg := '更新成功！';
   GOTO label_end;
   <<label_end>>
   IF p_result ='0' THEN COMMIT;
   ELSE ROLLBACK;
   END IF;
   NULL;
EXCEPTION
   WHEN OTHERS
   THEN
      p_result := '255';
      p_msg := '失败！'||substr(SQLERRM,1,200);
      NULL;
/*  IF CUR_TOTAL_SCORE%ISOPEN THEN
    CLOSE CUR_TOTAL_SCORE;
  END IF; */
END pp_insert_total_score;

Answer 6

CREATE OR REPLACE PROCEDURE pp_insert_total_score  /* 这里注释可以吗*/
(
p_result OUT VARCHAR2,
p_msg OUT VARCHAR2,注释要写在一行的结尾吧，你的注释是夹在句子中间的

Answer 7

这个存储过程在别的存储过程里面调用没有问题，而且，我在PL/SQL里面test它也没有问题，执行的很好

Answer 8

哪个错误？还是在line2 column 3的错误？？？
MSN:[email protected]