※隱式游標
就是不用打出「CURSOR」關鍵字，有四種，參考官網※%ROWCOUNT
DECLARE xxx_count NUMBER; BEGIN SELECT COUNT(*) INTO xxx_count FROM EMP; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); INSERT INTO DEPT VALUES(50, '業務部', '舊金山'); DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); UPDATE DEPT SET DNAME='採購部' WHERE DEPTNO = 50; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); DELETE FROM DEPT WHERE DEPTNO = 50; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); END;
※使用%ROWCOUNT可得知操作的行數，此例的結果是4個1
※%FOUND、%NOTFOUND
DECLARE
BEGIN
UPDATE EMP SET SAL = SAL * 1.2;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('修改了' || SQL%ROWCOUNT || '筆');
ELSE
DBMS_OUTPUT.PUT_LINE('沒有資料被修改');
END IF;
END;
※顯式游標的查詢
就是要明確的打出「CURSOR」關鍵字※和LOOP、WHILE迴圈配合使用
DECLARE
CURSOR xxx_cur IS SELECT * FROM DEPT;
xxx_dept DEPT%ROWTYPE;
BEGIN
IF NOT xxx_cur%ISOPEN THEN
OPEN xxx_cur;
END IF;
/*
FETCH xxx_cur INTO xxx_dept;
WHILE xxx_cur%FOUND LOOP
DBMS_OUTPUT.PUT(xxx_cur%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE('=' || xxx_dept.dname);
FETCH xxx_cur INTO xxx_dept;
END LOOP;
*/
LOOP
FETCH xxx_cur INTO xxx_dept;
EXIT WHEN xxx_cur%NOTFOUND;
DBMS_OUTPUT.PUT(xxx_cur%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE('=' || xxx_dept.dname);
END LOOP;
CLOSE xxx_cur;
END;
※以上是兩種迴圈和游標的使用
※CURSOR xxx_cur IS SELECT * FROM DEPT 是省略寫法，全部是
CURSOR xxx_cur RETURN DEPT%ROWTYPE IS SELECT * FROM DEPT，也就是多了RETURN DEPT%ROWTYPE，因為查的是DEPT，所以預設也回傳DEPT的ROWTYPE，所以可以省略
※和FOR迴圈配合使用
DECLARE
CURSOR xxx_cur IS SELECT * FROM DEPT;
xxx_dept DEPT%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('方法一');
FOR xxx_dept IN xxx_cur LOOP
DBMS_OUTPUT.PUT(xxx_cur%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE('=' || xxx_dept.dname);
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || '方法二');
FOR xxx_dept IN (SELECT * FROM DEPT) LOOP
DBMS_OUTPUT.PUT_LINE(xxx_dept.dname);
END LOOP;
END;
※結果：
方法一
1=ACCOUNTING
2=RESEARCH
3=SALES
4=OPERATIONS
方法二
ACCOUNTING
RESEARCH
SALES
OPERATIONS
※和FOR迴圈配合時，不需要打開和關閉游標，也不用判斷哪時跳離迴圈，一切都交給系統控制，所以大部分的人都使用這一種
※方法一和方法二的差別就在方法二直接將SQL寫進去了，所以也無法使用%ROWCOUNT
※有參數的游標
DECLARE CURSOR xxx_cur(ooo DEPT.DEPTNO%TYPE) IS SELECT * FROM DEPT WHERE DEPTNO = ooo; BEGIN FOR xxx_dept IN xxx_cur(&pk) LOOP DBMS_OUTPUT.PUT(xxx_cur%ROWCOUNT || ' '); DBMS_OUTPUT.PUT(xxx_dept.deptno || ' '); DBMS_OUTPUT.PUT(xxx_dept.dname || ' '); DBMS_OUTPUT.PUT_LINE(xxx_dept.loc); END LOOP; END;
※結果：
1 10 ACCOUNTING NEW YORK
※&pk為使用者輸入的參數值
※LOOP和WHILE要寫在OPEN xxx_cur(&pk)
※注意傳進去的參數不能用來判斷，如果是in的話，網路有用一種子查詢的方式，但我沒試出來，我用的是第26篇的動態SQL解決in的問題
※和陣列配合使用
DECLARE CURSOR xxx_cur IS SELECT * FROM DEPT; TYPE xxx_index IS TABLE OF DEPT%ROWTYPE INDEX BY PLS_INTEGER; xxx xxx_index; BEGIN FOR xxx_dept IN xxx_cur LOOP xxx(xxx_dept.deptno) := xxx_dept; DBMS_OUTPUT.PUT_LINE(xxx(xxx_dept.deptno).dname); END LOOP; END;
※結果：
ACCOUNTING
RESEARCH
SALES
OPERATIONS
※和巢狀表配合使用
DECLARE CURSOR xxx_cur IS SELECT * FROM DEPT; TYPE xxx_nested IS TABLE OF DEPT%ROWTYPE; xxx xxx_nested; BEGIN IF NOT xxx_cur%ISOPEN THEN OPEN xxx_cur; END IF; FETCH xxx_cur BULK COLLECT INTO xxx; CLOSE xxx_cur; FOR i IN xxx.FIRST..xxx.LAST LOOP DBMS_OUTPUT.PUT_LINE(xxx(i).dname); END LOOP; END;
※結果：
ACCOUNTING
RESEARCH
SALES
OPERATIONS
※使用BULK COLLECT一次取出，所以可以馬上關閉游標
※和VARRAY配合使用
DECLARE CURSOR xxx_cur IS SELECT * FROM DEPT; TYPE xxx_varray IS VARRAY(3) OF DEPT%ROWTYPE; xxx xxx_varray; BEGIN IF NOT xxx_cur%ISOPEN THEN OPEN xxx_cur; END IF; FETCH xxx_cur BULK COLLECT INTO xxx LIMIT 3; CLOSE xxx_cur; FOR i IN xxx.FIRST..xxx.LAST LOOP DBMS_OUTPUT.PUT_LINE(xxx(i).dname); END LOOP; END;
※結果：
ACCOUNTING
RESEARCH
SALES
※BULK COLLECT INTO ~~~ LIMIT 可以限制資料量
