Functions in PL/SQL

 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:

 ·       A Function is created with the Create [OR Replace] Function statement.

·        ·    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;

 In his program to separate the customer name in comma  (,) separator using

 LISTAGG clause.

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;

 

 

 

 

Share: