创建函数的基本语法如下：

CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | IN OUT }] Type,
      argment [ { IN | OUT | IN OUT } ] Type ]
    [ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type 
{ IS | AS }
 delarification_block
BEGIN
    FUNCTION_body
EXCEPTION
    exception_handler
END;

示例如下：

CREATE OR REPLACE FUNCTION get_salary_by_dept(
    v_in_dept_id NUMBER,
    v_out_emp_count OUT NUMBER)
    RETURN NUMBER 
IS
    v_sum NUMBER;
BEGIN
    SELECT SUM(salary), count(*) INTO v_sum, v_out_emp_count
        FROM employees WHERE deptno=v_in_dept_id;
    RETURN v_sum;
EXCEPTION
   WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('Dept id '||v_in_dept_id||' not found');
   WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE('ERR: '||SQLCODE||': '||SQLERRM);
END get_salary_by_dept;

参数的传递

向函数中传递参数有三种方法：

• 第一种是位置表示法，即按照参数定义的顺序逐个传入，语法是:argument_value1[,argument_value2 …]

  示例如下：

obclient> DECLARE
    ->     V_num NUMBER;
    ->     V_sum NUMBER;
    -> BEGIN
    ->     V_sum :=get_salary_by_dept(30, v_num);
    ->     DBMS_OUTPUT.PUT_LINE('Total salary: '||v_sum);
    ->     DBMS_OUTPUT.PUT_LINE('Total amount of person: '||V_num);
    -> END;
    -> /
Query OK, 0 rows affected (0.06 sec)
Total salary: 24900
Total amount of person: 6

• 第二种参数传递的方法是名称表示法，即显式地指定参数名和传入值，不必按照参数定义的顺序，语法是：argument1 => parameter1 [, argument2 => parameter2[, …]]

  示例如下：

obclient> DECLARE
    ->     V_num NUMBER;
    ->     V_sum NUMBER;
    -> BEGIN
    ->     V_sum :=get_salary_by_dept(v_out_emp_count => V_num, v_in_dept_id => 30 );
    ->     DBMS_OUTPUT.PUT_LINE('Total salary: '||v_sum);
    ->     DBMS_OUTPUT.PUT_LINE('Total amount of person: '||V_num);
    ->     END;
    -> /
Query OK, 0 rows affected (0.06 sec)
Total salary: 24900
Total amount of person: 6

• 第三种方法为混合表示法，即同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。

  示例如下：

obclient> DECLARE
    ->     V_num NUMBER;
    ->     V_sum NUMBER;
    -> BEGIN
    ->     V_sum :=get_salary_by_dept(30, v_out_emp_count => V_num );
    ->     DBMS_OUTPUT.PUT_LINE('Total salary: '||v_sum);
    ->     DBMS_OUTPUT.PUT_LINE('Total amount of person: '||V_num);
    -> END;
    -> /
Query OK, 0 rows affected (0.06 sec)
Total salary: 24900
Total amount of person: 6

参数默认值

在CREATE OR REPLACE FUNCTION语句中声明函数参数时可以使用 DEFAULT 关键字为输入参数指定默认值。示例如下：

CREATE OR REPLACE FUNCTION demo_def_args(
    name VARCHAR2,
    age INTEGER,
    -- 如果gender不传入值，则默认为male
    gender VARCHAR2 DEFAULT 'Male')
    RETURN VARCHAR2 
AS
    V_var VARCHAR2(32);
BEGIN
    V_var := name||', '||gender||', '||TO_CHAR(age)||' years old.';
    RETURN v_var;
END;

具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时，函数将使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。

obclient> DECLARE
    -> Var VARCHAR(32);
    -> BEGIN
    ->     Var := demo_def_args('Roger', 30);
    ->     DBMS_OUTPUT.PUT_LINE(var);
    ->
    ->     Var := demo_def_args('Allen', age => 40);
    ->     DBMS_OUTPUT.PUT_LINE(var);
    ->
    ->     Var := demo_def_args('Tracy', gender => 'Female', age => 20);
    ->     DBMS_OUTPUT.PUT_LINE(var);
    -> END;
    -> /
Query OK, 0 rows affected (0.05 sec)
Roger, Male, 30 years old.
Allen, Male, 40 years old.
Tracy, Female, 20 years old.