How to fetch the merchant id by giving the customer id by using function?-CodePudding

I have table called "CUSTOMER BASIC INFO". which are having customer id and merchant id. now I want write a function for fetching merchant id by giving customer id as input parameter. so please answer me detail;

CodePudding user response：

CREATE PROCEDURE GetMerchantId @cid dataTypeOfCustomerId(length)
AS
SELECT merchant_id FROM CUSTOMER_BASIC_INFO WHERE customer_id = @cid
GO;

Call it as

EXEC GetMerchantId @cid = '5';

CodePudding user response：

Code is kind of simple:

create or replace function f_test (par_customer_id in customer_basic_info.customer_id%type)
  return customer_basic_info.merchant_id%type
is
  retval customer_basic_info.merchant_id%type;
begin
  select merchant_id
  into retval
  from customer_basic_info
  where customer_id = par_customer_id;

  return retval;
end;
/

However, this will return TOO_MANY_ROWS error of customer has more than a single merchant (which is quite possible), so - what would you want to return in such a case? Any merchant? No merchant? Let function raise an error? Raise it, but you'll handle it somehow?