set serveroutput on;
DECLARE
CURSOR Point IS
SELECT MIN_SALARY,MAX_SALARY FROM JOBS FOR UPDATE NOWAIT;
Min_Monmany number(6);
Max_Monmany number(6);
num NUMBER(3) :=0;
Begin
---For,CASE 用法
SELECT COUNT(JOB_ID) INTO num FROM JOBS;
DBMS_OUTPUT.PUT_LINE(num);
IF num != 0 THEN
OPEN Point;
FOR i IN 1..num LOOP
FETCH Point INTO Min_Monmany,Max_Monmany;
CASE
WHEN Min_Monmany < 10000 THEN UPDATE JOBS SET MIN_SALARY = MIN_SALARY - 1000 WHERE CURRENT OF Point;
WHEN Min_Monmany > 10000 THEN UPDATE JOBS SET MIN_SALARY = MIN_SALARY - 1000 WHERE CURRENT OF Point;
END CASE;
END LOOP;
COMMIT;
CLOSE Point;
END IF;
end;
提示错误如下:
20
DECLARE
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 15怎么改啊?
DECLARE
CURSOR Point IS
SELECT MIN_SALARY,MAX_SALARY FROM JOBS FOR UPDATE NOWAIT;
Min_Monmany number(6);
Max_Monmany number(6);
num NUMBER(3) :=0;
Begin
---For,CASE 用法
SELECT COUNT(JOB_ID) INTO num FROM JOBS;
DBMS_OUTPUT.PUT_LINE(num);
IF num != 0 THEN
OPEN Point;
FOR i IN 1..num LOOP
FETCH Point INTO Min_Monmany,Max_Monmany;
CASE
WHEN Min_Monmany < 10000 THEN UPDATE JOBS SET MIN_SALARY = MIN_SALARY - 1000 WHERE CURRENT OF Point;
WHEN Min_Monmany > 10000 THEN UPDATE JOBS SET MIN_SALARY = MIN_SALARY - 1000 WHERE CURRENT OF Point;
END CASE;
END LOOP;
COMMIT;
CLOSE Point;
END IF;
end;
提示错误如下:
20
DECLARE
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 15怎么改啊?
UPDATE JOBS SET(CASE
WHEN Min_Monmany < 10000 THEN MIN_SALARY
WHERE CURRENT OF Point;
WHEN Min_Monmany > 10000 THEN MIN_SALARY
WHERE CURRENT OF Point;
END CASE;) = MIN_SALARY - 1000;
两个结果好像一样?
CURSOR POINT IS
SELECT * FROM JOBS FOR UPDATE NOWAIT;
BEGIN
FOR SUBPOINT IN POINT LOOP
CASE
WHEN SUBPOINT.MIN_SALARY > 8000 THEN
UPDATE JOBS SET MIN_SALARY = MIN_SALARY - 2000;
WHEN SUBPOINT.MIN_SALARY < 8000 THEN
UPDATE JOBS SET MIN_SALARY = MIN_SALARY + 2000;
END CASE;
END LOOP;
COMMIT;
END;
CURSOR POINT IS
SELECT * FROM JOBS FOR UPDATE NOWAIT;
BEGIN
FOR SUBPOINT IN POINT LOOP
CASE
WHEN SUBPOINT.MIN_SALARY > 8000 THEN
UPDATE JOBS SET MIN_SALARY = MIN_SALARY - 2000;
WHEN SUBPOINT.MIN_SALARY < 8000 THEN
UPDATE JOBS SET MIN_SALARY = MIN_SALARY + 2000;
END CASE;
END LOOP;
COMMIT;
END;
这个能执行,但结果是错的.