`

快速掌握Oracle数据库游标的使用方法

阅读更多
显式游标

当查询返回结果超过一行时,就需要一个显式游标,此时用户不能使用select into语句。PL/SQL管理隐式游标,当查询开始时隐式游标打开,查询结束时隐式游标自动关闭。显式游标在PL/SQL块的声明部分声明,在执行部分或异常处理部分打开,取出数据,关闭。


使用游标

这里要做一个声明,我们所说的游标通常是指显式游标,因此从现在起没有特别指明的情况,我们所说的游标都是指显式游标。要在程序中使用游标,必须首先声明游标。


声明游标

语法:



CURSOR cursor_name IS select_statement;

在PL/SQL中游标名是一个未声明变量,不能给游标名赋值或用于表达式中。


例:


DECLARE
CURSOR C_EMP IS SELECT empno,ename,salary
FROM emp
WHERE salary>2000
ORDER BY ename;
........
      BEGIN

在游标定义中SELECT语句中不一定非要表可以是视图,也可以从多个表或视图中选择的列,甚至可以使用*来选择所有的列 。

打开游标

使用游标中的值之前应该首先打开游标,打开游标初始化查询处理。打开游标的语法是:


OPEN cursor_name

cursor_name是在声明部分定义的游标名。


例:

OPEN C_EMP; 打开游标C_EMP。

语法:

CLOSE cursor_name


例:

CLOSE C_EMP; 从游标提取数据。从游标得到一行数据使用FETCH命令。每一次提取数据后,游标都指向结果集的下一行。语法如下:

FETCH cursor_name INTO variable[,variable,...]

对于SELECT定义的游标的每一列,FETCH变量列表都应该有一个变量与之相对应,变量的类型也要相同。




例:


SET SERVERIUTPUT ON
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
CLOSE c_emp;
      END

这段代码无疑是非常麻烦的,如果有多行返回结果,可以使用循环并用游标属性为结束循环的条件,以这种方式提取数据,程序的可读性和简洁性都大为提高,下面我们使用循环重新写上面的程序:


SET SERVERIUTPUT ON
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
      END


记录变量

定义一个记录变量使用TYPE命令和%ROWTYPE,关于%ROWsTYPE的更多信息请参阅相关资料。

记录变量用于从游标中提取数据行,当游标选择很多列的时候,那么使用记录比为每列声明一个变量要方便得多。

当在表上使用%ROWTYPE并将从游标中取出的值放入记录中时,如果要选择表中所有列,那么在SELECT子句中使用*比将所有列名列出来要得多。

例:


SET SERVERIUTPUT ON
DECLARE
R_emp EMP%ROWTYPE;
CURSOR c_emp IS SELECT * FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||'is'|| r_emp.salary);
END LOOP;
CLOSE c_emp;
      END;

%ROWTYPE也可以用游标名来定义,这样的话就必须要首先声明游标:


SET SERVERIUTPUT ON
DECLARE
CURSOR c_emp IS SELECT ename,salary FROM emp;
R_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('Salary of Employee'
||r_emp.ename||'is'|| r_emp.salary);
END LOOP;
CLOSE c_emp;
      END;

带参数的游标

与存储过程和函数相似,可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下:


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

定义参数的语法如下:


Parameter_name [IN] data_type[{:=|DEFAULT} value]

与存储过程不同的是,游标只能接受传递的值,而不能返回值。参数只定义数据类型,没有大小。

另外可以给参数设定一个缺省值,当没有参数值传递给游标时,就使用缺省值。游标中定义的参数只是一个占位符,在别处引用该参数不一定可靠。

在打开游标时给参数赋值,语法如下:


OPEN cursor_name[value[,value]....];

参数值可以是文字或变量。

例:


DECLARE
CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
r_dept DEPT%ROWTYPE;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
v_tot_salary EMP.SALARY%TYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
OPEN c_emp(r_dept.deptno);
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('Name:'|| v_ename||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE
('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
CLOSE c_dept;
      END;

游标FOR循环

在大多数时候我们在设计程序的时候都遵循下面的步骤:

1、打开游标。

2、开始循环。

3、从游标中取值。

4、那一行被返回。

5、处理。

6、关闭循环。

7、关闭游标。

可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同,这就是FOR循环,用于FOR循环的游标按照正常的声明方式声明,它的优点在于不需要显式的打开、关闭、取数据,测试数据的存在、定义存放数据的变量等等。游标FOR循环的语法如下:


FOR record_name IN
(corsor_name[(parameter[,parameter]...)]
| (query_difinition)
LOOP
statements
      END LOOP;

下面我们用for循环重写上面的例子:


DECLARE
CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN c_dept LOOP
DBMS_OUTPUT.PUT_LINE
('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN c_emp(r_dept.deptno) LOOP
DBMS_OUTPUT.PUT_LINE
('Name:' || v_ename || 'salary:' || v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE
('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
      END;

在游标FOR循环中使用查询

在游标FOR循环中可以定义查询,由于没有显式声明所以游标没有名字,记录名通过游标查询来定义。


DECLARE
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN (SELECT ename,salary
   FROM emp
   WHERE deptno=p_dept
   ORDER BY ename) LOOP
DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
      END;

游标中的子查询

语法如下:


CURSOR C1 IS SELECT * FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
      WHERE dname!='ACCOUNTING');

可以看出与SQL中的子查询没有什么区别。

游标中的更新和删除

在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。

UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法,在声明游标时必须使用FOR UPDATE子串,当对话使用FOR UPDATE子串打开一个游标时,所有返回集中的数据行都将处于行级(ROW-LEVEL)独占式锁定,其他对象只能查询这些数据行,不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。

语法:


FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
      [nowait]

在多表查询中,使用OF子句来锁定特定的表,如果忽略了OF子句,那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定,那么正常情况下ORACLE将等待,直到数据行解锁。

在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下:


WHERE{CURRENT OF cursor_name|search_condition}

例:



DECLARE
CURSOR c1 IS SELECT empno,salary
FROM emp
WHERE comm IS NULL
FOR UPDATE OF comm;
v_comm NUMBER(10,2);
BEGIN
FOR r1 IN c1 LOOP
IF r1.salary<500 THEN
v_comm:=r1.salary*0.25;
ELSEIF r1.salary<1000 THEN
v_comm:=r1.salary*0.20;
ELSEIF r1.salary<3000 THEN
v_comm:=r1.salary*0.15;
ELSE
v_comm:=r1.salary*0.12;
END IF;
UPDATE emp;
SET comm=v_comm
WHERE CURRENT OF c1l;
END LOOP;
      END


转载:http://www.examda.com/oracle/jishu/20070625/154300890.html
分享到:
评论

相关推荐

    快速掌握Oracle数据库中的Copy命令

    Oracle数据库中的Copy命令是SQL*Plus提供的一种便捷的数据复制工具,尤其对于DBA来说,它在处理表间数据复制任务时具有较高的效率。Copy命令允许用户在同一个数据库的不同表之间,甚至不同服务器的数据库之间复制...

    oracle数据库从入门到精通.pdf

    文档《oracle数据库从入门到精通.pdf》详细地介绍了Oracle数据库的基础知识和高级操作,使得学习者能够从基础的表空间创建到存储过程的编写,以及游标的应用等高级特性有深入理解和掌握。 首先,文档指出了创建表...

    oracle数据库操作手册

    本操作手册将深入探讨Oracle数据库的核心概念、安装配置、管理维护以及SQL查询等多个方面,帮助用户全面理解和掌握Oracle数据库的操作。 1. **Oracle数据库核心概念** - **数据库实例与数据库**:Oracle数据库实例...

    ORACLE 数据库入门教程

    本教程旨在为初学者提供一个全面的Oracle数据库入门指南,帮助你快速掌握Oracle的基础知识和操作技能。 一、Oracle数据库简介 Oracle数据库是由甲骨文公司开发的一款面向企业级的高性能、高可靠性的数据库系统。它...

    多个ORACLE 游标+SQL 游标 小例子+帮助文档

    Oracle数据库中的游标主要分为两种类型:隐式游标和显式游标。隐式游标是Oracle自动管理的,每次执行DML语句(INSERT、UPDATE、DELETE)时都会使用。而显式游标则需要我们手动声明和管理,适用于更复杂的逻辑处理。 ...

    oracle官方数据库教程

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,尤其在企业级应用中占据重要地位。...通过深入学习并实践这些内容,你将能够熟练掌握Oracle数据库的管理和开发技能,为未来的职业生涯奠定坚实基础。

    oracle数据库速学

    以上就是Oracle数据库速学的一些关键知识点,这些内容将帮助初学者快速入门并逐步深入理解Oracle数据库。在实际学习过程中,结合“数据库.txt”中的资料,理论与实践相结合,相信你会对Oracle有更全面的认识。

    Oracle数据库系统应用实例集锦与编程技巧

    熟悉其语法特性,掌握异常处理和游标使用,能实现复杂业务逻辑。 12. **数据库升级与迁移**:Oracle提供了一系列工具和技术进行版本升级和跨平台迁移,如Data Pump、DBUA等,确保数据完整无损地转移到新环境。 ...

    21天自学速成 Oracle 数据库 电子书

    《21天自学速成 Oracle 数据库》是一本旨在帮助初学者快速掌握Oracle数据库系统核心概念和技术的电子书籍。在21天的学习计划中,它将带你逐步深入了解Oracle的各个方面,从基础安装到高级管理技巧,助你在短时间内...

    零基础学习oracle数据库

    本教程旨在帮助那些没有任何数据库背景的人快速理解和掌握Oracle的基本概念和操作。 【备课笔记】部分可能包含以下知识点: 1. 数据库基础知识:介绍什么是数据库,以及为什么我们需要数据库,特别是Oracle数据库...

    Oracle 数据库学习教程

    Oracle教程通过系统性的讲解,帮助读者掌握Oracle数据库的各项技能,从入门到高级特性,从理论到实际应用。Oracle和Sun的合并背景信息提供了对Oracle公司发展及其产品战略的宏观认识,特别是其在云计算领域的布局和...

    Excel表的Oracle数据库插入工具

    此外,如果Excel文件非常大,还可以考虑先将数据导出为CSV文件,然后通过SQL*Loader等工具快速导入到Oracle数据库。 总之,将Excel数据插入Oracle数据库涉及的关键技术点包括:Excel文件的读取(如使用pandas)、...

    oracle数据库开发工具

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,其强大的功能和高效的数据处理能力深受企业和开发者喜爱。在Oracle数据库的开发过程中,PL/SQL(Procedural Language/Structured Query Language)是一种不...

    ORACLE数据库教程下载

    本教程将全面介绍Oracle数据库的核心概念、安装配置、管理操作以及高级特性,帮助初学者快速掌握Oracle数据库的基础知识和实战技能。 一、Oracle数据库系统概述 Oracle数据库系统是一个大型分布式数据库管理系统,...

    Oracle11g 数据库中文 文档

    8. **高可用性与故障恢复**:这部分会介绍RAC(实时应用集群)、数据守护、闪回技术等,这些都是保证Oracle数据库高可用性和快速故障恢复的关键特性。 9. **数据迁移与升级**:文档会提供从旧版本Oracle数据库升级...

    Oracle数据库技术课程学习大纲详细.docx

    因此,掌握Oracle数据库技术成为了许多计算机相关专业学生以及IT行业从业者的迫切需求。 课程的内容安排科学合理,从基础知识讲起,逐步深入到Oracle数据库的核心技术。在课程的伊始,我们首先对数据库系统的基本...

    oracle数据库21个入门教程

    这个"Oracle数据库21个入门教程"很可能是为初学者设计的一系列学习资源,帮助他们快速掌握Oracle数据库的基本概念、操作和管理技巧。下面我们将深入探讨Oracle数据库的一些关键知识点。 1. **数据库概念**:数据库...

Global site tag (gtag.js) - Google Analytics