jeudi 27 février 2020

Select statement inside IF statement for comparing a given string with records in table

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