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!

Wrong. @LalitKumarB can you expand on that please? Why is a function not meant for DML? — Drumbeg
– Drumbeg, Commented Mar 2, 2015 at 9:11
Ok, so why would you execute an operation to create data (addMovie) as part of a SQL statement? I don't think you would. Instead you would execute addMovie as a discrete call in PL/SQL and you may quite sensibly want to return the unique ID generated by this operation, in which case, use of a function is perfectly adequate. — Drumbeg
– Drumbeg, Commented Mar 2, 2015 at 9:38
You provided a solution, but you also provided a bit of nonsense to go with it. Your suggestion that addMovie may be used as part of a SQL statement and that this would therefore make a Function unsuitable is poor judgement. — Drumbeg
– Drumbeg, Commented Mar 2, 2015 at 9:49
Just my 2c: I agree with Lalit that this should be a procedure, not a function, but disagree with the rationale. addMovie changes the database state, so it should be a procedure (possibly with an OUT parameter). Functions with non-trivial side effects (e.g. inserting data) are (generally) poor programming practice. — Jeffrey Kemp
– Jeffrey Kemp, Commented Mar 4, 2015 at 3:58

Sathyajith Bhat · Accepted Answer · 2015-03-02 09:25:42Z

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.

edited Mar 2, 2015 at 9:25

answered Mar 2, 2015 at 8:56

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

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

2 Comments

anders Over a year ago

I'm aware that a sequence would probably be the best way to generate a unique id. However this is not possible as far as this assignment goes. The idea is to use something like max(movie.movie_id)+1.

Lalit Kumar B Over a year ago

Or an IDENTITY column if you are on 12c version. See my answer. Sequence and trigger was prior 12c approach.

Collectives™ on Stack Overflow

Creating a function using PL/SQL

1 Answer 1

Generate the unique movie id

Insert into the table

Return the generated id back

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Generate the unique movie id

Insert into the table

Return the generated id back

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related