Oracle function call Stored Procedure

1. Call a stored procedure without Parameters

-- Create a stored procedure without Parameters
Create or replace function stu_proc RETURN VARCHAR2 IS
-- Declaration statement segment
V_name varchar2 (20 );
BEGIN
-- Execution statement segment
SELECT o. sname INTO v_name FROM student o where o. id = 1;
RETURN v_name;
END;

-- Call a stored procedure without Parameters
DECLARE
BEGIN
DBMS_OUTPUT.put_line ('result printed in PL/SQL: '| stu_proc );
END;

 

2. Call the input parameter Stored Procedure

-- Create an input parameter Stored Procedure
Create or replace function stu_proc (v_id in number) RETURN VARCHAR2 IS
-- Declaration statement segment
V_name varchar2 (20 );
BEGIN
-- Execution statement segment
SELECT o. sname INTO v_name FROM student o where o. id = v_id;
RETURN v_name;
END;

-- Call the input parameter Stored Procedure
DECLARE
BEGIN
DBMS_OUTPUT.put_line ('result printed in PL/SQL: '| stu_proc (1 ));
END;

3. Call the output parameter Stored Procedure

-- Create an output parameter Stored Procedure
Create or replace function stu_proc (v_name OUT VARCHAR2) RETURN VARCHAR2 IS
BEGIN
-- Execution statement segment
SELECT o. sname INTO v_name FROM student o where o. id = 2;
RETURN v_name;
END;

-- Call the output parameter Stored Procedure
DECLARE
V_name student. sname % type;
BEGIN
DBMS_OUTPUT.put_line ('result printed in PL/SQL: '| stu_proc (v_name ));
END;

4. Call the stored procedure of Input and Output Parameters

-- Create a stored procedure for Input and Output Parameters
Create or replace function stu_proc (v_id in number, v_name OUT VARCHAR2) RETURN VARCHAR2 IS
BEGIN
-- Execution statement segment
SELECT o. sname INTO v_name FROM student o where o. id = v_id;
RETURN v_name;
END;

-- Call the stored procedure of Input and Output Parameters
DECLARE
V_name VARCHAR2 (20 );
BEGIN
DBMS_OUTPUT.put_line ('result printed in PL/SQL: '| stu_proc (1, v_name ));
END;