【Oracle】PL/SQL游标

超、凢脫俗 2023-10-05 09:26 34阅读 0赞

#### 目录 ####

*   *  基本原理
     *  显式游标
     *  隐式游标
     *  游标属性
     *   *   *  标题是否找到游标（\`%FOUND\`）
             *  是否没找到游标（\`%NOTFOUND\`）
             *  游标行数（\`%ROWCOUNT\`）
             *  游标是否打开（\`%ISOPEN\`）
             *  参数化游标
     *  游标变量
     *  效率分析

### 基本原理 ###

在PL/SQL块中执行语句时，Oracle会在内存空间中为其分配上下文区（Context Area），即一个缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用程序提供了一种对具有多行数据查询结果集中的没一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

游标分为显式游标和隐式游标两种。显式游标由用户声明和操作的一种游标；隐式游标是Oracle为所有数据操作语句（包括只返回单行数据的查询语句）自动声明和操作的一种游标。在每个用户会话中，可以同时打开多个游标，其数量由数据库的初始化参数文件中的`OPEN CURSOR`参数定义。

### 显式游标 ###

显式游标的处理包括声明游标、打开游标、提取游标、关闭游标4个步骤，其操作如下：

-- 定义变量用于承载从游标中获取的值
    DECLARE
    	V_ID VARCHAR2(37);
    	V_FEE NUMBER;
    	V_TRANS_TIME DATE;
    
    -- 定义游标
    CURSOR cur_getRecord IS
    SELECT ID, FEE, TRANS_TIME FROM TB_TRANS_RECORD；
    
    BEGIN
    	-- 打开游标
    	OPEN cur_getRecord;
    	
    	-- 提取游标
    	FETCH cur_getRecord INTO V_ID, V_FEE, V_TRANS_TIME;
    
    	LOOP
    		-- 检测游标是否结束,若游标结束则终止循环
    		EXIT WHEN NOT cur_getRecord%FOUND;
    
    		-- 打印获取的值
    		DBMS_OUTPUT.PUT_LINE(V_ID || ',' || V_FEE || ',' || V_TRANS_TIME);
    		
    		FETCH cur_getRecord INTO V_ID, V_FEE, V_TRANS_TIME;
    	END LOOP;
    
    	-- 关闭游标
    	CLOSE cur_getRecord;
    END;

1.  显式游标声明部分必须在`DECLARE`中，因为游标名是一个PL/SQL标识符，所以必须在被引用前声明。而且对于任何的`SELECT`语句都是合法的，包括连接和`UNION`或’MINUS’子句的语句。
2.  打开游标时，需要执行以下操作：检查联编变量的取值；根据联编变量的取值，确定活动集；将活动集的指针指向第一行。

> 打开一个已经被打开的游标是合法的，在第二次执行`OPEN`语句前，PL/SQL将在重新打开该游标前隐式的执行一条`CLOSE`语句。也可一次打开多个游标。

1.  一旦关闭了游标，也就关闭了`SELECT`操作，释放占用的内存区。如果再从游标提取数据就是非法的。同理关闭一个已关闭的游标也是非法的。

使用显式游标时，需要注意以下事项：

*  使用前须用`%ISOPEN`检查其打开状态，只有此值为`TRUE`的游标才可使用，否则需要先将游标打开。
 *  使用游标过程中，每次都要用`%FOUND`或`%NOTFOUND`属性检查是否返回成功，即是否还有要操作的行。
 *  将游标中行取到变量中时，对应变量个数和数据类型都必须完全一致。
 *  使用完游标必须将其关闭，以释放对应内存资源。

### 隐式游标 ###

如果在PL/SQL中用`SELECT`语句进行操作，则隐式地使用了游标，也就是隐式游标，这种游标无需定义，也不需要打开和关闭。例如：

BEGIN
    	SELECT FEE, TRANS_TIME INTO V_FEE, V_TRANS_TIME FROM TB_TRANS_RECORD
    END;

> 对于每个隐式游标来说，必须要有一个`INTO`语句，因此使用隐式游标的`SELECT`语句必须只选中一行数据或只产生一行数据

### 游标属性 ###

无论是显式游标还是隐式游标，均有`%ISOPEN`,`%FOUND`,`%NOTFOUND`,`%ROWCOUNT`四种属性。它们描述与游标操作相关的DML语句的执行情况。游标属性只能用在PL/SQL的流程控制语句内，而不能用在SQL语句中。

##### 标题是否找到游标（`%FOUND`） #####

该属性表示当前游标是否指向有效的一行，若是则为TRUE，否则为FALSE。检查此属性可以判断是否结束游标的使用。

LOOP
    	-- 检测游标是否结束,若游标结束则终止循环
    	EXIT WHEN NOT cur_getRecord%FOUND;
    	......
    END LOOP;

在隐式游标中此属性的引用方法是`SQL%FOUND`。

BEGIN
    	UPDATE TB_TRANS_RECORD SET TRANS_TIME = SYSDATE() WHERE ID = '1';
    	-- 获取SQL语句执行结果,若执行成功则执行打印语句
    	IF SQL%FOUND THEN
    		DBMS_OUTPUT.PUT_LINE('SUCCESS');
    	ELSE
    		DBMS_OUTPUT.PUT_LINE('FAILURE');
    	END IF;
    END;

##### 是否没找到游标（`%NOTFOUND`） #####

该属性与`%FOUND`类似，结果相反。

##### 游标行数（`%ROWCOUNT`） #####

该属性记录了游标抽取过的记录行数，也可以理解为当前游标所在的行号。这个属性在循环判断中在特定位置中断循环。

LOOP
    	-- 当执行到第10条记录时终止循环
    	EXIT WHEN cur_getRecord%ROWCOUNT = 10;
    	......
    END LOOP;

还可以使用`FOR`语句控制游标循环，此时系统隐式的包含了一个数据类型为`%ROWCOUNT`的记录，作为循环计数器，并将隐式打开和关闭循环。

DECLARE
    	TYPE TYPE_TRANS_RECORD IS RECORD (
    		ID TB_TRANS_RECORD.ID%TYPE,
    		FEE TB_TRANS_RECORD.FEE%TYPE,
    		TRANS_TIME TB_TRANS_RECORD.TRANS_TIME%TYPE
    	);
    	
    	-- 定义游标
    	CURSOR cur_getRecord IS
    	SELECT ID, FEE, TRANS_TIME FROM TB_TRANS_RECORD；
    
    BEGIN
    	FOR TYPE_TRANS_RECORD IN cur_getRecord LOOP
    		DBMS_OUTPUT.PUT_LINE(TYPE_TRANS_RECORD .ID || ',' || TYPE_TRANS_RECORD .FEE || ',' || TYPE_TRANS_RECORD .TRANS_TIME);
    	END LOOP;
    END;

##### 游标是否打开（`%ISOPEN`） #####

该属性表示游标是否处于打开状态，在实际应用中，使用一个游标前，第一步往往是检查它的开启状态，防止运行过程中出现异常。

IF cur_getRecord%ISOPEN THEN
    	FETCH cur_getRecord INTO V_ID, V_FEE, V_TRANS_TIME;
    ELSE
    	OPEN cur_getRecord;
    	FETCH cur_getRecord INTO V_ID, V_FEE, V_TRANS_TIME;
    END IF;

在隐式游标中此属性的引用方式是`SQL%ISOPEN`。隐式游标的`SQL%ISOPEN`值总是TRUE。因为隐式游标在使用中不用打开或关闭游标。

##### 参数化游标 #####

在定义游标时可以带上参数，使得在使用游标时，根据不同的参数选中不同的数据，达到动态使用的目的。

DECLARE
    	V_FEE TB_TRANS_RECORD.FEE%TYPE;
    
    	CURSOR cur_getfee(transTime DATE) IS
    	SELECT FEE FROM TB_TRANS_RECORD WHERE TRANS_TIME >= transTime;
    
    BEGIN
    	-- 打开游标的同时带入参数
    	OPEN cur_getfee(TO_DATE('2020-08-01', 'YYYY-MM-DD'));
    	FETCH cur_getfee INTO V_FEE;
    	
    	LOOP
    		EXIT WHEN cur_getfee%NOTFOUND;
    
    		DBMS_OUTPUT.PUT_LINE('交易金额为' || V_FEE);
    
    		FETCH cur_getfee INTO V_FEE;
    	END LOOP;
    
    	CLOSE cur_getfee;
    END;

参数的不同对于游标中存储的结果集也不同，执行结果也不同。

### 游标变量 ###

无论是显式游标或隐式游标都与一个明确的SQL语句关联，并且在编译该块的时候此语句是可知的，是静态的。而游标变量可以在运行时与不同的语句关联，是动态的。游标变量被用于处理多行的查询结果集。在同一个PL/SQL块中，游标变量不同于特定的查询绑定，而是在打开游标时才确定所对应的查询。因此，游标变量可以依次对应多个查询。

使用游标变量之前，必须先声明，然后在运行时必须为其分配存储空间，因为游标变量是REF类型的变量，类似于指针。

DECLARE
    	-- 定义一个记录类型
    	TYPE TYPE_TRANS_RECORD IS RECORD (
    		FEE TB_TRANS_RECORD.FEE%TYPE
    	);
    	
    	V_TRANS_RECORD TYPE_TRANS_RECORD;
    	
    	-- 定义游标变量,返回类型为定义的记录类型
    	TYPE TYPE_TRANS_RECORD_REF IS REF CURSOR
    	RETURN TYPE_TRANS_RECORD;
    	
    	cur_getfee TYPE_TRANS_RECORD_REF;
    BEGIN
    	-- 打开游标并定义数据集
    	OPEN cur_getfee FOR
    	SELECT FEE FROM TB_TRANS_RECORD;
    	
    	FETCH cur_getfee INTO V_TRANS_RECORD;
    	
    	LOOP
    		DBMS_OUTPUT.PUT_LINE(V_TRANS_RECORD.FEE);
    
    		FETCH cur_getfee INTO V_TRANS_RECORD;
    		
    		IF cur_getfee%NOTFOUND THEN
    			EXIT; 
    		END IF; 
    	END LOOP;
    	
    	CLOSE cur_getfee;
    END;

> 带有返回值的游标在`FOR`之后必须为一个明确定义的`SELECT`语句

上述的游标变量是受限制的，它的返回类型只能是特定类型。而在PL/SQL语言中，还有一种非受限游标变量，它在声明的时候没有`RETURN`子句。一个非受限游标变量可以为任意查询打开。

DECLARE
    	V_ID VARCHAR2(37);
    	V_TABLE_NAME VARCHAR2(128) := 'TB_TRANS_RECORD';
    	V_SQL VARCHAR2(2000);
    	
    	TYPE TYPE_TRANS_RECORD_REF IS REF CURSOR;
    	cur_getfee TYPE_TRANS_RECORD_REF;
    BEGIN
    	-- 打开游标,对于无限制的游标变量可以接一个动态SQL语句
    	V_SQL := 'SELECT ID FROM ' || V_TABLE_NAME;
    	OPEN cur_getfee FOR V_SQL;
    	
    	FETCH cur_getfee INTO V_ID;
    	
    	LOOP
    		DBMS_OUTPUT.PUT_LINE(V_ID);
    		
    		FETCH cur_getfee INTO V_ID;
    
    		IF cur_getfee%NOTFOUND THEN
    			EXIT; 
    		END IF; 
    	END LOOP;
    	
    	CLOSE cur_getfee;
    END;

> 一般使用显式OPEN打开游标后，无法使用`FOR`循环

### 效率分析 ###

一般来说批量处理的效果最好，其次是使用隐式游标，使用单值循环效率最差。

DECLARE
    	V_SQL VARCHAR(2000);
    	-- 定义类型
    	TYPE TYPE_TRANS_RECORD IS RECORD (
    		ID TB_TRANS_RECORD.ID%TYPE,
    		FEE TB_TRANS_RECORD.FEE%TYPE
    	);
    	-- 创建类型的表类型
    	TYPE TYPE_TRANS_RECORD_TB IS TABLE OF TYPE_TRANS_RECORD;
    	-- 实例化数据集
    	V_TRANS_RECORD_TB TYPE_TRANS_RECORD_TB;
    	
    	V_TABLE_NAME VARCHAR2(128) := 'TB_TRANS_RECORD';
    	-- 定义游标变量
    	TYPE TYPE_TRANS_RECORD_REF IS REF CURSOR;
    	CUR_TRANS_RECORD TYPE_TRANS_RECORD_REF;
    BEGIN
    	V_SQL := 'SELECT ID, FEE FROM ' || V_TABLE_NAME;
    	OPEN CUR_TRANS_RECORD FOR V_SQL;
    	-- 以5条数据为一组,按批次处理,大数据量可定义500
    	LOOP
    		-- 将批量结果放入数据集中
    		FETCH CUR_TRANS_RECORD BULK COLLECT INTO V_TRANS_RECORD_TB LIMIT 5;
    		
    		EXIT WHEN V_TRANS_RECORD_TB.COUNT = 0;
    		FOR i IN V_TRANS_RECORD_TB.FIRST..V_TRANS_RECORD_TB.LAST LOOP
    			DBMS_OUTPUT.PUT_LINE(V_TRANS_RECORD_TB(i).ID || ',' || V_TRANS_RECORD_TB(i).FEE);
    		END LOOP;
    		
    	END LOOP;
    	-- 关闭游标
    	CLOSE CUR_TRANS_RECORD;
    END;