How to call a Function in Oracle?
Creating two functions to test all the cases:
Function 1: This will take employee_id and a string as parameters and return employee's salary or total salary, average salary, maximum salary, minimum salary of employee's department based on string parameter passed.
--How to call a Function in Oracle? : Example
CREATE OR REPLACE FUNCTION FUN_GET_EMP_SAL(P_EMPID NUMBER, P_STRING VARCHAR2)
RETURN NUMBER
IS
V_SAL EMPLOYEES.EMPLOYEE_ID%TYPE := 0;
BEGIN
IF P_STRING = 'E' THEN
BEGIN
SELECT SALARY
INTO V_SAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID;
EXCEPTION
WHEN OTHERS THEN
V_SAL := 0;
END;
ELSIF P_STRING = 'S' THEN
SELECT SUM(SALARY)
INTO V_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID
);
ELSIF P_STRING = 'A' THEN
SELECT AVG(SALARY)
INTO V_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID
);
ELSIF P_STRING = 'H' THEN
SELECT MAX(SALARY)
INTO V_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID
);
ELSIF P_STRING = 'L' THEN
SELECT MIN(SALARY)
INTO V_SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID
);
END IF;
--
RETURN V_SAL;
EXCEPTION
WHEN OTHERS THEN
V_SAL := 0;
RETURN V_SAL;
END;
/ Function 2: This function will take employee_id and a string as parameter and return employee's full name or employee's department name based on the string value passed.
--How to call a Function in Oracle? : Example
CREATE OR REPLACE FUNCTION FUN_GET_EMP_NAME(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE, P_STRING VARCHAR2)
RETURN VARCHAR2
IS
V_EMP_DEPT_NAME VARCHAR2(100);
BEGIN
IF P_STRING = 'E' THEN
BEGIN
SELECT FIRST_NAME||' '||LAST_NAME
INTO V_EMP_DEPT_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID;
EXCEPTION
WHEN OTHERS THEN
V_EMP_DEPT_NAME := NULL;
END;
--
ELSIF P_STRING = 'D' THEN
BEGIN
SELECT DEPARTMENT_NAME
INTO V_EMP_DEPT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID
);
EXCEPTION
WHEN OTHERS THEN
V_EMP_DEPT_NAME := NULL;
END;
END IF;
RETURN V_EMP_DEPT_NAME;
EXCEPTION
WHEN OTHERS THEN
V_EMP_DEPT_NAME := NULL;
RETURN V_EMP_DEPT_NAME;
END;
/ How to call a function from another package in Oracle?
We will create a package for calling the above two functions:
--How to call a Function in Oracle? : Example CREATE OR REPLACE PACKAGE PKG_GET_EMP_DATA IS PROCEDURE SP_GET_EMP_DATA(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE, P_STRING OUT VARCHAR2); FUNCTION FUN_GET_EMP_DATA(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN VARCHAR2; END; /
--How to call a Function in Oracle? : Example
CREATE OR REPLACE PACKAGE BODY PKG_GET_EMP_DATA
IS
V_EMP_SAL EMPLOYEES.SALARY%TYPE := 0;
V_TOTAL_SAL EMPLOYEES.SALARY%TYPE := 0;
V_AVG_SAL EMPLOYEES.SALARY%TYPE := 0;
V_MAX_SAL EMPLOYEES.SALARY%TYPE := 0;
V_MIN_SAL EMPLOYEES.SALARY%TYPE := 0;
--
V_EMP_NAME VARCHAR2(100) := NULL;
V_DEPT_NAME VARCHAR2(100) := NULL;
--
PROCEDURE SP_GET_EMP_DATA(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE, P_STRING OUT VARCHAR2)
IS
BEGIN
V_EMP_SAL := FUN_GET_EMP_SAL(P_EMPID, 'E');
V_TOTAL_SAL := FUN_GET_EMP_SAL(P_EMPID, 'S');
V_AVG_SAL := FUN_GET_EMP_SAL(P_EMPID, 'A');
V_MAX_SAL := FUN_GET_EMP_SAL(P_EMPID, 'H');
V_MIN_SAL := FUN_GET_EMP_SAL(P_EMPID, 'L');
--
V_EMP_NAME := FUN_GET_EMP_NAME(P_EMPID, 'E');
V_DEPT_NAME := FUN_GET_EMP_NAME(P_EMPID, 'D');
--
P_STRING := 'EMPLOYEE -> '||V_EMP_NAME||' HAS SALARY OF '||V_EMP_SAL||
', WORKING IN DEPARTMENT '||V_DEPT_NAME||' HAVING TOTAL SALARY '||
V_TOTAL_SAL||', AVERAGE SALARY '||V_AVG_SAL||', HIGHEST SALARY '||
V_MAX_SAL||', LOWEST SALARY '||V_MIN_SAL;
EXCEPTION
WHEN OTHERS THEN
P_STRING := NULL;
END;
--
FUNCTION FUN_GET_EMP_DATA(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE)
RETURN VARCHAR2
IS
V_STRING VARCHAR2(1000) := NULL;
BEGIN
V_EMP_SAL := FUN_GET_EMP_SAL(P_EMPID, 'E');
V_EMP_NAME := FUN_GET_EMP_NAME(P_EMPID, 'E');
V_DEPT_NAME := FUN_GET_EMP_NAME(P_EMPID, 'D');
--
V_STRING := 'EMPLOYEE -> '||V_EMP_NAME||' HAS SALARY OF '||V_EMP_SAL||
', WORKING IN DEPARTMENT '||V_DEPT_NAME;
--
RETURN V_STRING;
EXCEPTION
WHEN OTHERS THEN
V_STRING := NULL;
RETURN V_STRING;
END;
END;
/ Calling part:
--How to call a Function in Oracle? : Example calling
SET SERVEROUTPUT ON;
DECLARE
V_STRING VARCHAR2(1000) := NULL;
BEGIN
V_STRING := PKG_GET_EMP_DATA.FUN_GET_EMP_DATA(100);
DBMS_OUTPUT.PUT_LINE('DETAILS -> '||V_STRING);
END;
/ PL/SQL procedure successfully completed. DETAILS -> EMPLOYEE -> Steven King HAS SALARY OF 24000, WORKING IN DEPARTMENT Executive
How to call a function inside another function in Oracle?
We will create a new function to call the functions FUN_GET_EMP_SAL and FUN_GET_EMP_NAME:
--How to call a Function in Oracle? : Example
CREATE OR REPLACE FUNCTION FUN_GET_EMP_DATA_CALL(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE)
RETURN VARCHAR2
IS
V_EMP_SAL EMPLOYEES.SALARY%TYPE := 0;
V_EMP_NAME VARCHAR2(100) := NULL;
V_DEPT_NAME VARCHAR2(100) := NULL;
V_STRING VARCHAR2(1000) := NULL;
BEGIN
V_EMP_SAL := FUN_GET_EMP_SAL(P_EMPID, 'E');
V_EMP_NAME := FUN_GET_EMP_NAME(P_EMPID, 'E');
V_DEPT_NAME := FUN_GET_EMP_NAME(P_EMPID, 'D');
--
V_STRING := 'EMPLOYEE -> '||V_EMP_NAME||' HAS SALARY OF '||V_EMP_SAL||
', WORKING IN DEPARTMENT '||V_DEPT_NAME;
--
RETURN V_STRING;
EXCEPTION
WHEN OTHERS THEN
V_STRING := NULL;
RETURN V_STRING;
END;
/ Calling part:
SET SERVEROUTPUT ON;
DECLARE
V_STRING VARCHAR2(1000) := NULL;
BEGIN
V_STRING := FUN_GET_EMP_DATA_CALL(100);
DBMS_OUTPUT.PUT_LINE('DETAILS -> '||V_STRING);
END;
/ PL/SQL procedure successfully completed. DETAILS -> EMPLOYEE -> Steven King HAS SALARY OF 24000, WORKING IN DEPARTMENT Executive
How to call function inside a procedure in Oracle?
We will create a new procedure to call the functions FUN_GET_EMP_SAL and FUN_GET_EMP_NAME:
--How to call a Function in Oracle? : Example
CREATE OR REPLACE PROCEDURE SP_GET_EMP_DATA_CALL
(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE,
P_STRING OUT VARCHAR2
)
IS
V_EMP_SAL EMPLOYEES.SALARY%TYPE := 0;
V_TOTAL_SAL EMPLOYEES.SALARY%TYPE := 0;
V_AVG_SAL EMPLOYEES.SALARY%TYPE := 0;
V_MAX_SAL EMPLOYEES.SALARY%TYPE := 0;
V_MIN_SAL EMPLOYEES.SALARY%TYPE := 0;
--
V_EMP_NAME VARCHAR2(100) := NULL;
V_DEPT_NAME VARCHAR2(100) := NULL;
BEGIN
V_EMP_SAL := FUN_GET_EMP_SAL(P_EMPID, 'E');
V_TOTAL_SAL := FUN_GET_EMP_SAL(P_EMPID, 'S');
V_AVG_SAL := FUN_GET_EMP_SAL(P_EMPID, 'A');
V_MAX_SAL := FUN_GET_EMP_SAL(P_EMPID, 'H');
V_MIN_SAL := FUN_GET_EMP_SAL(P_EMPID, 'L');
--
V_EMP_NAME := FUN_GET_EMP_NAME(P_EMPID, 'E');
V_DEPT_NAME := FUN_GET_EMP_NAME(P_EMPID, 'D');
--
P_STRING := 'EMPLOYEE -> '||V_EMP_NAME||' HAS SALARY OF '||V_EMP_SAL||
', WORKING IN DEPARTMENT '||V_DEPT_NAME||' HAVING TOTAL SALARY '||
V_TOTAL_SAL||', AVERAGE SALARY '||V_AVG_SAL||', HIGHEST SALARY '||
V_MAX_SAL||', LOWEST SALARY '||V_MIN_SAL;
EXCEPTION
WHEN OTHERS THEN
P_STRING := NULL;
END;
/ Calling part:
SET SERVEROUTPUT ON;
DECLARE
V_STRING VARCHAR2(1000) := NULL;
BEGIN
SP_GET_EMP_DATA_CALL(100, V_STRING);
DBMS_OUTPUT.PUT_LINE('DETAILS -> '||V_STRING);
END;
/ PL/SQL procedure successfully completed. DETAILS -> EMPLOYEE -> Steven King HAS SALARY OF 24000, WORKING IN DEPARTMENT Executive HAVING TOTAL SALARY 58000, AVERAGE SALARY 19333, HIGHEST SALARY 24000, LOWEST SALARY 17000
How to call function in view in Oracle?
Creating a new view for calling the functions FUN_GET_EMP_SAL and FUN_GET_EMP_NAME
--How to call a Function in Oracle? : Example
CREATE OR REPLACE VIEW VW_GET_EMP_DATA_CALL
AS
SELECT EMPLOYEE_ID,
FUN_GET_EMP_NAME(EMPLOYEE_ID, 'E') EMP_NAME,
FUN_GET_EMP_SAL(EMPLOYEE_ID, 'E') SALARY,
FUN_GET_EMP_SAL(EMPLOYEE_ID, 'S') TOTAL_DEPT_SALARY,
FUN_GET_EMP_SAL(EMPLOYEE_ID, 'A') AVG_DEPT_SALARY,
FUN_GET_EMP_SAL(EMPLOYEE_ID, 'H') MAX_DEPT_SALARY,
FUN_GET_EMP_SAL(EMPLOYEE_ID, 'L') MIN_DEPT_SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; SELECT * FROM VW_GET_EMP_DATA_CALL;
How to call function in a SQL query in Oracle?
--How to call a Function in Oracle? : Example
SELECT EMPLOYEE_ID,
FUN_GET_EMP_NAME(EMPLOYEE_ID, 'E') EMP_NAME,
FUN_GET_EMP_SAL(EMPLOYEE_ID, 'E') SALARY,
FUN_GET_EMP_SAL(EMPLOYEE_ID, 'S') TOTAL_DEPT_SALARY,
FUN_GET_EMP_SAL(EMPLOYEE_ID, 'A') AVG_DEPT_SALARY,
FUN_GET_EMP_SAL(EMPLOYEE_ID, 'H') MAX_DEPT_SALARY,
FUN_GET_EMP_SAL(EMPLOYEE_ID, 'L') MIN_DEPT_SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20; RELATED TOPICS: