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;

执行存储过程

exec pro_add<10,'张三','五班'>;

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

上面我们使用in是显示学生的姓名，现在我们要返回学生的姓名就要使用out，语法如下

create or replace procedure pro_show1<
	var_id in student.id%type,	--定义in参数
	var_name out student.name%type	--定义out参数
> is
	no_result exception;
begin
	select name init var_name from student where id = var_id;	--取值
exception
	when no_data_found then
		dbms_output.put_line<'没有对应此编号的学生'>;	--错误处理
end pro_show1;

调用含有out参数的存储过程需要提前声明一个相应类型的变量，然后用来接收。

variable var_name varchar2<10>;
exec pro_show1<10,:var_name>;

在调用的时候，使用“：”后面紧跟变量名。

4、存储过程的查询

存储过程的查询需要使用到数据字典user_source,语法如下

select distinct name from user_source where type=upper('procedure');

上面这个语句查询当前用户下所有的存储过程的名字。

此外，我们还可以查询存储过程的内容，查询语句如下所示。

select text from user_source where name = upper('pro_aa');

二、函数

上面的存储过程有输入参数和输出参数，但是没有返回值，函数和存储过程非常类似，也是可以存储在oracle数据库中的PL/SQL代码块，但是有返回值，可以把经常使用的功能定义为一个函数，就像系统自带的函数（例如大小写转换，求绝对值等函数）一样使用。

1、函数的创建

函数的创建的基本语法格式如下所示。

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

其中的参数说明如下。

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

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

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

4、函数的返回值类型是必选项。

5、局部变量声明中所定义的变量只在该函数中有效。

6、局部变量声明、程序语句序列和异常处理语句序列定义以及使用PL/SQL块。

在函数的主程序中，必须使用return语句返回最终的函数值，并且返回值的数据类型要和声明的时候说明的类型一样。

## 2、隐式游标的创建与使用
>和显示游标不同，隐式游标是系统自动创建的，用于处理DML语句（例如insert、update、delete等指令）的执行结果或者select查询返回的单行数据，这时隐式游标是指向缓冲区的指针。使用时不需要进行声明、打开和关闭，因此不需要open、fetch、close这样的操作指令。隐式游标也有前述介绍的4种属性，使用时需要在属性前面加上隐式游标的默认名称SQL，因此隐式游标也叫SQL游标。

###  1、将student表中张三的学生年龄增加10岁，然后使用隐式游标的%rowcount属性输出涉及的员工数量
```go
begin
	update student set age=age+10	--年龄增加10
	where name = '张三';
	if sql%notfound then	--是否有符合条件的记录
		dbms_output.put_line<'没有符合条件的学生'>;
	else
		dbms_output.put_line<'符合条件的学生数量为：' || sql%rowcount>;
	end if;
end;

2、函数的调用与删除

函数的调用基本上与系统内置函数的调用方法相同。可以直接在SQL plus中使用，也可以在存储过程中使用。

函数的删除与存储过程的删除类似，语法如下：

drop function 函数名

3、函数的使用

1、创建一个函数，如果是偶数则计算其平方，如果是奇数则计算其平方根

create or replace function fun_cal
	<var_num number>	--声明函数参数
	return number		--声明函数返回类型
is
i int:=2;
begin
	if mod<var_num,2>=0 then	--判断奇偶性
		return power<var_num,i>;	--返回平方
	flse
		return round<sqrt<var_num>,2>;	--返回平方根
	end if;
end fun_cal;

4、函数的查询

在实际使用中经常会需要查询数据库中已有的函数或者某一个函数的内容，使用的方法和存储过程类似，也需要使用到数据字典user_source，使用的查询语句如下所示。

select distinct name from user_source where type=upper('function');

上面这个语句查询当前用户下所有的用户定义的函数名字。

此外，我们还可以查询函数的内容，查询语句如下所示。

select text from user_source where name=upper('fun_cal') and type=upper('function')

补充：存储过程与存储函数的区别和联系

相同点：1.创建语法结构相似，都可以携带多个传入参数和传出参数。

　　　　2.都是一次编译，多次执行。

不同点：1.存储过程定义关键字用procedure，函数定义用function。

　　　　2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。

　　　　3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。

总结：如果只有一个返回值，用存储函数，否则，一般用存储过程。

总结

这里的相关内容还没有整理完毕，文章后面持续更新，建议收藏。

文章中涉及到的命令大家一定要像我一样每个都敲几遍，只有在敲的过程中才能发现自己对命令是否真正的掌握了。