目前創(chuàng)新互聯(lián)已為上千家的企業(yè)提供了網站建設、域名、網絡空間、網站托管維護、企業(yè)網站設計、武山網站維護等服務，公司將堅持客戶導向、應用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

================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的創(chuàng)建和使用
文章源于：http://jinyejixie.com/article30/ghhipo.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供品牌網站建設、服務器托管、品牌網站設計、網站建設、品牌網站制作、網頁設計公司

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)