PL/SQL 子程序 -- 函数

最新推荐文章于 2023-05-06 19:38:42 发布

shangboerds

最新推荐文章于 2023-05-06 19:38:42 发布

阅读量1.6k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： oracle plsql sql

本文链接：https://blog.csdn.net/shangboerds/article/details/43028983

Oracle 专栏收录该内容

125 篇文章

订阅专栏

本文详细探讨了Oracle中的PL/SQL函数，包括嵌套函数、独立函数、包函数、重载函数和管道函数的概念及用法。通过函数的重载特性，可以在包函数中实现根据不同参数执行不同操作的功能。同时提到了管道函数的威力，类比Linux管道，展示了其在数据处理中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- Start

子程序其实就是一个有名字的语句块，有两种类型的子程序，存储过程和函数。它们之间的区别是函数有返回值而存储过程没有。

嵌套函数(Nested Function)

一个函数可以定义在匿名块或另一个子程序中，我们把它叫做嵌套函数。

DECLARE    
  ming    varchar2(20) := 'Bo';    
  xing    varchar2(20) := 'Shang';    
        
  -- 定义嵌套函数   
  FUNCTION full_name(    
    first_name    varchar2, -- 注意，此处参数不能指定长度哦    
    last_name     varchar2    
  )
    RETURN varchar2 -- 此处也不能指定长度哦 
  AS  
    -- 在此处申明变量  
    full_name varchar2(40);  
  BEGIN  
    full_name :=  last_name || ' ' || first_name;  
    return full_name;    
  END full_name;    
    
BEGIN    
  -- 调用函数方式一    
  DBMS_OUTPUT.PUT_LINE(full_name(ming, xing));    
      
  -- 调用函数方式二
  DBMS_OUTPUT.PUT_LINE(full_name(first_name => ming, last_name => xing)); 
END;

独立函数(Standalone Function)

一个函数还可以定义在模式(schema)中，我们把它叫做独立函数。

-- 定义函数
CREATE OR REPLACE FUNCTION full_name(      
	first_name    varchar2,
	last_name     varchar2      
)  
	RETURN varchar2  
AS    
	full_name varchar2(40);    
BEGIN    
	full_name :=  last_name || ' ' || first_name;    
	return full_name;      
END;

-- 调用函数
DECLARE  
  ming    varchar2(20) := 'bo';  
  xing    varchar2(20) := 'Shang';
  
BEGIN    
  DBMS_OUTPUT.PUT_LINE(full_name(ming, xing));  
END;

包函数 (Package Function)

一个函数还可以定义在包(package)中，我们把它叫做包函数。

-- 声明包  
CREATE OR REPLACE PACKAGE util AS  
    FUNCTION full_name (first_name varchar2, last_name varchar2) RETURN varchar2;
END util;
  
-- 定义包
CREATE PACKAGE BODY util AS

  --定义函数
  FUNCTION full_name(      
    first_name    varchar2,
    last_name     varchar2      
  )  
    RETURN varchar2
  AS   
  BEGIN    
    return last_name || ' ' || first_name;    
  END full_name;  
END util;  
  
-- 测试包函数  
DECLARE    
  ming    varchar2(20) := 'Bo';  
  xing    varchar2(20) := 'Shang';  
  
BEGIN    
  -- 调用包存储过程    
  DBMS_OUTPUT.PUT_LINE(util.full_name(ming, xing));   
END;

重载函数

像 Java 一样，函数也是可以重载的，它特别适用于包函数，这样我们就可以使用同一个 API 根据不同的参数处理不同的事情。

管道函数

用过 Linux 的人都知道管道的强大魅力，例如下面的例子取出 test.txt 文件中的第一列，然后排序，去掉重复值。

cut -d ',' -f 1 test.txt | sort | uniq

Oracle 允许我们编写管道函数，它的参数可以是集合或游标，返回一个集合或游标，我们可以将多个管道函数串联起来使用。下面是一个简单的例子。

-- 定义测试包
CREATE OR REPLACE PACKAGE pkg_test AS
	TYPE type_num_list IS TABLE OF NUMBER;
	FUNCTION f_test(x NUMBER) RETURN type_num_list DETERMINISTIC PARALLEL_ENABLE PIPELINED;
END pkg_test;

-- 实现测试包
CREATE PACKAGE BODY pkg_test AS

	-- DETERMINISTIC 表示只要参数不变，返回值永远不变
	-- PIPELINED 指定该函数是一个管道函数
	-- PARALLEL_ENABLE 指定允许并发执行
	FUNCTION f_test(x NUMBER) RETURN type_num_list DETERMINISTIC PARALLEL_ENABLE PIPELINED IS
	BEGIN
		FOR i IN 1..x LOOP
			-- PIPE ROW 表示返回一行给调用者
			PIPE ROW(i);
		END LOOP;
		
		EXCEPTION
			-- 如果调用者表示它不再需要数据了，如： 调用者包含 ROWNUM < 10
			-- 调用者会抛出 NO_DATA_NEEDED 异常
			-- 我们需要在此处捕获该异常
			WHEN NO_DATA_NEEDED THEN
				RAISE;
			WHEN OTHERS THEN
				RAISE_APPLICATION_ERROR(-20000, 'Fatal error.');

		-- RETURN 不能返回实际值，它表示返回控制权给调用者
		RETURN;
	END f_test;

END pkg_test;

-- 调用管道函数
SELECT * FROM TABLE(pkg_test.f_test(5));

-- 调用管道函数并抛出 NO_DATA_NEEDED 异常
SELECT * FROM TABLE(pkg_test.f_test(5)) WHERE ROWNUM <= 3;

上面的例子只是用来说明问题，没有太多实际意义，下面我们看一个能在工作中用到的例子。

-- 定义测试包
CREATE OR REPLACE PACKAGE pkg_test IS
	TYPE type_cur_test IS REF CURSOR RETURN employees%ROWTYPE;
	
	TYPE type_rec_test IS RECORD (
		var_num1 NUMBER(6,2),
		var_num2 NUMBER(6,2),
		var_num3 NUMBER(6,2)
	);
	
	TYPE type_tbl_rec_test IS TABLE OF type_rec_test;
	
	FUNCTION f_test (p type_cur_test) RETURN type_tbl_rec_test PIPELINED;
END pkg_test;

-- 实现测试包
CREATE OR REPLACE PACKAGE BODY pkg_test IS
	FUNCTION f_test (p type_cur_test) RETURN type_tbl_rec_test PIPELINED IS
		out_rec type_rec_test;
		in_rec p%ROWTYPE;
	BEGIN
		LOOP
			FETCH p INTO in_rec; 
			EXIT WHEN p%NOTFOUND;
			
			-- 此处可以进行复杂运算
			out_rec.var_num1 := in_rec.salary;
			out_rec.var_num2 := in_rec.salary * 1.1;
			out_rec.var_num3 := in_rec.salary * 1.2;
			
			--传给下个函数
			PIPE ROW(out_rec);
			
		END LOOP;
		
		EXCEPTION
			WHEN NO_DATA_NEEDED THEN
				RAISE;
			WHEN OTHERS THEN
				RAISE_APPLICATION_ERROR(-20000, 'Fatal error.');
		
		CLOSE p;
		RETURN;
	END f_test;
END pkg_test;

-- 调用管道函数
SELECT * FROM TABLE (pkg_test.f_test (CURSOR (SELECT * FROM employees)));

--更多参见：Oracle PL/SQL 精萃

-- 声明：转载请注明出处

-- Last Edited on 2015-07-01

-- Created by ShangBo on 2015-01-22

-- End