开发者

how to apply parameters in this procedure?

开发者 https://www.devze.com 2023-01-17 09:59 出处:网络
How do I apply IN parameter as my_email and OUT as my_salary in this procedure: CREATE OR REPLACE PROCEDURE FYI_CENTER AS

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
    FRO开发者_运维技巧M employees WHERE employee_id = 101;

  DBMS_OUTPUT.PUT_LINE('My email = ' || my_email);
  DBMS_OUTPUT.PUT_LINE('My salary = ' || my_salary);
END;


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;
/


You set the IN parameter and add the OUT parameter as a variable like this:

DECLARE   
    salary employees.salary%TYPE;    
BEGIN    
    FYI_CENTER('jonhdoe@email.com', 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
...
0

精彩评论

暂无评论...
验证码 换一张
取 消