Home » SQL & PL/SQL » SQL & PL/SQL » Select statement inside IF statement for comparing a given string with records in table
Select statement inside IF statement for comparing a given string with records in table [message #679409] Fri, 28 February 2020 00:06 Go to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
Write a procedure (oracle plsql) to do any one of the following: (a) update the table course and set the fee of the input course name equal to fee of java course. (b) insert a new row for given input course and set the fee lowest of all courses available in the table. Condition is: do (a) if the input course name is already present in the table otherwise do (b) if the input course name is not in the table.

I am providing here the basic details of table:

create table course(cid number primary key, cname varchar2(100), duration number, fee number);
insert into course (CID, CNAME, DURATION, FEE)
values (101, 'java', 30, 13000);

insert into course (CID, CNAME, DURATION, FEE)
values (102, 'c', 20, 5000);

insert into course (CID, CNAME, DURATION, FEE)
values (104, 'oracle', 20, 20000);

insert into course (CID, CNAME, DURATION, FEE)
values (105, 'python', 20, 30000);

insert into course (CID, CNAME, DURATION, FEE)
values (106, 'sql', 20, 1000);
I tried the below code but i don't know how to compare the given name for each rows in the table inside IF statement. Please take a look in the code and help me.

create or replace procedure proc_CourseFeeUpdateTry(coursename in course.cname%type,
                                                    java_fee   out number) is
  n_fee number;
  j_fee number;
begin
    if course.cname = coursename then --i'm getting error here

      select t.fee into j_fee from course t where t.cname = 'java';
      java_fee := j_fee;
      update course t set t.fee = java_fee where t.cname = coursename;
      dbms_output.put_line('new course added');
    else
      dbms_output.put_line(sqlerrm || '-' || sqlcode);
      select min(t.fee) into n_fee from course t;
      java_fee := n_fee;
      insert into course values (103, coursename, 40, java_fee);
    end if;
  commit;
end;

[Updated on: Fri, 28 February 2020 00:27] by Moderator

Report message to a moderator

Re: Select statement inside IF statement for comparing a given string with records in table [message #679410 is a reply to message #679409] Fri, 28 February 2020 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
if course.cname = coursename then --i'm getting error here
To check this condition you have to SELECT the table for the input course; if it returns a row then do (a) UPDATE, if it raises a NO_DATA_FOUND error, you have to trap it (in EXCEPTION block) and do (b) INSERT.

Re: Select statement inside IF statement for comparing a given string with records in table [message #679411 is a reply to message #679410] Fri, 28 February 2020 03:12 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
i don't understand what you replied. You wrote the same thing which i mentioned in my question. Please correct the program if you know. Many thanks. Smile
Re: Select statement inside IF statement for comparing a given string with records in table [message #679413 is a reply to message #679411] Fri, 28 February 2020 03:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're getting an error because oracle has no idea what course.cname is in the context of the IF statement.
To look at data in tables you need to write select statements to select from the table.
You've got a select, but it's inside the IF.
Michel's telling you that you need to move it.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679414 is a reply to message #679413] Fri, 28 February 2020 03:32 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
ok i got it. But how can i write a select statement inside IF statement and then provide the condition? or can i? Please help me in this.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679416 is a reply to message #679414] Fri, 28 February 2020 03:56 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
MC has given you a method which will let you show good technique. If you prefer to present your teacher with a quick-and-dirty approach, you could do something like this:
orclz> create or replace procedure p1(en varchar2) as
  2  n number;
  3  begin
  4  select count(*) into n from emp where ename=en;
  5  if n<>0 then dbms_output.put_line('found');
  6  else dbms_output.put_line('not found');
  7  end if;
  8  end;
  9  /

Procedure created.

orclz> exec p1('KING')
found

PL/SQL procedure successfully completed.

orclz> exec p1('GARB')
not found

PL/SQL procedure successfully completed.

orclz>
Re: Select statement inside IF statement for comparing a given string with records in table [message #679418 is a reply to message #679416] Fri, 28 February 2020 04:07 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
thanks for this but i don't want to use any COUNTER variable. I just want to use IF statement and compare it in IF statement. If so by using CURSOR and then compare it in IF statement. Can we do this? Because i know we can compare the integer/number datatype or boolean exp in IF statement but now sure if we can use SELECT statement and compare it in IF statement or not.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679419 is a reply to message #679418] Fri, 28 February 2020 04:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't use a SELECT statement directly in an IF. You can either:
a) run the select to populate some variables before the IF and then check the variables in the IF.
b) put the select in a function and use the function in the IF.

However Michel is suggesting not having an IF and instead relying on the SELECT throwing no_data_found to control the code:

BEGIN

  SELECT stuff
  FROM table
  WHERE

  UPDATE table

EXCEPTION WHEN no_data_found THEN

  insert into table

END
Re: Select statement inside IF statement for comparing a given string with records in table [message #679420 is a reply to message #679419] Fri, 28 February 2020 04:35 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
INSERT or UPDATE is a normally case for MERGE.

So here as an alternative a simplified version with MERGE:

CREATE OR REPLACE PROCEDURE p1(coursename IN course.cname%type, in_fee   IN number) IS
BEGIN
     
      MERGE INTO course 
      USING dual
         ON (cname = coursename)
       WHEN MATCHED 
       THEN UPDATE
        SET fee = in_fee
       WHEN NOT MATCHED 
       THEN INSERT 
         VALUES (103, coursename, 40, in_fee);

END;

SHOW ERRORS;

EXEC p1('c',42) 

EXEC p1('plsql',30) 
 
SELECT * FROM course;

CID	CNAME	DURATION	FEE
--------------------------------------
101	java	30		13000
102	c	20		42
104	oracle	20		20000
105	python	20		30000
106	sql	20		1000
103	plsql	40		30

Re: Select statement inside IF statement for comparing a given string with records in table [message #679422 is a reply to message #679420] Fri, 28 February 2020 04:41 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
--msg for COOKIEMONSTER
i did exactly (a) as you suggested. I write the select statement and put its value in a variable and then i compare that variable with input variable inside the IF statement but it's showing me error.
If possible, can you modify my original code with what are you trying to say. I have provided the question also.

--msg for _JUM
thanks for your suggestion, i will try to understand your code also as im new. but atleast tell me that can i use IF statement for comparing or matching multiple STRINGS from select statement or not? of IF statement is only for boolean, numbers and one time string/word only?
Re: Select statement inside IF statement for comparing a given string with records in table [message #679423 is a reply to message #679420] Fri, 28 February 2020 04:42 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Oooh! Forum guidelines, jum: you are not meant to do other people's college homework for them Mad
Re: Select statement inside IF statement for comparing a given string with records in table [message #679424 is a reply to message #679423] Fri, 28 February 2020 04:52 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
---msg for JOHN WATSON
bro, its not like he is doing my home work. I already did but since im new i got some errors. I already did it using COUNTER but im trying using IF statement. So he is helping me. This is called help Smile Smile Smile
Re: Select statement inside IF statement for comparing a given string with records in table [message #679425 is a reply to message #679422] Fri, 28 February 2020 04:52 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
sfksuperman wrote on Fri, 28 February 2020 10:41
--msg for COOKIEMONSTER
i did exactly (a) as you suggested.
No, you definitely did not.

This is obvious at a glance.

John's code above is an example of a.

Your code does not have a select before the IF.
It has selects inside the IF.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679426 is a reply to message #679425] Fri, 28 February 2020 04:55 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
--msg for COOKIEMASTER

I tried using CURSOR where i defined the select statement and then i compared the value but getting error:

create or replace procedure proc_CourseFeeUpdateTry(coursename in course.cname%type,
                                                    java_fee   out number) is
  n_fee number;
  j_fee number;
  cursor get_cname is select t.cname from course t;
  xyz varchar2(100);
begin
    if coursename = get_cname.cname then
      open get_cname;
      fetch get_cname into xyz;
      select t.fee into j_fee from course t where t.cname = 'java';
      java_fee := j_fee;
      update course t set t.fee = java_fee where t.cname = coursename;
      dbms_output.put_line('new course added');
    else
      dbms_output.put_line(sqlerrm || '-' || sqlcode);
      select min(t.fee) into n_fee from course t;
      java_fee := n_fee;
      insert into course values (103, coursename, 40, java_fee);
    end if;
  commit;
end;

[Updated on: Fri, 28 February 2020 04:56]

Report message to a moderator

Re: Select statement inside IF statement for comparing a given string with records in table [message #679427 is a reply to message #679426] Fri, 28 February 2020 05:02 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
--msg for COOKIEMONSTER

I tried this code:

create or replace procedure proc_CourseFeeUpdateTry(coursename in course.cname%type,
                                                    java_fee   out number) is
  n_fee number;
  j_fee number;
  xyz varchar2(100);
begin
     select t.cname into xyz from course t;
    if xyz = coursename then
      select t.fee into j_fee from course t where t.cname = 'java';
      java_fee := j_fee;
      update course t set t.fee = java_fee where t.cname = coursename;
      dbms_output.put_line('new course added');
    else
      dbms_output.put_line(sqlerrm || '-' || sqlcode);
      select min(t.fee) into n_fee from course t;
      java_fee := n_fee;
      insert into course values (103, coursename, 40, java_fee);
    end if;
  commit;
end;
but im getting error at line no.7 where it says "ORA-01422: exact fetch returns more than requested number of rows".
and when im using CURSOR with INTO clause in BEGIN statement it is throwing some different error.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679428 is a reply to message #679427] Fri, 28 February 2020 05:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You are selecting every row in the table, whereas your variable can accept only one value.
If you go back to the example I gave you, you will see how to avoid the the ora-1422.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679429 is a reply to message #679426] Fri, 28 February 2020 05:06 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to read up on how cursors work.
get_cname.cname is not a thing.

Cursors are pointers to select statements.
Select statements are a way of getting data, but they don't hold any data.
To get the data you need to execute the select and fetch it into some variable(s)
If you're using a cursor then open and fetch commands do that.

You've got those commands, but they're inside the IF.
They need to be before the IF.

And the IF needs to check the variable(s) that the cursor is fetched into.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679433 is a reply to message #679429] Fri, 28 February 2020 05:58 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
--msg for cookiemonster

yeah yeah you are correct, for more than one row, i cannot insert/update inside the IF statement as it will throw unique constraint error which it does eventually.

--msg for John Watson
I got it. I tried your code, but no row is affecting when the input course matches with course in table. below is my code:

CREATE OR REPLACE NONEDITIONABLE PROCEDURE P1(COURSENAME IN COURSE.CNAME%TYPE,
                                              MIN_FEE    OUT NUMBER) IS
  MATCH_FEE NUMBER;

BEGIN

  SELECT T.FEE INTO MATCH_FEE FROM COURSE T WHERE T.CNAME = 'java';--13000
  SELECT MIN(T.FEE) INTO MIN_FEE FROM COURSE T;--1000

  MERGE INTO COURSE
  USING DUAL
  ON (CNAME = COURSENAME)
  WHEN MATCHED THEN
    UPDATE SET FEE = MATCH_FEE
  WHEN NOT MATCHED THEN
    INSERT VALUES (103, COURSENAME, 40, MIN_FEE);
  COMMIT;
exception when others then
  dbms_output.put_line(sqlerrm||' - '||sqlcode);
END;
Re: Select statement inside IF statement for comparing a given string with records in table [message #679434 is a reply to message #679433] Fri, 28 February 2020 06:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Works when I tried it.
How did you call it exactly.

Also - delete that exception handler. It's a great way to not notice an error happening.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679448 is a reply to message #679434] Sun, 01 March 2020 22:33 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
---cookiemaster & johnwatsaon

Yes its working. I am using 'plsql developer by allroundautomation' and basically we 'test' the procedure to run it using right-click and mouse. So, in its output it is showing '1000' in the 'min_fee' field which is an out parameter. It should not show it like that but anyway its showing. And i check the course query and data is reflecting well. I think it's this software way to work.

but anyway, a very BIG BIG thanks to you guys. You guys are so supportive. I am very new to PLSQL so im trying few codes on my own and im confused so i ask here.

one more thing: My question remains same that CAN WE USE 'SELECT' statement to fetch a value of column one or multiple time and the COMPARES it to some passed string or value which is NON-NUMERIC? (because i know comparing numbers or single value in IF statement is easy and less confusing). Also, tell if we can use CURSOR to get more values and compare the same inside IF statement? Please clear this confusion. And if there is any other way to solve this question which i asked in this thread other than using MERGE?

Thanks again guys.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679452 is a reply to message #679448] Mon, 02 March 2020 04:58 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
PL/SQL developer is behaving as expected (and behaving exactly the same as any other DB GUI tool would). You've set the out parameter to min(t.fee), which is 1000, so that's what it's going to show. If you want to show something else then you need to change the procedure code accordingly.

Re: Select statement inside IF statement for comparing a given string with records in table [message #679453 is a reply to message #679452] Mon, 02 March 2020 06:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Selects/cursors can absolutely return multiple rows - you would do that either with a loop (for loop normally) or with bulk collect (which gets all the rows in one hit and puts them in an array).

As for non-numeric - what datatype did you have in mind. SQL has lots and you most of them have no restrictions on comparisons - as long as the things you are comparing are of the same datatype.

I can think of multiple ways of implementing your requirement and most of them don't involve IF at all. And your example here really isn't a good one for demoing SQL loops - you only need to check one thing in the IF - does the course exist in the table. You do need to look up two other bits of data - min(fee) and 'java' fee - but the IF isn't dependant on them.

Here's one way, which is what Michel described earlier:
CREATE OR REPLACE NONEDITIONABLE PROCEDURE P1(COURSENAME IN COURSE.CNAME%TYPE,
                                              NEW_FEE    OUT NUMBER) IS
                                              
  l_exists VARCHAR2(1);

BEGIN

  --check if course exists
  SELECT NULL --null because we don't care about any of the column values
  INTO l_exists
  FROM course
  WHERE cname = coursename
  FOR UPDATE; --for update locks the row in case some-one else is trying to update it
  
  --get the java fee
  SELECT jc.FEE INTO new_fee
  FROM COURSE jc WHERE jc.CNAME = 'java'
  --This code only runs if the select finds a row (go to exception handler otherwise)
  UPDATE course c
  SET c.fee = new_fee
  WHERE c.cname = coursename;
  
EXCEPTION 
  WHEN no_data_found THEN
    --If the there isn't a row for the course no_data_found is thrown and we end up here  
    --Get the min fee
    SELECT MIN(T.FEE) INTO NEW_FEE FROM COURSE T;
    --insert new row
    INSERT INTO course (cid, cname, duration, fee)
    VALUES (<SOME SEQUENCE>.nextval, coursename, 40, new_fee);

END;
I've set that to return the value fee is set to in the out parameter in all cases, which is what I assume you want.
There's no IF, because the existence check throws no_data_found.

Also I've removed the commit because procedures like this really shouldn't have one. The client should do commit.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679456 is a reply to message #679453] Mon, 02 March 2020 22:42 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
thanks for explaining. but im more curious that i took this example because it has to do one thing that the course name is unique here and hence we will find only one row in this case.
What happens when we have duplicate courses and then we need to update among only one course from it? Then we need cursors for doing that? correct? and if we use cursor, how would we compare them using IF? or we avoid IF all the time?
Re: Select statement inside IF statement for comparing a given string with records in table [message #679458 is a reply to message #679456] Tue, 03 March 2020 01:18 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
What happens when we have duplicate courses and then we need to update among only one course from it?
If you have duplicates, then it does not matter which row you update. So, for example,
...
  UPDATE course c
  SET c.fee = new_fee
  WHERE c.cname = coursename and rownum = 1;
...
Re: Select statement inside IF statement for comparing a given string with records in table [message #679459 is a reply to message #679458] Tue, 03 March 2020 01:30 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
update is for first row fetched in cursor, what if the select returns, say 3 rows. how do you update it separately by comparing all 3 rows one by one?
Re: Select statement inside IF statement for comparing a given string with records in table [message #679460 is a reply to message #679459] Tue, 03 March 2020 01:35 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
sfksuperman wrote on Tue, 03 March 2020 07:30
update is for first row fetched in cursor, what if the select returns, say 3 rows. how do you update it separately by comparing all 3 rows one by one?
You said before that you wanted to update only one row, and I showed you how. Now you say you want to update all the rows. Make your mind up, man!

However, it is pretty obvious: if you do not append AND ROWNUM=1 then how many rows do you think will be updated? How about: all of them.



Re: Select statement inside IF statement for comparing a given string with records in table [message #679461 is a reply to message #679460] Tue, 03 March 2020 01:38 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
no, i was thinking what if we have more than 1 row in select query. It's not like im changing my question, its just i want to know if other conditions to learn more about how we deal with these situation in IF statement and all.
Re: Select statement inside IF statement for comparing a given string with records in table [message #679462 is a reply to message #679461] Tue, 03 March 2020 02:43 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you need some logic to determine which of the matching rows gets updated, which you so far haven't specified.
Generally you would apply that logic in the where clause of the select or the update so that you only interact with the row you're really interested in.

The point where you would use a loop is when you actually need to do something with all three and the logic of that is too complicated to actually put in the select/update.

First rule of DB coding - if there's rows you don't care about, don't fetch them.
Previous Topic: convert rows to column in SQL
Next Topic: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block (merged)
Goto Forum:
  


Current Time: Thu Mar 28 18:28:31 CDT 2024