Oracle游标的概念和简单应用实例讲解

oracle游标的概念和简单应用实例讲解

概念: 

    游标用于处理select语句的查询结果(结果集 resultset) , 

    有些人也认为 游标就是查询结果 

使用步骤与语法格式(非滚动游标)

    1.  声明游标

            申明区: 
                cursor 游标名称  is select语句;
    2.  打开游标

            open 游标名称;

    3.  提取一行数据(游标向下移动)

            fetch 游标名称 into 变量;--将游标中的某一行赋值给一个变量!

    4.  关闭游标
            close  游标名称;

            1.  资源为什么要关闭: 
                    句柄数量 1024

            2.  已关闭的资源可以重复关闭吗? 

                游标的打开与关闭  不可重复执行, 否则报错

 

例子: 定义一个游标 , 用来存放s_emp表中的 id, last_name,salary ,然后提取游标的前五条数据, 并打印 , 最后关闭游标!


set serveroutput on;
declare
    /*声明游标*/
    cursor s_emp_c is select id,last_name,salary from s_emp;
    /*声明一个类型, 匹配结果集中一行的数据类型*/
    type myemp is record(
        id s_emp.id%type,
        last_name s_emp.last_name%type,
        salary s_emp.salary%type
    );
    /*声明一个变量,用来承接游标取出的数据*/
    var_me myemp;
begin
    /*打开游标*/
    open s_emp_c;
    /*游标向下移动一行, 并取出数据*/
    for i in 1..5 loop
        fetch s_emp_c into var_me;
        dbms_output.put_line('这个员工的编号为:'||var_me.id||',名称为:'||var_me.last_name||',他的月薪为:'||var_me.salary);
    end loop;
    close s_emp_c;
end;

使用 游标名称%rowtype

定义一个游标 , 用来存放s_emp表中的 id, last_name,salary ,然后提取游标的前五条数据, 并打印 , 最后关闭游标!


set serveroutput on;
declare
    /*声明游标*/
    cursor s_emp_c is select id,last_name,salary from s_emp;

    /*声明一个变量, 通过游标名称%rowtype 获取一个匹配当前游标中数据类型订单record变量
    */
    var_me  s_emp_c%rowtype;
begin
    /*打开游标*/
    open s_emp_c;
    /*游标向下移动一行, 并取出数据*/
    for i in 1..5 loop
        fetch s_emp_c into var_me;
        dbms_output.put_line('这个员工的编号为:'||var_me.id||',名称为:'||var_me.last_name||',他的月薪为:'||var_me.salary);
    end loop;
    close s_emp_c;
end;
/

通过游标 获取 多个表中的数据

查询s_dept 中的name字段 和 s_region中的name字段 ,通过游标进行操作

set serveroutput on;

declare
    cursor dr_c is select d.name dn,r.name rn from s_dept d,s_region r where d.region_id=r.id;

    var_dr dr_c%rowtype;
begin
    open dr_c;

    fetch dr_c into var_dr;

    dbms_output.put_line('部门名称:'||var_dr.dn||',地区名称:'||var_dr.rn);

    close dr_c;
end;
/

游标属性

使用一些属性, 进行循环遍历游标操作

1.  游标名称%found  

    如果游标提取到了新数据 , 则返回true , 否则返回false .

2.  游标名称%notfound

    如果游标提取不到新数据 , 则返回true , 提取到了数据返回false

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

上面的两个属性, 想要使用, 必须满足两个前提条件: 

    1.  游标 必须 是打开状态 ! 否则出现非法游标操作

    2.  游标 必须 执行过 fetch (游标在结果集中任意一行) , 否则返回null

   通过游标获取s_emp表格中的(id,last_name,salary), 获取所有行

declare
    cursor myemp is select id,last_name,salary from s_emp;
    var_me myemp%rowtype;
begin
    open myemp;
    loop
        fetch myemp into var_me;
        exit when myemp%notfound;
        dbms_output.put_line('员工编号:'||var_me.id||',姓名:'||var_me.last_name||',员工月薪:'||var_me.salary);
    end loop;
    close myemp;
end;

智能循环遍历游标

for循环 , 在进行游标的迭代时 , 

    会自动定义变量, 
    自动打开游标, 
    自动提取数据, 
    自动关闭游标 .


使用for循环 获取s_emp表格中的id,last_name,salary

declare
    cursor myemp is select id,last_name name,salary from s_emp;
begin
    for var_me in myemp loop
            /*
                在这个循环中 , var_me就是每次循环迭代时的 每一行的数据
            */
        dbms_output.put_line('id='||var_me.id||', name='||var_me.name||', 月薪='||var_me.salary);
    end loop;
end;
/

带参数的游标

一个游标在定义时 , 
可以设计,在打开时需要传递参数 , 这个参数是 可以在select语句中使用的

格式:

cursor 游标名称(参数列表) is select语句
1.  参数的类型不能使用长度修饰 , 可以通过%type传递类型

2.  参数传递的时机:
    在打开游标时 传入 ,
    例如:
    open 游标名称(参数);


根据用户输入的id , 查询一个员工的信息(id,last_name_salary)
set serveroutput on;
declare
    cursor myemp(var_id number) is select id,last_name name,salary from s_emp where id=var_id;

    var_me myemp%rowtype;
    var_input number;
begin
    var_input :=&请输入您要查询的员工的id;
    open myemp(var_input);
    fetch myemp into var_me;
    dbms_output.put_line(var_me.name);
    close myemp;
end;
/

使用智能循环时 ,如何传递参数

根据用户输入的id , 查询员工编号大于用户输入的员工编号的员工的信息(id,last_name_salary)
set serveroutput on;
declare
    cursor myemp(var_id number) is select id,last_name name,salary from s_emp where id>var_id;

    var_input number;
begin
    var_input :=&请输入员工的id;

    for  var_me in myemp(var_input) loop

        dbms_output.put_line('id='||var_me.id); 

    end loop;

end;
/

参考游标 ref cursor

概念: 

    游标对应的select语句, 不必在申明区就指定, 可以在打开游标时 指定sql语句

语法格式:

    1.  申明区定义一个参考游标的类型

        type 参考游标类型名称 is ref cursor;

    2.  定义一个变量

        变量名称 参考游标类型;

    3.  打开游标, 并关联select语句

        open 游标变量名称 for 'select语句';

例子:

根据用户输入的id , 查询员工编号大于用户输入的员工编号的员工的信息

之前进行游标的遍历时 ,我们通过游标的名称%rowtype获取类型

在参考游标中 时无法这样去做的, 因为在申明区, 游标还不知道自己的行类型
set serveroutput on;
declare
    type mc is ref cursor;
    var_mc mc;
    -- 这个字符串 , 就是用来做sql查询语句的
    var_select varchar2(3000);
    var_input number;

    type myemp is record(
        id  s_emp.id%type,
        salary s_emp.salary%type
    );
    var_me myemp;
begin
    var_input:=&请输入员工id;
    var_select:='select id,salary from s_emp where id>'||var_input;
    open var_mc for var_select;

    loop
        fetch var_mc into var_me;
        exit when var_mc%notfound;
        dbms_output.put_line('员工的id:'||var_me.id||',员工的薪资:'||var_me.salary);
    end loop;
end;
/