发布时间 : 星期六 文章数据库讲义--2--游标、异常(课堂版)更新完毕开始阅读
1.游标
游标提供了一种从表中检索数据并进行操作的灵活手段。PL/SQL在执行SELECT、INSERT、DELETE和UPDATE语句时,ORACLE会在内存中为其分配上下文区(Context Area),即缓冲区。游标是指向该区的一个指针,通过游标PL/SQL程序可以一次处理查询结果集中的一行,并可以对该行数据执行特定操作,从而为用户在处理数据的过程中提供方便。 在oracle中,主要使用显式游标,隐式游标,动态游标(以后讲—存储过程实现分页的时候)。
2. 显式(示)游标
显式游标是由用户声明和操作的一种游标,通常用于操作查询结果集(即由select语句返回的查询结果),使用它处理数据的步骤为:声明游标、打开游标、读取游标和关闭游标4个步骤。其中,读取游标很可能是一个反复的步骤(迭代),因为游标每次只能读取一行数据,对于多条记录,需要反复读取,直到游标读取不到数据为止。
2-1 游标使用的步骤 a. 声明游标
定义游标名字,以及对应的select语句。
CURSOR cursor_name*(input_parameter1*, input_parameter2+…)+ [RETURN ret_type] IS
select_statement; cursor_name: 所声明游标名称
ret_name: 执行游标操作后的返回值类型,这是一个可选项。
select_statement: 游标所使用的select语句,为游标的反复读取提供了结果集。
input_parameter1: 游标的“输入参数”,可以有多个,这是可选项。它指定用户在打开游标后,向游标中传递的值,该参数的定义和初始化格式如下: para_name [in] datatype [ {:= | default } para_value ]
其中,para_name表示参数名称,其后面的关键字\表示输入方向,可以省略;datatype表示参数的数据类型,但数据不可以指定长度; para_value表示该参数的初始值或默认值,也可以是表达式; para_name参数的初始值可以用常规方式赋值(:=),也可以使用关键字default。
示例代码:
声明一个游标,用来读取emp表中职务是销售员(SALESMEN)的雇员信息。 declare
cursor cur_emp(var_job in varchar2:='SALESMEN')
is select empno, ename, sal from emp where job=var_job;
b. 打开游标
在游标声明完毕之后,必须打开才能使用,打开游标的语法: open cur_name[(para_values1[,para_values2]...)]; cur_name: 打开游标的名字
para_values1: 指定“输入参数”的值,根据声明游标时的实际情况,可以是多个或一个,
这是可选项。 示例代码:
open cur_emp('MANAGER');
c. 读取游标
fetch cur_name into {variable};
variable: 表明一个变量列表或者“记录”变量(RECORD类型),oracle使用“记录”变量来存储游标中的数据。
在游标中包含了一个数据行指针,其用来指向当前数据行。刚刚打开游标时,指针指向结果集中的第一行,当使用fetch--into语句读取数据完毕后,游标中的指针自动指向下一行数据,直到指针指向结果集中最后一条记录之后为止(当最后一条记录为空时,表示读取完毕),这是游标的%found属性值为false.
4. 关闭游标
游标使用完毕后,需要关闭,以释放资源。 close cur_name;
整个游标操作的示例代码:
declare
cursor cur_emp (var_job varchar2 := 'SALESMAN')
is select empno, ename, sal from emp where job = var_job; type record_emp is record
(var_empno emp.empno%type, var_ename emp.ename%type, var_sal emp.sal%type); emp_row record_emp; begin
open cur_emp('MANAGER'); fetch cur_emp into emp_row; while cur_emp%found loop
dbms_output.put_line(emp_row.var_ename||'的编号'||emp_row.var_empno||',的工资'||emp_row.var_sal);
fetch cur_emp into emp_row; end loop; close cur_emp; end; /
2-2 游标的属性 游标有4个属性:
%found: 布尔型属性,如果SQL语句至少影响到一行的数据,该属性为true, 否则为false
%notfound:布尔型属性,与%found相反。 %isopen: 布尔型属性,游标是否打开
%rowcount: 数字型属性,返回受SQL语句影响的行数
使用指南,声明一个游标,检索指定员工编号的雇员信息,然后使用游标的%found属性来判断是否检索到指定员工编号的雇员信息。 declare
var_name varchar2(50); var_job varchar2(50);
/*声明游标*/
cursor cur_emp
is select ename, job form emp where empno=7499; begin
open cur_emp;
fetch cur_emp into var_name, var_job; if cur_emp%found then
dbms_output.put_line('编号是7499'||var_name); else
dbms_output.put_line('查无此人'); endif; end; /
3. 隐式游标
在执行一个SQL语句时,oracle会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。隐式游标主要处理数据操纵语句(如update, delete语句)的执行结果,当然,特殊情况下,也可以处理select语句的查询结果。隐式游标,也有属性。当使用隐式游标的属性时,需要在属性前面加上隐式游标的默认名称--SQL. 示例代码:
把emp表中销售员(SALESMAN)的工资上调20%,使用隐式游标的属性输出上调工资的员工数量。 begin
update emp set sal=sal*(1+0.2) where job='SALESMAN'; if sql%notfound then
dbms_output.put_line('没有雇员上调工资'); else
dbms_output.put_line('有'||sql%rowcount||'个雇员上调工资'); end if; end; /
4. 通过for语句循环遍历游标
使用for语句遍历游标中的数据时,可以把其计数器看做一个自动的RECORD类型变量。
4-1 遍历显式游标
for var_auto_record in cur_name loop plsqlsentence; end loop;
var_auto_record: 自动的RECORD类型的变量,可以是任意合法的变量名称。 cur_name: 指定游标的名称 plsqlsentence: PL/SQL语句
示例代码:
使用显示游标和for语句检索部门编号是30的雇员信息并输出: declare
cursor cur_emp
is select * from emp where deptno=30; begin
for emp_record in cur_emp loop
dbms_output.put(emp_record.empno); dbms_output.put(emp_record.ename); dbms_output.put(emp_record.job); end loop; end; \\
4-2 使用隐式游标和for语句 begin
for emp_record in (select empno, ename, sal from emp where job='SALESMAN') loop
dbms_output.put(emp_record.empno); dbms_output.put(emp_record.ename); dbms_output.put_line(emp_record.sal); end loop; end; /
5. 异常—理解成java try—catch--….
在PL/SQL程序中,有exception块的定义。 declare begin exception end; /