Oracle游标.docx
《Oracle游标.docx》由会员分享，可在线阅读，更多相关《Oracle游标.docx（17页珍藏版）》请在冰豆网上搜索。
Oracle游标
本篇主要内容如下：
4.1 游标概念
4.1.1 处理显式游标
4.1.2 处理隐式游标
4.1.3 关于 NO_DATA_FOUND 和 %NOTFOUND的区别
4.1.4 使用游标更新和删除数据
4.2 游标变量
4.2.1 声明游标变量
4.2.2 游标变量操作
游标的使用
在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。
4.1 游标概念
在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。
游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。
它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。
在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。
对于不同的SQL语句，游标的使用情况不同：
SQL语句
游标
非查询语句
隐式的
结果是单行的查询语句
隐式的或显示的
结果是多行的查询语句
显示的
4.1.1 处理显式游标
1.?
显式游标处理
显式游标处理需四个 PL/SQL步骤:
●✍定义/声明游标：
就是定义一个游标名，以及与其相对应的SELECT 语句。
格式：
CURSOR cursor_name[（parameter[, parameter]…）]
[RETURN datatype]
IS
select_statement;
复制代码
游标参数只能为输入参数，其格式为：
parameter_name [IN] datatype [{:
= | DEFAULT} expression]
在指定数据类型时，不能使用长度约束。
如NUMBER（4）,CHAR（10） 等都是错误的。
[RETURN datatype]是可选的，表示游标返回数据的数据。
如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。
一般是记录数据类型或带“%ROWTYPE”的数据。
●✍打开游标：
就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。
如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。
格式：
OPEN cursor_name[（[parameter =>] value[, [parameter =>] value]…）];
在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。
PL/SQL 程序不能用OPEN 语句重复打开一个游标。
●✍提取游标数据：
就是检索结果集合中的数据行，放入指定的输出变量中。
格式：
FETCH cursor_name INTO {variable_list | record_variable };
执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。
当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。
所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。
●✍对该记录进行处理；
●✍继续处理，直到活动集合中没有记录；
●✍关闭游标：
当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。
关闭后的游标可以使用OPEN 语句重新打开。
格式：
CLOSE cursor_name;
注：
定义的游标不能有INTO 子句。
例1. 查询前10名员工的信息。
DECLARE
CURSOR c_cursor
IS SELECT first_name || last_name, Salary
FROM EMPLOYEES
WHERE rownum<11;
v_ename EMPLOYEES.first_name%TYPE;
v_sal EMPLOYEES.Salary%TYPE;
BEGIN
OPEN c_cursor;
FETCH c_cursor INTO v_ename, v_sal;
WHILE c_cursor%FOUND LOOP
DBMS_OUTPUT.PUT_LINE（v_ename||'---'||to_char（v_sal） ）;
FETCH c_cursor INTO v_ename, v_sal;
END LOOP;
CLOSE c_cursor;
END;
复制代码
例2. 游标参数的传递方法。
DECLARE
DeptRec DEPARTMENTS%ROWTYPE;
Dept_name DEPARTMENTS.DEPARTMENT_NAME%TYPE;
Dept_loc DEPARTMENTS.LOCATION_ID%TYPE;
CURSOR c1 IS
SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 30;
CURSOR c2（dept_no NUMBER DEFAULT 10） IS
SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= dept_no;
CURSOR c3（dept_no NUMBER DEFAULT 10） IS
SELECT * FROM DEPARTMENTS
WHERE DEPARTMENTS.DEPARTMENT_ID <=dept_no;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO dept_name, dept_loc;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE（dept_name||'---'||dept_loc）;
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO dept_name, dept_loc;
EXIT WHEN c2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE（dept_name||'---'||dept_loc）;
END LOOP;
CLOSE c2;
OPEN c3（dept_no =>20）;
LOOP
FETCH c3 INTO deptrec;
EXIT WHEN c3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE（deptrec.DEPARTMENT_ID||'---'||deptrec.DEPARTMENT_NAME||'---'||deptrec.LOCATION_ID）;
END LOOP;
CLOSE c3;
END;
复制代码
2.游标属性
Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；
Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反；
Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。
例3：
给工资低于1200 的员工增加工资50。
DECLARE
v_empno EMPLOYEES.EMPLOYEE_ID%TYPE;
v_sal EMPLOYEES.Salary%TYPE;
CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES;
BEGIN
OPEN c_cursor;
LOOP
FETCH c_cursor INTO v_empno, v_sal;
EXIT WHEN c_cursor%NOTFOUND;
IF v_sal<=1200 THEN
UPDATE EMPLOYEES SET Salary=Salary+50 WHERE EMPLOYEE_ID=v_empno;
DBMS_OUTPUT.PUT_LINE（'编码为'||v_empno||'工资已更新!
'）;
END IF;
DBMS_OUTPUT.PUT_LINE（'记录数:
'|| c_cursor %ROWCOUNT）;
END LOOP;
CLOSE c_cursor;
END;
复制代码
例4：
没有参数且没有返回值的游标。
DECLARE
v_f_name employees.first_name%TYPE;
v_j_id employees.job_id%TYPE;
CURSOR c1 --声明游标,没有参数没有返回值
IS
SELECT first_name, job_id FROM employees
WHERE department_id = 20;
BEGIN
OPEN c1; --打开游标
LOOP
FETCH c1 INTO v_f_name, v_j_id; --提取游标
IF c1%FOUND THEN
DBMS_OUTPUT.PUT_LINE（v_f_name||'的岗位是'||v_j_id）;
ELSE
DBMS_OUTPUT.PUT_LINE（'已经处理完结果集了'）;
EXIT;
END IF;
END LOOP;
CLOSE c1; --关闭游标
END;
复制代码
例5：
有参数且没有返回值的游标。
DECLARE
v_f_name employees.first_name%TYPE;
v_h_date employees.hire_date%TYPE;
CURSOR c2（dept_id NUMBER, j_id VARCHAR2） --声明游标,有参数没有返回值
IS
SELECT first_name, hire_date FROM employees
WHERE department_id = dept_id AND job_id =
升级会员 