orcale 之游标的属性

た入场券 2021-06-26 16:06 330阅读 0赞

无论是显式游标还是隐式游标都会有四个属性分别是：`%ISOPEN、%FOUND、%NOTFOUND、%ROWCOUNT` 下面我们分别对着几种属性进行分析。

## `%FOUND` ##

该属性表示当前游标是否指向有效的一行，结果是一个`Boolean` 类型的。这个属性一般用来判断是否结束当前游标的使用。

OPEN emp_cur; --打开游标
      --将第一行的数据放入变量中，之后游标后移
      FETCH emp_cur INTO emp_no, emp_nanme;
      LOOP
        EXIT WHEN NOT emp_cur%FOUND; --如果游标已经到末尾结束
        IF emp_no = '7839' THEN
          INSERT INTO t_emp values(emp_no,emp_nanme);
        else
          INSERT INTO t_emp2 values(emp_no,emp_nanme);
        END IF;
        FETCH emp_cur INTO emp_no, emp_nanme;
      END LOOP;
    CLOSE emp_cur; --关闭游标

在隐式游标中引用方法为 `SQL%FOUND`。

delete from student where sid = 1;
    if SQL%FOUND THEN
        INSERT INTO states values(1,1);
    else
        INSERT INTO states values(1,0);
    end if;

## `%NOTFOUND` ##

这个属性和上面的 `%FOUND` 很类似，只不过结果相反。这里就不举例子了，大家自己练习下。

## `%ROWCOUNT` ##

这个属性记录了游标取过记录的行数，也可以理解为当前游标所在的行数。这个属性在循环的时候十分有效。

LOOP
        FETCH emp_cur INTO emp_no, emp_nanme;
        EXIT WHEN emp_cur%ROWCOUNT=10;
        ...
    END LOOP;

在隐式游标中使用 `%ROWCOUNT`

## `%ISOPEN` ##

顾名思义是表示游标是否处于打开的状态。在我们的实际使用前最好使用该属性进行检查。

IF emp_cur%ISOPEN THEN
        FETCH emp_cur INTO emp_no, emp_nanme;
    ELSE
        OPEN emp_cur
    END IF;

在隐式游标中可以使用 `SQL%ISOPEN` 进行引用，但是需要说的是隐式游标中 `SQL%ISOPEN` 总是为 `TRUE` 因此隐式游标中不需要打开游标关闭游标，也不需要对游标的状态进行检查。

--------------------

## oracle中的`sql%found`、`sql%notfound`、`sql%rowcount`和`sql%isopen` ##

Oracle 存储过程 删除表记录时删除不存在的记录也是显示删除成功

create or replace procedure delDept(p_deptno in dept.deptno%type) is
    begin
        delete from dept where deptno=p_deptno;
        dbms_output.put_line('部门删除成功...');
        exception when others then
        dbms_output.put_line('部门删除失败...');
    end;

删除不存在的数据并不会促发Oracle的异常  
对于`delete from dept where deptno=p_deptno;`  
判断是否有数据被删除可以用`sql%rowcount`，`sql%notfound` 来判断

if sql%rowcount = 0 then
        dbms_output.put_line('部门删除失败...');
        end if;
        或者
        if sql%notfound then
        dbms_output.put_line('部门删除失败...');
        end if;
    
    create or replace procedure delDept(p_deptno in dept.deptno%type) is
    EmpCount    NUMBER; --影响的记录数
    begin
        delete from dept where deptno=p_deptno;
         EmpCount := SQL%ROWCOUNT;
         if(EmpCount <>0)
         then
        dbms_output.put_line('部门删除成功...');
        exception when others then
        dbms_output.put_line('部门删除失败...');
    end;

`sql%rowcount`用于记录修改的条数，就如你在`sqlplus`下执行`delete from`之后提示已删除xx行一样，这个参数必须要在一个修改语句和`commit`之间放置，否则你就得不到正确的修改行数。

例如：

SQL> declare n number;
      2  begin
      3  insert into test_a select level lv from dual connect by level<500;
      4  n:=sql%rowcount;
      5  commit;
      6  dbms_output.put_line(n);
      7  end;
      8  /

如果我连续执行了3个`select`语句  
然后调用`sql%rowcount`，得到的结果是最后一个`select`的条数。 要统计所有的，可以使用三个变量接收`sql%rowcount`，然后相加即可，不过貌似直接`select`的写法无法使用`sql%rowcount`。

在执行`DML`（`insert，update，delete`）语句时，可以用到以下三个隐式游标（游标是维护查询结果的内存中的一个区域，运行`DML`时打开，完成时关闭，用`sql%isopen`检查是否打开）：

`sql%found` （布尔类型，默认值为`null`）  
`sql%notfound`（布尔类型,默认值为`null`）  
`sql%rowcount`(数值类型默认值为`0`)  
`sql%isopen`(布尔类型)

当执行一条DML语句后，`DML`语句的结果保存在四个游标属性中，这些属性用于控制程序流程或者了解程序的状态。当运行DML语句时，`PL/SQL`打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，游标在运行`DML`语句时打开，完成后关闭。隐式游标只使用`SQL%FOUND`,`SQL%NOTFOUND`,`SQL%ROWCOUNT`三个属性.`SQL%FOUND`,`SQL%NOTFOUND`是布尔值，`SQL%ROWCOUNT`是整数值。

`SQL%FOUND`和`SQL%NOTFOUND`在执行任何DML语句前`SQL%FOUND`和`SQL%NOTFOUND`的值都是`NULL`,**在执行DML语句后，`SQL%FOUND`的属性值将是：**

*  TRUE :`INSERT`
 *  TRUE :`DELETE`和`UPDATE`，至少有一行被`DELETE`或`UPDATE`.
 *  TRUE :`SELECT INTO`至少返回一行

当`SQL%FOUND`为`TRUE`时,`SQL%NOTFOUND`为`FALSE`。

### `SQL%ROWCOUNT` ###

在执行任何DML语句之前，`SQL%ROWCOUNT`的值都是`NULL`,对于`SELECT INTO`语句，如果执行成功，`SQL%ROWCOUNT`的值为`1`,如果没有成功或者没有操作(如`update、insert、delete`为0条），`SQL%ROWCOUNT`的值为0.

### `SQL%ISOPEN` ###

`SQL%ISOPEN`是一个布尔值，如果游标打开，则为`TRUE`, 如果游标关闭，则为`FALSE`.对于隐式游标而言`SQL%ISOPEN`总是`FALSE`，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。

**`no_data_found` 与`sql%notfound` 以及`sql%rowcount` 的区别：**

`NO_DATA_FOUND`：该异常可以在两种不同的情况下出现：第一种：当`SELECT。。。。INTO`语的 `WHERE`子句 没匹配任何数据行时；第二种：试图引用尚未赋值的`PL/SQL index-by`表元素时。

`SQL%NOTFOUND`：是隐匿游标的属性，当没有可检索的数据时，该属性为：`TRUE`；常作为检索循环退出的条件。若某`UPDATE`或`DELETE`语句的WHERE子句不匹配任何数据行，该属性为：`TRUE`，但不并不出现`NO_DATA_FOUND`异常.

`SQL%ROWCOUNT`：该数字属性返回了到目前为止，游标所检索数据库行的个数。