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行记录)
