15.PL_SQL——Function的创建和使用

================Example 1===============

[oracle@localhost notes]$ vim s81.sql

CREATE OR REPLACE FUNCTION check_sal RETURN Boolean

IS

    v_dept_idemployees.department_id%TYPE;

    v_empnoemployees.employee_id%TYPE;

    v_sal   employees.salary%TYPE;

    v_avg_salemployees.salary%TYPE;

BEGIN

    v_empno:=205;

    SELECT salary,department_id

    INTO v_sal,v_dept_id

    FROM employees

    WHERE employee_id= v_empno;

    SELECT avg(salary)

    INTO v_avg_sal

    FROM employees

    WHEREdepartment_id=v_dept_id;

    IF v_sal > v_avg_salTHEN

        RETURN TRUE;

    ELSE

        RETURN FALSE;

    END IF;

EXCEPTION

    WHEN NO_DATA_FOUNDTHEN

        RETURN NULL;

END;

/

[oracle@localhost notes]$ vim s81_1.sql

SET SERVEROUTPUT ON

BEGIN

    IF (check_sal ISNULL) THEN

        DBMS_OUTPUT.PUT_LINE('Thefunction returned NULL due to exception');

    ELSIF(check_sal)THEN

       DBMS_OUTPUT.PUT_LINE('Salary > average');

    ELSE

       DBMS_OUTPUT.PUT_LINE('Salary < average');

    END IF;

END;

/

SQL> @notes/s81.sql 

Function created.

SQL> @notes/s81_1.sql

Salary > average

PL/SQL procedure successfully completed

===========Example 2==============

[oracle@localhost notes]$ vim s82.sql

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER)

    RETURN NUMBER IS

BEGIN

    RETURN (p_value *0.08);

END tax;

/

SELECT employee_id, last_name, salary, tax(salary)

FROM employees

WHERE department_id = 100;

SQL> @notes/s82.sql

Function created.

EMPLOYEE_ID LAST_NAME                     SALARY TAX(SALARY)

----------- ------------------------- ---------- -----------

        108Greenberg                      12008      960.64

        109Faviet                          9000         720

        110 Chen                            8200         656

        111Sciarra                         7700         616

        112 Urman                           7800         624

        113 Popp                            6900         552

6 rows selected.

================Example 3=====================

[oracle@localhost notes]$ vim s87.sql

CREATE OR REPLACE FUNCTION dml_call_sql(p_sal NUMBER)

    RETURN NUMBER

    IS

    BEGIN

        INSERT INTOemployees(employee_id, last_name, email, hire_date, job_id, salary)

        VALUES(1,'Frost', 'jfrost@company.com', SYSDATE, 'SA_MAN', p_sal);

        RETURN (p_sal+ 100 );

    END;

    /

    UPDATE employees SET salary =dml_call_sql(2000)

    WHERE employee_id= 170;

SQL> @notes/s87.sql

 Function created.

        UPDATEemployees SET salary = dml_call_sql(2000)

                                      *

ERROR at line 1:

ORA-04091: table HR.EMPLOYEES is mutating, trigger/functionmay not see it

ORA-06512: at "HR.DML_CALL_SQL", line 5

[oracle@localhost notes]$ vim s87.sql

CREATE OR REPLACE FUNCTION dml_call_sql(p_sal NUMBER)

    RETURN NUMBER

    IS

    BEGIN

        RETURN (p_sal+ 100 );

    END;

    /

    UPDATE employeesSET salary = dml_call_sql(2000)

WHERE employee_id = 170;

SQL> @notes/s87.sql

Function created.

1 row updated.

==================Example 4: Can’t searchthe same table===================

[oracle@localhost notes]$ vims88.sql

CREATE OR REPLACE FUNCTIONdml_call_sql(p_sal NUMBER)

    RETURN NUMBER

    IS

        name employees.last_name%TYPE;

    BEGIN

        SELECT last_name

        INTO name

        FROM employees

        WHERE employee_id = 170;

        RETURN (p_sal + 100 );

    END;

    /

    UPDATE employees SET salary =dml_call_sql(2000)

    WHERE employee_id = 170;

SQL> @notes/s88.sql

Function created.

        UPDATE employees SET salary =dml_call_sql(2000)

                                      *

ERROR at line 1:

ORA-04091: table HR.EMPLOYEES ismutating, trigger/function may not see it

ORA-06512: at"HR.DML_CALL_SQL", line 9

==================Example 5 : Can searchanother table========================

[oracle@localhost notes]$ vims89.sql

CREATE OR REPLACE FUNCTIONdml_call_sql(p_sal NUMBER)

    RETURN NUMBER

    IS

        id departments.department_id%TYPE;

    BEGIN

        SELECT department_id

        INTO id

        FROM departments

        WHERE department_id = 270;

        DBMS_OUTPUT.PUT_LINE('Department ID is:' || id);

        RETURN (p_sal + 100 );

    END;

    /

    UPDATE employees SET salary =dml_call_sql(2000)

    WHERE employee_id = 170;

SQL> @notes/s89.sql

 Function created.

Department ID is: 270

1 row updated.

==================Example 6=========================

[oracle@localhostnotes]$ vim s90.sql

CREATE OR REPLACEFUNCTION f(

    p_parameter_1 IN NUMBER DEFAULT 1,

    p_parameter_5 IN NUMBER DEFAULT 5)

RETURN NUMBER

IS

    v_var number;

BEGIN

    v_var := p_parameter_1 + (p_parameter_5 *2);

    RETURN v_var;

END f;

/

SELECTf(p_parameter_5 => 10) FROM DUAL;

SQL> @notes/s90.sql

Function created.

F(P_PARAMETER_5=>10)

--------------------

                  21