Function in PL/SQL:
Functions are similar to Procedure except Function
can return a value using RETURN keyword.
Syntax:
|
Create
[OR Replace] Function Function_name [(Parameter_Name
[IN | OUT |IN OUT] type [,…])] Return
return_datatype IS
| As [ Declaration statements ] Begin [ Executable statements ] Return variable_name; Exception [ Exception handler ] End
Function_name; |
Syntax Explanation:
· · RETURN keyword is used to return a value
Example:
This example for finding total number
of customers in the customer table:
|
CREATE OR
REPLACE FUNCTION total_Customers RETURN number IS total number := 0; BEGIN SELECT count(*) into total FROM hz_cust_accounts; RETURN total; END; |
Execution method:
DECLARE
cust
number(2);
BEGIN
cust:=
total_Customers();
dbms_output.put_line('Total no.of Customers: ' || cust);
END;
Following Function used to return the
employee name in the particular
Department using cursor.
|
CREATE OR
REPLACE FUNCTION Find_emp (p_deptno IN NUMBER) RETURN VARCHAR2 IS v_ename
VARCHAR2 (2000); CURSOR c_cur IS SELECT deptno FROM dept WHERE deptno = p_deptno; BEGIN FOR v_cur IN c_cur LOOP BEGIN SELECT LISTAGG(ename, ',') WITHIN
GROUP (ORDER BY ename) INTO v_ename FROM emp WHERE emp.deptno = v_cur.deptno; DBMS_OUTPUT.put_line ( 'deptno=' ||
v_cur.deptno ||' ' || 'emp name
=' || v_ename ); EXCEPTION WHEN NO_DATA_FOUND THEN RETURN v_ename; WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.put_line ( 'dept no=' ||
v_cur.deptno ||' ' || ',emp name :' || v_ename ); END; END LOOP; RETURN v_ename; END Find_emp; |
LISTAGG :
LISTAGG (<expression>,<separator> WITHIN GROUP(order by ...)
Execution method:
DECLARE
v_result VARCHAR2(4000);
BEGIN
v_result :=
xxdtj_emp (p_deptno => 10 );
DBMS_OUTPUT.put_line
('Return :' || v_result);
END;
Drop Function:
|
Drop
function function_name;
|
Example:
|
Drop
function find_emp;
|
