Oracle --游标

最新推荐文章于 2022-07-02 17:16:56 发布

weixin_34060741

最新推荐文章于 2022-07-02 17:16:56 发布

阅读量105

点赞数

原文链接：https://my.oschina.net/u/2312022/blog/489064

版权

2019独角兽企业重金招聘Python工程师标准>>>

为了处理SQL语句得到的信息,oracle必须要分配一片叫做上下文的区域来处理如上信息。游标是一个指向上下文的句柄或指针，通过游标，pl/sql可以控制上下文区域所得到的信息。

1.对于不同的SQL语句，游标的使用情况也不同

SQL语句游标

非查询语句隐式游标

结果是单行的查询语句隐式或显示游标

结果是多行的查询语句显示的

2.处理显示游标

declare
        --定义游标类型
        cursor c_cursor is select user_name,age from t_user;
        --定义两个变量
        v_userName t_user.user_name%type;
        v_age            t_user.age%type; 
begin
        --打开游标
        open c_cursor;
        --提取游标数据，即检索结果集中的数据行，放入到指定的输出变量中
        fetch c_cursor into v_userName,v_age;
        --这里可以理解c_cursor就是select user_name,age from t_user
        --即下面的c_cursor可以用select user_name,age from t_user代替
        --当select语句找得到值时，就loop
        while c_cursor%found loop
                dbms_output.put_line('执行自己的操作');
                fetch c_cursor into v_userName,v_age;
        end loop;
        --关闭游标
        close c_cursor;        
end;

3.游标属性

%found 布尔型属性，当最近一次读记录成功时返回true

%notfound 与%found相反

%isopen 布尔型属性，当游标已打开时返回true

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

例如：给工资低于4000员工增加工资800

declare
        v_empNo emp.empNo%type;
        v_salary emp.salary%type;
        cursor c_cursor is select empNo,salary from emp;
begin
        open c_cursor;
        loop
            fetch c_cursor into v_empNo,v_salary;
            --当游标没有找到数据时，停止
            exit when c_cursor%notfound;
            if v_salary <=4000 then
                update emp 
                set salary=salary+800
                where empNo=v_empNo; 
            end if;
        end loop;  
        close c_cursor;  
end;

4.游标for循环

pl/sql语言提供了游标for循环语句，它会自动执行游标的open、fetch、close语句。当进入循环时，for循环语句自动打开游标，并提取第一行的游标数据，当程序处理完当前所提取的数据进入下一次循环时，游标for循环语句自动提取下一行数据进行处理，当提取完结果集中所有数据行后结束循环，并自动关闭游标。

declare
        cursor c_cursor is select empNo,empName,empSalary from emp;
begin
        --隐含打开游标
        for v_salary in c_cursor loop
        --隐含执行fetch语句
        dbms_output.put_line('执行自己的操作'); 
        --隐含监测c_cursor%notfound语句
        end loop;
        --隐含关闭游标
end;

5.隐式游标处理

显示游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况。而对于非查询语句，如修改、删除操作等，则由oracle系统自动的为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由oracle系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由oracle系统自动完成，用户只能通过隐式游标的相关属性来完成相应的操作。

实际例子

删除emp表中某部门的所有员工，如果该部门中没有员工，则在dept表中删除该部门。

declare
        v_deptNo emp.deptNo%type;
begin
        delete from emp where deptNo = v_deptNo;
        --如果如上where语句不成立，即v_deptNo字段中没有员工
        if SQL%notfound then
            --在部门表中删除该部门号
            delete from dept where deptNo = v_deptNo;
        end if;
end;

6.no_data_found与%notfound的区别

select ... into 语句会触发no_data_found;

当一个显示游标的where子句未找到时会触发%notfound；

当update或delete语句的where子句未找到时会触发SQL%notfound。

7.游标变量

游标变量是一个指向多行查询结果集中当前数据行的指针。

1>游标变量是动态的，游标是静态的。

2>游标只能与指定的查询相连，即固定指向一个查询的内存处理区域；而游标变量是动态的，它可以与不同的查询语句相连，它可以指向不同的查询语句的内存处理区域（同一时刻只能与一个查询语句相连，且要求这些查询语句的返回类型兼容）。

在定义游标变量时，有强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不用说明返回类型。

例如，创建两个强类型、一个弱类型游标变量

--定义一个游标变量，返回类型是表t_dept类型
type c_deptType is ref cursor return t_dept%rowtype; 
--定义一个游标变量，返回类型是t_dept表字段
type c_deptType2 is ref cursor return t_dept.deptName%type;
--定义一个游标变量，无返回类型
type c_deptType3 is ref cursor;

强类型游标变量举例

declare
        type emp_rec is record(
        empId emp.empNo%type;
        empName emp.empName%type;
        empJob emp.empJob%type
        );
        --定义游标变量，返回类型emp_rec
        type empRefcurType is ref cursor return emp_rec;
        --定义游标变量，类型为empRefcurType 
        emp_refcur empRefcurType;
        --定义变量，类型为emp_rec
        emp_job emp_rec;
begin
        open emp_refcur for
        select empNo,empName,empJob from emp;
        fetch emp_refcur into emp_job;
        while emp_refcur%found loop
                dbms_output.put_line('执行自己的操作');    
                fetch emp_refcur into emp_job;
        end loop;        
end;

弱类型游标变量举例

declare
        type c_refcur if ref cursor;
        refcur c_refcur;
        type sample_refType is record(
        v_id number(2);
        v_description varchar2(30)
        );
        sample sample_refType;
begin
        open refcur for
        select deptNo,deptName from dept;
        fetch refcur into sample;
        while refcur%found loop
               dbms_output.put_line('执行自己的操作'); 
                fetch refcur into sample; 
         end loop;
         close refcur;                   
end;

转载于:https://my.oschina.net/u/2312022/blog/489064