游标的概念:
游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下,需要把数据从存放在磁盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高,否则频繁的磁盘数据交换会降低效率。
游标有两种类型:显式游标和隐式游标。
在前述程序中用到的SELECT...INTO...查询语句,一次只能从数据库中提取一行数据,对于这种形式的查询和DML操作,系统都会使用一个隐式游标,名字固定叫sql。
但是如果要提取多行数据,就要由程序员定义一个显式游标,并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。
游标一旦打开,数据就从数据库中传送到游标变量中,然后应用程序再从游标变量中分解出需要的数据,并进行处理。
隐式游标
如前所述,DML操作和单行SELECT语句会使用隐式游标,它们是:
* 插入操作:INSERT。
* 更新操作:UPDATE。
* 删除操作:DELETE。
* 单行查询操作:SELECT ... INTO ...。
当系统使用一个隐式游标时,可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程。隐式游标可以使用名字SQL来访问,但要注意,通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后,立即使用SQL游标名来访问属性。游标的属性有四种,如下所示。
隐式游标的属性 返回值类型 意 义
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真,结束后为假
--单行查询应用: DECLARE no kw_cc.CC_NO%TYPE; mc kw_cc.CC_MC%TYPE; kssj kw_cc.CC_KSSJ%TYPE; BEGIN --查询结果只能为一条,否则报错 SELECT CC_NO,CC_MC,CC_KSSJ INTO no,mc,kssj from kw_cc WHERE CC_KSSJ between to_date('2018/10/27 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018/10/27 12:00:00','yyyy-mm-dd hh24:mi:ss'); IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('编号:'||no||'|名称:'||mc||'|时间:'||kssj); --COMMIT; ELSE DBMS_OUTPUT.PUT_LINE('查询时间失败!'); END IF; END; sql editor执行结果: 编号:001|名称:10月27号上午|时间:27-OCT-18
select查询结果:
显式游标
游标的定义和操作
游标的使用分成以下4个步骤。
1.声明游标
在DECLEAR部分按以下格式声明游标:
CURSOR 游标名[(参数1 数据类型[,参数2 数据类型...])]
IS SELECT语句;
参数是可选部分,所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数,则必须在打开游标时传递相应的实际参数。
SELECT语句是对表或视图的查询语句,甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句,但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
2.打开游标
在可执行部分,按以下格式打开游标:
OPEN 游标名[(实际参数1[,实际参数2...])];
打开游标时,SELECT语句的查询结果就被传送到了游标工作区。
3.提取数据
在可执行部分,按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。
FETCH 游标名 INTO 变量名1[,变量名2...];
或
FETCH 游标名 INTO 记录变量;
游标打开后有一个指针指向数据区,FETCH语句一次返回指针所指的一行数据,要返回多行需重复执行,可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。
下面对这两种格式进行说明:
第一种格式中的变量名是用来从游标中接收数据的变量,需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
第二种格式一次将一行数据取到记录变量中,需要使用%ROWTYPE事先定义记录变量,这种形式使用起来比较方便,不必分别定义和使用多个变量。
定义记录变量的方法如下:
变量名 表名|游标名%ROWTYPE;
其中的表必须存在,游标名也必须先定义。
4.关闭游标
CLOSE 游标名;
显式游标打开后,必须显式地关闭。游标一旦关闭,游标占用的资源就被释放,游标变成无效,必须重新打开才能使用。
以下是使用显式游标的一个简单练习。
for循环使用游标: DECLARE cursor c IS select KMID from KW_KM; v_kmid c%ROWTYPE; BEGIN FOR v_kmid IN c loop DBMS_OUTPUT.PUT_LINE('ss'||v_kmid.KMID); end loop; end; 执行结果: ss16 ss17 ss18 ... ss31
loop循环多查询select传参给多查询select: DECLARE cursor q is select A.KMID ee from KW_KM A LEFT JOIN KW_CC B on A.KM_CCID=B.CCID WHERE B.CC_KSSJ IN(SELECT CC_KSSJ from kw_cc WHERE CC_KSSJ between to_date('2018/10/27 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018/10/27 12:00:00','yyyy-mm-dd hh24:mi:ss')); --显示游标的参数自定义声明,注意参数类型要一致 cursor w(kmid NUMBER) is SELECT count(*) total,a.KSKM_ISWJ 标记, ( CASE a.KSKM_ISWJ WHEN 1 THEN '违纪' ELSE '未违纪' END )是否违纪 from QY_KD b LEFT JOIN KW_KSKM a on a.KSKM_KDID=b.KDID LEFT JOIN QY_XQ c on b.kd_xqid=c.xqid --传参给查询条件 WHERE a.KSKM_KMID in(kmid) GROUP BY a.KSKM_ISWJ; --以游标为参照的变量 v_kmid q%ROWTYPE; v_wj w%ROWTYPE; --最终打印的变量 num_wj NUMBER; num_nowj NUMBER; BEGIN --因为要作为最后打印的参数,所以要以'成员变量'的类型初始化 num_wj :=0; num_nowj :=0; open q; loop FETCH q INTO v_kmid; EXIT WHEN q%notfound; --打开游标q,将游标q中的ee字段值传给游标w open w(v_kmid.ee); --loop循环 loop --将游标中的一条记录传给以游标为参照的变量 FETCH w INTO v_wj; --遍历从游标获取的数据是否为空 EXIT WHEN w%notfound; --循环统计违纪和不违纪总数 IF v_wj.标记=1 THEN num_wj :=num_wj+v_wj.total; ELSE num_nowj :=num_nowj+v_wj.total; END IF; end loop; close w; end loop; close q; DBMS_OUTPUT.PUT_LINE('违纪总数:'|| num_wj||' 是否违纪:'||v_wj.是否违纪); DBMS_OUTPUT.PUT_LINE('非总数:'|| num_nowj||' 是否违纪:'||v_wj.是否违纪); END; sql editor执行结果: 违纪总数:1 是否违纪:未违纪 非总数:16816 是否违纪:未违纪
--存储过程中使用显示游标进行多查询结果select CREATE OR REPLACE PROCEDURE "multi_select" AS cursor q is select A.KMID ee from KW_KM A LEFT JOIN KW_CC B on A.KM_CCID=B.CCID WHERE B.CC_KSSJ IN(SELECT CC_KSSJ from kw_cc WHERE CC_KSSJ between to_date('2018/10/27 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018/10/27 12:00:00','yyyy-mm-dd hh24:mi:ss')); --显示游标的参数自定义声明,注意参数类型要一致 cursor w(kmid NUMBER) is SELECT count(*) total,a.KSKM_ISWJ 标记, ( CASE a.KSKM_ISWJ WHEN 1 THEN '违纪' ELSE '未违纪' END )是否违纪 from QY_KD b LEFT JOIN KW_KSKM a on a.KSKM_KDID=b.KDID LEFT JOIN QY_XQ c on b.kd_xqid=c.xqid --传参给查询条件 WHERE a.KSKM_KMID in(kmid) GROUP BY a.KSKM_ISWJ; --以游标为参照的变量 v_kmid q%ROWTYPE; v_wj w%ROWTYPE; --最终打印的变量 num_wj NUMBER; num_nowj NUMBER; BEGIN --因为要作为最后打印的参数,所以要以'成员变量'的类型初始化 num_wj :=0; num_nowj :=0; open q; loop FETCH q INTO v_kmid; EXIT WHEN q%notfound; --打开游标q,将游标q中的ee字段值传给游标w open w(v_kmid.ee); --loop循环 loop --将游标中的一条记录传给以游标为参照的变量 FETCH w INTO v_wj; --遍历从游标获取的数据是否为空 EXIT WHEN w%notfound; --循环统计违纪和不违纪总数 IF v_wj.标记=1 THEN num_wj :=num_wj+v_wj.total; ELSE num_nowj :=num_nowj+v_wj.total; END IF; end loop; close w; end loop; close q; DBMS_OUTPUT.PUT_LINE('违纪总数:'|| num_wj||' 是否违纪:'||v_wj.是否违纪); DBMS_OUTPUT.PUT_LINE('非总数:'|| num_nowj||' 是否违纪:'||v_wj.是否违纪); END; pl/sql脚本脚本执行结果: 违纪总数:1 是否违纪:未违纪 非总数:16816 是否违纪:未违纪
--while循环多select DECLARE cursor q is select A.KMID ee from KW_KM A LEFT JOIN KW_CC B on A.KM_CCID=B.CCID WHERE B.CC_KSSJ IN(SELECT CC_KSSJ from kw_cc WHERE CC_KSSJ between to_date('2018/10/27 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018/10/27 12:00:00','yyyy-mm-dd hh24:mi:ss')); --显示游标的参数自定义声明,注意参数类型要一致 cursor w(kmid NUMBER) is SELECT count(*) total,a.KSKM_ISWJ 标记, ( CASE a.KSKM_ISWJ WHEN 1 THEN '违纪' ELSE '未违纪' END )是否违纪 from QY_KD b LEFT JOIN KW_KSKM a on a.KSKM_KDID=b.KDID LEFT JOIN QY_XQ c on b.kd_xqid=c.xqid --传参给查询条件 WHERE a.KSKM_KMID in(kmid) GROUP BY a.KSKM_ISWJ; --以游标为参照的变量 v_kmid q%ROWTYPE; v_wj w%ROWTYPE; --最终打印的变量 num_wj NUMBER; num_nowj NUMBER; BEGIN --因为要作为最后打印的参数,所以要以'成员变量'的类型初始化 num_wj :=0; num_nowj :=0; open q; FETCH q INTO v_kmid; while q% found loop --打开游标q,将游标q中的ee字段值传给游标w open w(v_kmid.ee); FETCH w INTO v_wj; while w% found loop --循环统计违纪和不违纪总数 IF v_wj.标记=1 THEN num_wj :=num_wj+v_wj.total; ELSE num_nowj :=num_nowj+v_wj.total; END IF; --取下一条数据继续循环 FETCH w INTO v_wj; end loop; close w; --取下一条数据继续循环 FETCH q INTO v_kmid; end loop; close q; DBMS_OUTPUT.PUT_LINE('违纪总数:'|| num_wj||' 是否违纪:'||v_wj.是否违纪); DBMS_OUTPUT.PUT_LINE('非总数:'|| num_nowj||' 是否违纪:'||v_wj.是否违纪); END; sql语句执行结果: 违纪总数:1 是否违纪:未违纪 非总数:16816 是否违纪:未违纪
--sql editor中多select查询多记录for循环传值 DECLARE cursor q is select A.KMID ee from KW_KM A LEFT JOIN KW_CC B on A.KM_CCID=B.CCID WHERE B.CC_KSSJ IN(SELECT CC_KSSJ from kw_cc WHERE CC_KSSJ between to_date('2018/10/27 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018/10/27 12:00:00','yyyy-mm-dd hh24:mi:ss')); --显示游标的参数自定义声明,注意参数类型要一致 cursor w(kmid NUMBER) is SELECT count(*) total,a.KSKM_ISWJ 标记, ( CASE a.KSKM_ISWJ WHEN 1 THEN '违纪' ELSE '未违纪' END )是否违纪 from QY_KD b LEFT JOIN KW_KSKM a on a.KSKM_KDID=b.KDID LEFT JOIN QY_XQ c on b.kd_xqid=c.xqid --传参给查询条件 WHERE a.KSKM_KMID in(kmid) GROUP BY a.KSKM_ISWJ; --以游标为参照的变量 v_kmid q%ROWTYPE; v_wj w%ROWTYPE; --最终打印的变量 num_wj NUMBER; num_nowj NUMBER; BEGIN --因为要作为最后打印的参数,所以要以'成员变量'的类型初始化 num_wj :=0; num_nowj :=0; --1.自动open和close游标 --2.自动定义了一个记录类型及声明该类型的变量,并自动fetch数据到这个变量中,无需要在循环外进行声明,无需要为其指定数据类型。 --3.它应该是一个记录类型,具体的结构是由游标决定的,这个变量的作用域仅仅是在循环体内。 FOR v_kmid1 IN q loop FOR v_wj1 IN w(v_kmid1.ee) loop IF v_wj1.标记=1 THEN num_wj :=num_wj+v_wj1.total; ELSE num_nowj :=num_nowj+v_wj1.total; END IF; end loop; END loop; DBMS_OUTPUT.PUT_LINE('违纪总数:'|| num_wj); DBMS_OUTPUT.PUT_LINE('非总数:'|| num_nowj); END; sql editor执行结果: 违纪总数:1 非总数:16816
Oracle的游标使用方法 三种循环:https://blog.csdn.net/s630730701/article/details/68938837
--显示游标->从键盘获取输入值作为参数 SQL>set serveroutput on --开启打印开关 SQL> accept starttime prompt '请输入开始时间'; 请输入开始时间'2018/10/27 00:00:00' SQL> accept endtime prompt '请输入结束时间'; 请输入结束时间'2018/10/27 12:00:00' SQL> DECLARE --v_starttime键盘输入:'2018/10/27 00:00:00' --v_endtime键盘输入:'2018/10/27 12:00:00' v_starttime VARCHAR2(100); --变量要先声明才能引用 v_endtime VARCHAR2(100); --变量要先声明才能引用 cursor q is select A.KMID ee from KW_KM A LEFT JOIN KW_CC B on A.KM_CCID=B.CCID WHERE B.CC_KSSJ IN(SELECT CC_KSSJ from kw_cc WHERE CC_KSSJ between to_date(v_starttime,'yyyy-mm-dd hh24:mi:ss') and to_date(v_endtime,'yyyy-mm-dd hh24:mi:ss')); --显示游标的参数自定义声明,注意参数类型要一致 cursor w(kmid NUMBER) is SELECT count(*) total,a.KSKM_ISWJ 标记, ( CASE a.KSKM_ISWJ WHEN 1 THEN '违纪' ELSE '未违纪' END )是否违纪 from QY_KD b LEFT JOIN KW_KSKM a on a.KSKM_KDID=b.KDID LEFT JOIN QY_XQ c on b.kd_xqid=c.xqid --传参给查询条件 WHERE a.KSKM_KMID in(kmid) GROUP BY a.KSKM_ISWJ; --以游标为参照的变量 v_kmid q%ROWTYPE; v_wj w%ROWTYPE; --最终打印的变量 num_wj NUMBER; num_nowj NUMBER; BEGIN v_starttime :=&starttime; v_endtime :=&endtime; --因为要作为最后打印的参数,所以要以'成员变量'的类型初始化 num_wj :=0; num_nowj :=0; open q; loop FETCH q INTO v_kmid; EXIT WHEN q%notfound; --打开游标q,将游标q中的ee字段值传给游标w open w(v_kmid.ee); --loop循环 loop --将游标中的一条记录传给以游标为参照的变量 FETCH w INTO v_wj; --遍历从游标获取的数据是否为空 EXIT WHEN w%notfound; --循环统计违纪和不违纪总数 IF v_wj.标记=1 THEN num_wj :=num_wj+v_wj.total; ELSE num_nowj :=num_nowj+v_wj.total; END IF; end loop; close w; end loop; close q; DBMS_OUTPUT.PUT_LINE('违纪总数:'|| num_wj||' 是否违纪:'||v_wj.是否违纪); DBMS_OUTPUT.PUT_LINE('非总数:'|| num_nowj||' 是否违纪:'||v_wj.是否违纪); END; / old 26: v_starttime :=&starttime; new 26: v_starttime :='2018/10/27 00:00:00'; old 27: v_endtime :=&endtime; new 27: v_endtime :='2018/10/27 12:00:00'; ????????:1 ????????:?????? ??????:16816 ????????:?????? PL/SQL procedure successfully completed. SQL> 【注】sqlplus仅支持unicode编码,其他编码时中文会显示为乱码。
https://blog.csdn.net/paul50060049/article/details/51724019
https://www.cnblogs.com/xiaoliu66007/p/7495753.html
https://m.jb51.net/article/127299.htm