Creating a function using PL/SQL

Question

I have the following table:

create table movie(
    movie_id integer primary key,
    title varchar(500) not null,
    kind varchar(30),
    year integer not null
);

I want to create a function:

addMovie(title, kind, year)

The function must insert a new row into the movie table with a unique movie_id, and then return the movie_id.

This is the first time I'm using PL/SQL, so I'm kind of lost at the moment - couldn't find any (good) examples so far.

Thanks!

Answer 1

Your function needs to do 3 things

• Generate the unique movie id
• Insert into the table
• Return the generated id

Let's take it one step at a time

Generate the unique movie id

The best way to do it is to use a seqeuence which will generated a id for you. Look up on sequences

Insert into the table

This is done by a straightforward insert. Since the movie id is generated by the sequence, we use sequence_name.nextval in the insert statement. Thus the insert statement looks like

INSERT INTO movie(movie_id, title, kind, year) values (movie_id_seq.nextval, title, kind, year) 

Return the generated id back

You can make use of the Returning clause in a DML to return the generated id back into a variable. And then use the RETURN statement to return the value back.

So this is how your function will look like

FUNCTION addmovie(p_title,
                  p_kind,
                  p_year)
  RETURN NUMBER
IS
  v_id movie.id%TYPE;
BEGIN
  INSERT INTO movie
              (
                          movie_id,
                          title,
                          kind,
                          year
              )
              VALUES
              (
                          movie_id_seq.NEXTVAL,
                          p_title,
                          p_kind,
                          p_year
              )
  returning   id
  INTO        v_id;

  RETURN v_id;
END;

Note that this is a fairly basic function, with no error checking, exception handling - I'll leave it up to you.

---

Note that max(movie_id)+1 isn't the best way forward, for purposes of the assignment. You'll need

SELECT max(movie_id)+1 into v_id from movies;

before the insert statement.

Also, because of the DML, you can't use this function as part of a query.