Oracle create precedure with cursor loop

Question

I'm creating a procedure in Oracle to search for some keywords specified in the parameter and print out the line. This is my code below. It works but I don't know why it keeps printing the last line twice. Please help

    CREATE TABLE Testimonial( TestimonialID integer PRIMARY KEY,
                Content char(100));

INSERT INTO Testimonial VALUES (100,'Great website');
INSERT INTO Testimonial VALUES (101,'I like it');
INSERT INTO Testimonial VALUES (102,'I bought two items from here and I really like them');
INSERT INTO Testimonial VALUES (103,'My girlfriends likes my presents I bought here');
INSERT INTO Testimonial VALUES (104,'Nice products');
INSERT INTO Testimonial VALUES (105,'Friendly customer service');

COMMIT;


Create or replace procedure Search_Testimonials (search_string IN char)
IS
        Testimonial_record   Testimonial%ROWTYPE;

 cursor cur_Testimonial is 
    select *
    from Testimonial
    WHERE content LIKE '%' || search_string || '%';
BEGIN
    open cur_Testimonial;
    Loop
        Fetch cur_Testimonial into Testimonial_record;
        DBMS_OUTPUT.PUT_LINE( 'Content: ' || Testimonial_record.content );
        EXIT WHEN cur_Testimonial%NOTFOUND;
    END LOOP;
    close cur_Testimonial;
    COMMIT;
END;
/

set serveroutput on

exec Search_Testimonials('bought')

OUTPUT

Content: I bought two items from here and I really like them
Content: My girlfriends likes my presents I bought here
Content: My girlfriends likes my presents I bought here

I guess that it needs to move past the last record before NOTFOUND is true. — Nick.Mc
– Nick.Mc, Commented Apr 8, 2013 at 3:27
No, the simple reason is that NOTFOUND was being checked AFTER the call to DBMS_OUTPUT. — Jeffrey Kemp
– Jeffrey Kemp, Commented Apr 8, 2013 at 5:20

Nick.Mc · Accepted Answer · 2013-04-08 03:28:32Z

2

This might work a bit better.

Loop
    Fetch cur_Testimonial into Testimonial_record;
    EXIT WHEN cur_Testimonial%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( 'Content: ' || Testimonial_record.content );
END LOOP;

answered Apr 8, 2013 at 3:28

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

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

2 Comments

Harry Over a year ago

Yeah, it works. Thanks a lot. Don't know why it makes different.

Nick.Mc Over a year ago

Because I guess it needs to move past the last record before it knows about NOTFOUND, and when it does, it must still contain the last record in .content

Collectives™ on Stack Overflow

Oracle create precedure with cursor loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related