PL/SQL游标总结

游标：指向处理SQL语句的环境区域的指针或句柄

-----|-1 静态游标

       |---1.1 隐式游标

       |          处理：INSERT,DELETE,UPDATE及返回一行的SELECT语句

       |          操作：由PL/SQL自动定义并完成打开与关闭动作

       |          属性：%FOUND,%NOTFOUND,%ISOPEN,%ROWCOUNT

       |---1.2 显示游标

       |          处理：返回多行的SELECT语句

       |          操作：1.2.1 显示推进循环

       |                                --声明游标

       |                                --打开游标

       |                                --推进游标

       |                                --关闭游标

       |                     1.2.2 自动推进循环

       |                                --声明游标

       |                                --游标的FOR循环

       |          属性：%FOUND,%NOTFOUND,%ISOPEN,%ROWCOUNT

-----|-2 游标变量

       |    定义游标变量类型            ：TYPE t_curRef IS REF CURSOR;

       |    声明游标变量类型的变量：v_cur t_curRef;

       |    打开游标变量类型的变量：OPEN v_cur FOR select-statment;

       |    推进游标变量类型的变量：FETCH v_cur INTO list-of-var;

       |    关闭游标变量类型的变量：CLOSE v_cur;

 

 

举例说明：

CREATE TABLE test_employee
(
    emp_num  NUMBER DEFAULT 0 NOT NULL,
    emp_name VARCHAR2(50) DEFAULT '' NOT NULL,
    -- 1:男 2:女
    emp_sex  CHARACTER(1) DEFAULT '1' NOT NULL,
    emp_age  NUMBER,
    emp_lvl  NUMBER
);

ALTER TABLE test_employee ADD PRIMARY KEY ( emp_num );

INSERT INTO test_employee VALUES (10001, 'TOM'  , '1', 25, 3);
INSERT INTO test_employee VALUES (10002, 'KITTY', '2', 26, 3);
INSERT INTO test_employee VALUES (10003, 'TONY' , '1', 30, 4);
INSERT INTO test_employee VALUES (10004, 'JACKY', '1', 35, 5);
INSERT INTO test_employee VALUES (10005, 'LILY' , '2', 23, 2);
INSERT INTO test_employee VALUES (10006, 'JANEY', '2', 22, 2);
INSERT INTO test_employee VALUES (10007, 'MARK' , '1', 31, 4);
INSERT INTO test_employee VALUES (10008, 'TOMAS', '1', 33, 4);
INSERT INTO test_employee VALUES (10009, 'LEE'  , '1', 28, 3);
INSERT INTO test_employee VALUES (10010, 'YUNG' , '1', 28, 3);

 

 

-- 1.1隐式游标
DECLARE
    v_empNum NUMBER;
    v_empNam VARCHAR2(50);
BEGIN
    v_empNum := 10009;
    SELECT emp_name INTO v_empNam FROM test_employee WHERE emp_num = v_empNum;
    dbms_output.put_line('当前行:'||v_empNum||'''s name is '||v_empNam);
    dbms_output.put_line('********************************');
END;

 

 

-- 1.2.1显示推进循环

DECLARE
    v_emp1 test_employee%ROWTYPE;
    CURSOR c_test1 IS SELECT * FROM test_employee;
    v_empNum test_employee.emp_num%TYPE;
    v_empNam test_employee.emp_name%TYPE;
    CURSOR c_test2 IS SELECT emp_num,emp_name FROM test_employee;
BEGIN
    OPEN c_test1;
    LOOP
        FETCH c_test1 INTO v_emp1;
        EXIT WHEN c_test1%NOTFOUND;
        dbms_output.put_line('当前行:'||v_emp1.emp_num||'''s name is '||v_emp1.emp_name);
    END LOOP;
    dbms_output.put_line('共查询到'||c_test1%ROWCOUNT||'行');
    dbms_output.put_line('********************************');
    CLOSE c_test1;
    OPEN c_test2;
    LOOP
        FETCH c_test2 INTO v_empNum,v_empNam;
        EXIT WHEN c_test2%NOTFOUND;
        dbms_output.put_line('当前行:'||v_empNum||'''s name is '||v_empNam);
    END LOOP;
    dbms_output.put_line('共查询到'||c_test2%ROWCOUNT||'行');
    dbms_output.put_line('********************************');
    CLOSE c_test2;
END;

 

 

-- 1.2.2自动推进循环

DECLARE
    CURSOR c_test3 IS SELECT emp_num,emp_name FROM test_employee;
BEGIN
    FOR v_emp3 IN c_test3 LOOP
        dbms_output.put_line('当前行:'||v_emp3.emp_num||'''s name is '||v_emp3.emp_name);
    END LOOP;
    dbms_output.put_line('********************************');
END;

 

 

-- 2 游标变量
DECLARE
    TYPE t_curRef IS REF CURSOR;
    c_test4 t_curRef;
    v_emp4 test_employee%ROWTYPE;
BEGIN
    OPEN c_test4 FOR SELECT * FROM test_employee WHERE ROWNUM <= 5;
    LOOP
        FETCH c_test4 INTO v_emp4;
        EXIT WHEN c_test4%NOTFOUND;
        dbms_output.put_line('当前行:'||v_emp4.emp_num||'''s name is '||v_emp4.emp_name);
    END LOOP;
    dbms_output.put_line('共查询到'||c_test4%ROWCOUNT||'行');
    dbms_output.put_line('********************************');
    CLOSE c_test4;
END;