oracle处理的类型 oracle行排序
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;
引用
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;
