RETURN 子句且執行路徑必須返回值。OUT/IN OUT 參數的函數**不可**在 SQL 中呼叫。CREATE [OR REPLACE] FUNCTION func_name
(param1 [IN] datatype, ...)
RETURN return_datatype
IS | AS
BEGIN
RETURN expr;
EXCEPTION
...
END;
CREATE OR REPLACE FUNCTION current_time RETURN VARCHAR2 AS
BEGIN
RETURN TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日" HH24"時"MI"分"SS"秒"');
END;
調用:SELECT current_time FROM DUAL; 或 PL/SQL 變數指派。
CREATE OR REPLACE FUNCTION get_ename (p_eno IN NUMBER) RETURN VARCHAR2 AS
v_ename VARCHAR2(20);
BEGIN
SELECT ENAME INTO v_ename FROM EMP WHERE EMPNO = p_eno;
RETURN v_ename;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN '無此員工';
END;
CREATE OR REPLACE FUNCTION get_ename_job (
p_eno IN NUMBER,
p_job OUT VARCHAR2) RETURN VARCHAR2 AS
v_ename VARCHAR2(20);
BEGIN
SELECT ENAME, JOB INTO v_ename, p_job FROM EMP WHERE EMPNO = p_eno;
RETURN v_ename;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_job := 'N/A'; RETURN 'NOT FOUND';
END;
調用:
DECLARE v_job VARCHAR2(20); BEGIN
DBMS_OUTPUT.PUT_LINE(get_ename_job(7788, v_job));
DBMS_OUTPUT.PUT_LINE(v_job);
END;
DETERMINISTIC:相同輸入必得相同輸出,優化器可快取結果 (適用於函數索引、物化視圖)。PARALLEL_ENABLE:宣告可平行執行 (12c+)。PRAGMA RESTRICT_REFERENCES。WITH FUNCTION (12c) 內聯。AUTONOMOUS_TRANSACTION),否則 SQL 呼叫會報 ORA-14551。