Oracle預存程序,遊標使用

Oracle預存程序:

文法：
CREATE [OR REPLACE] PROCEDURE procedure_name
(arg1 [mode1] datatype1,arg2 [mode2] datatype2,...)
IS [AS]
PL/SQL BLOCK;
mode用於指定輸入輸出參數：IN為輸入參數，OUT為輸出參數，當為輸入參數時可以省去IN,OUT不能省去。
如果沒有輸入輸出參數，可以省去
(arg1 [mode1] datatype1,arg2 [mode2] datatype2,...)這一行

一個帶有輸入，輸出參數的簡單的例子

CREATE OR REPLACE PROCEDURE proc_in_out_test(var_empno NUMBER, var_ename OUT VARCHAR2) IS BEGIN SELECT ename INTO var_ename FROM emp WHERE empno = var_empno; EXCEPTION WHEN no_data_found THEN raise_application_error(-20000, '該員工不存在'); END;

調用該過程

 SQL> var vempno number; SQL> var vename varchar2(10); SQL> exec :vempno:=7934; SQL> exec proc_in_out_test(:vempno,:vename); PL/SQL procedure successfully completed vename --------- MILLER SQL> exec :vempno:=7935; SQL> exec proc_in_out_test(:vempno,:vename); begin proc_in_out_test(:vempno,:vename); end; ORA-20000: 該員工不存在 ORA-06512: 在 "SCOTT.PROC_IN_OUT_TEST", line 7 ORA-06512: 在 line 1

 

顯示遊標文法：

CURSOR cursor_name IS select_statement

一個簡單的例子：

DECLARE v_empname emp.ename%TYPE; v_job emp.job%TYPE; v_deptno emp.deptno%TYPE; CURSOR emp_test IS --聲明遊標 SELECT ename, job FROM emp WHERE deptno = v_deptno; BEGIN v_deptno := 10; OPEN emp_test; --開啟遊標 --迴圈遊標 LOOP FETCH emp_test INTO v_empname, v_job; --取值 EXIT WHEN emp_test%NOTFOUND; --當沒有記錄時退出迴圈 dbms_output.put_line('empname=' || v_empname || ',job=' || v_job); END LOOP; CLOSE emp_test; END; --遊標的屬性有：%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT; --%FOUND:已檢索到記錄時，返回true --%NOTFOUNRD：檢索不到記錄時，返回true --%ISOPEN:遊標已開啟時返回true --%ROWCOUNT:代表檢索的記錄數，從1開始

 參數化遊標只是聲明方式和開啟遊標時有些不同

CURSOR emp_test2(p_deptno emp.deptno%TYPE) IS --聲明遊標
       SELECT ename, job FROM emp WHERE deptno = p_deptno;

 OPEN emp_test2(10); --開啟遊標

 

遊標變數：

定義遊標變更類型
TYPE type_name IS REF CURSOR[RETURN return_type];

CREATE OR REPLACE PROCEDURE cursorvariabletest(p_table IN VARCHAR2) IS TYPE t_emp_dept IS REF CURSOR; --定義遊標變數類型 v_cursorvar t_emp_dept; --聲明遊標變數 v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_job emp.job%TYPE; v_dname dept.dname%TYPE; v_loc dept.loc%TYPE; BEGIN IF p_table = 'emp' THEN v_empno := 7369; OPEN v_cursorvar FOR SELECT ename, job FROM emp WHERE empno = v_empno; --開啟遊標變數 ELSE IF p_table = 'dept' THEN OPEN v_cursorvar FOR SELECT dname, loc FROM dept; --開啟遊標變數 ELSE raise_application_error(-20000, '請輸入emp或dept!'); END IF; END IF; LOOP IF p_table = 'emp' THEN FETCH v_cursorvar INTO v_ename, v_job; EXIT WHEN v_cursorvar%NOTFOUND; dbms_output.put_line('ename=' || v_ename || ',job=' || v_job); ELSE FETCH v_cursorvar INTO v_dname, v_loc; EXIT WHEN v_cursorvar%NOTFOUND; dbms_output.put_line('dname=' || v_dname || ',loc=' || v_loc); END IF; END LOOP; CLOSE v_cursorvar; --關閉遊標變數 END;

約束與無約束的遊標變數
無約束的遊標變數包含特定的傳回型別（type,rowtype,record等），查詢的挑選清單必須匹配遊標的傳回型別，否則會出現預

定義的ROWTYPE_MISMATCH異常。約束的遊標變數沒有Return子句。