oracle说明游标的步骤,Oracle超详细笔记11--游标

1、游标的概念及类型数据库

1.游标的概念  (游动的光标)指针

游标(CURSOR)是Oracle系统在内存中开辟的一个工做区，在其中存放SELECT语句返回的查询结果。对象

使用游标时，SELECT语句查询的结果能够是单条记录，多条记录，也能够是零条记录。内存

游标工做区中，存在着一个指针(POINTER),在初始状态它指向查询结果的首记录。资源

2.游标的类型table

显式游标变量

由用户定义、操做，用于处理返回多行数据的SELECT查询。select

隐式游标循环

由系统自动进行操做，用于处理DML语句和返回单行数据的SELECT查询。          数据类型

2、显式游标

1.显式游标的操做

步骤：

定义游标

打开游标

检索游标

关闭游标

2.定义游标

语法

CURSOR cursor_name IS select_statement ;

说明

游标必须在PL/SQL块的声明部分进行定义；

游标定义时能够引用PL/SQL变量，但变量必须在游标定义以前定义；

定义游标时并无生成数据，只是将定义信息保存到数据字典中；

游标定义后，可使用cursor_name%ROWTYPE定义游标类型变量。

3.打开游标

语法

OPEN cursor_name;

说明

检查变量的值

执行游标定义时对应的SELECT语句，将查询结果检索到工做区中。

游标指针指向第一个元组

一旦游标打开，就没法再次打开，除非先关闭

若是游标定义中的变量值发生变化，则只能在下次打开游标时才起做用。

4.检索游标

语法格式

FETCH cursor_name INTO variable_list|record_variable;

说明

在使用FETCH语句以前必须先打开游标

对游标第一次使用FETCH语句时，游标指针指向第一条记录，所以操做的对象是第一条记录，使用后，游标指针指向下一条记录。

游标指针只能向下移动，不能回退

INTO子句中的变量个数、顺序、数据类型必须与工做区中每行记录的字段数、顺序以及数据类型一一对应。

5.关闭游标

语法格式

CLOSE cursor_name;

说明

游标所对应的内存工做区变为无效，释放与游标相关的系统资源。

例：

根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。

DECLARE

v_deptno emp.deptno%TYPE;

CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_deptno;

v_emp c_emp%ROWTYPE;

BEGIN

v_deptno:=&x;

OPEN c_emp;

LOOP

FETCH c_emp INTO v_emp;

EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||

v_emp.ename||' '||

v_emp.sal ||'  '||

v_deptno);

END LOOP;

CLOSE c_emp;

END;

6.显式游标的属性

%ISOPEN

布尔型。若是游标已经打开，返回TRUE,不然为FALSE。

%FOUND

布尔型，若是最近一次使用FETCH语句，有返回结果则为TRUE,不然为FALSE;

%NOTFOUND

布尔型，若是最近一次使用FETCH语句,没有返回结果则为TRUE,不然为FALSE;

%ROWCOUNT

数值型，返回到目前为止从游标缓冲区检索的元组数。

7.参数化显式游标

参数化游标定义语法格式

CURSOR cursor_name(parameter1

datatype[,parameter2 datatype…])

IS select_statement

打开参数化游标的方法

OPEN cursor_name(parameter1

[,parameter2…])

注意：

定义参数化游标时，只能指定参数的类型，而不能指定参数的长度、精度、刻度；

打开带参数的游标时，实参的个数和数据类型等必须与游标定义时形参个数和数据类型等相匹配。

例：

查询并输出某个部门的员工信息。

DECLARE

CURSOR c_emp(p_deptno emp.deptno%TYPE)IS

SELECT * FROM emp WHERE deptno=p_deptno;

v_emp    c_emp%ROWTYPE;

BEGIN

OPEN c_emp(10);

LOOP

FETCH c_emp INTO v_emp;

EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);

END LOOP;

CLOSE c_emp;

OPEN c_emp(20);

LOOP

FETCH c_emp INTO v_emp;

EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);

END LOOP;

CLOSE c_emp;

END;

利用游标更新或删除数据

书本P230, 例13-14

3、隐式游标

概念

全部的SQL语句都有一个执行的缓冲区，隐式游标就是指向该缓冲区的指针，由系统隐含地打开、处理和关闭。隐式游标又称为SQL游标。

隐式游标主要用于处理INSERT、UPDATE，DELETE以及单行的SELECT…INTO语句，没有OPEN，FETCH，CLOSE等操做命令。

隐式游标属性

SQL%ISOPEN：

布尔型值，判断隐式游标是否已经打开。对用户而言，该属性值始终为FALSE，由于操做时系统自动打开，操做完后当即自动关闭。

SQL%FOUND：

布尔型值，判断当前的操做是否会对数据库产生影响。若是有数据的插入、删除、修改或查询到数据，则返回TRUE，不然返回FALSE。

SQL%NOTFOUND：

布尔型值，判断当前的操做是否对数据库产生影响。若是没有数据的插入、删除、修改或没有查询到数据，则返回TRUE，不然返回FALSE。

SQL%ROWCOUNT：

数值型，返回当前操做所涉及的数据库中的行数。

例：

修改员工号为1000的员工工资，将其工资增长100。若是该员工不存在，则向emp表中插入一个员工号为1000，工资为1600的员工。

BEGIN

UPDATE emp SET sal=sal+100 WHERE empno=1000;

IF SQL%NOTFOUND THEN

INSERT INTO emp(empno,sal) VALUES(1000,1600);

END IF;

END;

或

BEGIN

UPDATE emp SET sal=sal+100 WHERE empno=1000;

IF SQL%ROWCOUNT=0 THEN

INSERT INTO emp(empno,sal) VALUES(1000,1600);

END IF;

END;

4、游标变量

1.概念

游标变量是一个指向多行查询结果集的指针，不与特定的查询绑定，所以具备很是大的灵活性，能够在打开游标变量时定义查询，能够返回不一样结构的结果集

2.使用游标变量包括

游标引用类型(REF CURSOR)

声明游标变量

打开游标变量

检索游标变量

关闭游标变量

3.定义游标引用类型及游标变量

语法

TYPE ref_cursor_type_name

IS REF CURSOR

[RETURN return_type]

ref_cursor_type_name variable_name;

RETURN子句用于指定定义的游标类型返回结果集的类型，该类型必须是记录类型。若是定义游标引用类型时带有RETURN子句，则用其定义的变量称为强游标变量，不然称为弱游标变量。

例：

TYPE emp_cursor_type IS REF CURSOR RETURN emp%ROWTYPE;

TYPE general_cursor_type IS REF CURSOR;

v_emp emp_cursor_type;

v_general general_cursor_type;

4.打开游标变量

语法

OPEN cursor_variable FOR select_statement;

注意

若是打开的游标变量是强游标变量，则查询语句的返回类型必须与游标引用类型定义中RETURN子句指定的返回类型相匹配。

例：

OPEN v_emp FOR SELECT * FROM emp;

OPEN v_general FOR SELECT empno,ename,sal,deptno FROM emp;

5.检索游标变量

语法

LOOP

FETCH cursor_variable INTO variable1, variable2, …;

EXIT WHEN cursor_variable%NOTFOUND;

……

END LOOP;

注意

检索游标变量时只能使用简单循环或WHILE循环，不能采用FOR循环。

6.关闭游标变量

语法

CLOSE cursor_variable;

例：

要求根据输入的不一样表名进行不一样处理，若表名为emp，则显示高于10号部门平均工资的员工信息；若表名为dept，则显示各个部门的人数。

DECLARE

v_table CHAR(20);

TYPE type_cursor IS REF CURSOR;

v_cursor type_cursor;

v_emp emp%ROWTYPE;

v_deptno emp.deptno%TYPE;

v_num NUMBER;

BEGIN

v_table:='&table_name';

IF v_table = 'emp' THEN

OPEN v_cursor FOR SELECT * FROM emp WHERE sal>(

SELECT AVG(sal) FROM emp WHERE deptno=10);

ELSIF v_table = 'dept' THEN

OPEN v_cursor FOR SELECT deptno,count(*) num

FROM emp GROUP BY deptno;

ELSE

RAISE_APPLICATION_ERROR(-20000,'Input must be  ''emp'‘ or ''dept''');

END IF;

LOOP

IF v_table = 'emp' THEN

FETCH v_cursor INTO v_emp;

EXIT WHEN v_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||

v_emp.ename||' '||

v_emp.sal||' '||

v_emp.deptno);

ELSE

FETCH v_cursor INTO v_deptno,v_num;

EXIT WHEN v_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_deptno||' '||v_num);

END IF;

END LOOP;

CLOSE v_cursor;

END;