15.PL_SQL——Function的創(chuàng)建和使用

目前創(chuàng)新互聯(lián)已為上千家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)絡(luò)空間、網(wǎng)站托管維護(hù)、企業(yè)網(wǎng)站設(shè)計(jì)、武山網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長(zhǎng)，共同發(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

本文標(biāo)題：15.PL_SQL——Function的創(chuàng)建和使用
文章源于：http://muchs.cn/article30/ghhipo.html

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

廣告

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