1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PL\SQL Writing scripts

Discussion in 'SQL PL/SQL' started by jramosent, Dec 11, 2013.

  1. jramosent

    jramosent Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I need some help writing a script.

    Create a script that asks the user to enter an employee's ID number. The script then prints out the employee's first and last names.

    The output should look like this, with the values pertaining to the question above:

    DECLARE
    job_row jobs%ROWTYPE;
    BEGIN
    select *
    INTO job_row
    FRom jobs
    WHERE job_id = '&newjob_id';
    DBMS_OUTPUT.PUT_LINE(job_row.job_title);
    DBMS_OUTPUT.PUT_LINE(job_row.max_salary);
    END;
    /
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What have you written to try to solve this problem? It's homework, and we won't do your homework for you. We will, however, assist you once we see your code.

    Post what you have done to answer this problem, and someone will be happy to assist you.
     
  3. jramosent

    jramosent Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    this is currently what I have, with error code :
    Not enough values

    DECLARE
    emp_id employees.employee_id%TYPE;

    BEGIN
    select last_name, first_name
    into emp_id
    from employees
    where employee_id = &emp_id;
    END;
    /
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are trying to select first name and last_name into the variable you declared for the employee_id input and you don't need to make such a declaration. I have corrected your code to populate variables for first name and last name:

    Code (SQL):
    SQL> DECLARE
      2  v_fname employees.first_name%TYPE;
      3  v_lname employees.last_name%TYPE;
      4
      5  BEGIN
      6  SELECT last_name, first_name
      7  INTO v_fname, v_lname
      8  FROM employees
      9  WHERE employee_id = &emp_id;
     10  END;
     11  /
    Enter VALUE FOR emp_id: 144
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    I have not added any code to output the populated variables; that's for you to do.
     
  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    In addition...a good place to go for LOTS of exercises on PL/SQL is :

    http://www.plsqlchallenge.com/


    The site may be changing a bit next year but all the past puzzles should still be available.


    CJ