IN OUT Mode:

The IN OUT parameter is used to pass initial values to the subprogram. It is a read-write variable and can be used for both writing and reading values. Inside the subprogram the IN out parameter acts like an un-initialized variable. An instance for displaying the product name and the price is display below:

CREATE OR REPLACE PROCEDURE disp_prod(no IN OUT NUMBER, name OUT VARCHAR2) AS

BEGIN

SELECT pname,ucSt INTO name,no FROM product WHERE pcode=no;

END;

The output of the above program is , Procedure created.

The procedure, which contains an IN OUT parameter, cannot be executed at SQL prompt. A subprogram that contains an IN OUT parameter must be executed only inside a PL/SQL block. The below program shows how to execute an IN OUT parameter.

Example

DECLARE

Name VARCHAR2(20);

Var_x NUMBER:=&X;

BEGIN

DISP_PROD(VAR_X,NAME);

DBMS_OUTPUT.PUT_LINE('name is '||name ||' rate is '||var_x);

END;

The program accepts the number and the rate are assigned to the similar variable and the name of the product is assigned to the variable name.

The output would display like:

Enter value for x: 102

old       3: Var_x NUMBER:=&X;

new     3: Var_x NUMBER:=102;

name is storewell rate is 10000

PL/SQL procedure successfully finished.

The below table describes the differences among IN, OUT and IN OUT Parameter modes.