I'm a beginner to sql and i'm getting a compilation error, can anyone pls guide me with my mistake in the code.

set serveroutput on;

create or replace procedure p1(n in varchar) as
  cursor c1 is 
    select e_name, inv_amount
    from employee, investment
    where employee.e_id=investment.e_id
    and inv_amount=50000;
  c  c1 %rowtype;
begin
  open c1;
  dbms_output.put_line('e_name'||"||'inv_amount');
  loop
    fetch c1 into c;
    exit when c1 %notfound;
    if(c.inv_amount=n)then
      dbms_output.put_line(c.e_name||c.inv_amount);
    end if;
  end loop;
  close c1;
end;
/

CodePudding user response：

I can see some potential errors in your code. Which must be fixed as -

set serveroutput on;

create or replace procedure p1(n in varchar)
AS
cursor c1 is 
    select e_name,inv_amount
      from employee,investment
     where employee.e_id=investment.e_id
       and inv_amount=50000;

c c1%rowtype;    -- No space between cursor and ROWTYPE keyword

begin

  open c1;

  dbms_output.put_line('e_name'||''||'inv_amount');  -- It must be 2 single quotes instead of 1 double quotes

  loop

    fetch c1 into c;
    exit when c1%notfound;      -- Remove space
    if(c.inv_amount=n)then
      dbms_output.put_line(c.e_name||c.inv_amount);
    end if;

  end loop;

  close c1;

end;
/

CodePudding user response：

You can simplify this quite a lot:

1. You only care about rows where inv_amount = n, so why not add that as a filter condition in the cursor.
2. The Cursor FOR loop construction avoids the need to declare everything explicitly.

create or replace procedure p1
    ( n in number )
as
begin
    for r in (
        select e_name, inv_amount
        from   employee e
               join investment i on i.e_id = e.e_id
        where  inv_amount = n
    )
    loop
        dbms_output.put_line(r.e_name || ': ' || r.inv_amount);
    end loop;
end p1;

It's also best to write joins using the join keyword.

Note that SQL is the query language and PL/SQL is the programming language, so this a PL/SQL procedure.