oracle 自定义函数(非常简单明了)

语法说明

create [or replace] function functionName   (parameterName1 mode1 dataType1,  parameterName2 mode2 dataType2,  ...)  
 return returnDataType  
 is/as  
 	-- 定义使用变量、返回变量
 begin  
 	function_body  
 	return expression  
end functionName; -- 结束函数的声明，也可以直接写end不加函数名。  

-- 其中mode1、mode2表示参数类型，dataType表示参数的数据类型。returnDataType表示返回值类型。  
1
2
3
4
5
6
7
8
9
10

举例说明

1.举一个简单的例子

定义一个简单的函数，计算两数之和

create or replace function useEasy(add1 in number, add2 in number) 
  return number 
  is
    FunctionResult number;
  begin
    FunctionResult := add1 + add2;
    return(FunctionResult);
end useEasy;
1
2
3
4
5
6
7
8

函数的使用请继续往下看

2.举一个复杂的例子（虽然复杂，但是很实用）

1⃣️、定义函数的返回类型

创建 TYPE 类型 atrr_type

1、CREATE OR REPLACE TYPE atrr_type  AS OBJECT (
       attrId varchar2(40),
       objType varchar2(40)
);
1
2
3
4

2、将 TYPE 类型 atrr_type 定义为表， 用做接收返回值

CREATE OR REPLACE TYPE attr_table AS TABLE of atrr_type;
1

2⃣️、定义函数（这里介绍三种方式）

1、以游标形式返回，有很大的局限性

create or replace function selectAttrId(objType in VARCHAR2)
  return SYS_REFCURSOR 
  is
    attrId SYS_REFCURSOR;
  begin
    OPEN attrId FOR
      select attr_id, obj_type from CPS_OBJ_ATTR where obj_type = objType;
    return(attrId);
end selectAttrId;
1
2
3
4
5
6
7
8
9

2、以 Table 形式 返回结果集

create or replace function resultFunction(objType in VARCHAR2)
  return attr_table 
  is
    attr_row 	atrr_type;        	 		  -- 定义单条数据变量
    attr     	attr_table := attr_table();   -- 定义返回结果，并初始化
  begin
    for thisrow in (select attr_id as attrId, obj_type as objType from CPS_OBJ_ATTR where obj_type = objType) 
    loop
      attr_row := atrr_type(thisrow.attrId, thisrow.objType);
      attr.extend;
      attr(attr.count) := attr_row;
    end loop;
  return(attr);
end resultFunction;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3、以管道形式返回结果集

create or replace function returnPiperesult(objType in VARCHAR2)
  return attr_table pipelined
  is
    attr_row atrr_type;		 --定义attr_row为行对象类型
  begin
    for thisrow in (select attr_id as attrId, obj_type as objType from CPS_OBJ_ATTR where obj_type = objType)
    loop
      attr_row:= atrr_type(thisrow.attrId, thisrow.objType);
      pipe row (attr_row);
    end loop;
  return;
end returnPiperesult;
1
2
3
4
5
6
7
8
9
10
11
12

3⃣️、函数的使用

select resultFunction('turck') from dual;			-- 直接调用函数

select * from table(resultFunction('turck'));  	-- 返回结果集table时，可以调用（自定义一个type为table 作为返回结果集）
1
2
3

觉得文章实用，请在右上方点个?