Oracle PL/SQL DB Function 数据库函数

在本节中，我们将讨论看看PL/SQL中的函数。函数与过程(也叫程序)相同，只不过函数有返回一个值，而过程没有返回值。 因此，上一节中所有有关存储过程的内容也适用于函数。

• 新手最容易犯错的语法问题，语句以分号结尾; – 记录一下，以提醒自己
• 不太熟悉DB Function基本语法的，可以点击看看DB Function的基础知识
• 下贴上来记录着，后期有空再整理一下

DB Function(sample) 示例

CREATE OR REPLACE FUNCTION FUNC_EXTENDPALLETIDFORMAT(P_OldPalletID IN VARCHAR2, P_CartonList IN VARCHAR2) RETURN VARCHAR2 IS
   /* =============================================================================
  | Author: Winds007
  | Purpose: Extend 5 yards (carton Qty) variable after the original pallet ID format.
  | Input:  P_OldPalletID, P_CartonList
  | OutPut: V_NewPalletID
  | Change history:
  |   2020/06/16: Initial version, by Winds007
  ============================================================================= */
  V_NewPalletID VARCHAR2(30);
  V_CartonQty VARCHAR2(5);
BEGIN
  SELECT To_Char(COUNT(*),'FM00000') INTO V_CartonQty 
  FROM (SELECT regexp_substr(P_CartonList, '[^,]+', 1, level) FROM dual CONNECT BY regexp_substr(P_CartonList, '[^,]+', 1, level) is not NULL) t; 
  
  V_NewPalletID := P_OldPalletID || V_CartonQty;
  RETURN V_NewPalletID;
EXCEPTION
WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20001, 'An error was encountered on FUNC_EXTENDPALLETIDFORMAT- ' || SQLCODE ||
   ' -ERROR- ' || SQLERRM);
END; 

Test DB Function 执行

• 用法1：普通用法

 SELECT FUNC_EXTENDPALLETIDFORMAT('A6F0520090803.0001','SA,12,54') FROM dual;
 --语法  select * FROM table(包名称.方法名(参数))，如:
select * FROM table(testrecords.mResult('11111'))

• 用法2：返回结果集（表）的用法

 --语法  select * FROM table(包名称.方法名(参数))，如:
select * FROM table(testrecords.mResult('11111'));
select * FROM table(Func_name('2222'))

regexp_substr 正则的用法

SELECT regexp_substr('CartonID001,CartonID002', '[^,]+', 1,1) FROM dual;
select regexp_substr('WINDSCIDyyyymmdd.sssss','s+',1,1,'c') from dual;
select regexp_instr('WINDSCIDyyyymmdd.sssss','s+') from dual;
SELECT LENGTH('12') from dual;

可以执行 DML 语句的 DB Function

---参考的 DB Function（可以执行 DML 语句） ————  自治事务 AUTONOMOUS_TRANSACTION
CREATE OR REPLACE FUNCTION FUN_WINDSTEST
(p_usn SFCUSNINFO.USN%TYPE, 
 p_infoname SFCUSNINFO.INFONAME%TYPE, 
 p_infoValue SFCUSNINFO.INFOVALUE%TYPE) 
RETURN VARCHAR2 IS
  varMSG    VARCHAR2(500):='OK';
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO SFCUSNINFO (USN, INFONAME, INFOVALUE)
  VALUES (p_usn, p_infoname, p_infoValue);
  COMMIT;  -- 自治事务 执行DML SQL, 需要COMMIT;
  RETURN varMSG;
EXCEPTION
   WHEN OTHERS
   THEN
      varMSG :=SQLERRM(SQLCODE);
      RETURN varMSG;
END FUN_WINDSTEST; 

样例

DROP FUNCTION FUNC_EXTENDCARTONIDFORMAT;
-- SFCFA/SFSPCB: New DB Function for Extend Carton ID format(Just is a sample).
CREATE OR REPLACE FUNCTION FUNC_EXTENDCARTONIDFORMAT(P_OriginCartonID IN VARCHAR2, P_PackLevel IN NUMBER, P_USNQty IN NUMBER, P_CartonIDFormat IN VARCHAR2) 
RETURN VARCHAR2 IS
   /* =============================================================================
  | Author: Winds007
  | Purpose:(sample)Extend (USN Qty) variable after the original Carton ID format, For....
  | Input:  P_OriginCartonID, P_PackLevel, P_USNQty, P_CartonIDFormat
  | OutPut: V_NewCartonID
  | Change history:
  |   2020/09/17: Initial version, by Winds007
  ============================================================================= */
  V_NewCartonID VARCHAR2(30);
  V_USNQty NUMBER(6);
  V_StreamCode NUMBER(6);
  V_ScodePos NUMBER(6);
  V_ScodeLen NUMBER(6);
  PRAGMA AUTONOMOUS_TRANSACTION;  /*Commit is required for autonomous transactions to execute DML SQL*/
BEGIN
   IF P_CARTONIDFORMAT IS NULL THEN
     /* If USN is Carton ID(1 Pack 1), Direct return USN */
      V_NewCartonID := P_OriginCartonID;
   ELSE
      IF P_USNQty <= 0 THEN
         /* Get USN Qty by CartonID and P_PackLevel */
         SELECT COUNT(*) INTO V_USNQty FROM SFCCARTONITEM WHERE CARTONID = P_OriginCartonID;
      ELSE
         V_USNQty := P_USNQty;
      END IF; 
    
      IF V_USNQty < 10 THEN
         SELECT P_OriginCartonID || To_Char(V_USNQty, 'FM00') INTO V_NewCartonID FROM dual;
      ELSE
          SELECT P_OriginCartonID || To_Char(V_USNQty) INTO V_NewCartonID FROM dual;
      END IF;
  
      /* Get Stream Code 'ssssss' and to number (by Carton ID Format)*/  
      SELECT regexp_instr(P_CartonIDFormat,'s+'), LENGTH(regexp_substr(P_CartonIDFormat,'s+',1,1,'c')) INTO V_ScodePos, V_ScodeLen FROM dual;
      V_StreamCode := to_number(SUBSTR(P_OriginCartonID, V_ScodePos, V_ScodeLen));    /* here ssssss must be number */
   
      IF P_PackLevel = 1 THEN
         INSERT INTO ROSA_CARTONPACKAGEMAPPING(CARTONID, SEQ, PACKAGEID) VALUES(P_OriginCartonID, V_StreamCode, V_NewCartonID); /* For WZS F131 Rosa */
         UPDATE SFCCARTON SET CARTONID = V_NewCartonID WHERE CARTONID = P_OriginCartonID;
         UPDATE SFCCARTONITEM SET CARTONID = V_NewCartonID WHERE CARTONID = P_OriginCartonID;
         /* IF the P_OriginCartonID exist in SFCPalletItem(USN Status = Close), then should Update SFCPalleItem */
         UPDATE SFCPALLETITEM SET CARTONID = V_NewCartonID WHERE CARTONID = P_OriginCartonID; 
         COMMIT;
      END IF;  
   END IF;
  
   RETURN V_NewCartonID;
EXCEPTION
WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20001, 'An error was encountered on FUNC_EXTENDCARTONIDFORMAT- ' || SQLCODE ||
   ' -ERROR- ' || SQLERRM);
END;

-- Test DB Function
SELECT FUNC_EXTENDCARTONIDFORMAT('WINDSCID20200908.00002', 1, 1, '') FROM dual;

基础知识

1. 创建函数

使用CREATE FUNCTION语句创建独立函数。CREATE OR REPLACE PROCEDURE语句的简化语法如下：

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];

其中，

• function-name是指定要创建的函数的名称。
• [OR REPLACE]选项指示是否允许修改现有的函数。
• 可选参数列表包含参数的名称，模式和类型。 IN表示将从外部传递的值，OUT表示将用于返回过程外的值的参数。
• 函数必须包含一个返回(RETURN)语句。
• RETURN子句指定要从函数返回的数据类型。
• function-body包含可执行部分。
• 使用AS关键字代替IS关键字，用来创建独立的函数。

2. 调用函数

在创建一个函数时，我们给出一个定义函数的语句以及实现的功能。 要使用一个函数，必须调用该函数来执行定义的任务。当程序调用一个函数时，程序控制被传递给被调用的函数。
被调用的函数执行定义的任务，当执行返回语句或达到最后一个结束语句时，它将程序控制返回到主程序。
如果调用一个函数，只需要传递所需的参数和函数名，如果函数返回一个值，那么可以存储返回的值.