PL/SQL函数 #

函数特点
├── 必须返回值
│   └── RETURN子句
├── 可在SQL中使用
│   └── SELECT, WHERE等
├── 参数模式
│   └── 通常使用IN参数
└── 表达式使用
    └── 可用于赋值、条件判断

-- 创建函数基本语法
CREATE [OR REPLACE] FUNCTION function_name
    [(parameter1 [IN] datatype [, parameter2 ...])]
    RETURN datatype
    [DETERMINISTIC|PARALLEL_ENABLE|PIPELINED]
    [AUTHID DEFINER|CURRENT_USER]
AS|IS
    -- 声明部分
BEGIN
    -- 执行部分
    RETURN value;
EXCEPTION
    -- 异常处理
END [function_name];

-- 创建简单函数
CREATE OR REPLACE FUNCTION hello_world
RETURN VARCHAR2 AS
BEGIN
    RETURN 'Hello World!';
END;
/

-- 调用函数
SELECT hello_world FROM DUAL;

-- 在PL/SQL中调用
DECLARE
    v_message VARCHAR2(100);
BEGIN
    v_message := hello_world;
    DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

-- 带参数的函数
CREATE OR REPLACE FUNCTION get_employee_name(
    p_emp_id IN NUMBER
) RETURN VARCHAR2 AS
    v_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_name
    FROM employees
    WHERE employee_id = p_emp_id;
    
    RETURN v_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

-- 调用
SELECT get_employee_name(100) FROM DUAL;

-- 在WHERE中使用
SELECT * FROM employees
WHERE first_name || ' ' || last_name = get_employee_name(100);

-- 返回RECORD类型
CREATE OR REPLACE FUNCTION get_employee_record(
    p_emp_id IN NUMBER
) RETURN SYS_REFCURSOR AS
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT * FROM employees WHERE employee_id = p_emp_id;
    
    RETURN v_cursor;
END;
/

-- 调用
VARIABLE rc REFCURSOR;
EXEC :rc := get_employee_record(100);
PRINT rc;

-- 返回嵌套表
CREATE OR REPLACE TYPE t_number_list IS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION get_dept_employees(
    p_dept_id IN NUMBER
) RETURN t_number_list AS
    v_list t_number_list;
BEGIN
    SELECT employee_id BULK COLLECT INTO v_list
    FROM employees
    WHERE department_id = p_dept_id;
    
    RETURN v_list;
END;
/

-- 调用
SELECT * FROM TABLE(get_dept_employees(10));

-- 在SELECT中使用
SELECT 
    employee_id,
    first_name,
    get_employee_name(employee_id) AS full_name
FROM employees;

-- 在WHERE中使用
SELECT * FROM employees
WHERE salary > get_avg_salary();

-- 在GROUP BY中使用
SELECT 
    department_id,
    COUNT(*)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > get_avg_salary();

-- 在ORDER BY中使用
SELECT * FROM employees
ORDER BY get_employee_name(employee_id);

-- 在SQL中调用的函数限制
-- 1. 不能有OUT或IN OUT参数
-- 2. 不能修改数据库表（DML）
-- 3. 不能执行事务控制（COMMIT, ROLLBACK）
-- 4. 不能调用违反上述限制的过程

-- 错误示例：在SQL调用的函数中执行DML
CREATE OR REPLACE FUNCTION bad_function(
    p_emp_id IN NUMBER
) RETURN NUMBER AS
BEGIN
    -- 错误：在SQL调用的函数中不能执行DML
    UPDATE employees SET salary = salary * 1.1
    WHERE employee_id = p_emp_id;
    
    RETURN 1;
END;
/

-- 这会导致错误
SELECT bad_function(100) FROM DUAL;
-- ORA-14551: cannot perform a DML operation inside a query

-- DETERMINISTIC：相同输入返回相同输出
CREATE OR REPLACE FUNCTION tax_amount(
    p_salary IN NUMBER
) RETURN NUMBER DETERMINISTIC AS
    c_tax_rate CONSTANT NUMBER := 0.1;
BEGIN
    RETURN p_salary * c_tax_rate;
END;
/

-- 好处：Oracle可以缓存结果

-- PARALLEL_ENABLE：支持并行执行
CREATE OR REPLACE FUNCTION calculate_bonus(
    p_salary IN NUMBER,
    p_performance IN NUMBER
) RETURN NUMBER PARALLEL_ENABLE AS
BEGIN
    RETURN p_salary * p_performance / 100;
END;
/

-- RESULT_CACHE（11g+）：结果缓存
CREATE OR REPLACE FUNCTION get_dept_name(
    p_dept_id IN NUMBER
) RETURN VARCHAR2 RESULT_CACHE AS
    v_name VARCHAR2(30);
BEGIN
    SELECT department_name INTO v_name
    FROM departments
    WHERE department_id = p_dept_id;
    
    RETURN v_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

-- 标量函数：返回单个值
CREATE OR REPLACE FUNCTION calculate_tax(
    p_salary IN NUMBER
) RETURN NUMBER AS
BEGIN
    IF p_salary > 20000 THEN
        RETURN p_salary * 0.3;
    ELSIF p_salary > 10000 THEN
        RETURN p_salary * 0.2;
    ELSE
        RETURN p_salary * 0.1;
    END IF;
END;
/

SELECT calculate_tax(15000) FROM DUAL;

-- 创建类型
CREATE OR REPLACE TYPE t_emp_row AS OBJECT (
    employee_id NUMBER,
    employee_name VARCHAR2(100),
    salary NUMBER
);
/

CREATE OR REPLACE TYPE t_emp_table IS TABLE OF t_emp_row;
/

-- 创建表函数
CREATE OR REPLACE FUNCTION get_high_salary_employees(
    p_min_salary IN NUMBER
) RETURN t_emp_table PIPELINED AS
BEGIN
    FOR rec IN (
        SELECT employee_id, first_name || ' ' || last_name, salary
        FROM employees
        WHERE salary >= p_min_salary
    ) LOOP
        PIPE ROW(t_emp_row(rec.employee_id, rec.employee_name, rec.salary));
    END LOOP;
    
    RETURN;
END;
/

-- 调用表函数
SELECT * FROM TABLE(get_high_salary_employees(10000));

-- 创建用户定义聚合函数（需要使用ODCIAggregate接口）
-- 示例：字符串连接聚合

-- 创建类型
CREATE OR REPLACE TYPE string_agg_type AS OBJECT (
    result VARCHAR2(4000),
    
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
        RETURN NUMBER,
    
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, value IN VARCHAR2)
        RETURN NUMBER,
    
    MEMBER FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER)
        RETURN NUMBER,
    
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
        RETURN NUMBER
);
/

-- 创建函数
CREATE OR REPLACE FUNCTION string_agg(input VARCHAR2)
RETURN VARCHAR2
AGGREGATE USING string_agg_type;
/

-- 使用
SELECT department_id, string_agg(first_name) AS names
FROM employees
GROUP BY department_id;

-- 查看函数源代码
SELECT text FROM user_source 
WHERE name = 'GET_EMPLOYEE_NAME' AND type = 'FUNCTION'
ORDER BY line;

-- 查看函数信息
SELECT object_name, status, created, last_ddl_time
FROM user_objects
WHERE object_type = 'FUNCTION';

-- 查看函数参数
SELECT argument_name, position, data_type, in_out
FROM user_arguments
WHERE object_name = 'GET_EMPLOYEE_NAME'
ORDER BY position;

-- 使用CREATE OR REPLACE修改
CREATE OR REPLACE FUNCTION get_employee_name(
    p_emp_id IN NUMBER
) RETURN VARCHAR2 AS
    v_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_name
    FROM employees
    WHERE employee_id = p_emp_id;
    
    RETURN v_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'Employee not found';
END;
/

-- 编译函数
ALTER FUNCTION get_employee_name COMPILE;

-- 删除函数
DROP FUNCTION get_employee_name;

-- 删除不存在函数（避免错误）
DROP FUNCTION IF EXISTS get_employee_name;

-- 授予执行权限
GRANT EXECUTE ON get_employee_name TO user_name;

-- 授予所有用户
GRANT EXECUTE ON get_employee_name TO PUBLIC;

-- 回收权限
REVOKE EXECUTE ON get_employee_name FROM user_name;

-- 定义者权限（默认）
CREATE OR REPLACE FUNCTION definer_func
RETURN NUMBER
AUTHID DEFINER AS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    RETURN v_count;
END;
/

-- 调用者权限
CREATE OR REPLACE FUNCTION invoker_func
RETURN NUMBER
AUTHID CURRENT_USER AS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    RETURN v_count;
END;
/

-- 格式化电话号码
CREATE OR REPLACE FUNCTION format_phone(
    p_phone IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
    RETURN REGEXP_REPLACE(
        REGEXP_REPLACE(p_phone, '[^0-9]', ''),
        '([0-9]{3})([0-9]{4})([0-9]{4})',
        '\1-\2-\3'
    );
END;
/

SELECT format_phone('13812345678') FROM DUAL;
-- 138-1234-5678

-- 格式化金额
CREATE OR REPLACE FUNCTION format_currency(
    p_amount IN NUMBER,
    p_currency IN VARCHAR2 DEFAULT 'CNY'
) RETURN VARCHAR2 AS
BEGIN
    RETURN CASE p_currency
        WHEN 'CNY' THEN '￥' || TO_CHAR(p_amount, '999,999,999.00')
        WHEN 'USD' THEN '$' || TO_CHAR(p_amount, '999,999,999.00')
        WHEN 'EUR' THEN '€' || TO_CHAR(p_amount, '999,999,999.00')
        ELSE TO_CHAR(p_amount, '999,999,999.00')
    END;
END;
/

SELECT format_currency(12345.67) FROM DUAL;
-- ￥12,345.67

-- 计算年龄
CREATE OR REPLACE FUNCTION calculate_age(
    p_birth_date IN DATE
) RETURN NUMBER AS
BEGIN
    RETURN FLOOR(MONTHS_BETWEEN(SYSDATE, p_birth_date) / 12);
END;
/

SELECT calculate_age(TO_DATE('1990-05-15', 'YYYY-MM-DD')) FROM DUAL;

-- 计算工作年限
CREATE OR REPLACE FUNCTION calculate_years_of_service(
    p_hire_date IN DATE
) RETURN NUMBER AS
BEGIN
    RETURN ROUND(MONTHS_BETWEEN(SYSDATE, p_hire_date) / 12, 1);
END;
/

-- 计算奖金
CREATE OR REPLACE FUNCTION calculate_bonus(
    p_salary IN NUMBER,
    p_hire_date IN DATE,
    p_performance IN NUMBER
) RETURN NUMBER AS
    v_years NUMBER;
    v_bonus NUMBER;
BEGIN
    v_years := calculate_years_of_service(p_hire_date);
    
    v_bonus := p_salary * p_performance / 100;
    
    -- 工龄加成
    IF v_years >= 10 THEN
        v_bonus := v_bonus * 1.5;
    ELSIF v_years >= 5 THEN
        v_bonus := v_bonus * 1.2;
    END IF;
    
    RETURN v_bonus;
END;
/

-- 验证邮箱
CREATE OR REPLACE FUNCTION is_valid_email(
    p_email IN VARCHAR2
) RETURN NUMBER DETERMINISTIC AS
BEGIN
    IF REGEXP_LIKE(p_email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END;
/

SELECT is_valid_email('john@example.com') FROM DUAL;  -- 1
SELECT is_valid_email('invalid-email') FROM DUAL;     -- 0

-- 验证身份证号
CREATE OR REPLACE FUNCTION is_valid_id_card(
    p_id_card IN VARCHAR2
) RETURN NUMBER DETERMINISTIC AS
BEGIN
    IF REGEXP_LIKE(p_id_card, '^[0-9]{17}[0-9X]$') THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END;
/

-- DETERMINISTIC函数：相同输入返回相同输出
-- Oracle可以缓存结果

CREATE OR REPLACE FUNCTION get_tax_rate(
    p_salary IN NUMBER
) RETURN NUMBER DETERMINISTIC AS
BEGIN
    IF p_salary > 20000 THEN RETURN 0.3;
    ELSIF p_salary > 10000 THEN RETURN 0.2;
    ELSE RETURN 0.1;
    END IF;
END;
/

-- 在查询中使用
SELECT employee_id, salary, get_tax_rate(salary) AS tax_rate
FROM employees;

-- RESULT_CACHE：缓存函数结果（11g+）
CREATE OR REPLACE FUNCTION get_department_name(
    p_dept_id IN NUMBER
) RETURN VARCHAR2 RESULT_CACHE AS
    v_name VARCHAR2(30);
BEGIN
    SELECT department_name INTO v_name
    FROM departments
    WHERE department_id = p_dept_id;
    
    RETURN v_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

-- 查看缓存状态
SELECT * FROM V$RESULT_CACHE_OBJECTS WHERE NAME LIKE '%GET_DEPARTMENT_NAME%';

-- 避免：递归调用
CREATE OR REPLACE FUNCTION factorial_bad(n NUMBER) RETURN NUMBER AS
BEGIN
    IF n <= 1 THEN RETURN 1;
    ELSE RETURN n * factorial_bad(n - 1);
    END IF;
END;
/

-- 推荐：迭代实现
CREATE OR REPLACE FUNCTION factorial_good(n NUMBER) RETURN NUMBER AS
    v_result NUMBER := 1;
BEGIN
    FOR i IN 1..n LOOP
        v_result := v_result * i;
    END LOOP;
    RETURN v_result;
END;
/