PL/SQL之八存储过程

本文主要是介绍PL/SQL之八存储过程，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1.概述

1.1模块化代码的好处：复用性好、易于管理。

1.2语句块结构：所有模块类型而言，语句块结构相同。首先是语句块头部、模块名称、参数列表；声明部分由变量、游标等组成；可执行部分；异常处理部分。

2.创建存储过程

2.1创建语法

CREATE OR REPLACE PROCEDURE name
[(parmeter[,parmeter])]
AS[Local declarations]
BEGINexecutable statements;
[Exceptionexception handlers;]
END [name];

示例：

create or replace procedure pro_test
is
begindbms_output.put_line('当前系统时间：'||sysdate);
exceptionwhen others thendbms_output.put_line('发生错误！');
end pro_test;

2.2如何查询存储过程名称及源代码

--查找存储过程名称、状态
SELECT * FROM user_objects a where a.OBJECT_TYPE='PROCEDURE';--查找存储过程源代码
SELECT to_char(line,99)||'>',text
FROM USER_SOURCE b where b.name='PRO_TEST';

2.3存储过程传入和传出参数

参数在调用环境和服务器之间来回传递值，参数传递模式有三种：IN、OUT、IN OUT。通过参数传递模式来说明参数是传入还是传出的。

三种参数类型：

IN：给程序传值常量、字面值或表达式在程序的默认模式下不能修改；只读的值。

OUT：从程序返回值，不能赋予默认值，必须是变量，只有当程序执行成功时才能赋值。只写的值。

IN OUT：传入值，也从程序返回；必须是变量。

匹配形参和实参方法：位置表示法和命名表示法。位置表示法是根据位置序号来标识；命名表示法是显示的标识方法，使用符号‘=>’。

示例：

--1.创建存储过程
create or replace procedure pro_test1(p_c1 in varchar2,p_c2 out varchar2,p_c3 in out varchar2)
is
beginp_c2:=p_c1;if(p_c1='cde') thenp_c3:='cde';elsep_c3:=p_c1; end if;
end;--2.调用存储过程
declarev_c2 varchar2(20);v_inOut varchar2(20):='cdef';
beginpro_test1(p_c1 => 'abc',p_c2 => :v_c2,    --out模式的参数，p_c3 => :v_inOut  --in out 模式参数，必须以变量来传递或接受。);
end;

3.特别说明

3.1定义变量的规范变量以v_开头，常量以c_开头,游标以_cursor结尾，异常以e_开头。

3.2存储过程防止 select into中取不到值，在存储过程中添加异常处理部分，‘when no_data_found then dbms_output.put_line('no data found1')’;

3.3CURSOR,打开后，如果不fetch，是不能用游标的四大属性（SQL%rowcount,SQL%Found,SQL%notfound,SQL%isopen）来判断游标中是否有数据。原因：打开游标后，第一行成为当前行，这时只存在一个指向当前行的指针，它的数据是空的，无法判定；fetch时，行数据拷贝到变量或记录变量，并且指针指向下一行，这一行成为当前行。每fetch一次，指针向下移一行。

3.4.存储过程中捕获异常可以使用内置函数SQLCODE()，SQLERRM()来获得系统错误号和错误信息。

3.5.隐式游标的三种属性详细说胆

SQL%FOUND：未执行任何DML语句之前，值为null；DML语句执行后，SQL%Found值变化如下：

true:Insert\Delete\Update\Select into至少返回一行。

SQL%RowCount:没有执行任何DML语句之前，SQL%RowCount值为null;对于 SELECT INTO语句执行成功返回1；Insert into 时返回1；没有成功返回0，同时产生一个no_data_found异常。

SQL%IsOpen:它是布尔值，如果游标打开，则为true;游标关闭，则为false。对于隐式游标 SQL%Isopen一直是close,隐式游标在DML语句执行时打开，结束时关闭。

4.练习

4.1显示当天是周几、当前登录用户名和系统时间；显示当前用户模式下有效和无效的存储过程数量。

create or replace procedure pro_test2
isv_day varchar2(20);v_user varchar2(20);v_time varchar2(20);type rec_procedure is record  (rec_statue varchar2(20),rec_num number);v_rec_procdure rec_procedure;cursor c_objects is     select status,count(*) num  from user_OBJECTS a where a.OBJECT_TYPE='PROCEDURE' group by status;  
beginselect to_char(sysdate,'DAY'),user,to_char(sysdate,'HH24:mi:ss') into v_day,v_user,v_time  from dual;dbms_output.put_line('当前星期几：'||v_day||',当前用户:'||v_user||',当前时间：'||v_time);for r_object in c_objects loopv_rec_procdure.rec_statue:=r_object.STATUS;v_rec_procdure.rec_num:=r_object.NUM;dbms_output.put_line(v_rec_procdure.rec_statue||'的个数为:'||v_rec_procdure.rec_num);end loop;   
end;

4.2 实现查找某个科室代码的相关信息

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;