SQL Multiple rows on output from a stored procedure without Cursors

Question

What I have to do is to print in output several rows from a stored procedure query without using cursors (if there is a way). My database is about a video rental store and what I need to do is show a list of similar movies based on the genre of the given movie. The procedure takes in input the movie name and what I have to print in output a list of movies with the same genre.

The table FILM is made like this:

Genre VARCHAR2(50);
Title VARCHAR2(50);
releaseDate DATE;
duration INT;

I already did it using cursors and it works, but I have to do it without PL/SQL, just using "pure sql".

I guess the right query to do this is the following:

SELECT title,genre FROM FILM WHERE genre = (
              SELECT genre FROM FILM WHERE title='Pulp Fiction');

This query is working if I run into the DB, but How can I have the same result using this query from a procedure? I might need to use INTO for the SELECT and then I can't take a list of movies

---

I noticed that it isn't clear at all and I do apologize for that. I've made a procedure for the Rent of a specific Movie. I first check if the movie requested is available. If it is not, I want to print (via DBMS_OUTPUT.PUT_LINE...) a list of movies of the same genre of the movie selected.

I made this using cursors, but now all I want to do is doing the same without cursors. I write down the code I made for doing this with the cursors:

-- DECLARATION
CURSOR CU IS
SELECT title,genre
FROM FILM;

-- LOOP ..
SELECT genre INTO genre_sugg
FROM FILM
WHERE upper(title) = upper(film); -- film is the input name of the movie

FOR row_sugg IN CU LOOP
  IF(row_sugg.genre = genre_sugg AND row_sugg <> film ) THEN
    DBMS_OUTPUT.PUT_LINE('Film: ' || row_sugg.title);
  END IF;
END LOOP;

Answer 1

I think it is what you want:

CREATE OR REPLACE PROCEDURE get_movie(p_movie_name VARCHAR2)
AS

BEGIN
   FOR movie IN (SELECT title,genre FROM FILM WHERE genre = (
                          SELECT genre FROM FILM WHERE title=p_movie_name))
      LOOP
         dbms_output.put_line(movie.title||' '||movie.genre);
      END LOOP;                          
END;

Answer 2

You have to use a loop, unless you aggregate all of the titles into a single string and just print that single value; but you can avoid an explicit or implicit cursor with a table type and bulk collect:

create or replace procedure genre_match(p_title in film.title%type) is
type film_tab is table of film%rowtype;
films film_tab;
begin
  select *
  bulk collect into films
  from film
  where genre = (select genre from film where upper(title) = upper(p_title))
  and upper(title) != upper(p_title);

  for i in 1..films.count loop
     dbms_output.put_line('Film: ' || films(i).title || ' ' || films(i).genre);
  end loop;
end;
/

This declares a type which is a collection of films, and a variable films which is an instance of that type. The bulk collect puts the entire results of the query into that collection. You can then iterate over the collection, instead of over a cursor.

With some simple made-up data:

create table film (genre varchar2(30), title varchar2(30));

insert into film values ('Crime fiction', 'Pulp Fiction');
insert into film values ('Crime fiction', 'Ocean''s 11');
insert into film values ('Crime fiction', 'The Italian Job');
insert into film values ('Science fiction', 'Alien');

The procedure shows:

set serveroutput on
exec genre_match('pulp fiction');

anonymous block completed
Film: Ocean's 11 Crime fiction
Film: The Italian Job Crime fiction

(Technically Oracle still uses a cursor of some sort under the hood to retrieve the rows into the collection, as it does for any query anywhere - in plain SQL as well; but that's unavoidable and rather beyond the scope of the question...)