Oracle遊標總結

標籤：

1.聲明遊標

declareteacher_id number(5);teacher_name varchar2(5);teacher_title varchar2(50);teacher_sex char(1);cursor teacher_cur is    select tid,tiname,title,sex from teachers where tid<117;

2.開啟遊標

open 遊標名;

declareteacher_id number(5);teacher_name varchar2(5);teacher_title varchar2(50);teacher_sex char(1);cursor teacher_cur is    select tid,tiname,title,sex from teachers where tid<117;open teacher_cur;

3.提取遊標

fetch 遊標名 into 變數列表

declareteacher_id number(5);teacher_name varchar2(5);teacher_title varchar2(50);teacher_sex char(1);cursor teacher_cur is    select tid,tiname,title,sex from teachers where tid<117;open teacher_cur;fetch teacher_cur into tid,tinme,title,sex;

4.關閉遊標

close 遊標名

declare    teacher_id number(5);    teacher_name varchar2(5);    teacher_title varchar2(50);    teacher_sex char(1);cursor teacher_cur is    select tid,tiname,title,sex from teachers where tid<117;open teacher_cur;    fetch teacher_cur into tid,tinme,title,sex;    Loop         EXIT WHEN NOT teacher_cur%FUND;        IF teaher_sex = ‘M‘ THEN            INSERT INTO MALE_TEACHERS(TID,TNAME,TITLE) VALUES();            ELSE             INSERT INTO FEMALE_TEACHERS(TID,TNAME,TITLE) VALUES(teacher_id,teacher_name,teacher_title);        END IF;    FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex;    END LOOP;CLOSE teacher_cur;END;

使用顯示遊標:

1):使用前用遊標名%ISOPEN檢查開啟狀態,只有值為TRUE是才可使用

2):使用遊標每次都要用%NOTFUND,%FUND確認是否返回成功

3):提取遊標時對應變數個數一致

4):必須關閉遊標釋放資源

 

1.%fund是否找到有效行,是則為true 否則是false

open teacher_cur;fetch teacher_cur into teacher_id,teacher_name,teacher_title,teacher sex;loop exit when not teacher_cur%found;end loop

　　SQL%fund

delete from teachers    where tid=teacher_id;if SQL%found then    insert into success values(tid);else    insert into fail values(tid);end if;

2.%NOTFOUND

OPEN teacher_cur;FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex;LOOP    EXIT WHEN teacher_cur%NOTFOUND;END LOOP

　　SQL%NOTFOUND

DELETE FROM TEACHERS    WHERE TID = teacher_id;IF SQL%NOTFOUND THEN    INSERT INTO FALL VALUES(TID);ELSE    INSERT INTO SUCCESS VALUES(TID);END IF;

3.%ROWCOUNT

該屬性記錄了遊標抽取過的記錄行數,也可以理解為當前遊標所在的行號,這個屬性在迴圈判斷中有效

LOOP    FETCH teacher_our INTO teacher_id,teacher_name,teacher_title,teacher_sex;    EXIT WHEN teacher_cur%ROWCOUNT=10;--只抽取10條記錄    ...    END LOOP;

用FOR語句控制遊標的迴圈,系統隱含的定義了一個資料類型為%ROWCOUNT的記錄,作為迴圈計數器,並將隱士的開啟和關閉遊標

FOR teacher_record in teacher_cur LOOP --teacher_record作為記錄名,隱含的代開遊標teacher_cur    INSERT INTO TEMP TEACHERS(TID,TNAME,TITLE,SEX) VALUES(teacher_record,tid,teacher_record,tname,teacher_record,title,teacher_record.sex);END LOOP

4.%ISOPEN

... ...

5.參數話遊標:

DECLARE--定義遊標是帶上參數CURSOR_ID    CURSOR teacher_cur(CURSOR_id NUMBER) IS        SELECT TNAME,TITLE,SEX FROM TEACHERS WHERE TID=CURSOR_id;--使用參數BEGIN    OPEN teacher_cur(my_tid);--帶上參數    LOOP        FETCH teacher_cur INTO teacher_name,teacher_title,teacher_sex;        EXIT WHEN teacher_cur%NOTFOUND;        ...    END LOOP;    CLOSE teacher_cur;END;

 

Oracle遊標總結