Oracle游标

--显式游标---------------------------------------------------------//   
--001  
DECLARE   
    CURSOR c1 IS  --声明游标   
        SELECT name,address FROM student ORDER BY name;   
    v_name student.name%TYPE;   
    v_addr student.address%TYPE;   
BEGIN   
    OPEN c1;  --打开游标   
    FETCH c1 INTO v_name,v_addr;  --第一次定位读取数据，并保存在变量   
    --循环读取数据   
    WHILE c1%FOUND LOOP   
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(c1%ROWCOUNT) || '  ' || v_name || ' , ' || v_addr);   
        FETCH c1 INTO v_name,v_addr;   
    END LOOP;   
    DBMS_OUTPUT.PUT_LINE('Total rows is : ' || c1%ROWCOUNT);   
    CLOSE c1;  --关闭游标   
END;   
/   
--002  
DECLARE   
    CURSOR cur_emp IS   
        SELECT sal FROM emp WHERE deptno = 20 FOR UPDATE OF sal;   
    v_sal emp.sal%TYPE;   
BEGIN   
    OPEN cur_emp;   
    FETCH cur_emp INTO v_sal;   
    LOOP   
        EXIT WHEN cur_emp%NOTFOUND;   
        IF v_sal < 2000 THEN   
            UPDATE emp SET sal = 2000 WHERE current OF cur_emp;  --更新当前数据   
        END IF;   
        FETCH cur_emp INTO v_sal;   
    END LOOP;   
    DBMS_OUTPUT.PUT_LINE('命令执行完毕');   
       
    IF cur_emp%ISOPEN THEN   
        CLOSE cur_emp;   
        IF cur_emp%ISOPEN THEN   
            DBMS_OUTPUT.PUT_LINE('Cursor state : Open');   
            CLOSE cur_emp;   
        ELSE   
            DBMS_OUTPUT.PUT_LINE('Cursor state : Close');   
        END IF;   
    END IF;   
END;   
/   
  
--隐式游标---------------------------------------------------------//   
--不能显式的使用OPEN、CLOSE和FETCH语句，他会自动完成   
DECLARE   
    no   emp.empno%TYPE;   
    name emp.ename%TYPE;       
BEGIN   
    SELECT empno,ename INTO no,name FROM emp WHERE empno = '7788';   
    IF SQL%ISOPEN THEN   
        DBMS_OUTPUT.PUT_LINE('Cursor state : Open');   
    ELSE   
        DBMS_OUTPUT.PUT_LINE('Cursor state : Close');   
    END IF;   
    DBMS_OUTPUT.PUT_LINE(no || '    ' || name);   
    DBMS_OUTPUT.PUT_LINE('Return rows : ' || SQL%ROWCOUNT);   
EXCEPTION   
    WHEN CURSOR_ALREADY_OPEN THEN   
        DBMS_OUTPUT.PUT_LINE('Cursor already open');   
    WHEN NO_DATA_FOUND THEN   
        DBMS_OUTPUT.PUT_LINE('No data found');   
    WHEN TOO_MANY_ROWS THEN   
        DBMS_OUTPUT.PUT_LINE('Return many rows');   
END;   
/   
  
--游标变量（一个游标变量可以在一个PL\SQL块中使用多次）---------------------------//   
DECLARE   
    TYPE refcur IS REF CURSOR;  --[RETURN TYPE]   
    cur_emp refcur;  --引用游标   
    dept emp.deptno%TYPE;   
    name emp.ename%TYPE;   
BEGIN   
    OPEN cur_emp FOR SELECT deptno FROM emp WHERE empno = '7788';   
    FETCH cur_emp INTO dept;   
    DBMS_OUTPUT.PUT_LINE('Dept : ' || dept);   
    CLOSE cur_emp;   
  
    OPEN cur_emp FOR SELECT ename FROM emp WHERE empno = '7788';   
    FETCH cur_emp INTO name;   
    DBMS_OUTPUT.PUT_LINE('Name : ' || name);   
    CLOSE cur_emp;   
END;   
/   
  
--游标在三种循环中的使用-------------------------------------------//   
--001--Loop   
DECLARE   
    CURSOR cur_emp IS   
        SELECT ename FROM emp;   
    v_name emp.ename%TYPE;   
BEGIN   
    OPEN cur_emp;   
    FETCH cur_emp INTO v_name;   
    LOOP   
        EXIT WHEN cur_emp%NOTFOUND;   
        DBMS_OUTPUT.PUT_LINE('Name is : ' || v_name);   
        FETCH cur_emp INTO v_name;   
    END LOOP;   
    DBMS_OUTPUT.PUT_LINE('Return rows : ' || cur_emp%ROWCOUNT);   
    CLOSE cur_emp;   
END;   
/   
--002--While   
DECLARE   
    CURSOR cur_emp IS   
        SELECT ename FROM emp;   
    v_name emp.ename%TYPE;   
BEGIN   
    OPEN cur_emp;   
    FETCH cur_emp INTO v_name;   
    WHILE cur_emp%FOUND LOOP   
        DBMS_OUTPUT.PUT_LINE('Name is : ' || v_name);   
        FETCH cur_emp INTO v_name;   
    END LOOP;   
    DBMS_OUTPUT.PUT_LINE('Return rows : ' || cur_emp%ROWCOUNT);   
    CLOSE cur_emp;   
END;   
/   
--003--For   
--注：在使用 FOR 循环时，不能显式的使用 open、colse 和 FETCH 语句，他会自动完成   
DECLARE   
    rows number := 0;   
    CURSOR cur_emp IS   
        SELECT ename FROM emp;   
BEGIN   
    FOR v_emp in cur_emp LOOP   
        DBMS_OUTPUT.PUT_LINE('name is : ' || v_emp.ename);   
        rows := rows + 1;   
    END LOOP;   
    DBMS_OUTPUT.PUT_LINE('Return rows : ' || rows);   
END;   
/