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
