2014-06-02 BaoXinjian
一、汇总
1. 显示游标
2. 隐身游标
3. 动态游标REF Cursor
4. 游标的四属性
二、分步解析
1. 显示游标
游标从declare、open、fetch、close是一个完整的生命旅程
DECLARE
CURSOR get_wip_cur (c_wip_entity_name IN VARCHAR2)
IS
SELECT wip_entity_name
FROM wip_entities
WHERE wip_entity_name = c_wip_entity_name ;
v_wipentity_name wip_entities .wip_entity_name% TYPE;
BEGIN
OPEN get_wip_cur (‘W19‘);
LOOP
FETCH get_wip_cur INTO v_wipentity_name ;
EXIT WHEN get_wip_cur% NOTFOUND;
DBMS_OUTPUT.put_line (v_wipentity_name );
END LOOP;
CLOSE get_wip_cur ;
END;
2. 隐身游标
隐式cursor当然是相对于显式而言的，就是没有明确的cursor的declare
BEGIN
UPDATE wip_entities
SET wip_entity_name = ‘WIP_ENTITY_001‘
WHERE wip_entity_name LIKE ‘BXJ%‘;
IF SQL%ROWCOUNT = 0
THEN
DBMS_OUTPUT.put_line (‘NO Lines‘ );
END IF;
END;
3. 动态游标REF Cursor
属于动态cursor(直到运行时才知道这条查询)
DECLARE
TYPE RefCur_WIP IS REF CURSOR
RETURN wip_entities% ROWTYPE;
vRefCur_WIP RefCur_WIP ;
vTemp_WIP vRefCur_WIP% ROWTYPE;
BEGIN
OPEN vRefCur_WIP FOR
SELECT *
FROM wip_entities
WHERE created_by = 0;
LOOP
FETCH vRefCur_WIP INTO vTemp_WIP ;
EXIT WHEN vRefCur_WIP% NOTFOUND;
END LOOP;
CLOSE vRefCur_WIP ;
END;
4. 游标的四属性
%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT
Thanks and Regards
