Create Procedure with IN OUT parameter

Submitted by:Andery Smith

Date added:08 March, 2015

Category:PL SQL

You can define parameters with three types of parameters.

1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.

2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.

3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

Tags: procedure , function , parameter

Code Snippet:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];


-- example:

CREATE OR REPLACE FUNCTION inout_fn (outparm IN OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'Coming out';
RETURN 'return param';
END inout_fn;
/

SET serveroutput ON

DECLARE
retval VARCHAR2(20);
ioval VARCHAR2(20) := 'Going in';
BEGIN
DBMS_OUTPUT.put_line('In: ' || ioval);
retval := inout_fn(ioval);
DBMS_OUTPUT.put_line('Out: ' || ioval);
DBMS_OUTPUT.put_line('Return: ' || retval);
END;
/
 
 

Comments