在本节中,我们将讨论看看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;
<