oralce存储过程应用

Question

有一下存储过程，谁帮我分析一下语法（加上注释） 学习中
CREATE OR REPLACE PROCEDURE COMPUTE_HXT_BONUS(yy IN VARCHAR2,dept IN varchar2,message out varchar2) IS
       Type rec_hxtyg IS RECORD(
            userid   varchar(100),
            departid varchar(100)       );       hxtyg rec_hxtyg;
       xxfs       jxkh_yw_hxt_grjxdf%ROWTYPE; --选项分数
       grqz       NUMBER;    --个人权重
       tmp_count  NUMBER;    
       nzj        NUMBER;    --年终奖
       departname varchar2(100);
       username   varchar2(100);
       tmp_gz     NUMBER;
       tmp_xxqz     NUMBER;
       tmp_qzzf     NUMBER;
       tmp_grjx     NUMBER;
       tmp_bmqzzh   NUMBER:=0;
       tmp_sql      varchar(500);
       hypool       NUMBER;
       pfid         varchar2(100);
       type cursor_type is ref cursor;
       cur_xxfs cursor_type;       Cursor cur_hxt IS SELECT u.userid,u.departid  FROM Tuser u,Tdepartment d
                      WHERE u.departid=d.departid AND d.departtypeid ='5' AND d.departid=dept;       BEGIN
       DELETE FROM jxkh_yw_hxt_nzj n WHERE n.departmentid=dept AND n.year=yy;
       SELECT d.departname INTO departname FROM tdepartment d WHERE d.departid=dept;
       
       SELECT count(*) INTO tmp_count FROM Jxkh_Cs_Hxt_Grjx_Xxqz x WHERE x.year=yy;
       IF tmp_count=0 THEN
          message:=yy||'年度没有选项权重参数!';
          ROLLBACK;
          RETURN;
       END IF;
       SELECT COUNT(*) INTO tmp_count FROM jxkh_cs_hxt_jjc j WHERE j.departmentid=dept AND j.year=yy;    
       IF tmp_count=0 THEN
          message:=departname||yy||'年度没有奖金池';
          ROLLBACK;
          RETURN;
       END IF;
           
           
           
           
       SELECT SUM(x.point) INTO tmp_qzzf FROM Jxkh_Cs_Hxt_Grjx_Xxqz x WHERE x.year=yy;
       SELECT max(j.hypool) INTO hypool FROM jxkh_cs_hxt_jjc j WHERE j.departmentid=dept AND j.year=yy;       OPEN cur_hxt;
       LOOP
           tmp_grjx:=0;
           FETCH cur_hxt INTO hxtyg;
           EXIT WHEN cur_hxt%NOTFOUND;
           SELECT COUNT(*) INTO tmp_count FROM jxkh_salarys s WHERE s.nd=yy AND s.userid=hxtyg.userid;
           IF tmp_count=0 THEN
              SELECT u.username INTO username FROM tuser u WHERE u.userid=hxtyg.userid;
              message:=departname||' '||username ||'没有相应的工资';
              ROLLBACK;
              RETURN;
           END IF;
                      
           SELECT avg(s.salarys) INTO tmp_gz FROM jxkh_salarys s WHERE s.nd=yy AND s.userid=hxtyg.userid;
           
           SELECT COUNT(*) INTO tmp_count FROM jxkh_yw_hxt_grjxkh g WHERE g.userid=hxtyg.userid AND g.year=yy;
           IF tmp_count=0 THEN
              SELECT u.username INTO username FROM tuser u WHERE u.userid=hxtyg.userid;
              message:=departname||' '||username||' '||yy||'没有审核！';
              ROLLBACK;
              RETURN;
           END IF;
           SELECT max(g.id)INTO pfid  FROM jxkh_yw_hxt_grjxkh g WHERE g.userid=hxtyg.userid AND g.year=yy;
           
           tmp_sql:='SELECT * FROM jxkh_yw_hxt_grjxdf f WHERE f.pfid='||''''||pfid||'''';
           OPEN cur_xxfs FOR tmp_sql;
           LOOP
               FETCH cur_xxfs INTO xxfs;
               EXIT WHEN cur_xxfs%NOTFOUND;
               SELECT max(x.point) INTO tmp_xxqz FROM jxkh_cs_hxt_grjx_xxqz x WHERE x.id=xxfs.xxid;
               tmp_grjx:=tmp_grjx+tmp_xxqz*xxfs.df/tmp_qzzf;
           END LOOP;
           tmp_bmqzzh:=tmp_bmqzzh+tmp_gz*tmp_grjx/100;
           CLOSE cur_xxfs;
       END LOOP;
       CLOSE cur_hxt;      
       OPEN cur_hxt;
       LOOP
           tmp_gz:=0;
           tmp_grjx:=0;
           FETCH cur_hxt INTO hxtyg;
           EXIT WHEN cur_hxt%NOTFOUND;
           SELECT avg(s.salarys) INTO tmp_gz  FROM  jxkh_salarys s WHERE s.nd=yy AND s.userid=hxtyg.userid;
           SELECT max(g.id) INTO pfid FROM jxkh_yw_hxt_grjxkh g WHERE g.userid=hxtyg.userid AND g.year=yy;
           tmp_sql:='SELECT * FROM jxkh_yw_hxt_grjxdf f WHERE f.pfid='||''''||pfid||'''';
           OPEN cur_xxfs FOR tmp_sql;
           LOOP
               FETCH cur_xxfs INTO xxfs;
               EXIT WHEN cur_xxfs%NOTFOUND;
               SELECT max(x.point) INTO tmp_xxqz FROM jxkh_cs_hxt_grjx_xxqz x WHERE x.id=xxfs.xxid;
               tmp_grjx:=tmp_grjx+tmp_xxqz*xxfs.df/tmp_qzzf;
           END LOOP;
           CLOSE cur_xxfs;
           tmp_grjx:=tmp_grjx/100;
           grqz:=tmp_gz*tmp_grjx/tmp_bmqzzh;
           nzj:=grqz*hypool;
           INSERT INTO jxkh_yw_hxt_nzj(id,departmentid,employeeid,year,yprix,sortorder)  values(TO_CHAR(SEQ_BONUS.Nextval),dept,hxtyg.userid,yy,nzj,'1');
       END LOOP;
       CLOSE cur_hxt;
       commit;
       message:='计算成功';
       EXCEPTION 
       WHEN OTHERS THEN
       ROLLBACK;
END;

Answer 1

/* Formatted on 2008/05/26 20:22 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE compute_hxt_bonus (
   yy        IN       VARCHAR2,//输入参数1
   dept      IN       VARCHAR2,//输入参数2
   MESSAGE   OUT      VARCHAR2 //输出参数1
)
IS
   以下为定义变量类型
   TYPE rec_hxtyg IS RECORD (
      userid     VARCHAR (100),
      departid   VARCHAR (100)
   );   hxtyg        rec_hxtyg;
   xxfs         jxkh_yw_hxt_grjxdf%ROWTYPE;                         --选项分数
   grqz         NUMBER;                                             --个人权重
   tmp_count    NUMBER;
   nzj          NUMBER;                                               --年终奖
   departname   VARCHAR2 (100);
   username     VARCHAR2 (100);
   tmp_gz       NUMBER;
   tmp_xxqz     NUMBER;
   tmp_qzzf     NUMBER;
   tmp_grjx     NUMBER;
   tmp_bmqzzh   NUMBER                       := 0;
   tmp_sql      VARCHAR (500);
   hypool       NUMBER;
   pfid         VARCHAR2 (100);   //以下为定义游标
   TYPE cursor_type IS REF CURSOR;   cur_xxfs     cursor_type;   CURSOR cur_hxt//游标的SELECT语句
   IS
      SELECT u.userid, u.departid
        FROM tuser u, tdepartment d
       WHERE u.departid = d.departid
         AND d.departtypeid = '5'
         AND d.departid = dept;
BEGIN
   //删除此表中相关内容
   DELETE FROM jxkh_yw_hxt_nzj n
         WHERE n.departmentid = dept AND n.YEAR = yy;   //看样子是选择部门名
   SELECT d.departname
     INTO departname
     FROM tdepartment d
    WHERE d.departid = dept;   //查看表中是否有权生参数
   SELECT COUNT (*)
     INTO tmp_count
     FROM jxkh_cs_hxt_grjx_xxqz x
    WHERE x.YEAR = yy;
   
   //没有的话，直接跳出
   IF tmp_count = 0
   THEN
      MESSAGE := yy || '年度没有选项权重参数!';
      ROLLBACK;
      RETURN;
   END IF;
   //查看博是有没有奖金池，没有跳出
   SELECT COUNT (*)
     INTO tmp_count
     FROM jxkh_cs_hxt_jjc j
    WHERE j.departmentid = dept AND j.YEAR = yy;   IF tmp_count = 0
   THEN
      MESSAGE := departname || yy || '年度没有奖金池';
      ROLLBACK;
      RETURN;
   END IF;
   //查找point和，和最大的hypool
   SELECT SUM (x.point)
     INTO tmp_qzzf
     FROM jxkh_cs_hxt_grjx_xxqz x
    WHERE x.YEAR = yy;   SELECT MAX (j.hypool)
     INTO hypool
     FROM jxkh_cs_hxt_jjc j
    WHERE j.departmentid = dept AND j.YEAR = yy;
   //打开游标
   OPEN cur_hxt;
   //游标循环赋值
   LOOP
      tmp_grjx := 0;      FETCH cur_hxt
       INTO hxtyg;
      //游标到头跳出循环
      EXIT WHEN cur_hxt%NOTFOUND;      //查找用用户是否有工资纪录
      SELECT COUNT (*)
        INTO tmp_count
        FROM jxkh_salarys s
       WHERE s.nd = yy AND s.userid = hxtyg.userid;      IF tmp_count = 0
      THEN
         SELECT u.username
           INTO username
           FROM tuser u
          WHERE u.userid = hxtyg.userid;         MESSAGE := departname || ' ' || username || '没有相应的工资';
         ROLLBACK;
         RETURN;
      END IF;
      //取用户平均工资
      SELECT AVG (s.salarys)
        INTO tmp_gz
        FROM jxkh_salarys s
       WHERE s.nd = yy AND s.userid = hxtyg.userid;
      //有没有审核纪录
      SELECT COUNT (*)
        INTO tmp_count
        FROM jxkh_yw_hxt_grjxkh g
       WHERE g.userid = hxtyg.userid AND g.YEAR = yy;      IF tmp_count = 0
      THEN
         SELECT u.username
           INTO username
           FROM tuser u
          WHERE u.userid = hxtyg.userid;         MESSAGE := departname || ' ' || username || ' ' || yy || '没有审核！';
         ROLLBACK;
         RETURN;
      END IF;
      //找最大的g.id
      SELECT MAX (g.ID)
        INTO pfid
        FROM jxkh_yw_hxt_grjxkh g
       WHERE g.userid = hxtyg.userid AND g.YEAR = yy;      tmp_sql :=
            'SELECT * FROM jxkh_yw_hxt_grjxdf f WHERE f.pfid='
         || ''''
         || pfid
         || '''';
      打开另个游标
      OPEN cur_xxfs FOR tmp_sql;      LOOP
         FETCH cur_xxfs
          INTO xxfs;         EXIT WHEN cur_xxfs%NOTFOUND;
         //查找max point
         SELECT MAX (x.point)
           INTO tmp_xxqz
           FROM jxkh_cs_hxt_grjx_xxqz x
          WHERE x.ID = xxfs.xxid;         tmp_grjx := tmp_grjx + tmp_xxqz * xxfs.df / tmp_qzzf;
      END LOOP;      tmp_bmqzzh := tmp_bmqzzh + tmp_gz * tmp_grjx / 100;
      //关闭游标
      CLOSE cur_xxfs;
   END LOOP;   CLOSE cur_hxt;   OPEN cur_hxt;   LOOP
      tmp_gz := 0;
      tmp_grjx := 0;      FETCH cur_hxt
       INTO hxtyg;      EXIT WHEN cur_hxt%NOTFOUND;      SELECT AVG (s.salarys)
        INTO tmp_gz
        FROM jxkh_salarys s
       WHERE s.nd = yy AND s.userid = hxtyg.userid;      SELECT MAX (g.ID)
        INTO pfid
        FROM jxkh_yw_hxt_grjxkh g
       WHERE g.userid = hxtyg.userid AND g.YEAR = yy;      tmp_sql :=
            'SELECT * FROM jxkh_yw_hxt_grjxdf f WHERE f.pfid='
         || ''''
         || pfid
         || '''';      OPEN cur_xxfs FOR tmp_sql;      LOOP
         FETCH cur_xxfs
          INTO xxfs;         EXIT WHEN cur_xxfs%NOTFOUND;         SELECT MAX (x.point)
           INTO tmp_xxqz
           FROM jxkh_cs_hxt_grjx_xxqz x
          WHERE x.ID = xxfs.xxid;         tmp_grjx := tmp_grjx + tmp_xxqz * xxfs.df / tmp_qzzf;
      END LOOP;      CLOSE cur_xxfs;      tmp_grjx := tmp_grjx / 100;
      grqz := tmp_gz * tmp_grjx / tmp_bmqzzh;
      nzj := grqz * hypool;      INSERT INTO jxkh_yw_hxt_nzj
                  (ID, departmentid, employeeid, YEAR, yprix,
                   sortorder
                  )
           VALUES (TO_CHAR (seq_bonus.NEXTVAL), dept, hxtyg.userid, yy, nzj,
                   '1'
                  );
   END LOOP;   CLOSE cur_hxt;   COMMIT;
   MESSAGE := '计算成功';
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
END;很多是涉及到业务逻辑，语法解释后面都差不多的，无非就是用游标