Oracle stored procedure [with input and output parameters]

 

Create or replace procedure proc_emp (ENO in number, Sal out number) -- stored procedure with Input and Output Parameters

As

Emp_records EMP % rowtype; -- declares a record type to store the result set.

Begin

Select * into emp_records from EMP where empno = Eno; -- assign the query result a record type.

SAL: = emp_records.sal; -- assign a value to the output parameter

-- Dbms_output.put_line (emp_records.ename | emp_records.sal );

End;

-- Execute the Stored Procedure

Declare

Emp_no EMP. empno % type; -- declare the variable of an employee ID

Emp_sal EMP. Sal % type; -- declare an employee's salary variable

Begin

Emp_no: = '& amp; empno'; -- too input parameter for slave control

Proc_emp (emp_no, emp_sal); -- call the stored procedure and input parameters

Dbms_output.put_line (emp_sal); -- display output parameters

End;

 

 

Summary: or indicates that it is optional. Do not declare the length of the parameter type. Rowtype is used to declare the record type.

If you want to assign a value to the output parameter when using the output parameter, otherwise the result will not be displayed.