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

上面我们使用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;）。

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

总结

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

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

到此这篇关于Oracle存储过程与函数的详细使用的文章就介绍到这了,更多相关Oracle存储过程与函数内容请搜索

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » Oracle存储过程与函数的详细使用教程

Oracle存储过程与函数的详细使用教程