自定义函数
用户定义函数是存储在数据库中的代码块,可以把值返回到调用程序。调用时如同系统函数一样。
函数的参数类型:
参数类型 | 概述 |
---|---|
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 | 表示参数既可以传值也可以被赋值。 |
存储过程的优点
- 过程在服务器端运行,执行速度快。
- 过程执行一次后代码就驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译代码执行,提高了系统性能。
- 确保数据库的安全。可以不授权用户直接访问应用程序中的一些表,而是授权用户执行访问这些表的过程。非表的授权用户除非通过过程,否则就不能访问这些表。
- 自动完成需要预先执行的任务。过程可以在系统启动时自动执行,而不必在系统启动后再进行手工操作,大大方便了用户的使用,可以自动完成一些需要预先执行的任务。
存储过程的定义
以计算某专业总学分大于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;
函数与存储过程的区别
相同点:
- .创建语法结构相似,都可以携带多个传入参数和传出参数。
- 执行一次后代码就驻留在高速缓冲存储器,可以多次调用。
不同点:
- 存储过程定义关键字用procedure,函数定义用function。
- 存储过程中不能用return返回值,但函数中可以,而且函数中必须有return子句。
- 执行方式略有不同。存储过程的执行方式有两种( 1.execute 2.begin和end),函数不仅能使用这两种方式,还能当做表达式使用在SQL语句中。例如内置函数max(),min()。