PL/SQL中显式游标的使用

1.DECLARE
     v_locid NUMBER := 1700;
     CURSOR c_emp_cursor IS
        SELECT employee_id,last_name FROM employees
        WHERE department_id=v_locid;
        v_empno emplouees.employee_id%TYPE;
        v_lname employees.last_name%TYPE;
             //  v_emp_record c_emp_cursor%ROWTYPE;  使用记录
     BEGIN
        OPEN c_emp_cursor;
        LOOP
            FETCH c_emp_cursor INTO v_empno,v_lname;
             //   FETCH c_emp_cursor INTO v_emp_record;
            EXIT WHEN c_emp_cursor%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(vemp_record.employee_id || ' ' || v_emp_record.last_name);
        END LOOP;
        CLOSE c_emp_cursor;
   END;
  
2.游标FOR循环
   FOR record_name IN cursor_name
    LOOP
       statement1;
       statement2;
       ...
    END LOOP;
    在游标FOR循环中，游标可以被隐式的打开，提取，退出和关闭，且记录也可以被隐式的声明。
    
   例：DECLARE
           CURSOR c_emp_cursor IS
              SELECT employee_id,last_name,ROM employees
              WHERE department_id=30;
       BEGIN
           FOR emp_record IN c_emp_cursor
             LOOP
               DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || ' ' || emp_record.last_name);
             END LOOP;
       END;
       /
    在FOR 循环中游标的定义也可以省略，上述代码可以简化为：
       BEGIN
           FOR emp_record IN (SELECT employee_id,last_name FROM employees WHERE department_id=30)
             LOOP
               DBMS_OUTPUT.PUT_LINE(emp_record.employee_id ||' '||emp_record.last_name);
             END LOOP;
        END;
        /
 
3. 游标的属性
   %ISOPEN：判断游标是不是被打开；
        IF NOT c_emp_cursor%ISOPEN THEN
           OPEN c_emp_cursor;
        END IF
        LOOP
          FETCH c_emp_cursor
   %NOTFOUND：如果最近一次抓取记录的操作没有返回值，为TRUE；
   %FOUND：如果最近一次抓取记录的操作有返回值，为TRUE；
   %ROWCOUNT：每FETCH一次，次数加一。
        EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR c_emp_cursor%NOTFOUND;
       
4.游标的高级用法--带参数的游标：
      DECLARE
          CURSOR c_emp_cursor(deptno NUMBER) IS
             SELECT employee_id,last_name
             FROM employees
             WHERE department_id = deptno;
             ...
      BEGIN
          OPEN c_emp_cursor(10);
          ...
          CLOSE c_emp_cursor;
          OPEN c_emp_cursor(20);
          ...
         
5.FOR UPDATE子句用来锁定查询出来的记录用于以后修改。
      SELECT ...
      FROM
      FOR UPDATE [OF column_reference][NOWAIT | WAIT n];
     
6.WHERE CURRENT OF子句用来修改游标的内容=修改表的内容。
      UPDATE employees
         SET salary = ...
         WHERE CURRENT OF c_emp_cursor;
        
7.批量导入 BULK COLLECT
  例：不使用游标
       DECLARE
           TYPE emp_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
           l_emp emp_type;
           l_row PLS_INTEGER;
       BEGIN
           SELECT * BULK COLLECT INTO l_emp FROM employees;
          
           DBMS_OUTPUT.PUT_LINE('The count is:' || l_emp.COUNT);
          
           l_row := l_emp.FIRST;
           WHILE(l_row IS NOT NULL)
           LOOP
             DBMS_OUTPUT.PUT_LIN(l_row||':'||l_emp(l_row).employee_id||'--&gt'||l_emp(l_row).first_name);
             l_row := l_emp.NEXT(l_row);
           END LOOP;
        END;
        /
        使用游标的情况：
      DECLARE
           CURSOR e IS SELECT * FROM employees;
           TYPE emp_type IS TABLE OF e%ROWTYPE INDEX BY PLS_INTEGER;
           l_emp emp_type;
           l_row PLS_INTEGER;
       BEGIN
           OPEN e;
           FETCH e BULK COLLECT INTO l_emp;
           CLOSE E;
          
           DBMS_OUTPUT.PUT_LINE('The count is:' || l_emp.COUNT);
          
           l_row := l_emp.FIRST;
           WHILE(l_row IS NOT NULL)
           LOOP
             DBMS_OUTPUT.PUT_LIN(l_row||':'||l_emp(l_row).employee_id||'--&gt'||l_emp(l_row).first_name);
             l_row := l_emp.NEXT(l_row);
           END LOOP;
        END;
        /

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22012035/viewspace-694166/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22012035/viewspace-694166/