说是
PLS-00103: 出现符号 "LOOP"在需要下列之一时:
if
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
不清楚是啥状况
AVG_SPEED 是NUMBER(20, 8)DECLARE
V_CONGESTION_LEVEL NUMBER(1);
CURSOR c1 IS SELECT AVG_SPEED FROM TMP_WEEK_SPEED_1 FOR UPDATE OF CONGESTION_LEVEL;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO V_CONGESTION_LEVEL;
IF V_CONGESTION_LEVEL<20 THEN
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=1 WHERE CURRENT OF c1;
ELSE IF V_CONGESTION_LEVEL<45 THEN
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=2 WHERE CURRENT OF c1;
ELSE IF V_CONGESTION_LEVEL<70 THEN
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=3 WHERE CURRENT OF c1;
ELSE
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=4 WHERE CURRENT OF c1;
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
END;
PLS-00103: 出现符号 "LOOP"在需要下列之一时:
if
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
不清楚是啥状况
AVG_SPEED 是NUMBER(20, 8)DECLARE
V_CONGESTION_LEVEL NUMBER(1);
CURSOR c1 IS SELECT AVG_SPEED FROM TMP_WEEK_SPEED_1 FOR UPDATE OF CONGESTION_LEVEL;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO V_CONGESTION_LEVEL;
IF V_CONGESTION_LEVEL<20 THEN
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=1 WHERE CURRENT OF c1;
ELSE IF V_CONGESTION_LEVEL<45 THEN
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=2 WHERE CURRENT OF c1;
ELSE IF V_CONGESTION_LEVEL<70 THEN
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=3 WHERE CURRENT OF c1;
ELSE
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=4 WHERE CURRENT OF c1;
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
END;
2 V_CONGESTION_LEVEL NUMBER;
3 CURSOR c1 IS SELECT empno FROM emp FOR UPDATE ;
4 BEGIN
5 OPEN c1;
6 LOOP
7 FETCH c1 INTO V_CONGESTION_LEVEL;
8 IF V_CONGESTION_LEVEL=111 THEN
9 UPDATE emp SET empno=1 WHERE CURRENT OF c1;
10 ELSIF V_CONGESTION_LEVEL=222 THEN
11 UPDATE emp SET empno=2 WHERE CURRENT OF c1;
12 ELSIF V_CONGESTION_LEVEL=333 THEN
13 UPDATE emp SET empno=3 WHERE CURRENT OF c1;
14 END IF;
15 EXIT WHEN c1%NOTFOUND;
16 END LOOP;
17 END;
18 /
PL/SQL procedure successfully completed
SQL>
又出现这样的错:
错误报告:
ORA-01410: 无效的 ROWID
ORA-06512: 在 line 13
01410. 00000 - "invalid ROWID"
*Cause:
*Action:
DECLARE
V_AVG_SPEED NUMBER(20,8);
CURSOR c1 IS SELECT AVG_SPEED FROM TMP_WEEK_SPEED_1 FOR UPDATE OF CONGESTION_LEVEL;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO V_AVG_SPEED;
IF V_AVG_SPEED<20 THEN
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=1 WHERE CURRENT OF c1;
ELSIF V_AVG_SPEED<45 THEN
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=2 WHERE CURRENT OF c1;
ELSIF V_AVG_SPEED<70 THEN
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=3 WHERE CURRENT OF c1;
ELSE
UPDATE TMP_WEEK_SPEED_1 SET CONGESTION_LEVEL=4 WHERE CURRENT OF c1;
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
END;
--把EXIT WHEN c1%NOTFOUND;放到fetch后面
1 DECLARE
2 V_AVG_SPEED NUMBER(20,8);
3 CURSOR c1 IS SELECT sal FROM emp FOR UPDATE OF comm;
4 BEGIN
5 OPEN c1;
6 LOOP
7 FETCH c1 INTO V_AVG_SPEED;
8 EXIT WHEN c1%NOTFOUND;
9 IF V_AVG_SPEED<2000 THEN
10 UPDATE emp SET comm=1000 WHERE CURRENT OF c1;
11 ELSIF V_AVG_SPEED<3000 THEN
12 UPDATE emp SET comm=2000 WHERE CURRENT OF c1;
13 ELSIF V_AVG_SPEED<5000 THEN
14 UPDATE emp SET comm=3000 WHERE CURRENT OF c1;
15 ELSE
16 UPDATE emp SET comm=4000 WHERE CURRENT OF c1;
17 END IF;
18 END LOOP;
19* END;
20 /PL/SQL procedure successfully completed.
SQL> DECLARE
2 v_congestion_level NUMBER;
3 CURSOR c1 IS
4 SELECT empno FROM emp FOR UPDATE OF empno;
5 BEGIN
6 OPEN c1;
7 LOOP
8 FETCH c1
9 INTO v_congestion_level;
10 EXIT WHEN c1%NOTFOUND;
11 IF v_congestion_level = 111 THEN
12 UPDATE emp SET empno = 1 WHERE CURRENT OF c1;
13 ELSIF v_congestion_level = 222 THEN
14 UPDATE emp SET empno = 2 WHERE CURRENT OF c1;
15 ELSIF v_congestion_level = 333 THEN
16 UPDATE emp SET empno = 3 WHERE CURRENT OF c1;
17 ELSE
18 NULL;
19 END IF;
20 END LOOP;
21 END;
22 /
PL/SQL procedure successfully completed
SQL>
试试
再不行就
CREATE TABLE XXX AS SELECT * FROM XXXX
SQL> DECLARE
2 v_congestion_level NUMBER;
3 CURSOR c1 IS
4 SELECT empno FROM emp FOR UPDATE OF empno;
5 BEGIN
6 OPEN c1;
7 LOOP
8 FETCH c1
9 INTO v_congestion_level;
10
11 IF v_congestion_level = 111 THEN
12 UPDATE emp SET empno = 1 WHERE CURRENT OF c1;
13 ELSIF v_congestion_level = 222 THEN
14 UPDATE emp SET empno = 2 WHERE CURRENT OF c1;
15 ELSIF v_congestion_level = 333 THEN
16 UPDATE emp SET empno = 3 WHERE CURRENT OF c1;
17 ELSE
18 UPDATE emp SET empno = empno+1000 WHERE CURRENT OF c1;
19 END IF;
20 EXIT WHEN c1%NOTFOUND;
21 END LOOP;
22 END;
23 /
DECLARE
v_congestion_level NUMBER;
CURSOR c1 IS
SELECT empno FROM emp FOR UPDATE OF empno;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO v_congestion_level; IF v_congestion_level = 111 THEN
UPDATE emp SET empno = 1 WHERE CURRENT OF c1;
ELSIF v_congestion_level = 222 THEN
UPDATE emp SET empno = 2 WHERE CURRENT OF c1;
ELSIF v_congestion_level = 333 THEN
UPDATE emp SET empno = 3 WHERE CURRENT OF c1;
ELSE
UPDATE emp SET empno = empno+1000 WHERE CURRENT OF c1;
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
END;
ORA-01410: 无效的 ROWID
ORA-06512: 在 line 19
SQL>
SQL> DECLARE
2 v_congestion_level NUMBER;
3 CURSOR c1 IS
4 SELECT empno FROM emp FOR UPDATE OF empno;
5 BEGIN
6 OPEN c1;
7 LOOP
8 FETCH c1
9 INTO v_congestion_level;
10 EXIT WHEN c1%NOTFOUND;
11 IF v_congestion_level = 111 THEN
12 UPDATE emp SET empno = 1 WHERE CURRENT OF c1;
13 ELSIF v_congestion_level = 222 THEN
14 UPDATE emp SET empno = 2 WHERE CURRENT OF c1;
15 ELSIF v_congestion_level = 333 THEN
16 UPDATE emp SET empno = 3 WHERE CURRENT OF c1;
17 ELSE
18 UPDATE emp SET empno = empno + 1000 WHERE CURRENT OF c1;
19 END IF;
20 END LOOP;
21 END;
22 /
PL/SQL procedure successfully completed
SQL>
语法并没有问题 但可能逻辑会有问题
DECLARE
V_CONGESTION_LEVEL NUMBER(1);
CURSOR c1 IS
SELECT level FROM dual connect by level < 5;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO V_CONGESTION_LEVEL;
dbms_output.put_line(V_CONGESTION_LEVEL);
EXIT WHEN c1%NOTFOUND;
END LOOP;
END;
输出是
1
2
3
4
4