PL/SQL函数

目录

引言

PL/SQL函数

1、语法 

2、实例

3、函数的调用

4、参数默认值 

5、其他实例

---

引言

        过程和函数（另外还有包与触发器）统称为PL/SQL子程序，是命名的PL/SQL块。被编译后存储在数据库中，以备执行，因此，其它PL/SQL块可以按名称来使用他们。过程和函数通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

PL/SQL函数

        PL/SQL函数是返回值的PL / SQL块或方法，因此它可以在赋值的右侧使用。例：

n_value := to_number('123.45'); 

由于函数返回一个值，因此也可以在SQL语句中使用它，例：

select to_number('1') from dual;

1、语法 

CREATE [OR REPLACE] FUNCTION <function_name> [(
<parameter_name_1> [IN | OUT | IN OUT] <parameter_data_type_1> [DEFAULT value_1],
<parameter_name_2> [IN | OUT | IN OUT] <parameter_data_type_2> [DEFAULT value_2],
...
<parameter_name_N> [IN | OUT | IN OUT] <parameter_data_type_N> [DEFAULT value_N] )]
RETURN <return_data_type>
IS
    -- 类型.变量的声明部分
BEGIN
    -- 执行部分
    return <return_data_type>;
EXCEPTION
    -- 异常处理部分
END function_name;
/

• <function_name>是函数的名称；
• <parameter_name>是要传递的参数的名称，参数模式有[IN|OUT|IN OUT]三种；
• <parameter_data_type>是相应参数的PL / SQL数据类型； 
• <return_data_type>是函数完成执行时将返回的值的PL / SQL数据类型。

        一般，只有在确认function_name函数是新函数或是要更新的函数时，才使用OR REPALCE关键字，否则容易删除有用的函数。 

        IN,OUT,IN OUT是形参的模式。若省略，则为IN模式。IN模式的形参只能将实参传递给形参，进入函数内部，只能读不能写，函数返回时实参的值不变。OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。

2、实例

（1）获取某部门的工资总和：

--获取某部门的工资总和
CREATE OR REPLACE FUNCTION GET_SALARY
(
    N_DEP_NO NUMBER
   ,N_EMP_COUNT OUT NUMBER
)
RETURN NUMBER
IS
    N_SUM_COUNT NUMBER;
BEGIN
    SELECT SUM(EMP_SALARY), COUNT(*) INTO N_SUM_COUNT, N_EMP_COUNT
    FROM EMP WHERE DEP_NO = N_DEP_NO;
    RETURN N_SUM_COUNT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('data not found!');
    WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END GET_SALARY;
/

3、函数的调用

        函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数：

• 位置表示法：

        即在调用时按形参的排列顺序，依次写出实参的名称，而将形参与实参关联起来进行传递。用这种方法进行调用，形参与实参的名称是相互独立，没有关系，强调次序才是重要的。

DECLARE
    V_SUM NUMBER;
    V_NUM NUMBER;
BEGIN
    V_SUM := GET_SALARY(501, V_NUM);
    DBMS_OUTPUT.PUT_LINE('部门号为:501的工资总和：'||V_SUM||'，人数为：'||V_NUM);
END;
/

•  名称表示法：

        即在调用时按形参的名称与实参的名称，写出实参对应的形参，而将形参与实参关联起来进行传递。这种方法，形参与实参的名称是相互独立的，没有关系，名称的对应关系才是最重要的，次序并不重要。

DECLARE
    V_SUM NUMBER;
    V_NUM NUMBER;
BEGIN
    V_SUM := GET_SALARY(N_EMP_COUNT => V_NUM, N_DEP_NO => 501);
    DBMS_OUTPUT.PUT_LINE('部门号为:501的工资总和：'||V_SUM||'，人数为：'||V_NUM);
END;
/

• 组合传递：

        即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种方式传递参数时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。

DECLARE
    V_SUM NUMBER;
    V_NUM NUMBER;
BEGIN
    V_SUM := GET_SALARY(501, N_EMP_COUNT => V_NUM);
    DBMS_OUTPUT.PUT_LINE('部门号为:501的工资总和：'||V_SUM||'，人数为：'||V_NUM);
END;
/

        无论采用哪一种参数传递方法，实际参数和形式参数之间的数据传递只有两种方法：传址法和传值法。

        所谓传址法是指在调用函数时，将实际参数的地址指针传递给形式参数，使形式参数和实际参数指向内存中的同一区域，从而实现参数数据的传递。这种方法又称作参照法，即形式参数参照实际参数数据。输入参数均采用传址法传递数据。

        传值法是指将实际参数的数据拷贝到形式参数，而不是传递实际参数的地址。默认时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。

4、参数默认值 

        在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值（不能为输入/输出参数设置默认值）。

CREATE OR REPLACE FUNCTION demo_fun(
    Name VARCHAR2,
    Age INTEGER,
    Sex VARCHAR2 DEFAULT '男')
    RETURN VARCHAR2 
AS
    V_var VARCHAR2(32);
BEGIN
    V_var := name||':'||TO_CHAR(age)||'岁'||sex;
    RETURN v_var;
END DEMO_FUN;
/

DECLARE
    Var VARCHAR(32);
BEGIN
    Var := demo_fun('user1', 30);
    DBMS_OUTPUT.PUT_LINE(var);

    Var := demo_fun('user2', age => 40);
    DBMS_OUTPUT.PUT_LINE(var);

    Var := demo_fun('user3', sex => '女', age => 20);
    DBMS_OUTPUT.PUT_LINE(var);
END;

5、其他实例

（1）以下代码创建一个to_mmssyy_or_null()函数。

CREATE OR REPLACE FUNCTION TO_MMSSYY_OR_NULL (V_DATE IN VARCHAR2)
RETURN DATE IS
BEGIN
    RETURN TO_DATE(V_DATE,'MM/DD/YYYY');
EXCEPTION
    WHEN OTHERS THEN
    RETURN NULL;
END TO_MMSSYY_OR_NULL;
/

（2）调用方式：

-- 第一种方式
SELECT TO_MMSSYY_OR_NULL('01012022') FROM DUAL;

-- 第二种方式
BEGIN
    DBMS_OUTPUT.PUT_LINE(TO_MMSSYY_OR_NULL('01012022'));
END;
/

-- 第三种方式
DECLARE
    V_DATE DATE;
BEGIN
    V_DATE := TO_MMSSYY_OR_NULL('01012022');
    DBMS_OUTPUT.PUT_LINE(TO_MMSSYY_OR_NULL('01012022'));
END;
/