SQL: Function select column into variable

Question

I need to select two columns in two variables how can i do it, here the code

CREATE OR REPLACE FUNCTION checkUser(pUserName VARCHAR2, pCountry VARCHAR2) 
RETURN NUMBER 

IS
   vUsername VARCHAR2(100); 
   vCountry VARCHAR2(100);

BEGIN
   SELECT Person.Username INTO vUsername
   FROM Person
   WHERE Person.Username = pUserName;
   EXCEPTION WHEN NO_DATA_FOUND THEN
       vUsername := NULL;

   SELECT Person.Country INTO vCountry
   FROM Person
   WHERE Person.Country = pCountry;
   EXCEPTION WHEN NO_DATA_FOUND THEN
      vCountry := NULL;

   IF vUsername IS NULL OR vCountry IS NULL THEN
      RETURN False;
   ELSE
      RETURN True;
   END IF;
END checkUser;

Can i do that? I am new in SQL.

Pretty sure you will have a problem if two Persons have the same country (ORA-01422) because you will attempt to store a list of countries in a VARCHAR2. What is your function supposed to do anyway? — Dessma
– Dessma, Commented Oct 11, 2017 at 18:13

Pang · Accepted Answer · 2017-10-13 01:24:56Z

1

Based on what you are trying to do, the following code will give you the desired result. Note that RETURN NUMBER you are using is not right if you need to return strings 'True' or 'False' .

CREATE OR replace FUNCTION Checkuser(pusername VARCHAR2,
                                     pcountry  VARCHAR2)
RETURN VARCHAR2
IS
  v_count NUMBER(10);
BEGIN
    SELECT Count(1)
    INTO   v_count
    FROM   person p
    WHERE  p.username = pusername
           AND p.country = pcountry;

    IF v_count = 0 THEN
      RETURN 'False';
    ELSE
      RETURN 'True';
    END IF;
END checkuser;

edited Oct 13, 2017 at 1:24

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Oct 11, 2017 at 18:22

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dessma · Accepted Answer · 2017-10-11 18:18:51Z

1

Selecting more than a single column is not much more complicated than what you are doing at the moment :

SELECT p.Username,p.Country INTO vUsername, vCountry  
   FROM Person p
   WHERE p.Username = pUserName AND p.Country = pCountry;

Just separate the columns with ',' after the SELECT and after the INTO.

Off topic, you need to make sure you don't have multiple records corresponding to your argument username and country otherwise you will have an error attempting to store a list of data in scalar variables.

answered Oct 11, 2017 at 18:18

Dessma

6093 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL: Function select column into variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related