Oracle中的游標和函數詳解
1.游標
游標是一種 PL/SQL 控制結構；可以對 SQL 語句的處理進行顯示控制，便于對表的行數據
逐條進行處理。 游標并不是一個數據庫對象，只是存留在內存中。
操作步驟：
聲明游標
打開游標
取出結果，此時的結果取出的是一行數據
關閉游標 到底那種類型可以把一行的數據都裝進來
此時使用 ROWTYPE 類型，此類型表示可以把一行的數據都裝進來。 例如：查詢雇員編號為 7369 的信息（肯定是一行信息）。
例：查詢雇員編號為 7369 的信息（肯定是一行信息）。
|
1
2
3
4
5
6
7
8
9
|
DECLARE eno emp.empno%TYPE ; empInfo emp%ROWTYPE ; BEGIN eno := &en ; SELECT * INTO empInfo FROM emp WHERE empno=eno ; DBMS_OUTPUT.put_line('雇員編號：'||empInfo.empno) ; DBMS_OUTPUT.put_line('雇員姓名：'||empInfo.ename) ; END ; |
使用 for 循環操作游標（比較常用）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DECLARE -- 聲明游標 CURSOR mycur IS SELECT * FROM emp where empno=-1; empInfo emp%ROWTYPE ; cou NUMBER ; BEGIN -- 游標操作使用循環，但是在操作之前必須先將游標打開 FOR empInfo IN mycur LOOP --ROWCOUNT 對游標所操作的行數進行記錄 cou := mycur%ROWCOUNT ; DBMS_OUTPUT.put_line(cou||'雇員編號：'||empInfo.empno) ; DBMS_OUTPUT.put_line(cou||'雇員姓名：'||empInfo.ename) ; END LOOP ; END ; |
我們可以看到游標FOR循環確實很好的簡化了游標的開發，我們不在需要open、fetch和close語句，不在需要用%FOUND屬性檢測是否到最后一條記錄，這一切Oracle隱式的幫我們完成了。
編寫第一個游標，輸出全部的信息。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
DECLARE -- 聲明游標 CURSOR mycur IS SELECT * FROM emp ; -- 相當于一個List (EmpPo) empInfo emp%ROWTYPE ; BEGIN -- 游標操作使用循環，但是在操作之前必須先將游標打開 OPEN mycur ; -- 使游標向下一行 FETCH mycur INTO empInfo ; -- 判斷此行是否有數據被發現 WHILE (mycur%FOUND) LOOP DBMS_OUTPUT.put_line('雇員編號：'||empInfo.empno) ; DBMS_OUTPUT.put_line('雇員姓名：'||empInfo.ename) ; -- 修改游標，繼續向下 FETCH mycur INTO empInfo ; END LOOP ; END ; |
也可以使用另外一種方式循環游標：LOOP…END LOOP;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DECLARE -- 聲明游標 CURSOR mycur IS SELECT * FROM emp ; empInfo emp%ROWTYPE ; BEGIN -- 游標操作使用循環，但是在操作之前必須先將游標打開 OPEN mycur ; LOOP -- 使游標向下一行 FETCH mycur INTO empInfo ; EXIT WHEN mycur%NOTFOUND ; DBMS_OUTPUT.put_line('雇員編號：'||empInfo.empno) ; DBMS_OUTPUT.put_line('雇員姓名：'||empInfo.ename) ; END LOOP ; END ; |
注意 1： 在打開游標之前最好先判斷游標是否已經是打開的。
通過 ISOPEN 判斷
格式：
|
1
2
3
4
5
|
游標%ISOPEN IF mycur%ISOPEN THENnull ; ELSEOPEN mycur ; END IF ; |
注意 2：可以使用 ROWCOUNT 對游標所操作的行數進行記錄。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
DECLARE -- 聲明游標 CURSOR mycur IS SELECT * FROM emp ; empInfo emp%ROWTYPE ; cou NUMBER ; BEGIN -- 游標操作使用循環，但是在操作之前必須先將游標打開 IF mycur%ISOPEN THEN null ; ELSE OPEN mycur ; END IF ; LOOP -- 使游標向下一行 FETCH mycur INTO empInfo ; EXIT WHEN mycur%NOTFOUND ; cou := mycur%ROWCOUNT ; DBMS_OUTPUT.put_line(cou||'雇員編號：'||empInfo.empno) ; DBMS_OUTPUT.put_line(cou||'雇員姓名：'||empInfo.ename) ; END LOOP ; END ; |
2.函數
函數就是一個有返回值的過程。
定義一個函數：此函數可以根據雇員的編號查詢出雇員的年薪
|
1
2
3
4
5
6
|
CREATE OR REPLACE FUNCTION myfun(eno emp.empno%TYPE) RETURN NUMBER AS rsal NUMBER ; BEGIN SELECT (sal+nvl(comm,0))*12 INTO rsal FROM emp WHERE empno=eno ; RETURN rsal ; END ; |
直接寫 SQL 語句，調用此函數：
|
1
|
SELECT myfun(7369) FROM dual ; |
寫一個函數 輸入一個員工名字，判斷該名字在員工表中是否存在。存在返回 1，不存在返回 0
|
1
2
3
4
5
6
|
create or replace function empfun(en emp.ename%type) return number as is_exist number; begin select count(*) into is_exist from emp where ename=upper(en); return is_exist; end; |
感謝閱讀，希望能幫助到大家，謝謝大家對本站的支持！
原文鏈接：http://blog.csdn.net/smile_from_2015/article/details/53635276
