Oracle存储过程与函数的详细使用教程-木庄网络博客

本文整理自网络，侵删。

一、存储过程

存储过程是一种命名的PL/SQL数据块，存储在Oracle数据库中，可以被用户调用。存储过程可以包含参数，也可以没有参数，它一般没有返回值。存储过程是事先编译好的代码，再次调用的时候不需再次编译，因此程序的运行效率非常高。

1、存储过程的创建

语法如下

create [or replace] 过程名
[<参数1> inioutin out <参数类型>[默认值|:=初始值]]
[,<参数2> inioutin out <参数类型>[默认值|:=初始值],...]
isias
[局部变量声明]
begin
程序语句序列
[exception]
异常处理语句序列
end 过程名

参数说明如下：

1、or replace 可选参数，表示如果数据库中已经存在要创建的过程，则先把原先过程删除，再重新建立过程，或者说覆盖原先的过程。

2、如果过程中存在参数，则需要在参数后面用“inioutin out”关键字。如果是输入参数，则参数后面用“in”关键字，表示接受外部过程传递来的值；如果是输出参数，则参数后面用“out”关键字，表示此参数将在过程中被复制，并传递给过程体外；如果是“in out” 关键字则表示该参数既具有输入参数特性，又具有输出参数的特性。默认是in参数，即如果不写就默认为in参数。

3、参数类型不能指定长度，只需要给出类型即可。

4、局部变量声明中所定义的变量只在该过程中有效。

5、局部变量声明，程序语句序列和异常处理语句序列定义和使用同上一章PL/SQL块。

2、存储过程的调用及删除

存储过程创建后，以编译的形式存在于oracle数据库中，可以在sql plus中或者pl/sql块中调用。

1、在sql plus中调用存储过程

语法如下：

execute 过程名 [参数序列]

其中execute可以简写成exec。

2、在pl/sql块中调用存储过程

直接把过程名写到其他pl/sql块中即可调用，此时不需使用execute命令。

3、存储过程的删除

存储过程的删除和表的删除类似，基本语法如下所示。

drop procdure 过程名

3、存储过程的使用

1、不带参数的存储过程

1、创建一个存储过程，向student表中插入一条记录

create or replace procedure pro_stu is 
begin
	insert into student<id,name,class> values<10,'张三','五班'>;
	commit;
	dbms_output.put_line<'插入一条新纪录！！！'>;
end pro_stu;

上面存储过程已经成功创建，但是并没有执行，执行语句如下。

exec pro_stu;

上面是exec命令执行，我们也可以在PL/SQL块中直接调用，语法如下。

begin
	pro_stu;
end;

2、带in参数的存储过程
使用in参数可以向存储过程中的程序单元输入数据，在调用的时候提供参数值，被存储过程读取。这种模式是默认的参数模式。下面看一个范例。

2、创建一个存储过程，接收来自外部的数值，在存储过程中判断该数值是否大于零并显示。

create or replace procedure pro_decide<
	var_num in number
> is
begin
	if var_num>=0 then
		dbms_output.put_line<'传递进来的参数大于等于0'>;
	else
		dbms_output.put_line<'传递进来的参数小于0'>;
	end if;
end pro_decide;

执行存储过程

exec pro_decide<3>;

结果显示：
传递进来的参数大于等于0

3、输入一个编号，查询student表中是否有这个编号，如果有则显示对应学生的姓名，如果没有则提示没有对应的学生。

create or replace procedure pro_show<
	var_stuid in student.id%type	--定义in参数
> is
	var_name student.name%type;	--定义存储过程内部变量
	no_result exception;
begin
	select name into var_name from student where id = var_stuid;	--取值
	if sql%found then
		dbms_output.put_line<'所查询的学生姓名是：' || var_name>;	--显示
	end if;
		when no_data_found then
			dbms_output.put_line<'没有对应此编号的学生'>;	--错误处理
	end pro_show;

执行存储过程。

exec pro_show<10>

4、创建一个存储过程，向数据表student中插入一条记录。

create or replace procedure pro_add<
	var_id in number,
	var_name in varchar2,
	var_class in varchar2> is
begin
	insert into student values<var_id,var_name,var_class>;	--插入记录
	commit;
	dbms_output.put_line<'插入一条新纪录！！！'>;
end pro_add;

执行存储过程