刚刚写的ORACLE存储过程

CREATE OR REPLACE PROCEDURE P_SubtractPoint(uMsg OUT VARCHAR2)-- 在冻结、锁定、退单等要求减点数的操作中执行减点工作-- SW_HYCJ 会员处理表，记录退单、锁定、冻结的会员记录AS-- 定义游标，将未处理的记录检索出来CURSOR C_HYCL IS SELECT XH,UPPER(HYBH) HYBH,CLSJ,DS,HYZT FROM SW_HYCL WHERE CLID = 0;uXH NUMBER;uHYBH VARCHAR2(20);uCLSJ DATE;uDS NUMBER;uHYZT VARCHAR2(20);uFJDBH VARCHAR2(20);uFJDQY VARCHAR2(20);uMAX_CS NUMBER;uCS NUMBER;uExist NUMBER;uAZD NUMBER;uBZD NUMBER;uLPC NUMBER; uINC NUMBER;uAPQ NUMBER;uAPH NUMBER;uBPQ NUMBER;uBPH NUMBER;uCOUNTA NUMBER;uCOUNTB NUMBER;-- 进入事务处理流程BEGIN  -- 进入游标处理流程  FOR CC_HYCL IN C_HYCL LOOP    uXH   := CC_HYCL.XH;    uHYBH := CC_HYCL.HYBH;    uCLSJ := CC_HYCL.CLSJ;    uDS   := CC_HYCL.DS;    uHYZT := CC_HYCL.HYZT;    -- 获得标志，是否存在该会员编号的父节点记录    SELECT COUNT(*) INTO uExist FROM SW_FZB WHERE UPPER(HYBH) = uHYBH;    -- 判断是否存在父节点记录    IF (uExist > 0 ) THEN  -- 如果存在      -- 获得该会员所属团队的量碰层      SELECT LPC INTO uLPC FROM SW_CSB WHERE fGroup = F_GetGroup(uHYBH) AND uCLSJ >= KSSJ AND uCLSJ <= JSSJ;      -- 获得最小层数和最大层数，准备循环处理每一层的父节点      SELECT MIN(FJDCS),MAX(FJDCS) INTO uCS,uMAX_CS FROM SW_FZB WHERE UPPER(HYBH) = uHYBH;      -- 按层数循环开始处理每个父节点      WHILE uCS <= uMAX_CS LOOP        -- 获得指定层的父节点的编号、区域        SELECT UPPER(FJDBH),FJDQY INTO uFJDBH,uFJDQY FROM SW_FZB WHERE UPPER(HYBH) = uHYBH AND FJDCS = uCS;        -- 获得A、B区的余额点数和A、B区的总点数        SELECT AQYEDS,BQYEDS,AQLJDS,BQLJDS INTO uAPQ,uBPQ,uAZD,uBZD FROM SW_HYB WHERE UPPER(HYBH) = uFJDBH;        uINC := uDS;  --考虑大于量碰层后再增加点数        IF (uCS + uLPC - 1) > uMAX_CS THEN          uINC := 0;        END IF;        -- 判断父节点的区域        IF (uFJDQY = 'A区') THEN          uAPH := uAPQ - uINC;          uAZD := uAZD - uDS;          UPDATE SW_HYB SET            AQLJRS = AQLJRS - 1,            AQLJDS = AQLJDS - uDS,            AQYEDS = AQYEDS - uINC          WHERE UPPER(HYBH) = uFJDBH;        ELSE          uBPH := uBPQ - uINC;          uBZD := uBZD - uDS;          UPDATE SW_HYB SET            BQLJRS = BQLJRS - 1,            BQLJDS = BQLJDS - uDS,            BQYEDS = BQYEDS - uINC          WHERE UPPER(HYBH) = uFJDBH;        END IF;        -- 开始处理职务问题        -- 先处理主任级别的职务        IF (uAZD < 400 OR uBZD < 400) THEN          SELECT COUNT(*) INTO uExist FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC='主任';          IF uExist > 0 THEN            DELETE FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC='主任';            UPDATE SW_ZWB SET ZWMC = '' WHERE UPPER(HYBH) = uFJDBH;          END IF;        END IF;        -- 处理经理级别的职务        -- 计算A、B区的主任人数        SELECT COUNT(*) INTO uCOUNTA FROM SW_FZB WHERE UPPER(FJDBH) = uFJDBH AND FJDQY ='A区' AND HYBH IN (SELECT HYBH FROM SW_ZWB WHERE ZWMC = '主任');        SELECT COUNT(*) INTO uCOUNTB FROM SW_FZB WHERE UPPER(FJDBH) = uFJDBH AND FJDQY ='B区' AND HYBH IN (SELECT HYBH FROM SW_ZWB WHERE ZWMC = '主任');        IF (uCOUNTA = 0 OR uCOUNTB = 0) THEN          SELECT COUNT(*) INTO uExist FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC = '经理';          IF uExist > 0 THEN            DELETE FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC='经理';            UPDATE SW_ZWB SET ZWMC = '主任' WHERE UPPER(HYBH) = uFJDBH;          END IF;        END IF;        -- 处理总监级别的职务        -- 计算A、B区的经理人数        SELECT COUNT(*) INTO uCOUNTA FROM SW_FZB WHERE UPPER(FJDBH) = uFJDBH AND FJDQY ='A区' AND HYBH IN (SELECT HYBH FROM SW_ZWB WHERE ZWMC = '经理');        SELECT COUNT(*) INTO uCOUNTB FROM SW_FZB WHERE UPPER(FJDBH) = uFJDBH AND FJDQY ='B区' AND HYBH IN (SELECT HYBH FROM SW_ZWB WHERE ZWMC = '经理');        IF (uCOUNTA = 0 OR uCOUNTB = 0) THEN          SELECT COUNT(*) INTO uExist FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC = '总监';          IF uExist > 0 THEN            DELETE FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC='总监';            UPDATE SW_ZWB SET ZWMC = '经理' WHERE UPPER(HYBH) = uFJDBH;          END IF;        END IF;        -- 修改 SW_Change ，加入相关变动数据        DELETE FROM SW_Change WHERE F_Source = uHYBH AND F_Father = uFJDBH AND F_RQ = uCLSJ AND F_BZ = uHYZT;        INSERT INTO SW_Change (F_XH,F_RQ,F_Source,F_Father,F_Point,F_AQ,F_BQ,F_AH,F_BH,F_JE,F_AREA,F_BZ,F_DESC)        VALUES (CHANGE_XH.NEXTVAL,uCLSJ,uHYBH,uFJDBH,uDS,uAPQ,uBPQ,uAPH,uBPH,0,uFJDQY,uHYZT,uHYZT||'减点处理');        -- 层数加1，继续循环处理下一个父节点        uCS := uCS + 1;      END LOOP;    END IF;    -- 设置该记录为已处理    UPDATE SW_HYCL SET CLID = 1 WHERE XH = uXH;  END LOOP;  COMMIT;  uMsg := '会员处理事务成功！';EXCEPTION  WHEN Others THEN    ROLLBACK;    uMsg := '会员处理事务失败！';END P_SubtractPoint;-- 手工添加错误信息-- RAISE_APPLICATION_ERROR(-20010, 'ERROR：违反唯一索引约束！');