Select statement inside IF statement for comparing a given string with records in table [message #679409] |
Fri, 28 February 2020 00:06 |
|
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 #679416 is a reply to message #679414] |
Fri, 28 February 2020 03:56 |
John Watson
Messages: 8937 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 #679419 is a reply to message #679418] |
Fri, 28 February 2020 04:23 |
cookiemonster
Messages: 13925 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 |
_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 #679426 is a reply to message #679425] |
Fri, 28 February 2020 04:55 |
|
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 #679429 is a reply to message #679426] |
Fri, 28 February 2020 05:06 |
cookiemonster
Messages: 13925 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 #679453 is a reply to message #679452] |
Mon, 02 March 2020 06:31 |
cookiemonster
Messages: 13925 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 #679460 is a reply to message #679459] |
Tue, 03 March 2020 01:35 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
sfksuperman wrote on Tue, 03 March 2020 07:30update 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 #679462 is a reply to message #679461] |
Tue, 03 March 2020 02:43 |
cookiemonster
Messages: 13925 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.
|
|
|