目前创新互联已为上千家的企业提供了网站建设、域名、网络空间、网站托管维护、企业网站设计、武山网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。

================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

网站栏目：15.PL_SQL——Function的创建和使用
标题路径：http://lswzjz.com/article/ghhipo.html