Question

Consider these two Oracle Stored Procedures / Functions:

Assume that both have been successfully compiled.

CREATE OR REPLACE FUNCTION CALC(pamt number) RETURN number AS

vTot number;

BEGIN

    vTot := pamt + pamt ;

     Return vTot;

END;

CREATE OR REPLACE PROCEDURE SHOW(pstr VARCHAR2) AS

BEGIN

    dbms_output.put_line(pstr);

END;

Write an anonymous block that executes the function named CALC and then the procedure called SHOW.

The output generated by SHOW must be: The total is 20

Answer 1

-- Function to calculate sum of itself.
CREATE OR REPLACE FUNCTION CALC(pamt number) RETURN number AS

vTot number;

BEGIN

    vTot := pamt + pamt ;

     Return vTot;

END;

-- Procedure to print the given string.
CREATE OR REPLACE PROCEDURE SHOW(pstr VARCHAR2) AS

BEGIN

    dbms_output.put_line(pstr);

END;


-- Anonymous block to call function to find sum and then print the result by calling procedure SHOW.
DECLARE
    pamt varchar2(50);
    result varchar2(50);
BEGIN
    SELECT CALC(10) into pamt FROM DUAL; -- This is how you call function in PL/SQL 
    result := 'The Total is ' || pamt;
    SHOW(result); -- Usually procedure are called using EXEC statement. But since we are calling this inside anonymous block we can call it directly without exec statement.
END;

Summary:

Anonymous block is nothing but a procedure without any name. It contains three blocks DECLARE, BEGIN and END. Just like a stored procedure in Oracle.

Way to call function is :-

Select function_name( if any parameters) from dual;

Way to call procedure:-

EXEC procedure_name(if any parameters);