Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Unanswered: store a query from a select statement into a variable

    hi all

    PROCEDURES:

    i want to store a select statement into a variable which i want to call later.. how can this be done ???


    Varoable1 = select email from person where ....;

    insert into movie (email, imdbnr, moviename, duration, genre)
    values (Variable1, nimdbnr, nmoviename, nduration, ngenre);

    this doesnt work ... anyone has an idea ???

    thx

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: store a query from a select statement into a variable

    Originally posted by let99
    hi all

    PROCEDURES:

    i want to store a select statement into a variable which i want to call later.. how can this be done ???


    Varoable1 = select email from person where ....;

    insert into movie (email, imdbnr, moviename, duration, genre)
    values (Variable1, nimdbnr, nmoviename, nduration, ngenre);

    this doesnt work ... anyone has an idea ???

    thx
    What DBMS? If it was Oracle the answer would be:

    DECLARE
    v_email person.email%TYPE;
    BEGIN
    select email INTO v_email FROM person where ...;
    ...
    insert into movie (email, imdbnr, moviename, duration, genre)
    values (v_email, nimdbnr, nmoviename, nduration, ngenre);
    END;

    Mind you, what are nimdbnr etc.? They look like variables too!

  3. #3
    Join Date
    May 2003
    Posts
    87

    Re: store a query from a select statement into a variable

    declare
    lvar1 varchar2(50);
    begin
    select email into lvar1 from person where <condition>;
    insert into movie (email, imdbnr, moviename, duration, genre)
    values (lvar1, nimdbnr, nmoviename, nduration, ngenre);
    end;
    /

    Do not forget to handle exceptions like no_data_found or too_many_rows.

    Originally posted by let99
    hi all

    PROCEDURES:

    i want to store a select statement into a variable which i want to call later.. how can this be done ???


    Varoable1 = select email from person where ....;

    insert into movie (email, imdbnr, moviename, duration, genre)
    values (Variable1, nimdbnr, nmoviename, nduration, ngenre);

    this doesnt work ... anyone has an idea ???

    thx

  4. #4
    Join Date
    Jun 2003
    Posts
    5
    thank you thank you !!
    it was like this:

    PROCEDURE add (attr1, attr2, .....);
    v_email person.email%TYPE;
    BEGIN
    select person.email INTO v_email from person,member ....
    insert into movie (email, imdbnr, moviename, duration, genre)
    values (v_email, nimdbnr, nmoviename, nduration, ngenre);

    P.S. it was oracle & plsql

  5. #5
    Join Date
    May 2003
    Posts
    87
    Again, do not forget to handle the exception just in case your select statement fails i.e. returns no rows or more than 1 row.


    Originally posted by let99
    thank you thank you !!
    it was like this:

    PROCEDURE add (attr1, attr2, .....);
    v_email person.email%TYPE;
    BEGIN
    select person.email INTO v_email from person,member ....
    insert into movie (email, imdbnr, moviename, duration, genre)
    values (v_email, nimdbnr, nmoviename, nduration, ngenre);

    P.S. it was oracle & plsql

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •