How do I apply IN parameter as my_email and OUT as my_salary in this procedure:
CREATE OR REPLACE PROCEDURE FYI_CENTER AS
my_email employees.email%TYPE; -- **IN Parameter**
my_salary employees.salary%TYPE; -- **OUT Parameter**
BEGIN
SELECT email, salary INTO my_email, my_salary
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('My email = ' || my_email);
DBMS_OUTPUT.PUT_LINE('My salary = ' || my_salary);
END;
You set the IN parameter and add the OUT parameter as a variable like this:
DECLARE
salary employees.salary%TYPE;
BEGIN
FYI_CENTER('[email protected]', salary);
END;
IN and OUT relative to what? Neither is a parameter at the moment. It looks more like you want both of those to be OUT parameters, and the employee ID to be an IN parameter, something like:
CREATE OR REPLACE PROCEDURE FYI_CENTER(my_id IN number, my_email OUT varchar2,
my_salary OUT varchar2) AS
BEGIN
SELECT email, salary INTO my_email, my_salary
FROM employees WHERE employee_id = my_id;
END;
... which you call call something like:
DECLARE
my_email employees.email%TYPE;
my_salary employees.salary%TYPE;
BEGIN
FYI_CENTER(101, my_email, my_salary);
DBMS_OUTPUT.PUT_LINE('My email = ' || my_email);
DBMS_OUTPUT.PUT_LINE('My salary = ' || my_salary);
END;
You can't specify the exact format of the input and output variables (`%TYPE') in a procedure, just the generic format type.
As Tony pointed out, you could declare the procedure as:
CREATE OR REPLACE PROCEDURE FYI_CENTER(my_id IN employees.employee_id%TYPE,
my_email OUT employees.email%TYPE
my_salary OUT employees.salary%TYPE) AS
BEGIN
...
You can't return a value into an IN parameter. What I suspect you want is a general purpose procedure like this:
CREATE OR REPLACE PROCEDURE FYI_CENTER
( p_employee_id IN employees.employee_id%TYPE
, p_email OUT employees.email%TYPE
, p_salary OUT employees.salary%TYPE
)
AS
BEGIN
SELECT email, salary INTO p_email, p_salary
FROM employees WHERE employee_id = p_employee_id;
END;
/
... which you can then call like this:
DECLARE
my_email employees.email%TYPE; -- **IN Parameter**
my_salary employees.salary%TYPE; -- **OUT Parameter**
BEGIN
fyi_center (101, my_email, my_salary);
DBMS_OUTPUT.PUT_LINE('My email = ' || my_email);
DBMS_OUTPUT.PUT_LINE('My salary = ' || my_salary);
END;
/