Orcla数据库自定义函数和存储过程

自定义函数

用户定义函数是存储在数据库中的代码块，可以把值返回到调用程序。调用时如同系统函数一样。

函数的参数类型：

参数类型	概述
IN	表示输入给函数的参数。
OUT	表示参数在函数中被赋值，可以传给函数调用程序。
IN OUT	表示参数既可以传值也可以被赋值。

函数的特点
　1. 自定义函数必须有返回值
　2. 不能执行DML语句，只能执行DQL语句
　3. Orcla数据中函数的参数out可以将值输出。
　4.可以在SQL语句中使用，例如使用max函数。
函数的定义
以计算某门课程全体学生的平均成绩为例子：

//关键字OR REPLACE 为当函数存在时，修改函数而不创建函数
CREATE OR REPLACE FUNCTION average (cnum IN char)//括号内为参数与参数类型
//定义返回值类型
	RETURN number
//AS部分为声明变量部分  AS也可以为IS
AS
	//定义返回值变量
	avger number;				
BEGIN
	//函数体部分，INTO是将AVG（CJ）的值赋给avger变量
	SELECT AVG(CJ) INTO avger
		FROM CJB
		WHERE KCH=cnum
		GROUP BY KCH;
	//返回值的类型
	RETURN(avger);
END;

函数的调用
语法格式：variable_name:=function_name[(实参1,实参2,…)]
函数的调用类似于内建函数。例如max()、min()。

//定义变量块
DECLARE 
	//定义变量，保存函数返回的值
	man_average number;
//函数调用
BEGIN
	//调用函数
	man_average:=average('计算机组成原理');
	//以字符串的形式输出结果
	DBMS_OUTPUT.PUT_LINE(TO_CHAR(man_average));
END;

函数的删除

// average为要删除的函数名称。
DROP FUNCTION average;

存储过程

存储过程是一条一条或多条SQL语句的集合。
存储过程参数类型
与函数的参数类型相同。

参数类型	概述
IN	表示输入给函数的参数。
OUT	表示参数在函数中被赋值，可以传给函数调用程序。
IN OUT	表示参数既可以传值也可以被赋值。

存储过程的优点

1. 过程在服务器端运行，执行速度快。
2. 过程执行一次后代码就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译代码执行，提高了系统性能。
3. 确保数据库的安全。可以不授权用户直接访问应用程序中的一些表，而是授权用户执行访问这些表的过程。非表的授权用户除非通过过程，否则就不能访问这些表。
4. 自动完成需要预先执行的任务。过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。

存储过程的定义
以计算某专业总学分大于40的人数为例

//关键字OR REPLACE 为当函数存在时，修改函数而不创建函数
CREATE OR REPLACE PROCEDURE count_grade
	( zy IN char, person_num OUT number )//zy为输入的专业，person_num为输入的人数
//AS为定义变量块
AS
//BEGIN为执行块
BEGIN
	SELECT COUNT(XH)
		INTO person_num //将COUNT(XH)查询到的值赋值给
		FROM XSB
		WHERE ZY=zy AND ZXF>40;
END;

存储过程的调用

DECLARE
	//定义变量
	man_num number;
BEGIN
	count_grade('计算机信息与技术', man_num);//在存储过程中将值赋给了变量man_num
	DBMS_OUTPUT.PUT_LINE(man_num);//输出man_num的值
END;

函数与存储过程的区别

相同点：

1. .创建语法结构相似，都可以携带多个传入参数和传出参数。
2. 执行一次后代码就驻留在高速缓冲存储器，可以多次调用。

不同点：

1. 存储过程定义关键字用procedure，函数定义用function。
2. 存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。
3. 执行方式略有不同。存储过程的执行方式有两种（ 1.execute 2.begin和end），函数不仅能使用这两种方式，还能当做表达式使用在SQL语句中。例如内置函数max(),min()。