Creation and use of 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 VALUES, 'jfrost@company.com', SYSDATE,' SA_MAN', p_sal)
RETURN (pendant sal100)
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 (pendant sal100)
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 (Please PARAMETERTER 5 = > 10)
-
twenty-one
