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

老爷子88

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

　　>　　BEGIN
　　SELECT department_id

　　INTO>　　FROM departments
　　WHERE department_id = 270;

　　DBMS_OUTPUT.PUT_LINE('Department>　　RETURN (p_sal + 100 );
　　END;
　　/
　　UPDATE employees SET salary =dml_call_sql(2000)
　　WHERE employee_id = 170;
　　SQL> @notes/s89.sql
　　Function created.

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