PL/SQL 游标的使用



游标的使用

①游标概念

为了处理SQL 语句,ORACLE 必须分配一片叫上下文( context area )的区域来处理所必需的信息,

当中包含要处理的行的数目。一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)。

游标是一个指向上下文的句柄( handle)或指针。

通过游标,PL/SQL能够控制上下文区和处理语句时上下文区会发生些什么事情

②显式游标处理

1.显式游标处理需四个PL/SQL步骤:

定义游标:就是定义一个游标名,以及与其相相应的SELECT 语句。

格式:

CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;

游标參数仅仅能为输入參数,其格式为:

parameter_name [IN] datatype [{:= | DEFAULT} expression]

在指定数据类型时,不能使用长度约束。如NUMBER(4)、CHAR(10) 等都是错误的。

打开游标:就是运行游标所相应的SELECT 语句。将其查询结果放入工作区。而且指针指向工作区的首部,标识游标结果集合。

假设游标查询语句中带有FOR UPDATE选项。OPEN 语句还将锁定数据库表中游标结果集合相应的数据行。

格式:

OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];

在向游标传递參数时,能够使用与函数參数同样的传值方法。即位置表示法和名称表示法。PL/SQL 程序不能用OPEN 语句反复打开一个游标。

提取游标数据:就是检索结果集合中的数据行。放入指定的输出变量中。

格式:

FETCH cursor_name INTO {variable_list | record_variable };

对该记录进行处理。

继续处理。直到活动集合中没有记录。

关闭游标:当提取和处理完游标结果集合数据后,应及时关闭游标,以释放该游标所占用的系统资源。并使该游标的工作区变成无效,

不能再使用FETCH 语句取当中数据。关闭后的游标能够使用OPEN 语句又一次打开。

格式:

CLOSE cursor_name;

注:定义的游标不能有INTO 子句。

2.游标属性

%FOUND       布尔型属性。当近期一次读记录时成功返回,则值为TRUE。

%NOTFOUND   布尔型属性,与%FOUND相反;

%ISOPEN       布尔型属性。当游标已打开时返回TRUE;

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

3.游标的FOR循环

PL/SQL语言提供了游标FOR循环语句。自己主动运行游标的OPEN、FETCH、CLOSE语句和循环语句的功能;当进入循环时,游标FOR循环语句自己主动打开游标。并提取第一行游标数据。当程序处理完当前所提取的数据而进入下一次循环时。游标FOR循环语句自己主动提取下一行数据供程序处理,当提取完结果集合中的全部数据行后结束循环,并自己主动关闭游标。

格式:

FOR index_variable  IN cursor_name[value[, value]…]  LOOP

--游标数据处理代码

END LOOP;

当中:

index_variable为游标FOR 循环语句隐含声明的索引变量,该变量为记录变量。其结构与游标查询语句返回的结构集合的结构同样。在程序中能够通过引用该索引记录变量元素来读取所提取的游标数据,

index_variable中各元素的名称与游标查询语句选择列表中所制定的列名同样。

假设在游标查询语句的选择列表中存在计算列,则必须为这些计算列指定别名后才干通过游标FOR 循环语句中的索引变量来訪问这些列数据。

注:不要在程序中对游标进行人工操作。不要在程序中定义用于控制FOR 循环的记录。

③处理隐式游标

显式游标主要是用于对查询语句的处理,尤其是在查询结果为多条记录的情况下;

而对于非查询语句,如改动、删除操作,则由ORACLE 系统自己主动地为这些操作设置游标并创建其工作区,

这些由系统隐含创建的游标称为隐式游标。隐式游标的名字为SQL,这是由ORACLE 系统定义的。

对于隐式游标的操作,

如定义、打开、取值及关闭操作,都由ORACLE 系统自己主动地完毕。无需用户进行处理。用户仅仅能通过隐式游标的相关属性。来完毕对应的操作。

在隐式游标的工作区中,所存放的数据是与用户自己定义的显示游标无关的、最新处理的一条SQL 语句所包括的数据。

格式调用为:SQL%

隐式游标属性

SQL%FOUND      布尔型属性,当近期一次读记录时成功返回,则值为TRUE;

SQL%NOTFOUND   布尔型属性,与%FOUND相反。

SQL %ROWCOUNT  数字型属性, 返回已从游标中读取得记录数。

SQL %ISOPEN    布尔型属性, 取值总是FALSE。SQL命令运行完成马上关闭隐式游标。

④关于NO_DATA_FOUND 和%NOTFOUND的差别

SELECT … INTO 语句触发NO_DATA_FOUND。(EXCEPTION  when  NO_DATA_FOUND then ......)

当一个显式游标的WHERE子句未找到时触发%NOTFOUND。

当UPDATE或DELETE 语句的WHERE 子句未找到时触发SQL%NOTFOUND。

在提取循环中要用%NOTFOUND 或%FOUND 来确定循环的退出条件,不要用NO_DATA_FOUND.

⑤游标改动和删除操作

游标改动和删除操作是指在游标定位下,改动或删除表中指定的数据行。

这时。要求游标查询语句中必须使用FOR UPDATE选项。以便在打开游标时锁定游标结果集合在表中相应数据行的全部列和部分列。

为了对正在处理(查询)的行不被另外的用户修改。ORACLE 提供一个FOR UPDATE 子句来对所选择的行进行锁住。

该需求迫使ORACLE锁定游标结果集合的行。能够防止其它事务处理更新或删除同样的行。直到您的事务处理提交或回退为止。

语法:

SELECT . . . FROM … FOR UPDATE [OF column[, column]…] [NOWAIT]

假设还有一个会话已对活动集中的行加了锁。那么SELECT FOR UPDATE操作一直等待到其他的会话释放这些锁后才继续自己的操作。

对于这样的情况,当加上NOWAIT子句时,假设这些行真的被还有一个会话锁定,则OPEN马上返回并给出:

ORA-0054 :resource busy  and  acquire with nowait specified.

假设使用FOR UPDATE 声明游标,则可在DELETE和UPDATE 语句中使用WHERE CURRENT OF cursor_name子句。

改动或删除游标结果集合当前行相应的数据库表中的数据行

⑥例:使用游标

1.要求: 打印出 80 部门的全部的员工的工资:salary: xxx

declare

--1. 定义游标

cursor salary_cursor is select salary from employees where department_id = 80;

v_salary employees.salary%type;

begin

--2. 打开游标

open salary_cursor;

--3. 提取游标

fetch salary_cursor into v_salary;

--4. 对游标进行循环操作: 推断游标中是否有下一条记录

while salary_cursor%found loop

dbms_output.put_line(‘salary: ‘ || v_salary);

fetch salary_cursor into v_salary;

end loop;

--5. 关闭游标

close  salary_cursor;

end;

2.要求: 打印出 80 部门的全部的员工的工资: Xxx ‘s salary is: xxx

declare

cursor sal_cursor is select salary ,last_name from employees where department_id = 80;

v_sal number(10);

v_name varchar2(20);

begin

open sal_cursor;

fetch sal_cursor into v_sal,v_name;

while sal_cursor%found loop

dbms_output.put_line(v_name||‘`s salary is ‘||v_sal);

fetch sal_cursor into v_sal,v_name;

end loop;

close sal_cursor;

end;

3.打印出 manager_id 为 100 的员工的 last_name, email, salary 信息(使用游标, 记录类型)

declare

--声明游标

cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;

--声明记录类型

type emp_record is record(

name employees.last_name%type,

email employees.email%type,

salary employees.salary%type

);

-- 声明记录类型的变量

v_emp_record emp_record;

begin

--打开游标

open emp_cursor;

--提取游标

fetch emp_cursor into v_emp_record;

--对游标进行循环操作

while emp_cursor%found loop

dbms_output.put_line(v_emp_record.name || ‘, ‘ || v_emp_record.email || ‘, ‘ || v_emp_record.salary );

fetch emp_cursor into v_emp_record;

end loop;

--关闭游标

close emp_cursor;

end;

(法二:使用for循环)

declare

cursor emp_cursor is select last_name,email,salary from employees where manager_id = 100;

begin

for v_emp_record in emp_cursor loop

dbms_output.put_line(v_emp_record.last_name||‘,‘||v_emp_record.email||‘,‘||v_emp_record.salary);

end loop;

end;

4. 利用游标, 调整公司中员工的工资:

工资范围       调整基数

0 - 5000       5%

5000 - 10000   3%

10000 - 15000  2%

15000 -        1%

declare

--定义游标

cursor emp_sal_cursor is select salary, employee_id from employees;

--定义基数变量

temp number(4, 2);

--定义存放游标值的变量

v_sal employees.salary%type;

v_id employees.employee_id%type;

begin

--打开游标

open emp_sal_cursor;

--提取游标

fetch emp_sal_cursor into v_sal, v_id;

--处理游标的循环操作

while emp_sal_cursor%found loop

--推断员工的工资, 运行 update 操作

--dbms_output.put_line(v_id || ‘: ‘ || v_sal);

if v_sal <= 5000 then

temp := 0.05;

elsif v_sal<= 10000 then

temp := 0.03;

elsif v_sal <= 15000 then

temp := 0.02;

else

temp := 0.01;

end if;

--dbms_output.put_line(v_id || ‘: ‘ || v_sal || ‘, ‘ || temp);

update employees set salary = salary * (1 + temp) where employee_id = v_id;

fetch emp_sal_cursor into v_sal, v_id;

end loop;

--关闭游标

close emp_sal_cursor;

end;

使用SQL中的 decode 函数

update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,

1, 0.03,

2, 0.02,

0.01)))

5. 利用游标 for 循环完毕 4.

declare

--定义游标

cursor emp_sal_cursor is select salary, employee_id id from employees;

--定义基数变量

temp number(4, 2);

begin

--处理游标的循环操作

for c in emp_sal_cursor loop

--推断员工的工资, 运行 update 操作

--dbms_output.put_line(v_id || ‘: ‘ || v_sal);

if c.salary <= 5000 then

temp := 0.05;

elsif c.salary <= 10000 then

temp := 0.03;

elsif c.salary <= 15000 then

temp := 0.02;

else

temp := 0.01;

end if;

--dbms_output.put_line(v_id || ‘: ‘ || v_sal || ‘, ‘ || temp);

update employees set salary = salary * (1 + temp) where employee_id = c.id;

end loop;

end;

6*. 带參数的游标

declare

--定义游标

cursor emp_sal_cursor(dept_id number, sal number) is

select salary + 1000 sal, employee_id id

from employees

where department_id = dept_id and salary > sal;

--定义基数变量

temp number(4, 2);

begin

--处理游标的循环操作

for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop

--推断员工的工资, 运行 update 操作

--dbms_output.put_line(c.id || ‘: ‘ || c.sal);

if c.sal <= 5000 then

temp := 0.05;

elsif c.sal <= 10000 then

temp := 0.03;

elsif c.sal <= 15000 then

temp := 0.02;

else

temp := 0.01;

end if;

dbms_output.put_line(c.sal || ‘: ‘ || c.id || ‘, ‘ || temp);

--update employees set salary = salary * (1 + temp) where employee_id = c.id;

end loop;

end;

7. 隐式游标: 更新指定员工 salary(涨工资 10),假设该员工没有找到,则打印”查无此人” 信息

begin

update employees set salary = salary + 10 where employee_id = 1005;

if sql%notfound then

dbms_output.put_line(‘查无此人!‘);

end if;

end;

时间: 12-23

PL/SQL 游标的使用的相关文章

PL/SQL 游标的使用详解

一:通过游标,PL/SQL 指向语句被分析以后的活动集 二:对于不同的SQL语句,游标的使用情况不同:1:非查询语句--隐式的2:结果是单行的查询语句--隐式的或显式的3:结果是多行的查询语句--显式的 三:游标属性1:%FOUND 2:%NOTFOUND 3:%ISOPEN 4:%ROWCOUNT 三:显式游标的用法四个步骤(1)定义一个游标名,以及与其相对应的SELECT 语句.语法:CURSOR cursor_name IS select_statement (2)打开游标.语法:OPEN

Oracle笔记 九、PL/SQL 游标的使用

--演示隐式游标,系统自动声明,自动打开,自动使用并且自动关闭 begin update emp set sal = 1000; dbms_output.put_line('影响的行数:' || sql%rowcount); end;   rollback;   /*游标的使用方法: 第一步:声明游标 第二步:打开游标 第三步:使用游标进行循环操作 第四步:关闭游标*/   --普通游标,游标本身就是一个变量 declare --下面的这行代码声明了一个游标 cursor mycur is se

PL/SQL游标的使用

游标的使用语法 1).定义游标 declare cursor 游标名 [(参数名 参数类型[,参数名 参数类型])] is select statement; 2)打开游标 if not cursor_name%isopen then open cursor_name; end if; 3)使用游标读取数据 loop fetch cursor_name into variable; exit when cursor_name%notfound; --TODO task statements en

[Oracle系列整理04] oracle pl/sql 基础

PL/SQL块中只能直接嵌入SELECT,DML(INSERT,UPDATE,DELETE)以及事务控制语句 (COMMIT,ROLLBACK,SAVEPOINT),而不能直接嵌入DDL语句(CREATE,ALTER,DROP)和DCL语句 (GRANT,REVOKE) 1.检索单行数据    1.1使用标量变量接受数据  v_ename emp.ename%type;  v_sal   emp.sal%type;  select ename,sal into v_ename,v_sal fro

PL/SQL简介

本节要点: 什么是PL/SQL PL/SQL简介 PL/SQL 的优点 PL/SQL 的体系结构 PL/SQL 块简介 PL/SQL中的变量和常量 变量和常量的定义 PL/SQL 支持的数据类型 表达式 数值表达式 关系表达式 逻辑表达式 异常处理 处理异常的语法 预定义异常 预定义异常 自定义异常 1         什么是PL/SQL 1.1         PL/SQL简介 PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言,是对

PL/SQL详细介绍,设置oracle相关

1. 实现参照完整性      指若两个表之间具有主从关系(即主外键关系),当删除主表数据时,必须确保相关的从表数据已经被删除.  当修改主表的主键列数据时,必须确保相关从表数据已经被修改.为了实现级联删除,可在定义外部键约束时指定ON DELETE CASCADE关键字  alter table emp add constraint fk_deptno foreign key (deptno) references dept(deptno) on delete cascade; 实现级联更新,

4. Oracle PL/SQL语言及编程

PL/SQl基本结构 PL/SQL数据类型 数字类型:NUMBER(P,S).PLS_INTEGER.BINARY_INTEGER 字符类型:CHAR.NCHAR.VARCHAR2.NVARCHAR2.LONG 日期类型:DATE 布尔类型:BOOLEAN 定义数据类型:type type <数据类型名> is <数据类型>; 数据类型:Oracle允许定义RECODE.TABLE PL/SQL常量和变量 <常量名> constant <常量类型> := &

PL/SQL 编程(二)游标、存储过程、函数

游标--数据的缓存区 游标:类似集合,可以让用户像操作数组一样操作查询出来的数据集,实质上,它提供了一种从集合性质的结果中提取单条记录的手段. 可以将游标形象的看成一个变动的光标,他实质上是一个指针,在一段Oracle存放数据查询结果集或者数据操作结果集的内存中,这个指针可以指向结果集任何一条记录. 游标分静态游标和REF游标两类,静态游标包含显式游标和隐式游标. 显式游标: 在使用之前必须有明确的游标声明和定义,这样的游标定义会关联数据查询语句,通常会返回一行或多行.打开游标后,用户可以利用游

[推荐]ORACLE PL/SQL编程之四:把游标说透(不怕做不到,只怕想不到)

原文:[推荐]ORACLE PL/SQL编程之四:把游标说透(不怕做不到,只怕想不到) [推荐]ORACLE PL/SQL编程之四: 把游标说透(不怕做不到,只怕想不到) 继上两篇:ORACLE PL/SQL编程之八:把触发器说透 ORACLE PL/SQL编程之六:把过程与函数说透(穷追猛打,把根儿都拔起!) 得到了大家的强力支持,感谢.接下来再下猛药,介绍下一篇,大家一定要支持与推荐呀~!我也才有动力写后面的. 本篇主要内容如下: 4.1 游标概念 4.1.1 处理显式游标 4.1.2 处理