how to use a cursor within a function in PL/SQL

Question

I have an assignment, and the course materials do not cover how to do this, and a web search has not been help regarding cursors within functions.

The assignment is to return all last names in the database, but change any according to the case statement parameters. And I am required to use a cursor. Here is my code:

create or replace function Convert_New_Name

        return sys_refcursor
    as
        c_results sys_refcursor;
    begin
        open c_results for
            select 
                  case last_name
                       when 'King' then 'LION'
                       when 'Ford' then 'CAR'
                       when 'Miller' then 'BEER'
                       else last_name
                   end as new_name
            from   employees;

        return c_results;
    end convert_new_name;

I'm having a hard time finding anything pertinent in searches. The database has 20 rows. This code returns all 20 rows, with required changes, but it returns those 20 row 20 times, instead of once.

There was another post on here with almost the same assignment, but when I tried to post on there, it got deleted, and I got told to ask my own question.

The function looks fine. Please post the code that calls the function. — Kirby
– Kirby, Commented Aug 6, 2017 at 19:41
Ah, now I understand. The function returns 20 rows and is being called twenty times: once for every row in the employees table.Please see my revised answer. — Kirby
– Kirby, Commented Aug 8, 2017 at 1:15

Kirby · Accepted Answer · 2017-08-08 01:14:27Z

1

The function code you posted is fine. The calling code should call function Convert_New_Name only once. If you plan to call it from a SQL statement, then this query should do the job:

Select Convert_New_Name() From dual;

edited Aug 8, 2017 at 1:14

answered Aug 6, 2017 at 20:29

Kirby

7244 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to use a cursor within a function in PL/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related