Oracle function Select Into not working correctly-CodePudding

Hi all i have the code below :

CREATE OR REPLACE FUNCTION GET_STATE_USER (user_id IN NUMBER)
   RETURN VARCHAR2
AS
   staffName      VARCHAR2 (50);
BEGIN
DBMS_OUTPUT.put_line(user_id);
   SELECT bs.FIRST_NAME || ' ' || bs.LAST_NAME
     INTO staffName
     FROM EMPLOYEE e
    WHERE bs.USER_ID = user_id;

   RETURN (CASE
              WHEN staffName IS NOT NULL THEN staffName
              ELSE 'Invalid'
           END);
END;
/

And I'm getting following error : ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "GET_STATE_USER", line 8

CodePudding user response：

You must change the name of the input variable. The variable name must not be the same as the table column name.

try this :

CREATE OR REPLACE NONEDITIONABLE FUNCTION GET_STATE_USER (user__id IN NUMBER)
   RETURN VARCHAR2
AS
   staffName VARCHAR2 (50);
BEGIN
DBMS_OUTPUT.put_line(user__id);
   SELECT bs.FIRST_NAME || ' ' || bs.LAST_NAME
     INTO staffName
     FROM EMPLOYEE bs
    WHERE bs.USER_ID = user__id;

   RETURN (CASE
              WHEN staffName IS NOT NULL THEN staffName
              ELSE 'Invalid'
           END);
END;

CodePudding user response：

Actually I wanted to check two tables for existence of a record and finally the code below did the trick :

CREATE OR REPLACE FUNCTION GET_STATE_USER (userId IN NUMBER)
   RETURN VARCHAR2
AS
   r_count     NUMBER;
   userTitle   VARCHAR2 (100);
BEGIN
   SELECT COUNT (1)
     INTO r_count
     FROM EMPLOYEE bs
    WHERE bs.USER_ID = userId;

   IF r_count > 0
   THEN
      SELECT bs.FIRST_NAME || ' ' || bs.LAST_NAME
        INTO userTitle
        FROM EMPLOYEE bs
       WHERE bs.USER_ID = userId;
   ELSE
      SELECT ac.name
        INTO userTitle
        FROM allcustomer ac
       WHERE ac.CUSTOMER_USER_ID = userId;
   END IF;

   RETURN (CASE
              WHEN userTitle IS NOT NULL THEN userTitle
              ELSE 'inavlid'
           END);
END;
/