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;
Aucun commentaire:
Enregistrer un commentaire