PL/SQL之八 存储过程

CREATE OR REPLACE PROCEDURE pro_01(deptCode NUMBER --科室代码，接受某个科室编号)
AS
--标量变量
	v_varchar VARCHAR2(20);
	v_date DATE;
	v_language VARCHAR2(40);
	v_number NUMBER(3):=1;
	v_y_char VARCHAR2(20);
	v_y_date DATE;
	v_deptCode NUMBER(2):=deptCode;
--定义游标
	CURSOR dualCur IS SELECT 'abcde',SYSDATE,USERENV('LANGUAGE') FROM dual;
	CURSOR empCur IS SELECT a.empno,a.ename,a.job,a.mgr,a.hiredate,a.sal,a.comm,a.deptno FROM scott.emp a;
--参数游标，查询某个科室人员信息
	CURSOR s_empCur(v_deptNo NUMBER) IS SELECT empno,ename,job FROM scott.emp WHERE deptno=v_deptNo;
--定义基于游标的 记录变量
	empReocrd empCur%ROWTYPE;
	s_empRec s_empCur%ROWTYPE;

--记录变量 语法格式：变量名 表名|视图名|游标名%RowType
	v_y_empRec scott.emp%ROWTYPE;
--集合变量
	TYPE emp_table_type IS TABLE OF empCur%ROWTYPE INDEX BY BINARY_INTEGER;
	emp_table emp_table_type;
--异常处理
	v_errCode VARCHAR2(20);--错误代码
	v_errMsg VARCHAR2(100);--错误明细
	v_errLine VARCHAR2(200);--错误所在行
BEGINIF NOT dualCur%ISOPEN THENOPEN dualCur;END IF;IF dualCur%ISOPEN THENdbms_output.put_line('游标是否打开：打开');ELSEdbms_output.put_line('游标是否打开：关闭');END IF;FETCH dualCur INTO v_varchar,v_date,v_language;--dbms_output.put_line(dualCur%FOUND);IF dualCur%FOUND THENdbms_output.put_line('游标受影响行数：'||dualCur%Rowcount);dbms_output.put_line('input Char:'||v_varchar||',input date:'||v_date||',input language:'||v_language);ELSEdbms_output.put_line('没有找到数据');END IF ;IF dualCur%ISOPEN THENCLOSE dualCur;END IF;IF dualCur%ISOPEN THENdbms_output.put_line('游标是否打开：打开');ELSEdbms_output.put_line('游标是否打开：关闭');END IF;
--显式游标：基本格式：打开游标、取数、关闭游标 IF NOT empCur%ISOPEN THENOPEN empCur;END IF;LOOPFETCH empCur INTO empReocrd;EXIT WHEN empCur%NOTFOUND;dbms_output.put_line(empReocrd.Empno||empReocrd.Ename||empReocrd.Job||empReocrd.Mgr||empReocrd.Hiredate||empReocrd.Sal||empReocrd.Comm||empReocrd.Deptno);END LOOP;IF empCur%ISOPEN THENCLOSE empCur;END IF;
--显式游标，集合变量使用IF NOT empCur%ISOPEN THENOPEN empCur;END IF; LOOPFETCH empCur INTO emp_table(v_number);EXIT WHEN empCur%NOTFOUND;dbms_output.put_line('姓名：'||emp_table(v_number).ename);END LOOP;IF empCur%ISOPEN THENCLOSE empCur;END IF;--显式游标，带参数游标IF NOT s_empCur%ISOPEN THENOPEN s_empCur(v_deptCode);END IF;FOR s_empRow IN s_empCur LOOPdbms_output.put_line('NO='||s_empRow.Empno||',name='||s_empRow.Ename);END LOOP;--显式游标：for循环格式，这种格式不需要显式的打开游标，不需要显示的取数，不需要显示关闭游标FOR empRow IN empCur LOOPdbms_output.put_line('姓名：'||empRow.Ename||',工作:'||empRow.Job);END LOOP;--隐式游标使用--1)SELECT 'hij',SYSDATE  INTO v_y_char,v_y_date FROM dual;IF SQL%FOUND THENdbms_output.put_line(SQL%ROWCOUNT);dbms_output.put_line('v_y_char:'||v_y_char||',v_y_date:'||v_y_date);END IF ;--2)SELECT * INTO v_y_empRec FROM scott.emp a;LOOPEXIT WHEN SQL%NOTFOUND;dbms_output.put_line(v_y_empRec.Empno||','||v_y_empRec.Ename||'。');END LOOP;EXCEPTIONWHEN OTHERS THENv_errCode   := SQLCODE;v_errMsg    := substr(SQLERRM, 1, 200);v_errLine := SUBSTR(dbms_utility.format_error_backtrace, 1, 200);dbms_output.put_line(v_errCode || '|' || v_errMsg || '|' || v_errLine);
END pro_01;