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子句。