Issue when creating a stored procedure

Question

I'm trying to create a stored procedure as follows:

CREATE OR REPLACE PROCEDURE storedprocedure(emp number) AS
BEGIN  
  DECLARE
    -- create the cursor based on a query
    cursor emp_cursor is
      select e.employeeid, firstname, lastname, e.departmentid, e.title,
        salary, d.departmentname, billrate
      from employees e
      full join departments d on e.departmentID = d.departmentID
      full join employeeproject p on e.employeeID = p.employeeID where e.employeeID = emp;

    BEGIN
      open emp_cursor;
      -- first display information about the employee
      dbms_output.put_line('- -');
      dbms_output.put_line('- -');
      dbms_output.put_line('Employee#' || e.employeeid
        || '  Name:' || TRIM(e.firstname) || ' ' || TRIM(e.lastname)
        || ' Dept: ');
      dbms_output.put_line('_________________________________________________________');
      dbms_output.put_line('- -');
      dbms_output.put_line('- -    Title: ' || e.title
        || ' Salary: ' || to_char(e.salary,'$999,999,999.99'));
      dbms_output.put_line('- -    Billing Rate: ' || to_char(billrate,'$999,999.99'));
     -- next call the stored procedure to show department information
  END;
END;
/

But it compiles with errors. When I show errors it tells me e.employeeID, e.title, and billrate must all be declared, but they are the original query. What am I doing wrong here? Am I misunderstand what it means to have them declared?

These are all columns that exist within the tables being queried and running the query as SQL gets results.

Answer 1

You are opening the cursor, but you are not fetching it into anything - either a series of scalar variables or a record type - which would normally be done in a loop. Then when you are in the loop you refer to the variables/record, not the table used in the cursor query - that is out of scope outside the cursor declaration.

There's a slightly simpler implicit cursor loop that you might find a bit easier in this case:

CREATE OR REPLACE PROCEDURE storedprocedure(emp number) AS
BEGIN
  FOR rec IN (
    select e.employeeid, firstname, lastname, e.departmentid, e.title,
      salary, d.departmentname, billrate
    from employees e
    full join departments d on e.departmentID = d.departmentID
    full join employeeproject p on e.employeeID = p.employeeID where e.employeeID = emp
  )
  LOOP
    -- first display information about the employee
    dbms_output.put_line('- -');
    dbms_output.put_line('- -');
    dbms_output.put_line('Employee#' || rec.employeeid
      || '  Name:' || TRIM(rec.firstname) || ' ' || TRIM(rec.lastname)
      || ' Dept: ');
    dbms_output.put_line('_________________________________________________________');
    dbms_output.put_line('- -');
    dbms_output.put_line('- -    Title: ' || rec.title
      || ' Salary: ' || to_char(rec.salary,'$999,999,999.99'));
    dbms_output.put_line('- -    Billing Rate: ' || to_char(rec.billrate,'$999,999.99'));
    -- next call the stored procedure to show department information
  END LOOP;
END;
/

Notice that all the references to columns inside the loop are of the form rec.<field> - they are taken from the record for this time round the loop, not directly from the underlying tables.

You also had a nested block which isn't doing any harm but isn't useful, so I took that out.

---

If you particularly wanted to use the explicit open-fetch-close cursor handling it would look something like this:

CREATE OR REPLACE PROCEDURE storedprocedure(emp number) AS
  -- create the cursor based on a query
  cursor emp_cursor is
    select e.employeeid, firstname, lastname, e.departmentid, e.title,
      salary, d.departmentname, billrate
    from employees e
    full join departments d on e.departmentID = d.departmentID
    full join employeeproject p on e.employeeID = p.employeeID where e.employeeID = emp;

   -- record variable based on cursor definition
   rec emp_cursor%ROWTYPE;

BEGIN
  OPEN emp_cursor;
  LOOP
    -- fetch the next row result from the cursor into the record vairable
    FETCH emp_cursor INTO rec;
    -- break out of the loop if there are no more results to fetch
    EXIT WHEN emp_cursor%NOTFOUND;

    -- first display information about the employee
    dbms_output.put_line('- -');
    dbms_output.put_line('- -');
    dbms_output.put_line('Employee#' || rec.employeeid
      || '  Name:' || TRIM(rec.firstname) || ' ' || TRIM(rec.lastname)
      || ' Dept: ');
    dbms_output.put_line('_________________________________________________________');
    dbms_output.put_line('- -');
    dbms_output.put_line('- -    Title: ' || rec.title
      || ' Salary: ' || to_char(rec.salary,'$999,999,999.99'));
    dbms_output.put_line('- -    Billing Rate: ' || to_char(rec.billrate,'$999,999.99'));
    -- next call the stored procedure to show department information
  END LOOP;
  CLOSE emp_cursor;
END;
/