oracle处理的类型 oracle行排序

最新推荐文章于 2024-11-25 16:32:15 发布

原创最新推荐文章于 2024-11-25 16:32:15 发布 · 100 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

本文提供了一个使用 Oracle PL/SQL 进行数据处理的示例，展示了如何通过游标从数据库中检索数据，并对其进行排序和更新操作。具体包括了打开游标、循环读取记录、关闭游标等步骤。

 -Dpafa.log.home="D:\log1" -Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,server=y,address=3999,suspend=n -Dweblogic.ProductionModeEnabled=%PRODUCTION_MODE%


DECLARE
  CURSOR CUR_BALL IS
    SELECT * FROM BALL_ELEVEN;
  BALL_TBL BALL_ELEVEN%ROWTYPE;
  CURSOR CUR_BALL_TEMP IS
    SELECT BALL FROM BALL_ELEVEN_TEMP ORDER BY BALL;
  V_BALL   VARCHAR2(2);
  V_FIRST  VARCHAR2(2);
  V_SECOND VARCHAR2(2);
  V_THIRD  VARCHAR2(2);
  V_FOURTH VARCHAR2(2);
  V_FIFTH  VARCHAR2(2);
  V_COUNT  NUMBER(1);
BEGIN
  OPEN CUR_BALL;
  LOOP
    FETCH CUR_BALL
      INTO BALL_TBL;
    EXIT WHEN CUR_BALL%NOTFOUND;
    V_COUNT := 0;
    INSERT INTO BALL_ELEVEN_TEMP (BALL) VALUES (BALL_TBL.BALL_FIRST);
    INSERT INTO BALL_ELEVEN_TEMP (BALL) VALUES (BALL_TBL.BALL_SECOND);
    INSERT INTO BALL_ELEVEN_TEMP (BALL) VALUES (BALL_TBL.BALL_THIRD);
    INSERT INTO BALL_ELEVEN_TEMP (BALL) VALUES (BALL_TBL.BALL_FOURTH);
    INSERT INTO BALL_ELEVEN_TEMP (BALL) VALUES (BALL_TBL.BALL_FIFTH);
    OPEN CUR_BALL_TEMP;
    LOOP
      FETCH CUR_BALL_TEMP
        INTO V_BALL;
      EXIT WHEN CUR_BALL_TEMP%NOTFOUND;
      V_COUNT := V_COUNT + 1;
      IF V_COUNT = 1 THEN
        V_FIRST := V_BALL;
      ELSIF V_COUNT = 2 THEN
        V_SECOND := V_BALL;
      ELSIF V_COUNT = 3 THEN
        V_THIRD := V_BALL;
      ELSIF V_COUNT = 4 THEN
        V_FOURTH := V_BALL;
      ELSIF V_COUNT = 5 THEN
        V_FIFTH := V_BALL;
      END IF;
    END LOOP;
    CLOSE CUR_BALL_TEMP;
    DELETE FROM BALL_ELEVEN_TEMP;
    INSERT INTO BALL_ELEVEN_ORDER
      (BALL_NO,
       BALL_FIRST,
       BALL_SECOND,
       BALL_THIRD,
       BALL_FOURTH,
       BALL_FIFTH)
    VALUES
      (BALL_TBL.BALL_NO, V_FIRST, V_SECOND, V_THIRD, V_FOURTH, V_FIFTH);
  END LOOP;
  CLOSE CUR_BALL;
  COMMIT;
END;

[quote]
DECLARE
CURSOR cur_ball IS
SELECT * FROM ball WHERE FIRST = 8;
ball_tbl ball%ROWTYPE;
CURSOR cur_ball_tem IS
SELECT * FROM ball_temp ORDER BY ball;
ball_tem_tbl ball_temp%ROWTYPE;
v_count NUMBER(1);
v_first VARCHAR2(2);
v_second VARCHAR2(2);
v_third VARCHAR2(2);
v_fourth VARCHAR2(2);
v_fifth VARCHAR2(2);
BEGIN
OPEN cur_ball;
LOOP
FETCH cur_ball
INTO ball_tbl;
EXIT WHEN cur_ball%NOTFOUND;
INSERT INTO ball_temp VALUES (ball_tbl.FIRST);
INSERT INTO ball_temp VALUES (ball_tbl.SECOND);
INSERT INTO ball_temp VALUES (ball_tbl.third);
INSERT INTO ball_temp VALUES (ball_tbl.fourth);
INSERT INTO ball_temp VALUES (ball_tbl.fifth);
OPEN cur_ball_tem;
LOOP
FETCH cur_ball_tem
INTO ball_tem_tbl;
v_count := v_count + 1;
IF v_count = 1 THEN
v_first := ball_tem_tbl.ball;
ELSIF v_count = 2 THEN
v_second := ball_tem_tbl.ball;
ELSIF v_count = 3 THEN
v_third := ball_tem_tbl.ball;
ELSIF v_count = 4 THEN
v_fourht := ball_tem_tbl.ball;
ELSIF v_count = 5 THEN
v_fifth := ball_tem_tbl.ball;
END IF;
EXIT;
END LOOP;
CLOSE cur_ball_tem;
DELETE FROM ball_temp;
INSERT INTO ball_tem VALUES (v_first, v_second, v_third);
END LOOP;
CLOSE cur_ball;
COMMIT;
END;

[/quote]