【Oracle】PL/SQL 显式游标、隐式游标、动态游标

在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，Oracle会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。

在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。

对于不同的SQL语句，游标的使用情况不同：

view plain copy print?

1. DECLARE  
2.    CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --1、声明游标,有参数没有返回值  
3.    IS  
4.       SELECT first_name f_name, hire_date FROM employees  
5.       WHERE department_id = dept_id AND job_id = j_id;  
6.   
7.     --基于游标定义记录变量，比声明记录类型变量要方便，不容易出错  
8.     v_emp_record c4%ROWTYPE;  
9. BEGIN  
10.    OPEN c4(90, 'AD_VP');             --2、打开游标,传递参数值  
11.    LOOP  
12.       FETCH c4 INTO v_emp_record;    --3、提取游标fetch into  
13.       IF c4%FOUND THEN  
14.          DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'  
15.                             ||v_emp_record.hire_date);  
16.       ELSE  
17.          DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');  
18.          EXIT;  
19.       END IF;  
20.    END LOOP;  
21.    CLOSE c4;                         --4、关闭游标  
22. END;  

退出LOOP或者用：

EXIT WHEN c4%NOTFOUND;

游标属性：

 Cursor_name%FOUND     布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；

 Cursor_name%NOTFOUND   布尔型属性，与%FOUND相反；——注意区别于DO_DATA_FOUND(select into抛出异常)

 Cursor_name%ISOPEN     布尔型属性，当游标已打开时返回 TRUE；

 Cursor_name%ROWCOUNT   数字型属性，返回已从游标中读取的记录数。

view plain copy print?

1. DECLARE  
2.   CURSOR c_cursor(dept_no NUMBER DEFAULT 10)   
3.   IS  
4.     SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;  
5. BEGIN  
6.     --当dept_no参数值为30  
7.     FOR c1_rec IN c_cursor(30) LOOP          
8.          DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);  
9.     END LOOP;  
10.      
11.     --使用默认的dept_no参数值10  
12.     FOR c1_rec IN c_cursor LOOP         
13.          DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);  
14.     END LOOP;  
15. END;  

或者可以在游标FOR循环语句中使用子查询

[sql] view plain copy print?

1. BEGIN  
2.     FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP   
3.        DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);  
4.     END LOOP;  
5. END;  

view plain copy print?

1. DECLARE  
2.    v_rows NUMBER;  
3. BEGIN  
4.    --更新数据  
5.    UPDATE employees SET salary = 30000  
6.      WHERE department_id = 90 AND job_id = 'AD_VP';  
7.    --获取默认游标的属性值  
8.    v_rows := SQL%ROWCOUNT;  
9.    DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');  
10.      
11.     --删除指定雇员；如果部门中没有雇员，则删除部门  
12.     DELETE FROM employees WHERE department_id=v_deptno;  
13.     IF SQL%NOTFOUND THEN  
14.         DELETE FROM departments WHERE department_id=v_deptno;  
15.     END IF;  
16. END;  

view plain copy print?

1. DECLARE   
2.     V_deptno employees.department_id%TYPE :=&p_deptno;  
3.     CURSOR emp_cursor   
4.   IS   
5.   SELECT employees.employee_id, employees.salary   
6.     FROM employees WHERE employees.department_id=v_deptno  
7.   FOR UPDATE NOWAIT;                    --1、for update  
8. BEGIN  
9.     FOR emp_record IN emp_cursor LOOP  
10.       IF emp_record.salary < 1500 THEN  
11.         UPDATE employees SET salary=1500  
12.             WHERE CURRENT OF emp_cursor; --2、WHERE CURRENT OF cursor_name子句  
13.       END IF;  
14.     END LOOP;  
15. END;   

view plain copy print?

1. DECLARE  
2.    --定义一个游标数据类型  
3.    TYPE emp_cursor_type IS REF CURSOR;  
4.    --声明一个游标变量  
5.    c1 EMP_CURSOR_TYPE;  
6.    --声明两个记录变量  
7.    v_emp_record employees%ROWTYPE;  
8.    v_reg_record regions%ROWTYPE;  
9.   
10. BEGIN  
11.    OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;  
12.    LOOP  
13.       FETCH c1 INTO v_emp_record;  
14.       EXIT WHEN c1%NOTFOUND;  
15.       DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是'  
16.                             ||v_emp_record.hire_date);  
17.    END LOOP;  
18.    --将同一个游标变量对应到另一个SELECT语句  
19.    OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;  
20.    LOOP  
21.       FETCH c1 INTO v_reg_record;  
22.       EXIT WHEN c1%NOTFOUND;  
23.       DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'  
24.                             ||v_reg_record.region_name);  
25.    END LOOP;  
26.    CLOSE c1;  
27. END;