PostgreSQL 游标处理

简单循环处理I

此写法是先取一条数据，然后判断循环条件

--清空日志表.
Test=#truncatetablelog_table;
TRUNCATETABLE

Test=#select*fromtest_main;
id|value
----+-------
1|ONE
2|TWO
4|FOUR
(3行记录)




CREATEORREPLACEFUNCTIONTestCursor()RETURNSvoidAS
$$
DECLARE
--定义游标.
c_test_mainCURSORFOR
SELECTid,valueFROMtest_main;
--定义存储数据的变量.
v_idINT;
v_valueVARCHAR(10);
BEGIN
--打开游标.
OPENc_test_main;
--填充数据.
FETCHc_test_mainINTOv_id,v_value;

--假如检索到了数据，才处理.
whilefoundloop
INSERTINTOlog_tableVALUES(v_value);
	--填充下一条数据.
FETCHc_test_mainINTOv_id,v_value;
endloop;

--关闭游标
CLOSEc_test_main;
END;
$$
LANGUAGEplpgsql;




Test=#selectTestCursor();
testcursor
------------

(1行记录)


Test=#select*fromlog_table;
log_text
----------
ONE
TWO
FOUR
(3行记录)

如果SQL语句中，列的数量很多，那么定义很多的变量，对应没一列。
开发、维护成本都会比较高。
这种情况下，可以采用表名%rowtype的方式，只定义一个变量来进行处理.

--清空日志表.
Test=#truncatetablelog_table;
TRUNCATETABLE


CREATEORREPLACEFUNCTIONTestCursor()RETURNSvoidAS
$$
DECLARE
--定义游标.
c_test_mainCURSORFOR
SELECTid,valueFROMtest_mainORDERBYidDESC;
--定义存储数据的变量.
v_main_datatest_main%ROWTYPE;

BEGIN
--打开游标.
OPENc_test_main;
--填充数据.
FETCHc_test_mainINTOv_main_data;

--假如检索到了数据，才处理.
whilefoundloop
INSERTINTOlog_tableVALUES(v_main_data.value);
	--填充下一条数据.
FETCHc_test_mainINTOv_main_data;
endloop;

--关闭游标
CLOSEc_test_main;
END;
$$
LANGUAGEplpgsql;


Test=#selectTestCursor();
testcursor
------------

(1行记录)


Test=#select*fromlog_table;
log_text
----------
FOUR
TWO
ONE
(3行记录)

简单循环处理II

此写法是先开始循环，然后取数据，然后判断条件

--清空日志表.
Test=#truncatetablelog_table;
TRUNCATETABLE

Test=#select*fromtest_main;
id|value
----+-------
1|ONE
2|TWO
4|FOUR
(3行记录)




CREATEORREPLACEFUNCTIONTestCursor2()RETURNSvoidAS
$$
DECLARE
--定义游标.
c_test_mainCURSORFOR
SELECTid,valueFROMtest_main;
--定义存储数据的变量.
v_idINT;
v_valueVARCHAR(10);
BEGIN
--打开游标.
OPENc_test_main;

LOOP
--填充数据.
FETCHc_test_mainINTOv_id,v_value;
--假如没有检索到数据，结束循环处理
ExitwhenNOTfound;
	--游标每一行的实际操作.
INSERTINTOlog_tableVALUES(v_value);
ENDLOOP;

--关闭游标
CLOSEc_test_main;
END;
$$
LANGUAGEplpgsql;




Test=#selectTestCursor2();
testcursor2
-------------

(1行记录)


Test=#select*fromlog_table;
log_text
----------
ONE
TWO
FOUR
(3行记录)

支持来回滚动的游标

--清空日志表.
Test=#truncatetablelog_table;
TRUNCATETABLE


--为达到测试效果，多加几行测试数据.
INSERTINTOtest_main(id,value)VALUES(11,'ONEONE');
INSERTINTOtest_main(id,value)VALUES(12,'ONETWO');
INSERTINTOtest_main(id,value)VALUES(13,'ONETHREE');

Test=#SELECTid,valueFROMtest_mainORDERBYid;
id|value
----+----------
1|ONE1
2|TWO1
4|FOUR1
11|ONEONE
12|ONETWO
13|ONETHREE
(6行记录)


CREATEORREPLACEFUNCTIONTestCursorScroll()RETURNSvoidAS
$$
DECLARE
--定义游标.
c_test_mainSCROLLCURSORFOR
SELECTid,valueFROMtest_mainORDERBYid;

--定义存储数据的变量.
v_main_datatest_main%ROWTYPE;

BEGIN
--打开游标.
OPENc_test_main;

--填充数据.
FETCHFIRSTFROMc_test_mainINTOv_main_data;
INSERTINTOlog_tableVALUES('游标第一行:'||v_main_data.value);

--填充数据.
FETCHLASTFROMc_test_mainINTOv_main_data;
INSERTINTOlog_tableVALUES('游标最末行:'||v_main_data.value);

--填充数据.
FETCHABSOLUTE3FROMc_test_mainINTOv_main_data;
INSERTINTOlog_tableVALUES('游标绝对第3行:'||v_main_data.value);

--填充数据.
FETCHRELATIVE2FROMc_test_mainINTOv_main_data;
INSERTINTOlog_tableVALUES('游标相对第2行:'||v_main_data.value);

--填充数据.
FETCHPRIORFROMc_test_mainINTOv_main_data;
INSERTINTOlog_tableVALUES('游标上一行:'||v_main_data.value);

--填充数据.
FETCHNEXTFROMc_test_mainINTOv_main_data;
INSERTINTOlog_tableVALUES('游标下一行:'||v_main_data.value);

--关闭游标
CLOSEc_test_main;
END;
$$
LANGUAGEplpgsql;


Test=#selectTestCursorScroll();
testcursorscroll
------------------

(1行记录)


Test=#select*fromlog_table;
log_text
----------------------
游标第一行:ONE1
游标最末行:ONETHREE
游标绝对第3行:FOUR1
游标相对第2行:ONETWO
游标上一行:ONEONE
游标下一行:ONETWO
(6行记录)

带参数的游标

这里同时需要使用前面外键约束的test_sub表

--清空日志表.
Test=#truncatetablelog_table;
TRUNCATETABLE





CREATEORREPLACEFUNCTIONTestCursorMainSub()RETURNSvoidAS
$$
DECLARE
--定义游标(主表).
c_test_mainCURSORFOR
SELECTid,valueFROMtest_main;
--保存主表游标数据的变量
v_main_datatest_main%ROWTYPE;

--定义游标(子表).
c_test_subCURSOR(p_main_idINT)IS
SELECTid,main_id,value
FROMtest_sub
WHEREmain_id=p_main_id;
--保存子表游标数据的变量
v_sub_datatest_sub%ROWTYPE;
BEGIN

--打开游标(主表).
OPENc_test_main;

--开始循环处理(主表).
LOOP
--填充数据(主表).
FETCHc_test_mainINTOv_main_data;
--假如没有检索到(主表)数据，结束循环处理
ExitwhenNOTfound;

--主表每一行的实际操作.
INSERTINTOlog_tableVALUES('Main:'||v_main_data.value);

--打开游标(子表).
OPENc_test_sub(v_main_data.id);

--开始循环处理(子表).
LOOP
--填充数据(子表).
FETCHc_test_subINTOv_sub_data;
--假如没有检索到(主表)数据，结束循环处理
ExitwhenNOTfound;

--子表每一行的实际操作.
INSERTINTOlog_tableVALUES('-->Sub:'||v_sub_data.value);
ENDLOOP;
--关闭游标(子表).
CLOSEc_test_sub;
ENDLOOP;

--关闭游标(主表).
CLOSEc_test_main;
END;
$$
LANGUAGEplpgsql;



Test=#selectTestCursorMainSub();
testcursormainsub
-------------------

(1行记录)


Test=#select*fromlog_table;
log_text
-----------------
Main:ONE
-->Sub:ONEONE
Main:TWO
-->Sub:TWOTWO
Main:FOUR
-->Sub:FOURFOUR
(6行记录)

用于更新的游标

CREATEORREPLACEFUNCTIONTestUpdateCursor()RETURNSvoidAS
$$
DECLARE
--定义游标.
c_test_mainCURSORFOR
SELECTid,valueFROMtest_mainFORUPDATE;
--定义存储数据的变量.
v_main_datatest_main%ROWTYPE;

BEGIN
--打开游标.
OPENc_test_main;

LOOP
--填充数据.
FETCHc_test_mainINTOv_main_data;
--假如没有检索到数据，结束循环处理
ExitwhenNOTfound;
	
	--游标每一行的实际操作.

	--更新数据.
UPDATE
test_main
SET
value=value||'1'
WHERE
CURRENTOFc_test_main;

ENDLOOP;


--关闭游标
CLOSEc_test_main;
END;
$$
LANGUAGEplpgsql;



Test=#selectTestUpdateCursor();
testupdatecursor
------------------

(1行记录)


Test=#select*fromtest_main;
id|value
----+-------
1|ONE1
2|TWO1
4|FOUR1
(3行记录)

FOR的使用

--清空日志表.
Test=#truncatetablelog_table;
TRUNCATETABLE



CREATEORREPLACEFUNCTIONTestCursorFor()RETURNSvoidAS
$$
DECLARE
--定义存储数据的变量.
v_main_dataRECORD;
BEGIN

FORv_main_dataINSELECTid,valueFROMtest_mainLOOP
	--每一行的实际操作.
INSERTINTOlog_tableVALUES(v_main_data.value);
ENDLOOP;

END;
$$
LANGUAGEplpgsql;




Test=#SELECTTestCursorFor();
testcursorfor
---------------

(1行记录)


Test=#select*fromlog_table;
log_text
----------
ONE1
TWO1
FOUR1
(3行记录)